关于游标打开问题

Question

我用cobol写的程序，原来在数据是46W条的时候游标能够正常打开，但是数据变成66W条后程序执行到 open cursor的时候就停住了，开了1天都没有把游标打开，也不报错，在OB里执行sql虽然时间长，但是最后还是能把数据检索出来的。，不知道这是什么原因，请各位大大多多帮忙，急！在线等

Answer 1

               DECLARE  SNA0018_CUR1_TEST  CURSOR  FOR
               select
                 SRTS.NENDO
                ,MMS.MOSHI_CODE_KEINAVI
                ,KSK.IKKATSUKO_CODE
                ,SRTS.KAMOKU_CODE
                ,MSK.EDAMON_BANGO
                ,MMM.KAITO_MARK_KIGO
                ,MSK.SEITORITSU
                ,SMZ.SEITORITSU
                ,MMM.KANTO_HANI_KUBUN
                ,nvl(SMPSK.EDAMON_BANGO_SHURYO,0)
                ,nvl(SMPSK.KANTO_SEITORITSU,0)
                ,nvl(SMPSZ.KANTORITSU,0)
                ,nvl(SMRS.KAITO_BANGO,0)
                ,nvl(SMRS.KAITO_EDAMON_MARK_RITSU,0)
                ,MMM.MARK_PATTERN_KIGO_SENTAKUSHI
                ,MST.DAI_SETSUMON_BANGO
               from
                 ( ( ( ( ( ( ( ( (
                                   select
                                     MS.MOSHI_CODE
                                    ,MS.NENDO
                                    ,MC.SHUTSURYOKUJUN
                                    ,MJ.KAMOKU_CODE
                                   from
                                     MM_MOSHI MS
                                     inner join
                                     MM_CHOHYO_KAMOKU_MASTER MC
                                     on  MS.MOSHI_CODE
                                         = :ＫＹ－模試コード
                                     and MS.SHOGYOJI_CODE
                                     = MC.SHOGYOJI_CODE
                                     and MS.KAISU = MC.KAISU
                                     and MS.MOSHI_GAKUNEN
                                     = MC.MOSHI_GAKUNEN
                                     and :ＫＹ－出力パターン番号
                                     = MC.SHUTSURYOKU_PATTERN_BANGO
                                     inner join
                                     MM_MOSHI_JISSHI_KAMOKU MJ
                                     on  MS.MOSHI_CODE = MJ.MOSHI_CODE
                                     and MC.KAMOKU_CODE = MJ.KAMOKU_CODE
                                 ) SRTS
                                 inner join
                                 MM_MOSHI_MASTER2 MMS
                                 on  SRTS.MOSHI_CODE = MMS.MOSHI_CODE
                               )
                               inner join
                               SH_KAMOKU_SH_I_KO KSK
                               on  SRTS.MOSHI_CODE = KSK.MOSHI_CODE
                               and '0' = KSK.SAKKON_KUBUN
                               and SRTS.KAMOKU_CODE = KSK.KAMOKU_CODE
                               and :ＫＹ－一括校コード
                               <> KSK.IKKATSUKO_CODE
                             )
                             inner join
                             MM_SETSUMON MST
                             on  SRTS.MOSHI_CODE = MST.MOSHI_CODE
                             and SRTS.KAMOKU_CODE = MST.KAMOKU_CODE
                           )
                           inner join
                           MM_MARK_MASTER MMM
                           on  MST.MOSHI_CODE = MMM.MOSHI_CODE
                           and MST.KAMOKU_CODE = MMM.KAMOKU_CODE
                           and MST.SETSUMON_BANGO = MMM.SETSUMON_BANGO
                         )
                         inner join
                         SH_MARK_SH_I_KO MSK
                         on  MMM.MOSHI_CODE = MSK.MOSHI_CODE
                         and MMM.KAMOKU_CODE = MSK.KAMOKU_CODE
                         and KSK.IKKATSUKO_CODE = MSK.IKKATSUKO_CODE
                         and MMM.EDAMON_BANGO = MSK.EDAMON_BANGO
                       )
                       inner join
                       SH_MARK_SH_ZK SMZ
                       on  MMM.MOSHI_CODE = SMZ.MOSHI_CODE
                       and MMM.KAMOKU_CODE = SMZ.KAMOKU_CODE
                       and MMM.EDAMON_BANGO = SMZ.EDAMON_BANGO
                     )
                     inner join
                     SH_MARK_RITSU_SH_I_KO SMRS
                     on  MSK.MOSHI_CODE = SMRS.MOSHI_CODE
                     and MSK.KAMOKU_CODE = SMRS.KAMOKU_CODE
                     and MSK.IKKATSUKO_CODE = SMRS.IKKATSUKO_CODE
                     and MSK.EDAMON_BANGO = SMRS.EDAMON_BANGO
                   )
                   left join
                   SH_MARK_PAIR_SH_I_KO SMPSK
                   on  MSK.MOSHI_CODE = SMPSK.MOSHI_CODE
                   and MSK.KAMOKU_CODE = SMPSK.KAMOKU_CODE
                   and MSK.IKKATSUKO_CODE = SMPSK.IKKATSUKO_CODE
                   and MSK.EDAMON_BANGO = SMPSK.EDAMON_BANGO_SHURYO
                 )
                 left join
                 SH_MARK_PAIR_SH_ZK SMPSZ
                 on  SMZ.MOSHI_CODE = SMPSZ.MOSHI_CODE
                 and SMZ.KAMOKU_CODE = SMPSZ.KAMOKU_CODE
                 and SMZ.EDAMON_BANGO = SMPSZ.EDAMON_BANGO_SHURYO
               where rownum < = 100
               order by
                 KSK.IKKATSUKO_CODE
                ,SRTS.SHUTSURYOKUJUN
                ,MST.DAI_SETSUMON_BANGO
                ,MSK.EDAMON_BANGO
                ,SMRS.KAITO_EDAMON_MARK_RITSU desc
                ,SMRS.KAITO_BANGO
             END-EXEC
             EXEC  SQL
               OPEN  SNA0018_CUR1_TEST
             END-EXEC

Answer 2

单独拷出游标中那句Select 的语句去运行一下,看时间要多久?

Answer 3

应为取了rownum是100件，半个小时左右能取出数据

Answer 4

open cursor是不会出问题的
数据真正取出是在fetch的时候，一个小测试：
create table test(id number);begin
for i in 1..500000 loop
   insert into test values(i);
end loop;
end;declare 
   cursor c is select * from test;
   t number(10);
begin
   dbms_output.put_line(to_char(sysdate,'mm/dd/yyyy HH24:MI:SS')||'open');
   open c;
   dbms_output.put_line(to_char(sysdate,'mm/dd/yyyy HH24:MI:SS')||'start');
   loop
      fetch c into t;
      exit when c%notfound;
   end loop;
   dbms_output.put_line(to_char(sysdate,'mm/dd/yyyy HH24:MI:SS')||'end');
end;

Answer 5

这样大量的数据，可以在fetch时使用bulk collect into比如像这样：
open pat_cur for v_sql_row;
      loop
         fetch             pat_cur
         bulk collect into row_id_rec
         limit             10000;         forall i in row_id_rec.first..row_id_rec.last
            execute immediate v_sql_upd||
                              ' where  a.rowid = :1'
            using              row_id_rec(i);         commit;         exit when pat_cur%notfound;
      end loop;

Answer 6

这种批量处理，同时在loop里面使用绑定变量，速度会快很多