Oracle handbook Series II: BULK SQL)

PL/SQL introduces the corresponding elements of procedural languages, such as conditional branches or loops. However, SQL itself is still nested as the subject, because the SQL engine is required to execute SQL commands, for PL/SQL programs, there are often many PL/SQL engine-SQL engine interactions, too many such interactions will have a negative impact on performance.

Oracle introduces bulk SQL in PL/SQL to minimize the interaction between PL/SQL-SQL engines, in order to improve performance. Specifically, Oracle bulk SQL includes the FORALL statement and BULK COLLECT clause. The former sends multiple statements (usually DML) to the SQL engine at a time, and the latter returns the results obtained by the SQL engine to the PL/SQL engine at a time.

 

(1) FORALL

The following two examples compare the difference between FORALL and FOR loops:

SQL> create table t_bulk as select * from employees;
SQL> desc t_bulk;
Name Type Nullable Default Comments
-------------------------------------------------
EMPLOYEE_ID NUMBER (6) Y
FIRST_NAME VARCHAR2 (20) Y
LAST_NAME VARCHAR2 (25)
EMAIL VARCHAR2 (25)
PHONE_NUMBER VARCHAR2 (20) Y
HIRE_DATE DATE
JOB_ID VARCHAR2 (10)
Salary number (8, 2) Y
COMMISSION_PCT NUMBER (2, 2) Y
MANAGER_ID NUMBER (6) Y
DEPARTMENT_ID NUMBER (4) Y

DECLARE
TYPE NumList is varray (20) of number;
Depts NumList: = NumList (10, 30, 70 );
BEGIN
FOR I IN depts. FIRST... depts. LAST LOOP
Delete from t_bulk
WHERE department_id = depts (I );
End loop;
END;

DECLARE
TYPE NumList is varray (20) of number;
Depts NumList: = NumList (10, 30, 70); -- department numbers
BEGIN
FORALL I IN depts. FIRST... depts. LAST
Delete from t_bulk
WHERE department_id = depts (I );
END;
 

Although there is a big difference between the two loops in terms of the internal execution mechanism, the syntax is still very similar. Note that the FORALL statement does not have an END statement.

 

Let's take a look at how to handle exceptions when FORALL is used:

 

Create table t_bulk2 (f1 NUMBER (3 ));
DECLARE
TYPE type1 is table of number;
V type1: = type1 (1, 2, 3000, 4, 5, 6, 77777, 8, 9, 10001 );
BEGIN
Execute immediate 'truncate TABLE t_bulk2 ';
--
FORALL idx IN v. FIRST .. v. LAST
Insert into t_bulk2 VALUES (v (idx ));
--
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SQLERRM );
END;
ORA-01438: value greater than the allowable precision specified for this column
PL/SQL procedure successfully completed
SQL> SELECT * FROM t_bulk2;
F1
----
1
2
 

Oracle 9i introduced the save exceptions syntax and the corresponding "ORA-24381: error (s) in array DML" exception. With them, we can skip the statements with EXCEPTIONS in FORALL, and save the exception in the SQL % BULK_EXCEPTIONSP collection:

 

DECLARE
TYPE type1 is table of number;
V type1: = type1 (1, 2, 3000, 4, 5, 6, 77777, 8, 9, 10001 );
--
BULK_ERROR EXCEPTION;
PRAGMA EXCEPTION_INIT (BULK_ERROR,-24381 );
BEGIN
Execute immediate 'truncate TABLE t_bulk2 ';
--
FORALL idx IN v. FIRST .. v. LAST SAVE EXCEPTIONS
Insert into t_bulk2 VALUES (v (idx ));
--
EXCEPTION
WHEN BULK_ERROR THEN
FOR I IN 1 .. SQL % BULK_EXCEPTIONS.COUNT LOOP
DBMS_OUTPUT.PUT_LINE (SQLERRM (-SQL % BULK_EXCEPTIONS (I). ERROR_CODE) | ', Statement: #' | SQL % BULK_EXCEPTIONS (I). ERROR_INDEX );
End loop;
WHEN OTHERS THEN
NULL;
END;
ORA-01438: The value is greater than the allowable precision specified for this column, Statement: #3
ORA-01438: The value is greater than the allowable precision specified for this column, Statement: #7
ORA-01438: The value is greater than the allowable precision specified for this column, Statement: #10
PL/SQL procedure successfully completed
SQL> SELECT * FROM t_bulk2;
F1
----
1
2
4
5
6
8
9
 

(Note that a negative number must be added when ERROR_CODE is used .)

The following describes how to obtain the number of rows affected by the first statement, which requires SQL % BULK_ROWCOUNT:

Create table t_bulk3 (fid number );
Insert into t_bulk3 values (1 );
Insert into t_bulk3 values (2 );
Insert into t_bulk3 values (2 );
Insert into t_bulk3 values (3 );
Insert into t_bulk3 values (3 );
Insert into t_bulk3 values (3 );
Insert into t_bulk3 values (3 );
DECLARE
TYPE type1 is table of number;
V type1: = type1 (1, 2, 3, 4 );
--
BULK_ERROR EXCEPTION;
PRAGMA EXCEPTION_INIT (BULK_ERROR,-24381 );
BEGIN
FORALL idx IN v. FIRST .. v. LAST SAVE EXCEPTIONS
Delete from t_bulk3 WHERE fid = v (idx );
--
FOR idx IN v. FIRST .. v. LAST LOOP
DBMS_OUTPUT.PUT_LINE ('Statement: # '| idx |', '| SQL % BULK_ROWCOUNT (idx) | 'rows were impacted .');
End loop;
--
EXCEPTION
WHEN BULK_ERROR THEN
FOR I IN 1 .. SQL % BULK_EXCEPTIONS.COUNT LOOP
DBMS_OUTPUT.PUT_LINE (SQLERRM (-SQL % BULK_EXCEPTIONS (I). ERROR_CODE) | ', Statement: #' | SQL % BULK_EXCEPTIONS (I). ERROR_INDEX );
End loop;
WHEN OTHERS THEN
NULL;
END;
Statement: #1, 1 rows were impacted.
Statement: #2, 2 rows were impacted.
Statement: #3, 4 rows were impacted.
Statement: #4, 0 rows were impacted.
PL/SQL procedure successfully completed
 

 

(2) BULK COLLECT

Assume that an SQL query contains five rows in the returned records. If this query is executed in PL/SQL, five PL/SQL-SQL interactions are performed. If BULK COLLECT is used, it can be reduced to 1 time.

The bulk collect clause can appear in the following statement:

SELECT

FETCH

RETURNING

 

Create table t_bulk4 (fid number, fval varchar2 (20 ));
Insert into t_bulk4 values (1, 'abc ');
Insert into t_bulk4 values (2, 'def ');
Insert into t_bulk4 values (3, 'xyz ');
Insert into t_bulk4 values (4, 'xxx ');
Insert into t_bulk4 values (5, '20140901 ');
Commit;
DECLARE
TYPE type1 is table of t_bulk4 % ROWTYPE;
V type1;
BEGIN
SELECT * bulk collect into v FROM t_bulk4;
--
FOR I IN 1. v. COUNT LOOP
DBMS_OUTPUT.PUT_LINE (v (I). fid | ''| v (I). fval );
End loop;
END;
1 abc
2 def
3 xyz
4 xxx
5 123
PL/SQL procedure successfully completed
 

Another example of the index by set (in fact, The FOR loop is the same as the example ):

DECLARE
TYPE type1 is table of t_bulk4 % rowtype index by PLS_INTEGER;
V type1;
Idx PLS_INTEGER;
BEGIN
SELECT * bulk collect into v FROM t_bulk4;
--
Idx: = v. FIRST;
WHILE (idx is not null) LOOP
DBMS_OUTPUT.PUT_LINE (v (idx). fid | ''| v (idx). fval );
Idx: = v. NEXT (idx );
End loop;
END;
 

The use of FETCH cursor bulk collect into is similar to that in the preceding example.

 

Here is a comprehensive example of returning into + bulk collect:

 

Create table t_bulk5 (fid number );
DECLARE
TYPE type1 is table of number;
V type1: = type1 (1, 2, 3, 5 );
--
TYPE type2 is table of t_bulk5.fid % TYPE;
V2 type2;
BEGIN
FORALL idx IN v. FIRST .. v. LAST
Insert into t_bulk5 VALUES (v (idx) RETURNING fid bulk collect into v2;
--
DBMS_OUTPUT.PUT_LINE (v2.COUNT );
END;
4
PL/SQL procedure successfully completed
 

By the way, compare the results when using the FOR Loop:

DECLARE
TYPE type1 is table of number;
V type1: = type1 (1, 2, 3, 5 );
--
TYPE type2 is table of t_bulk5.fid % TYPE;
V2 type2;
BEGIN
FOR idx IN v. FIRST .. v. LAST LOOP
Insert into t_bulk5 VALUES (v (idx) RETURNING fid bulk collect into v2;
End loop;
--
DBMS_OUTPUT.PUT_LINE (v2.COUNT );
END;
1
PL/SQL procedure successfully completed
 

This is actually understandable, because the preceding INSERT statement affects only one row at a time, so in the second example, the number of rows affected by the last INSERT operation in the loop is reserved, of course, 1; because the FORALL statement will submit all statements to the database at a time, we can use returning into + bulk collect to obtain all the inserted data.

 

 

 

Author Snowtoday MSN: MyYe110w@hotmail.com