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;