转载

游标+bulk collect into limit的不同方法查询数据

如下需求想在查询表时先根据bulk collect into方式查询表中部分数据，然后处理，在查询表中其余数据知道查询出表中所有数据

如一个表有6条数据，第一次次批量取3条，显示后再批量取3条，已显示游标方式查询

具体构建环境

create TABLE TEST.COURSE
(      COURSE_NO NUMBER(38),
       DESCRIPTION VARCHAR2(50),
       COST NUMBER(9,2),
       PREREQUISITE NUMBER(8),
       CREATED_BY VARCHAR2(30),
       CREATED_DATE DATE,
       MODIFIED_BY VARCHAR2(30),
       MODIFIED_DATE DATE)

       INSERT INTO TEST.COURSE VALUES
       (
         1,'22223',1,1,'20150101',SYSDATE,'20150101',SYSDATE
       );
       INSERT INTO TEST.COURSE VALUES
       (
         2,'22223',1,1,'20150101',SYSDATE,'20150101',SYSDATE
       );
       INSERT INTO TEST.COURSE VALUES
       (
         3,'22223',1,1,'20150101',SYSDATE,'20150101',SYSDATE
       );
       INSERT INTO TEST.COURSE VALUES
       (
         4,'22223',1,1,'20150101',SYSDATE,'20150101',SYSDATE
       );
       INSERT INTO TEST.COURSE VALUES
       (
         5,'22223',1,1,'20150101',SYSDATE,'20150101',SYSDATE
       );
       INSERT INTO TEST.COURSE VALUES
       (
         6,'22223',1,1,'20150101',SYSDATE,'20150101',SYSDATE
       );

采用rowtype类型查询

DECLARE
   rn number;
   CURSOR course_cur IS
   SELECT * --注意此处如果cur_result游标结果是表行类型的，要查询所有字段，但如果是type就要查询出对应具体字段
   FROM TEST.COURSE;

   type delArray1 is table of TEST.COURSE%ROWTYPE index by binary_integer; --ROWTYPE表示表中一行的记录类型
   --type delArray1 is table of TEST.COURSE.COURSE_NO%TYPE index by binary_integer;
   cur_result delArray1;
   BEGIN
   rn := 2;
   OPEN course_cur;
    for i in 1..3 loop
      FETCH course_cur bulk collect into cur_result limit rn;
       for i in 1..rn loop
         DBMS_OUTPUT.PUT_LINE ('loop COURSE_NO1:   '||cur_result(i).COURSE_NO);
       end loop;
       DBMS_OUTPUT.PUT_LINE ('commit ' );

   END LOOP;
   close course_cur;

END;

采用type类型查询

DECLARE
   rn number;
   CURSOR course_cur IS
   SELECT course_no --注意此处如果cur_result游标结果不是表行类型的，是type就要查询出对应具体字段
   FROM TEST.COURSE;

   --type delArray1 is table of TEST.COURSE%ROWTYPE index by binary_integer;
   type delArray1 is table of TEST.COURSE.COURSE_NO%TYPE index by binary_integer;--%TYPE实际对应表中一列的数据类型
   cur_result delArray1;
   BEGIN
   rn := 2;
   OPEN course_cur;
    for i in 1..3 loop
      FETCH course_cur bulk collect into cur_result limit rn;
       for i in 1..rn loop
         DBMS_OUTPUT.PUT_LINE ('loop COURSE_NO1:   '||cur_result(i));
       end loop;
       DBMS_OUTPUT.PUT_LINE ('commit ' );

   END LOOP;
   close course_cur;

END;