ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Fetch limit and %NOTFOUND attribute

Fetch limit and %NOTFOUND attribute

原创 Linux操作系统 作者:dragondb 时间:2019-07-08 11:57:05 0 删除 编辑

SQL> create table t_test as select * from user_objects where rownum < 1;

Table created

SQL> SET serverout on;
SQL>
SQL> CREATE OR REPLACE PROCEDURE p_limit_test(p_array_size IN NUMBER DEFAULT 20) IS
  2 
  3  TYPE t_array IS TABLE OF T_TEST%ROWTYPE;
  4  CURSOR cur IS
  5   SELECT *
  6     FROM user_objects
  7    WHERE rownum < 11;
  8 
  9  lv_data       t_array;
 10  BEGIN
 11    OPEN CUR;
 12 
 13    LOOP
 14      FETCH CUR BULK COLLECT INTO lv_data LIMIT p_array_size;
 15      EXIT WHEN CUR%NOTFOUND;
 16 
 17      FORALL i IN 1..lv_data.count
 18      INSERT INTO T_TEST VALUES lv_data(i);
 19 
 20    END LOOP;
 21    COMMIT;
 22    CLOSE CUR;
 23 
 24  EXCEPTION
 25    WHEN OTHERS THEN
 26     DBMS_OUTPUT.PUT_LINE(SQLERRM);
 27  END p_limit_test;
 28  /

Procedure created

SQL> exec p_limit_test;

PL/SQL procedure successfully completed

SQL> select count(*) from t_test;

  COUNT(*)
----------
         0

SQL> exec p_limit_test(5);

PL/SQL procedure successfully completed

SQL> select count(*) from t_test;

  COUNT(*)
----------
        10

SQL>
SQL> CREATE OR REPLACE PROCEDURE p_limit_test(p_array_size IN NUMBER DEFAULT 20) IS
  2 
  3  TYPE t_array IS TABLE OF T_TEST%ROWTYPE;
  4  CURSOR cur IS
  5   SELECT *
  6     FROM user_objects
  7    WHERE rownum < 11;
  8 
  9  lv_data       t_array;
 10  BEGIN
 11    OPEN CUR;
 12 
 13    LOOP
 14      FETCH CUR BULK COLLECT INTO lv_data LIMIT p_array_size;
 15 
 16      FORALL i IN 1..lv_data.count
 17      INSERT INTO T_TEST VALUES lv_data(i);
 18 
 19      EXIT WHEN CUR%NOTFOUND;
 20    END LOOP;
 21    COMMIT;
 22    CLOSE CUR;
 23 
 24  EXCEPTION
 25    WHEN OTHERS THEN
 26     DBMS_OUTPUT.PUT_LINE(SQLERRM);
 27  END p_limit_test;
 28  /

Procedure created

SQL> truncate table t_test;

Table truncated

SQL> exec p_limit_test;

PL/SQL procedure successfully completed

SQL> select count(*) from t_test;

  COUNT(*)
----------
        10

SQL> exec p_limit_test(5);

PL/SQL procedure successfully completed

SQL> select count(*) from t_test;

  COUNT(*)
----------
        20

SQL>

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/45259/viewspace-594452/,如需转载,请注明出处,否则将追究法律责任。

上一篇: tailgate and piggyback
请登录后发表评论 登录
全部评论

注册时间:2003-05-05

  • 博文量
    98
  • 访问量
    77607