PLSQL

PL/SQL

Procedure Language & Structured Query Language

特点

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

优点

1. 过程化和模块化；
2. 错误处理；
3. 可移植性；
4. 集成；
5. 改善性能。

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

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

2. 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”)

3. 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 statement
• FETCH statement
• RETURNING INTO clause of:
  • DELETE statement
  • INSERT statement
  • UPDATE statement
  • EXECUTE 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

1. FORALL语句的执行体必须是一个单独的DML语句，比如INSERT，UPDATE或DELETE。
2. 不要显式定义index_row，它被PL/SQL引擎隐式定义为PLS_INTEGER类型，并且它的作用域也仅仅是FORALL。
3. 这个DML语句必须与一个集合的元素相关，并且使用FORALL中的index_row来索引。注意不要因为index_row导致集合下标越界。
4. lower_bound和upper_bound之间是按照步进 1 来递增的。
5. 在sql_statement中，不能单独地引用集合中的元素，只能批量地使用集合。
6. 在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 = 2
• SQL%BULK_EXCEPTIONS(1).ERROR_INDEX = 10
• SQL%BULK_EXCEPTIONS(1).ERROR_CODE = 12899
• SQL%BULK_EXCEPTIONS(2).ERROR_INDEX = 64
• SQL%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))