FETCH .. BULK COLLECT INTO
CREATE OR REPLACE PROCEDURE ORA.SP_TARGET_CUST (
i_GCD IN ORA.TBL_TARGET_CUST.GCD%TYPE
i_USE_VER IN ORA.TBL_TARGET_CUST.USE_VER%TYPE,
i_REG_ID IN ORA.TBL_TARGET_CUST.REG_ID%TYPE
)
IS
c_limit PLS_INTEGER := 10000;
CURSOR cur
IS
SELECT GCD, IMEI
FROM ORA.TBL_TARGET_CUST
WHERE GCD = i_GCD;
TYPE t_CUST IS TABLE OF cur%ROWTYPE INDEX BY BINARY_INTEGER;
l_CUST t_CUST;
BEGIN
OPEN cur;
LOOP
FETCH cur BULK COLLECT INTO l_CUST LIMIT c_limit;
FORALL i IN l_CUST.FIRST..l_CUST.LAST
INSERT INTO ORA.TBL_TARGET_CUST( GCD
, USE_VER
, IMEI
, REG_DT
, REG_ID )
VALUES( l_CUST(i).GCD
, i_USE_VER
, l_CUST(i).IMEI
, SYSDATE
, i_REG_ID );
COMMIT;
EXIT WHEN cur%NOTFOUND;
END LOOP;
CLOSE cur_CUST;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/