Oracle FORALL_Example_2

最新推荐文章于 2024-07-17 08:15:00 发布

原创最新推荐文章于 2024-07-17 08:15:00 发布 · 99 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#Oracle

Oracle 专栏收录该内容

84 篇文章

订阅专栏

本文对比了使用FOR循环和FORALL语句在数据库中执行批量插入操作的时间性能，通过创建局部变量、填充数据、执行插入操作，并记录执行时间来评估两种方法的效率。


-- 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;