不知道我的想法对不对,A,B,C,D,E,F为6个条件的值,A为必输。
cursor cdesc is select * from table_name where a=A
and b=Nvl(B,'%')
and c=Nvl(C,'%')
and d=Nvl(D,'%')
and e=Nvl(E,'%')
and f=Nvl(F,'%');
cursor cdesc is select * from table_name where a=A
and b=Nvl(B,'%')
and c=Nvl(C,'%')
and d=Nvl(D,'%')
and e=Nvl(E,'%')
and f=Nvl(F,'%');
......
end if
sql1 varchar2(50);
sql2 varchar2(50);
........
type t_sor is ref cursor;
.......
begin
if 项目1 is null then
sql1:='select * from table where ....';
open t_sor for sql1;
elsif 项目2 is null then
....
else
...
end if;
end;感觉问题不详!
改成
cursor cdesc is select * from table_name where a=A
and b=Nvl(B,b)
and c=Nvl(C,c')
and d=Nvl(D,d)
and e=Nvl(E,e)
and f=Nvl(F,f);
也许好些
给你个例子吧
TYPE TCursor IS REF CURSOR;
cc TCursor;
create procedure...
strSql varchar2(500):='select * from table1 where 1=1';
begin
if p_workid is not null then
strSql:=strSql||' and workid = '||''''||p_workid||'''';
end if;
if p_dept is not null then
strSql:=strSql||' and dept = '||''''||p_dept||'''';
end if;
.
.
.
open cc for strSql;
commit;
P_outCursor:=cc;
end;
select * from table_name
where a=:a
and (:b is null or b = :b)
and (:c is null or c = :c)
and (:d is null or d = :d)
and (:e is null or e = :e)
and (:f is null or f = :f)效率好像没有影响