［Oracle］The sample of using BULK COLLECT and FORALL
http://docs.oracle.com/cd/B13789_01/appdev.101/b10807/12_tune.htm#i48876
--- 1. Copy partial data to backup table, truncate target table, and Copy it back.
CREATE TABLE MY_SYS_LOOKUP_CODES_BK
(
LOOKUP_TYPE VARCHAR2(60 BYTE) NOT NULL ,
LOOKUP_CODE NUMBER(5,0) NOT NULL ,
LOOKUP_VALUE VARCHAR2(240 BYTE),
DESCRIPTION VARCHAR2(240 BYTE),
CREATED_BY VARCHAR2(10 BYTE),
CREATION_DATE DATE,
LAST_UPDATED_BY VARCHAR2(10 BYTE),
LAST_UPDATE_DATE DATE
);
DECLARE
STR LONG;
TYPE T_TABLE IS TABLE OF MY_SYS_LOOKUP_CODES%ROWTYPE ;
V_TABLE T_TABLE;
BEGIN
STR:='TRUNCATE TABLE MY_SYS_LOOKUP_CODES_BK';
EXECUTE IMMEDIATE STR;
SELECT * BULK COLLECT INTO V_TABLE FROM MY_SYS_LOOKUP_CODES ; -- Add where condition
FORALL I IN 1..V_TABLE.COUNT
INSERT INTO MY_SYS_LOOKUP_CODES_BK VALUES (V_TABLE(I)."LOOKUP_TYPE",
V_TABLE(I)."LOOKUP_CODE" ,
V_TABLE(I)."LOOKUP_VALUE" ,
V_TABLE(I)."DESCRIPTION" ,
V_TABLE(I)."CREATED_BY" ,
V_TABLE(I)."CREATION_DATE" ,
V_TABLE(I)."LAST_UPDATED_BY" ,
V_TABLE(I)."LAST_UPDATE_DATE"
);
STR:='TRUNCATE TABLE MY_SYS_LOOKUP_CODES';
EXECUTE IMMEDIATE STR;
SELECT * BULK COLLECT INTO V_TABLE FROM MY_SYS_LOOKUP_CODES_BK ;
FORALL I IN 1..V_TABLE.COUNT
INSERT INTO MY_SYS_LOOKUP_CODES VALUES (V_TABLE(I)."LOOKUP_TYPE",
V_TABLE(I)."LOOKUP_CODE" ,
V_TABLE(I)."LOOKUP_VALUE" ,
V_TABLE(I)."DESCRIPTION" ,
V_TABLE(I)."CREATED_BY" ,
V_TABLE(I)."CREATION_DATE" ,
V_TABLE(I)."LAST_UPDATED_BY" ,
V_TABLE(I)."LAST_UPDATE_DATE"
);
COMMIT;
end;
