PLSQL

PL/SQL

Procedure Language & Structured Query Language

特点

对SQL语言扩展。灵活性与过程化语言的过程性融为一体，具有过成化语言的控制结构；不是一个独立产品，嵌入在Oracle服务器和开发工具中。

优点

过程化和模块化；错误处理；可移植性；集成；改善性能。 Using 关键字 FETCH CURSOR c1 ISSELECT a,b,...,z from tabFETCH c1 into c2;

把第1个取到的record放到c2中(c2也有可能是查询字段如：a,b…)

变量命名规范

%TYPE

(1) 标识符表名.列名%TYPE

(2) 标识符之前定义的变量%TYPE

& 引用替代变量

Grammer FETCH Output fnd_file.put_line(fnd_file.LOG,‘打印到日志’||value)； dbms_output.put_line(‘打印到控制台’)； Optimization and Tuning

How the PL/SQL compiler optimizes your code and how to write efficient PL/SQL code and improve existing PL/SQL code.

1.PL/SQL Optimizer

PL/SQL uses an optimizer that can rearrange code for better performance.(11gR2 after)

PL/SQL uses an optimizer that can rearrange code for better performance.Setting PLSQL_OPTIMIZE_LEVEL=1 prevents the code from being rearranged. Subprogram Inlining

Subprogram inlining replaces a subprogram invocation with a copy of the invoked subprogram.

PLSQL_OPTIMIZE_LEVEL compilation parameter (which is 2) or set it to 3.With PLSQL_OPTIMIZE_LEVEL=3, the PL/SQL compiler seeks opportunities to inline subprograms. You need not specify subprograms to be inlined. If subprogram inlining slows the performance of a particular PL/SQL program, then use the PL/SQL hierarchical profiler to identify subprograms for which you want to turn off inlining. To turn off inlining for a subprogram, use the INLINE pragma:

PRAGMA INLINE (subprogram, 'NO') 2.Candidates for Tuning* Change older PL/SQL code

Ⅰ.The new PL/SQL language features.

Older code that does not take advantage of new PL/SQL language features.

Ⅱ.Older dynamic SQL statements written with the DBMS_SQL package.

If you know at compile time the number and data types of the input and output variables of a dynamic SQL statement, then you can rewrite the statement in native dynamic SQL, which runs noticeably faster than equivalent code that uses the DBMS_SQL package (especially when it can be optimized by the compiler). For more information, see PL/SQL Dynamic SQL.

The terrible code that spends much time

-> Ⅰ.processing SQL statements.

-> Ⅱ.Functions invoked in queries which might run millions of times.

-> Ⅲ.that spends much time looping through query results.

-> Ⅳ.that does many numeric computations.

-> Ⅴ.The statement opposed to issuing database definition language (DDL) statements that PL/SQL passes directly to SQL.

3.Minimizing CPU Overhead

Tune SQL Statements

Tune Function Invocations in Queries

Tune Subprogram Invocations

Tune Loops

Tune Computation-Intensive PL/SQL Code

Use SQL Character Functions

Put Least Expensive Conditional Tests First

*Tune SQL Statements Use appropriate indexes.Use query hints to avoid unnecessary full-table scans.Collect current statistics on all tables, using the subprograms in the DBMS_STATS package.Analyze the execution plans and performance of the SQL statements, using: EXPLAIN PLAN statementSQL Trace facility with TKPROF utility Use bulk SQL, a set of PL/SQL features that minimizes the performance overhead of the communication between PL/SQL and SQL. Tune Function Invocations in Queries

Do not invoke a function in a query unnecessarily, and make the invocation as efficient as possible. Create a function-based index on the table in the query.

Tune Subprogram Invocations

If a subprogram has OUT or IN OUT parameters, you can sometimes decrease its invocation overhead by declaring those parameters with the NOCOPY hint.

If your program does not require that an OUT or IN OUT parameter, then include the NOCOPY hint in the parameter declaration that the compiler pass the corresponding actual parameter by reference instead of value.the invocation of do_nothing2 is faster than the invocation of do_nothing1.

