动态游标使用例子

1.这里使用动态游标，根据传进来的参数构造sql语句，再把sql语句赋值给游标，要注意的是，这里的参数可能为空

create or replace procedure ProduceMainData(p_zbbh VARCHAR2,p_fbbh VARCHAR2, p_bh   VARCHAR2) is

  --定义一个动态游标
  TYPE ref_cur_type IS REF CURSOR;
  cur_query ref_cur_type;

  v_sqlStmt string(10000);

  --定义变量用来接收游标的值
  V_ZBBH   VARCHAR2(100);
  V_FBBH   VARCHAR2(100);
  V_FBMC   VARCHAR2(100);
  V_BH     VARCHAR2(100);
  V_XMDW   VARCHAR2(100);
  V_TBRMC  VARCHAR2(100);
  V_TBJG   VARCHAR2(100);
  V_TBSBZ  VARCHAR2(100);
  V_KBBZ   VARCHAR2(100);
  V_TIME   VARCHAR2(100);
  V_KBDATE VARCHAR2(100);
  V_ZGXJ   VARCHAR2(100);

begin
  --根据参数构造语句并赋值给游标
  v_sqlStmt := 'select a.ZBBH,
           a.FBBH,
           a.FBMC,
           a.BH,
           a.XMDW,
           a.TBRMC,
           a.TBJG,
           a.TBSBZ,
           a.KBBZ,
           a. TIME,
           a.KBDATE,
           b.ZGXJ
      from TB_kbylb a
     inner join tb_import_kbylb b on a.zbbh = b.zbbh
                                 and a.fbbh = b.fbbh
                                 and a.bh = b.bh
     where to_number(a.TBJG) > to_number(b.ZGXJ) ';--招标价格大于最高限价
  if p_zbbh != '0' then
    v_sqlStmt := v_sqlStmt || ' and  a.zbbh = ' || p_zbbh;
  end if;

  if p_fbbh != '0' then
    v_sqlStmt := v_sqlStmt || ' and  a.fbbh = '||chr(39)||p_fbbh||chr(39);--存储过程中单引号处理用chr(39)
  end if;

  if p_bh != '0' then
    v_sqlStmt := v_sqlStmt || ' and  a.bh = ' || p_bh;
  end if;
  
   DBMS_OUTPUT.put_line('SQL:' || v_sqlStmt);
  --将语句赋值给游标
  open cur_query for v_sqlStmt;

  LOOP
    --将游标的值给变量
    FETCH cur_query
      INTO V_ZBBH, V_FBBH, V_FBMC, V_BH, V_XMDW, V_TBRMC, V_TBJG, V_TBSBZ, V_KBBZ, V_TIME, V_KBDATE, V_ZGXJ;
    exit when cur_query%notfound;
    
    insert into tb_screen_discard_main
    values
      (seq_screen_discard_main.nextval,
       V_ZBBH,
       V_FBBH,
       V_FBMC,
       V_BH,
       V_XMDW,
       V_TBRMC,
       V_TBJG,
       V_TBSBZ,
       V_KBBZ,
       V_TIME,
       V_KBDATE,
       V_ZGXJ);
       
  END LOOP;
  CLOSE cur_query;

END;