create or replace procedure (col in varchar2,col2 in varchar2) as
...
begin
...
select * into v_name from tbname where name like nvl(col,'%') and
content like nvl(col2,'%');
...
end;
/
...
begin
...
select * into v_name from tbname where name like nvl(col,'%') and
content like nvl(col2,'%');
...
end;
/
wSQL VARCHAR2(2000);
wWHERE BOOLEAN;
TYPE ListRec_Cur IS REF CURSOR;
wCurListRec1 ListRec_Cur;
begin
...
IF col <>'' THEN
wWHERE:=' WHERE name =''' || col || '''';
END IF;
IF col1 <>'' THEN
IF wWHERE<>'' THEN
wWHERE:=' AND content =''' || col1 || '''';
ELSE
wWHERE:=' WHERE content =''' || col || '''';
END IF ;
END IF;
wSQL :=wSQL || wWHERE;
OPEN wCurListRec1 FOR wSQL;
...
end;
...
begin
...
select col into v_name from tbname where deccode(name,null,var1)=var1 and
deccode(name,null,var2)=var2;
...
end;
/
...
begin
...
select col into v_name from tbname where deccode(name,null,var1)=var1 and
deccode(content,null,var2)=var2;
...
end;
/
...
begin
...
select col into v_name from tbname where deccode(name,null,var1)=var1 and
deccode(content,null,var2)=var2;
...
end;
如果楼主所说的空是指 '',则必须:select col into v_name from tbname
where name like '%'||var1||'%'
and content like '%'||var2||'%'
as
v_name varchar2(10);
v_content varchar2(30);
begin
select name,content in v_name,v_content from tab_name where nvl2(p_name,name,1)=nvl(p_name,1) and nvl2(p_content,content,1)=nvl(p_content,1);
end;
/
where (name =?or name is null) and (content = ? or content is null),