看一个问题，游标

Question

CREATE OR REPLACE PROCEDURE SH_CHECK_BUYSALEINFO_VOLUM_5
 (
    s_code  stock_shanghai_newprice.stockcode%TYPE,
    t_time2 stock_shanghai_newprice.stockcode%TYPE
  )
IS
    bvolum1    number;
    bvolum2    number;
    bvolum3    number;
    bvolum4    number;
    bvolum5    number;
    svolum1    number;
    svolum2    number;
    svolum3    number;
    svolum4    number;
    svolum5    number;
    maxpri     number;
    minpri     number;
    closepri   number;
    openpri    number;
    rat        number;
    scode      sh_origin_stock.stockcode%TYPE;
    scode2     sh_origin_stock.stockcode%TYPE;
    t_time1    sh_origin_stock.indicationtime%TYPE;
    scode1     stock_shanghai_newprice.stockcode%TYPE;
    t_time     stock_shanghai_buysaleinfo.time%TYPE;
    t_time3    stock_shanghai_buysaleinfo.time%TYPE;
    t_time4    stock_shanghai_buysaleinfo.time%TYPE;
    id_1       stock_shanghai_buysaleinfo.id%TYPE;
    id_2       stock_shanghai_buysaleinfo.id%TYPE;
    rseq       sh_origin_stock.recordseq%TYPE;
CURSOR cur_tab
IS
       SELECT id id_1,time t_time3
       FROM  stock_shanghai_buysaleinfo
       WHERE  stockcode=NVL(s_code, stockcode) and time=NVL(to_date(t_time2,'yyyymmdd hh24miss'),time)
       order by id;
CURSOR cur_tab1 
IS      
       SELECT recordseq rseq,indicationtime t_time4
       FROM  sh_origin_stock
       WHERE  stockcode=NVL(s_code, stockcode) and indicationtime=NVL(to_date(t_time2,'yyyymmdd hh24miss'),indicationtime)
       order by recordseq;
BEGIN
      FOR each_rec IN cur_tab
      FOR each_rec1 IN cur_tab1  LOOP
       BEGIN
            --DBMS_OUTPUT.put_line (each_rec.t_time3);
            SELECT stockcode,
                   buyvolum1,buyvolum2,buyvolum3,buyvolum4,buyvolum5,
                   salevolum1,salevolum2,salevolum3,salevolum4,salevolum5
            INTO scode1,
                 bvolum1,bvolum2,bvolum3,bvolum4,bvolum5,
                 svolum1,svolum2,svolum3,svolum4,svolum5
            FROM stock_shanghai_buysaleinfo
            WHERE id=each_rec.id_1
            AND stockcode=NVL(s_code, stockcode)
            AND time=each_rec.t_time3;
            --DBMS_OUTPUT.put_line(each_rec.id_1);
            --DBMS_OUTPUT.put_line(scode1);
        END;
        BEGIN
            select stockcode ,indicationtime
            INTO   scode1,t_time3
            FROM   sh_origin_stock 
            WHERE  (
              buyvolum1!=bvolum1 or buyvolum2!=bvolum2 or buyvolum3!=bvolum3 or buyvolum4!=bvolum4 or buyvolum5!=bvolum5
              or
              salevolum1!=svolum1 or salevolum2!=svolum2 or salevolum3!=svolum3 or salevolum4!=svolum4 or salevolum5!=svolum5
              ) and 
                 stockcode = scode1 and indicationtime = each_rec.t_time3 and  rowid not IN 
                   (SELECT rowid FROM 
                   ( 
                    SELECT rowid, buyvolum1, buyvolum2, buyvolum3,buyvolum4,buyvolum5,
                    salevolum1,salevolum2,salevolum3,salevolum4,salevolum5,row_number() 
                    over(partition by buyvolum1, buyvolum2, buyvolum3,buyvolum4,buyvolum5,
                    salevolum1,salevolum2,salevolum3,salevolum4,salevolum5 order by rowid) rn 
                    FROM sh_origin_stock 
                    ) 
                    WHERE rn > 1 
                    ) and recordseq=each_rec1.rseq;
        /*BEGIN
            SELECT stockcode ,indicationtime
            INTO  scode,t_time1
            FROM  sh_origin_stock
            WHERE
            (
              buyvolum1!=bvolum1 or buyvolum2!=bvolum2 or buyvolum3!=bvolum3 or buyvolum4!=bvolum4 or buyvolum5!=bvolum5
              or
              salevolum1!=svolum1 or salevolum2!=svolum2 or salevolum3!=svolum3 or salevolum4!=svolum4 or salevolum5!=svolum5
            )
            AND  stockcode = scode2
            AND  indicationtime = t_time4
            AND  recordseq=rseq;
            --AND  id = each_rec.id_1 ;
            --DBMS_OUTPUT.put_line('feng');*/
            DBMS_OUTPUT.put_line (scode1||','||each_rec.t_time3||','||'Please check the data!');
       EXCEPTION
        WHEN ZERO_DIVIDE
        THEN
            DBMS_OUTPUT.put_line (scode1||','||each_rec.t_time3||','||'Can not divide zero!');
        WHEN NO_DATA_FOUND
        THEN
            DBMS_OUTPUT.put_line (scode1||','||each_rec.t_time3||','||'Data correctly!');
        WHEN OTHERS
        THEN
            DBMS_OUTPUT.PUT_LINE(scode1||','||each_rec.t_time3||','||'Unknown Error!');      END;
   end loop;
end;我能再定义一个cursor吗？红色部分，在下班的begin里边需要cursor中的参数

