Sql代码 www.2cto.com
CREATE TABLE blktest (num NUMBER( 20), name varchar2( 50));
CREATE OR REPLACE PROCEDURE bulktest IS
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;
BEGIN
FOR j IN 1 .. 1000000
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 .. 1000000
LOOP
INSERT INTO blktest
VALUES
(pnums(i)
,pnames(i));
END LOOP ;
SELECT dbms_utility.get_time
INTO t2
FROM dual;
FORALL i IN 1 .. 1000000
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));
END;
Execution Time (hsecs)
---------------------
FOR loop: 5747
FORALL: 90