Thursday, February 8, 2018
BULK COLLECT and BULK BIND in PLSQL
/*****************************************************************************
BULK COLLECT & FORALL (BULK BINDING)......
******************************************************************************/
SET SERVEROUTPUT ON;
create table bulk_table(no number);
create table bulk_bind(no number);
----------------------------
BEGIN
FOR i IN 1..1000000
LOOP
INSERT INTO bulk_table VALUES (i);
END LOOP;
COMMIT;
END;
/
select count(*) from bulk_table;
------------------------
DECLARE
TYPE rt IS TABLE OF bulk_table%rowtype;
vt rt;
BEGIN
SELECT * BULK COLLECT INTO vt FROM bulk_table;
FORALL i IN 1..vt.count
INSERT INTO bulk_bind VALUES vt(i);
COMMIT;
END;
/
----------------------------------
select count(*) from bulk_bind;
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment