OracleVillage

Bulk Binding using simple select statement

DECLARE
--Create a table type
TYPE emp_type IS TABLE OF emp%ROWTYPE
INDEX BY PLS_INTEGER;
--create a variable of Table Type
v_emp_type emp_type;
BEGIN
SELECT *
BULK COLLECT INTO v_emp_type
FROM emp;
-- Print data in the table using loop
FOR i IN v_emp_type.FIRST .. v_emp_type.LAST
LOOP
DBMS_OUTPUT.put_line (v_emp_type (i).ename);
END LOOP;
FORALL i IN v_emp_type.FIRST .. v_emp_type.LAST
INSERT INTO emp2
VALUES v_emp_type (i);
END;

Bulk Binding using cursor

DECLARE
CURSOR emp_cur IS SELECT * FROM EMP;
--Declare a type
TYPE emp_tab_t IS TABLE OF emp%ROWTYPE INDEX BY BINARY_INTEGER;
--Create a variable of type emp_tab_t
emp_tab emp_tab_t; -- In-memory table
rows NATURAL := 10000; -- Number of rows to process at a time (optional)
i BINARY_INTEGER := 0;
BEGIN
OPEN emp_cur;
LOOP
-- Bulk collect data into memory table - X rows at a time
FETCH emp_cur BULK COLLECT INTO emp_tab LIMIT rows;
EXIT WHEN emp_tab.COUNT = 0;
DBMS_OUTPUT.PUT_LINE( TO_CHAR(emp_tab.COUNT) ' rows bulk fetched.');
FOR i IN emp_tab.FIRST .. emp_tab.LAST loop
-- Manipumate data in the memory table...
dbms_output.put_line('i = 'i', EmpName='emp_tab(i).ename);
END LOOP;
-- Bulk bind of data in memory table...
FORALL i in emp_tab.FIRST..emp_tab.LAST
INSERT /*+APPEND*/ INTO emp2 VALUES emp_tab(i);
END LOOP;
CLOSE emp_cur;
END;

Bulk Delete

DECLARE
TYPE emp_tab_type IS TABLE OF ROWID
INDEX BY BINARY_INTEGER;
v_emp emp_tab_type;
CURSOR bulk_delete
IS
SELECT ROWID
FROM emp2;
BEGIN
OPEN bulk_delete;
LOOP
FETCH bulk_delete
BULK COLLECT INTO v_emp LIMIT 1000;
FORALL i IN 1 .. v_emp.COUNT
DELETE FROM emp2
WHERE ROWID = v_emp (i);
COMMIT;
EXIT WHEN bulk_delete%NOTFOUND;
END LOOP;
CLOSE bulk_delete;
END;