PL/SQL
Procedure Language & Structured Query Language
特点
- 对SQL语言扩展。灵活性与过程化语言的过程性融为一体,具有过成化语言的控制结构;
- 不是一个独立产品,嵌入在Oracle服务器和开发工具中。
优点
- 过程化和模块化;
- 错误处理;
- 可移植性;
- 集成;
- 改善性能。
Using
关键字
FETCH
CURSOR c1
IS
SELECT a,b,...,z from tab
FETCH 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
statement- SQL 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) IS
BEGIN
NULL;
END;
PROCEDURE do_nothing2 (tab IN OUT NOCOPY EmpTabTyp) IS
BEGIN
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 theSAVE
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 theSAVE
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
DECLARE
TYPE SalList IS TABLE OF emp.sal%TYPE;
sals SalList;
BEGIN
-- Limit the number of rows to 100.
SELECT sal BULK COLLECT INTO sals FROM emp
WHERE 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
DECLARE
TYPE DeptRecTab IS TABLE OF dept%ROWTYPE;
dept_recs DeptRecTab;
CURSOR c1 IS
SELECT deptno, dname, loc FROM dept WHERE deptno > 10;
BEGIN
OPEN c1;
FETCH c1 BULK COLLECT INTO dept_recs;
END;
-- 在returning into中使用bulk collect
CREATE TABLE emp2 AS SELECT * FROM employees;
DECLARE
TYPE NumList IS TABLE OF employees.employee_id%TYPE;
enums NumList;
TYPE NameList IS TABLE OF employees.last_name%TYPE;
names NameList;
BEGIN
DELETE FROM emp2 WHERE department_id = 30
RETURNING employee_id, last_name BULK COLLECT INTO enums, names;
dbms_output.put_line('Deleted ' || SQL%ROWCOUNT || ' rows:');
FOR i IN enums.FIRST .. enums.LAST
LOOP
dbms_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 Category | When This Binding Occurs |
---|---|
In-bind | When an INSERT , UPDATE , or MERGE statement stores a PL/SQL or host variable in the database |
Out-bind | When the RETURNING INTO clause of an INSERT , UPDATE , or DELETE statement assigns a database value to a PL/SQL or host variable |
DEFINE | When 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
发布评论