有这样一个存储过程
CREATE OR REPLACE PROCEDURE find_prjNumber (
fp_stageid IN integer,
fp_flowstate IN integer
)
AS
find_prjNumber integer;
begin
select count(*)
into find_prjNumber
......
and pp.stageid = fp_stageid and pp.flowstate = fp_flowstate;
END find_prjNumber;
现在我象让最后两个and条件 根据传进来的fp_stageid,和fp_flowstate值进行变化。比如 如果fp_stageid = 0时 ,
and pp.stageid = 1 pp.flowstate 等于某个值时 “and pp.flowstate = fp_flowstate”这部分就不要了,我该怎么写?顺便求教, fp_stageid 和 fp_flowstate 可以传空值进来吗?如果为空 条件怎么判断?if fp_stageid ==null?
CREATE OR REPLACE PROCEDURE find_prjNumber (
fp_stageid IN integer,
fp_flowstate IN integer
)
AS
find_prjNumber integer;
begin
select count(*)
into find_prjNumber
......
and pp.stageid = fp_stageid and pp.flowstate = fp_flowstate;
END find_prjNumber;
现在我象让最后两个and条件 根据传进来的fp_stageid,和fp_flowstate值进行变化。比如 如果fp_stageid = 0时 ,
and pp.stageid = 1 pp.flowstate 等于某个值时 “and pp.flowstate = fp_flowstate”这部分就不要了,我该怎么写?顺便求教, fp_stageid 和 fp_flowstate 可以传空值进来吗?如果为空 条件怎么判断?if fp_stageid ==null?
其实很简单就是一个根据条件的sql拼接。万一你要是不会你分成两个sql 也行啊(不推荐)为空判断
if nvl(fp_stageid,0)==0 --注意这个0的取值是fp_stageid里面不可能有的值,0是我瞎取的。
或者用动态语句来拼
动态语句如何拼呢?
fp_stageid IN integer,
fp_flowstate IN integer
)
AS
find_prjNumber integer;
begin
select count(*)
into find_prjNumber
......
and pp.stageid = case when fp_stageid = 0 then 1 else fp_stageid end
and pp.flowstate = case when pp.flowstate = ?? then pp.flowstate else fp_flowstate end;
END find_prjNumber;
'select count(*)......
and pp.stageid = :1'||case when [条件] then ' and pp.flowstate = '||fp_flowstate end
into find_prjNumber
using fp_stageid ;
满足条件时条件中加上and ....