PROCEDURE do_nothing1 (tab IN OUT EmpTabTyp) ISBEGIN NULL;END;PROCEDURE do_nothing2 (tab IN OUT NOCOPY EmpTabTyp) ISBEGIN NULL;END; Tune Loops

If you must loop through a result set more than once - cursor

Tune Computation-Intensive(计算密集型) PL/SQL Code

Use Data Types that Use Hardware Arithmetic

You can speed up computation-intensive code by passing variables of these data types to such functions, and by invoking the conversion functions TO_BINARY_FLOAT

Avoid Constrained Subtypes in Performance-Critical Code

The value to be assigned does not violate the constraint

Minimize Implicit Data Type Conversion

Tip:Declare the variable with the %TYPE attribute, described in “%TYPE Attribute”.

Use SQL Character Functions

Use low-level code that is more efficient than PL/SQL code.

CONCAT/INITCAP/LOWER/LPAD/LTRIM/NCHR/NLS_INITCAP/NLS_LOWER/NLS_UPPER/NLSSORT/REGEXP_REPLACE/REGEXP_SUBSTR/REPLACE/RPAD/RTRIM/SOUNDEX/SUBSTR/TRANSLATE/TRANSLATE … USING/TRIM/UPPER Put Least Expensive Conditional Tests First

The FORALL statement sends DML statements from PL/SQL to SQL in batches rather than one at a time；

In a FORALL statement without the SAVE EXCEPTIONS clause；

You can handle exceptions raised in a FORALL statement in either of these ways:

As each exception is raised (see “Handling FORALL Exceptions Immediately”)After the FORALL statement completes execution, by including the SAVE EXCEPTIONS clause (see “Handling FORALL Exceptions After FORALL Statement Completes”)

If one DML statement raises a handled exception, then PL/SQL rolls back the changes made by that statement, but does not roll back changes made by previous DML statements.

…略す

4. Bulk SQL and Bulk Binding

Bulk SQL minimizes the performance overhead of the communication between PL/SQL and SQL. The PL/SQL features that comprise bulk SQL are the FORALL statement(声明) and the BULK COLLECT clause(语句). The FORALL statement sends DML statements from PL/SQL to SQL in batches rather than one at a time.

BULK COLLECT

The BULK COLLECT clause can appear in:

SELECT INTO statementFETCH statementRETURNING INTO clause of: DELETE statementINSERT statementUPDATE statementEXECUTE IMMEDIATE statement DECLARETYPE SalList IS TABLE OF emp.sal%TYPE;sals SalList;BEGIN-- Limit the number of rows to 100.SELECT sal BULK COLLECT INTO sals FROM empWHERE ROWNUM <= 100;-- Retrieve 10% (approximately) of the rows in the table.SELECT sal BULK COLLECT INTO sals FROM emp SAMPLE 10; END; -- 在fetch into中使用bulk collect DECLARETYPE DeptRecTab IS TABLE OF dept%ROWTYPE;dept_recs DeptRecTab;CURSOR c1 ISSELECT deptno, dname, loc FROM dept WHERE deptno > 10;BEGINOPEN c1;FETCH c1 BULK COLLECT INTO dept_recs;END; -- 在returning into中使用bulk collect CREATE TABLE emp2 AS SELECT * FROM employees;DECLARETYPE NumList IS TABLE OF employees.employee_id%TYPE;enums NumList;TYPE NameList IS TABLE OF employees.last_name%TYPE;names NameList;BEGINDELETE FROM emp2 WHERE department_id = 30RETURNING employee_id, last_name BULK COLLECT INTO enums, names;dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows:');FOR i IN enums.FIRST .. enums.LASTLOOPdbms_output.put_line('Employee #' || enums(i) || ': ' || names(i));END LOOP;END;

With the BULK COLLECT clause, each of the preceding(先前的) statements retrieves an entire result set and stores it in one or more collection variables in a single operation (which is more efficient than using a loop statement to retrieve one result row at a time).

