PROCEDURE DUKESELECT
( vname IN Varchar2,vedu IN Varchar2,vheight1 IN Varchar2,vheight2 IN Varchar2,
vfanhuishu IN Varchar2,vsex IN Varchar2,vbirthday1 IN Varchar2,vbirthday2 IN Varchar2,
vnationality IN Varchar2
) AS
sqll varchar2(1000);
--CURSOR C1 --IS
sqll:='';
sqll := 'select name,sex,ID,BIRTHDAY,RESPOLICE,addictno from addict where 1=1 and rownum <= vfanhuishu';
if (vname <>'')then
sqll := sqll + 'and name like || vname% ';
end if; if (vNationality <>'')then
sqll = sqll + 'and Nationality like || vNationality% ' ;
end if; if (vsex <> '3')then
sqll := sqll + 'and sex = || vsex ';
end if; if (vedu <>'')then
sqll := sqll +' and education = || vedu ';
end if; if (vheight1 <> '')then
sqll := sqll + 'and height >= || vheight1 ';
end if; if (vheight2 <> '')then
sqll := sqll + ' and height <= || vheight2 ';
end if;
--execute immediate sqll;
--cursor_x C1%rowtype;
BEGIN
open C1;
fetch C1 into cursor_x
close C1;
EXCEPTION
WHEN exception_name THEN
statements ;
END; -- Procedure
( vname IN Varchar2,vedu IN Varchar2,vheight1 IN Varchar2,vheight2 IN Varchar2,
vfanhuishu IN Varchar2,vsex IN Varchar2,vbirthday1 IN Varchar2,vbirthday2 IN Varchar2,
vnationality IN Varchar2
) AS
sqll varchar2(1000);
--CURSOR C1 --IS
sqll:='';
sqll := 'select name,sex,ID,BIRTHDAY,RESPOLICE,addictno from addict where 1=1 and rownum <= vfanhuishu';
if (vname <>'')then
sqll := sqll + 'and name like || vname% ';
end if; if (vNationality <>'')then
sqll = sqll + 'and Nationality like || vNationality% ' ;
end if; if (vsex <> '3')then
sqll := sqll + 'and sex = || vsex ';
end if; if (vedu <>'')then
sqll := sqll +' and education = || vedu ';
end if; if (vheight1 <> '')then
sqll := sqll + 'and height >= || vheight1 ';
end if; if (vheight2 <> '')then
sqll := sqll + ' and height <= || vheight2 ';
end if;
--execute immediate sqll;
--cursor_x C1%rowtype;
BEGIN
open C1;
fetch C1 into cursor_x
close C1;
EXCEPTION
WHEN exception_name THEN
statements ;
END; -- Procedure
想实现 if(name <>'')then
sqll :=sqll +'and name = || namex';
end if;
if(num <>'')then
sqll :=sqll +'and num = || numx';
end if;
if(name <>'')then
sqll :=sqll || 'and name = || namex';
end if;
if(num <>'')then
sqll :=sqll || 'and num = || numx';
end if;
declare
-- Local variables here
v_corsor sys_refcursor;
x integer;
sql1 varchar2(2000);
begin
sql1:='select rownum from table where rownum<10';
open v_corsor for sql1;
loop
fetch v_corsor into x;
if(v_corsor%notfound) then
--dbms_output.put_line('end');
exit;
end if;
dbms_output.put_line(x);
end loop;
close v_corsor;
end;
--//这样行么??