如何在Oracle中为每个"for循环"迭代选择200条记录? [英] How to select 200 record for each 'for loop' iteration in oracle?

问题描述

我想要的是这个.

我有一个具有10000条记录的表

I have a table with 10000 record like this

Table A

user_id | name | rollno | state

我想要的是一次选择200条记录，然后在下一次迭代中选择下200条，依此类推.我能想到的是这个.

What i want is to select 200 record at once and then next 200 in next iteration and so on . What i can thought of is this.

.... declare statement


     SELECT count(*) into v_length from A;
        i := 0;
         FOR i into v_length /200  loop

             FOR rec IN (SELECT b.blurb_id, b.article_source_clob, row_number() over(ORDER BY b.blurb_id) myrow
                              FROM mobile.mobile_blurb b)
                        WHERE myrow < i*200 and myrow > (i+1)*200 )  loop
             ............. and 200 record every time
         end loops;

... end

推荐答案

正如我的评论中所述，您可以使用BULK collect和LIMIT选项来满足您的要求.请参见下面的演示.在这里，每次循环都会运行并选择200记录.

As mentioned in my comments, you can use BULK collect and LIMIT option to achieve your requirement. See below demo. Here in each time the loop will run and pick 200 records.

DECLARE
   CURSOR rec
   IS
          SELECT LEVEL
            FROM DUAL
      CONNECT BY LEVEL < 401;

   TYPE v_var IS TABLE OF NUMBER
      INDEX BY PLS_INTEGER;

   var   v_var;
BEGIN
   OPEN rec;

   LOOP
      FETCH rec BULK COLLECT INTO var LIMIT 200;

      EXIT WHEN (var.count = 0);

      FOR i IN 1 .. var.COUNT
      LOOP
         DBMS_OUTPUT.put_line (var (i));
      END LOOP;
   END LOOP;

   CLOSE rec;
END;

这篇关于如何在Oracle中为每个"for循环"迭代选择200条记录?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！