Sql代码 www.2cto.com
-- 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;