Answer 1

你这存储过程编译不通过啊，      FOR each_rec IN cur_tab 
      FOR each_rec1 IN cur_tab1 两个游标嵌套明显有问题

Answer 2

可以嵌套游标，你好像少写了个LOOPCREATE OR REPLACE PROCEDURE sh_check_buysaleinfo_volum_5 (
   s_code    stock_shanghai_newprice.stockcode%TYPE,
   t_time2   stock_shanghai_newprice.stockcode%TYPE
)
IS
   bvolum1    NUMBER;
   bvolum2    NUMBER;
   bvolum3    NUMBER;
   bvolum4    NUMBER;
   bvolum5    NUMBER;
   svolum1    NUMBER;
   svolum2    NUMBER;
   svolum3    NUMBER;
   svolum4    NUMBER;
   svolum5    NUMBER;
   maxpri     NUMBER;
   minpri     NUMBER;
   closepri   NUMBER;
   openpri    NUMBER;
   rat        NUMBER;
   scode      sh_origin_stock.stockcode%TYPE;
   scode2     sh_origin_stock.stockcode%TYPE;
   t_time1    sh_origin_stock.indicationtime%TYPE;
   scode1     stock_shanghai_newprice.stockcode%TYPE;
   t_time     stock_shanghai_buysaleinfo.TIME%TYPE;
   t_time3    stock_shanghai_buysaleinfo.TIME%TYPE;
   t_time4    stock_shanghai_buysaleinfo.TIME%TYPE;
   id_1       stock_shanghai_buysaleinfo.ID%TYPE;
   id_2       stock_shanghai_buysaleinfo.ID%TYPE;
   rseq       sh_origin_stock.recordseq%TYPE;   CURSOR cur_tab
   IS
      SELECT   ID id_1, TIME t_time3
          FROM stock_shanghai_buysaleinfo
         WHERE stockcode = NVL (s_code, stockcode)
           AND TIME = NVL (TO_DATE (t_time2, 'yyyymmdd hh24miss'), TIME)
      ORDER BY ID;   CURSOR cur_tab1
   IS
      SELECT   recordseq rseq, indicationtime t_time4
          FROM sh_origin_stock
         WHERE stockcode = NVL (s_code, stockcode)
           AND indicationtime =
                  NVL (TO_DATE (t_time2, 'yyyymmdd hh24miss'), indicationtime)
      ORDER BY recordseq;
BEGIN
   FOR each_rec IN cur_tab
   LOOP
      FOR each_rec1 IN cur_tab1
      LOOP
         BEGIN
            SELECT stockcode, buyvolum1, buyvolum2, buyvolum3, buyvolum4,
                   buyvolum5, salevolum1, salevolum2, salevolum3,
                   salevolum4, salevolum5
              INTO scode1, bvolum1, bvolum2, bvolum3, bvolum4,
                   bvolum5, svolum1, svolum2, svolum3,
                   svolum4, svolum5
              FROM stock_shanghai_buysaleinfo
             WHERE ID = each_rec.id_1
               AND stockcode = NVL (s_code, stockcode)
               AND TIME = each_rec.t_time3;
         END;         BEGIN
            SELECT stockcode, indicationtime
              INTO scode1, t_time3
              FROM sh_origin_stock
             WHERE (   buyvolum1 != bvolum1
                    OR buyvolum2 != bvolum2
                    OR buyvolum3 != bvolum3
                    OR buyvolum4 != bvolum4
                    OR buyvolum5 != bvolum5
                    OR salevolum1 != svolum1
                    OR salevolum2 != svolum2
                    OR salevolum3 != svolum3
                    OR salevolum4 != svolum4
                    OR salevolum5 != svolum5
                   )
               AND stockcode = scode1
               AND indicationtime = each_rec.t_time3
               AND ROWID NOT IN (
                      SELECT ROWID
                        FROM (SELECT ROWID, buyvolum1, buyvolum2, buyvolum3,
                                     buyvolum4, buyvolum5, salevolum1,
                                     salevolum2, salevolum3, salevolum4,
                                     salevolum5,
                                     ROW_NUMBER () OVER (PARTITION BY buyvolum1, buyvolum2, buyvolum3, buyvolum4, buyvolum5, salevolum1, salevolum2, salevolum3, salevolum4, salevolum5 ORDER BY ROWID)
                                                                           rn
                                FROM sh_origin_stock)
                       WHERE rn > 1)
               AND recordseq = each_rec1.rseq;            DBMS_OUTPUT.put_line (   scode1
                                  || ','
                                  || each_rec.t_time3
                                  || ','
                                  || 'Please check the data!'
                                 );
         EXCEPTION
            WHEN ZERO_DIVIDE
            THEN
               DBMS_OUTPUT.put_line (   scode1
                                     || ','
                                     || each_rec.t_time3
                                     || ','
                                     || 'Can not divide zero!'
                                    );
            WHEN NO_DATA_FOUND
            THEN
               DBMS_OUTPUT.put_line (   scode1
                                     || ','
                                     || each_rec.t_time3
                                     || ','
                                     || 'Data correctly!'
                                    );
            WHEN OTHERS
            THEN
               DBMS_OUTPUT.put_line (   scode1
                                     || ','
                                     || each_rec.t_time3
                                     || ','
                                     || 'Unknown Error!'
                                    );
         END;
      END LOOP;
   END LOOP;
END;

Answer 3

  
两个游标嵌套是可以的﹔for rec_1 in cur_1 loop
    for rec_2 in cur_2 loop
        null;
    end loop;
end loop;