create or replace procedure QIANFEIGL_JIAOKUANDY(
cebenh varchar2, kehuh varchar2, hetongh varchar2, v_cur out query_pkg.Query_cur
)
is
SQLSTR VARCHAR2(3000);
begin
SQLSTR:=' select a.s_ch,a.s_cid,c.s_hetongh,a.d_caozuosj from zw_yingyez a left join kg_biaokaxx b on a.s_cid=b.s_cid left join kg_zhanghuxx c on c.s_zhanghubh=b.s_zhanghubh where i_xiaozhang=0' ;
if cebenh is not null and length(cebenh)>0 then
SQLSTR:=SQLSTR||' and a.s_ch='''||cebenh||'''';
end if;
if hetongh is not null and length(hetongh)>0 then
SQLSTR:=SQLSTR||' and c.s_hetongh='''||hetongh||'''';
end if;
if kehuh is not null and length(kehuh)>0 then
SQLSTR:=SQLSTR||' and a.s_cid='''||kehuh||'''';
end if;
SQLSTR:=SQLSTR||' order by a.d_caozuosj desc';
OPEN V_CUR FOR SQLSTR;
end QIANFEIGL_JIAOKUANDY;
1、在Oracle中写有返回数据集的存储过程要用游标(CURSOR)，在查询语句前加 OPEN V_CUR FOR
2、没有if...else...，可以用if...end if ; 形式进行判断
3、可以在Oracle中声明一个varchar2类型的变量，用它来存放SQL语句，中间可以进行字符串拼接（用"||"）