FORALL与BULK COLLECT 综合运用

–FORALL与BULK COLLECT 综合运用
DROP TABLE tb_emp;

CREATE TABLE tb_emp AS -->创建表tb_emp
SELECT empno, ename, hiredate
FROM emp
WHERE 1 = 2;

DECLARE
CURSOR emp_cur IS -->声明游标
SELECT empno, ename, hiredate FROM emp;

TYPE nested_emp_type IS TABLE OF emp_cur%ROWTYPE; -->基于游标的嵌套表类型

emp_tab nested_emp_type; -->声明嵌套变量
BEGIN
SELECT empno, ename, hiredate
BULK COLLECT INTO emp_tab -->BULK COLLECT批量提取数据
FROM emp
WHERE sal > 1000;

FORALL i IN 1 … emp_tab.COUNT -->使用FORALL语句将变量中的数据插入到表tb_emp
INSERT INTO (SELECT empno, ename, hiredate FROM tb_emp)
VALUES emp_tab( i );

COMMIT;
DBMS_OUTPUT.put_line( ‘The total ’ || emp_tab.COUNT || ’ rows has been inserted to tb_emp’ );
END;