有这样一个存储过程,四个输入参数,一个输出参数。create procedure searchUsers(
i_usr_id in userinfo.usr_id%type;
i_usr_name in userinfo.usr_name%type;
i_usr_name in userinfo.usr_name%type;
i_usr_name in userinfo.usr_name%type;
o_resulet out cursor;
) is
begin
...
end;我想在存储过程中用四个输入参数对userinfo表进行查询。将结果保存在cursor中。执行查询的时候,我这样写
select (...) from userinfo where usr_id = i_usr_id and usr_name = i_usr_name ...问题在这里,这四个参数都允许为空,比如usr_id为空的话,就得写成where usr_id is null...(就不能用等号了,否则执行会报错)那这个逻辑我该如何来实现呢?
对pl/sql我实在了解甚少,还请朋友们指点。不胜感激。
i_usr_id in userinfo.usr_id%type;
i_usr_name in userinfo.usr_name%type;
i_usr_name in userinfo.usr_name%type;
i_usr_name in userinfo.usr_name%type;
o_resulet out cursor;
) is
begin
...
end;我想在存储过程中用四个输入参数对userinfo表进行查询。将结果保存在cursor中。执行查询的时候,我这样写
select (...) from userinfo where usr_id = i_usr_id and usr_name = i_usr_name ...问题在这里,这四个参数都允许为空,比如usr_id为空的话,就得写成where usr_id is null...(就不能用等号了,否则执行会报错)那这个逻辑我该如何来实现呢?
对pl/sql我实在了解甚少,还请朋友们指点。不胜感激。
create procedure searchUsers(
i_usr_id in userinfo.usr_id%type;
i_usr_name in userinfo.usr_name%type;
i_usr_name in userinfo.usr_name%type;
i_usr_name in userinfo.usr_name%type;
o_resulet out sys_refcursor;
) is
sqlstr varchar2(4000);
begin
sqlstr:='select * from table1 where '
if i_usr_id is null then
sqlstr:=sqlstr||' i_usr_id is null';
else
sqlstr:=sqlstr||' i_usr_id = '''||i_usr_id||'''';
end if;
if i_usr_name is null then
sqlstr:=sqlstr||' and i_usr_name is null';
else
sqlstr:=sqlstr||' and i_usr_name = '''||i_usr_name||'''';
end if;
...................
open o_resulet for sqlstr;
end;
(i_usr_id is null or usr_id = i_usr_id)如果i_usr_id为空则查询usr_id为空的记录,用:
nvl(usr_id,'@@@@@') = nvl(i_usr_id,'@@@@@')
usr_id中不能保存有@@@@@这种记录。