动态游标使用例子

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;