Oracle FORALL_Example_2

wd6rd09660

-- Created on 2010/05/07 by NAN
declare
-- Local variables here
TYPE numtab IS TABLE OF NUMBER(20 ) INDEX BY BINARY_INTEGER ;
TYPE nametab IS TABLE OF VARCHAR2 (50) INDEX BY BINARY_INTEGER;
pnums  numtab;
pnames nametab;
t1     NUMBER;
t2     NUMBER;
t3     NUMBER;
ERRORS NUMBER;
begin
FOR j IN 1 .. 1000
LOOP
pnums(j) := j;
pnames(j) := 'Seq No. ' || to_char(j);
END LOOP ;
SELECT dbms_utility.get_time
INTO   t1
FROM   dual;
FOR i IN 1 .. 1000
LOOP
INSERT INTO blktest
VALUES
(pnums(i)
,pnames(i));
END LOOP ;
SELECT dbms_utility.get_time
INTO   t2
FROM   dual;
pnums.delete( 10);
pnums.delete( 15);
FORALL i IN 1 .. 1000 SAVE EXCEPTIONS
INSERT INTO blktest
VALUES
(pnums(i)
,pnames(i));
SELECT dbms_utility.get_time
INTO   t3
FROM   dual;
dbms_output.put_line( 'Execution Time (hsecs)');
dbms_output.put_line( '---------------------');
dbms_output.put_line( 'FOR loop: ' || to_char(t2 - t1));
dbms_output.put_line( 'FORALL:   ' || to_char(t3 - t2));
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ERRORS     := SQL%BULK_EXCEPTIONS.COUNT;
FOR i IN 1 .. SQL%BULK_EXCEPTIONS.count LOOP
dbms_output.put_line( SQL%BULK_EXCEPTIONS (i).ERROR_INDEX);
dbms_output.put_line( SQL%BULK_EXCEPTIONS (i).ERROR_CODE);
END LOOP ;
dbms_output.put_line( SQLERRM);
COMMIT;
end;