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;





No comments:

Post a Comment