Binding CategoryWhen This Binding OccursIn-bindWhen an INSERT, UPDATE, or MERGE statement stores a PL/SQL or host variable in the databaseOut-bindWhen the RETURNING INTO clause of an INSERT, UPDATE, or DELETE statement assigns a database value to a PL/SQL or host variableDEFINEWhen a SELECT or FETCH statement assigns a database value to a PL/SQL or host variable FORALL Statement

If maybe DML of the run millions of times

Rules

FORALL语句的执行体必须是一个单独的DML语句，比如INSERT，UPDATE或DELETE。不要显式定义index_row，它被PL/SQL引擎隐式定义为PLS_INTEGER类型，并且它的作用域也仅仅是FORALL。这个DML语句必须与一个集合的元素相关，并且使用FORALL中的index_row来索引。注意不要因为index_row导致集合下标越界。lower_bound和upper_bound之间是按照步进 1 来递增的。在sql_statement中，不能单独地引用集合中的元素，只能批量地使用集合。在sql_statement中使用的集合，下标不能使用表达式。

inserts the same collection elements into two database tables, using a FOR LOOP statement for the first table and a FORALL statement for the second table and showing how long each statement takes. (Times vary from run to run.)

DROP TABLE parts1;CREATE TABLE parts1 ( pnum INTEGER, pname VARCHAR2(15)); DROP TABLE parts2;CREATE TABLE parts2 ( pnum INTEGER, pname VARCHAR2(15));DECLARE TYPE NumTab IS TABLE OF parts1.pnum%TYPE INDEX BY PLS_INTEGER; TYPE NameTab IS TABLE OF parts1.pname%TYPE INDEX BY PLS_INTEGER; pnums NumTab; pnames NameTab; iterations CONSTANT PLS_INTEGER := 50000; t1 INTEGER; t2 INTEGER; t3 INTEGER;BEGIN FOR j IN 1..iterations LOOP -- populate collections pnums(j) := j; pnames(j) := 'Part No. ' || TO_CHAR(j); END LOOP; t1 := DBMS_UTILITY.get_time; FOR i IN 1..iterations LOOP INSERT INTO parts1 (pnum, pname) VALUES (pnums(i), pnames(i)); END LOOP; t2 := DBMS_UTILITY.get_time; FORALL i IN 1..iterations INSERT INTO parts2 (pnum, pname) VALUES (pnums(i), pnames(i)); t3 := DBMS_UTILITY.get_time; DBMS_OUTPUT.PUT_LINE('Execution Time (secs)'); DBMS_OUTPUT.PUT_LINE('---------------------'); DBMS_OUTPUT.PUT_LINE('FOR LOOP: ' || TO_CHAR((t2 - t1)/100)); DBMS_OUTPUT.PUT_LINE('FORALL: ' || TO_CHAR((t3 - t2)/100)); COMMIT;END;

Subset of Collection

DROP TABLE employees_temp;CREATE TABLE employees_temp AS SELECT * FROM employees;DECLARE TYPE NumList IS VARRAY(10) OF NUMBER; depts NumList := NumList(5,10,20,30,50,55,57,60,70,75);BEGIN FORALL j IN 4..7 DELETE FROM employees_temp WHERE department_id = depts(j);END;

Ⅰ.INDICES OF

clause to populate a table with the elements of a sparse collection，以避免丢失记录异常和创建多余副本。

Ⅱ.SAVE EXCEPTION

Unhandled Exceptions

In a FORALL statement without the SAVE EXCEPTIONS clause, if one DML statement raises an unhandled exception, then PL/SQL stops the FORALL statement and rolls back all changes made by previous DML statements.

DELETE FROM employees_temp WHERE department_id = depts(10);DELETE FROM employees_temp WHERE department_id = depts(30);DELETE FROM employees_temp WHERE department_id = depts(70);

If the third statement raises an unhandled exception, then PL/SQL rolls back the changes that the first and second statements made. If the second statement raises an unhandled exception, then PL/SQL rolls back the changes that the first statement made and never runs the third statement.

Handling FORALL Exceptions

To allow a FORALL statement to continue even if some of its DML statements fail, include the SAVE EXCEPTIONS clause. When a DML statement fails, PL/SQL does not raise an exception; instead, it saves information about the failure. After the FORALL statement completes, PL/SQL raises a single exception for the FORALL statement (ORA-24381).

create or replace procedure P_EXCH(DT_IN DATE) is TYPE TAB_EXCH IS TABLE OF T_EXCH_ERR%ROWTYPE; L_TAB_EXCH TAB_EXCH; ERR EXCEPTION; PRAGMA EXCEPTION_INIT(ERR, -24381 ); ERRMSG VARCHAR2(4000); bad_id PLS_INTEGER;begin --将增量数据批量提取到本地集合变量 SELECT GUID,NAME,STIME,ETIME,CONTENT,STATUS,CREATEDATE,MOBILE,NULL BULK COLLECT INTO L_TAB_EXCH FROM T_EXCH@DB2 WHERE CREATEDATE>=DT_IN; --执行forall批量写入 FORALL I IN L_TAB_EXCH.FIRST .. L_TAB_EXCH.LAST SAVE EXCEPTIONS INSERT INTO T_RECV (GUID,NAME,STIME,ETIME,CONTENT,STATUS,CREATEDATE,MOBILE) VALUES(L_TAB_EXCH(I).GUID,L_TAB_EXCH(I).NAME,TO_DATE(L_TAB_EXCH(I).STIME,'YYYY-MM-DD HH24:MI:SS') ,TO_DATE(L_TAB_EXCH(I).ETIME,'YYYYMMDDHH24MISS'),L_TAB_EXCH(I).CONTENT ,L_TAB_EXCH(I).STATUS,L_TAB_EXCH(I).CREATEDATE,LTRIM(L_TAB_EXCH(I).MOBILE,'0')); COMMIT;EXCEPTION WHEN ERR THEN-- 处理24381错误 FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP ERRMSG := SQLERRM(-(SQL%BULK_EXCEPTIONS(i).ERROR_CODE)); bad_id:= SQL%BULK_EXCEPTIONS(i).ERROR_INDEX; -- 记录错误数据 L_TAB_EXCH(BAD_ID).ERRMSG:=ERRMSG; INSERT INTO T_EXCH_ERR VALUES L_TAB_EXCH(bad_id); END LOOP; COMMIT; WHEN OTHERS THEN ERRMSG:=SQLERRM; DBMS_OUTPUT.PUT_LINE(ERRMSG);end P_EXCH;

SQL%BULK_EXCEPTIONS is like an associative array of information about the DML statements that failed during the most recently run FORALL statement.

SQL%BULK_EXCEPTIONS.COUNT is the number of DML statements that failed. If SQL%BULK_EXCEPTIONS.COUNT is not zero, then for each index value i from 1 through SQL%BULK_EXCEPTIONS.COUNT:

SQL%BULK_EXCEPTIONS(i).ERROR_INDEX is the number of the DML statement that failed.SQL%BULK_EXCEPTIONS(i).ERROR_CODE is the Oracle Database error code for the failure.

For Example, if a FORALL SAVE EXCEPTIONS statement runs 100 DML statements, and the tenth and sixty-fourth ones fail with error codes ORA-12899 and ORA-19278, respectively, then:

SQL%BULK_EXCEPTIONS.COUNT = 2SQL%BULK_EXCEPTIONS(1).ERROR_INDEX = 10SQL%BULK_EXCEPTIONS(1).ERROR_CODE = 12899SQL%BULK_EXCEPTIONS(2).ERROR_INDEX = 64SQL%BULK_EXCEPTIONS(2).ERROR_CODE = 19278

With the error code, you can get the associated error message with the SQLERRM function.

SQLERRM(-(SQL%BULK_EXCEPTIONS(i).ERROR_CODE))

PLSQL

科技改变生活-雨落星辰 - 所有的伟大,都源于一个勇敢的开始

PLSQL

PLSQL

与本文相关的文章

评论列表(0)