oracle forall for,Oracle Forall

Oracle Forall

发布时间：2020-07-10 08:57:13

来源：51CTO

阅读：411

作者：断情漠

FORALL

Note:[from official document]

Example 12-7DELETE Statement in FOR LOOP StatementDROP TABLE employees_temp;

CREATE TABLE employees_temp AS SELECT *FROM employees;

DECLARE

TYPE NumList IS VARRAY(20) OF NUMBER;

depts NumList := NumList(10, 30, 70); -- department numbers

BEGIN

FORi IN depts.FIRST..depts.LAST LOOP

DELETE FROM employees_temp

WHERE department_id = depts(i);

ENDLOOP;

END;

/

Example 12-9Time Difference for INSERT Statement in FOR LOOP and FORALL StatementsDROP 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

FORj IN 1..iterations LOOP  -- populatecollections

pnums(j) := j;

pnames(j) := 'Part No. ' || TO_CHAR(j);

ENDLOOP;

t1:= DBMS_UTILITY.get_time;

FORi IN 1..iterations LOOP

INSERT INTO parts1 (pnum, pname)

VALUES (pnums(i), pnames(i));

ENDLOOP;

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;

/

Result is similar to:

Execution Time (secs)

---------------------

FOR LOOP: 2.16

FORALL:  .11

PL/SQL procedure successfully completed.

Example 12-10FORALL Statement for Subset of CollectionDROP 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;

/

Example 12-26DELETE with RETURN BULK COLLECT INTO in FORALL StatementDROP TABLE emp_temp;

CREATE TABLE emp_temp AS

SELECT * FROM employees

ORDER BY employee_id, department_id;

DECLARE

TYPE NumList IS TABLE OF NUMBER;

depts  NumList :=NumList(10,20,30);

TYPE enum_t IS TABLE OF employees.employee_id%TYPE;

e_ids  enum_t;

TYPE dept_t IS TABLE OF employees.department_id%TYPE;

d_ids  dept_t;

BEGIN

FORALL j IN depts.FIRST..depts.LAST

DELETE FROM emp_temp

WHERE department_id = depts(j)

RETURNING employee_id, department_id

BULK COLLECT INTO e_ids, d_ids;

DBMS_OUTPUT.PUT_LINE ('Deleted ' || SQL%ROWCOUNT || ' rows:');

FORi IN e_ids.FIRST .. e_ids.LAST

LOOP

DBMS_OUTPUT.PUT_LINE (

'Employee #' || e_ids(i) || ' from dept #' || d_ids(i)

);

ENDLOOP;

END;

/

Deleted 9 rows:

Employee #200 from dept #10

Employee #201 from dept #20

Employee #202 from dept #20

Employee #114 from dept #30

Employee #115 from dept #30

Employee #116 from dept #30

Employee #117 from dept #30

Employee #118 from dept #30

Employee #119 from dept #30