没有结束符,你的过程问题太多,你在参数中没有定义mycursor,你可以在package中定义,应该不止PLS-00103: Encountered the symbol "(" when expecting one of the following:这一个错,我编译了你的过程,问题太多,好好看看游标和动态sql地用法,别忘了给分
不是,我在包中定义的。 包中的定义为: type mycursor is ref cursor;procedure proc_query_employee( intype in employee_userinfo.type%type, inbmh in employee_userinfo.bmh%type, inmaxfee in employee_userinfo.maxfee%type, instatus in employee_userinfo.status%type, user_cursor out mycursor);
if length(rtrim(ltrim(intype)))>0 then strsql:=strsql||' type='||intype; --把所有都改成这种形式,字符型就不用多加一对单引号了
procedure proc_query_employee( intype in employee_userinfo.type%type, inbmh in employee_userinfo.bmh%type, inmaxfee in employee_userinfo.maxfee%type, instatus in employee_userinfo.status%type, user_cursor out mycursor) as tag varchar2(1); strsql VARCHAR2(500); v_cursor mycursor; begin tag :='0';if length(rtrim(intype))+length(rtrim(inbmh))+inmaxfee+length(rtrim(instatus))=0 then open v_cursor for select svcnum,employee_name,type,bmh,status,maxfee from employee_userinfo order by type,bmh,status,maxfee,employee_name; elsestrsql :='select svcnum,employee_name,type,bmh,status,maxfee from employee_userinfo where '; if length(rtrim(ltrim(intype)))>0 then strsql :=strsql||' type='||intype; tag :='1'; end if;if length(rtrim(ltrim(inbmh)))>0 then if tag='1' then strsql := strsql||' and bmh='||inbmh; else strsql := strsql||' bmh='||inbmh; tag :='1'; end if; end if;if inmaxfee>0 then if tag='1' then strsql := strsql||' and maxfee='||inmaxfee ; else strsql := strsql||' maxfee='||inmaxfee; tag :='1'; end if; end if;if length(rtrim(ltrim(instatus)))>0 then if tag='1' then strsql := strsql||' and status='||instatus; else strsql := strsql||' status='||instatus; end if; end if; strsql := strsql||' order by type,bmh,status,maxfee,employee_name'; DBMS_OUTPUT.PUT_LINE(strsql); OPEN v_cursor FOR strsql; 可是还是有问题,在OPEN v_cursor FOR strsql; 出错,提示 PLS-00103: Encountered the symbol "(" when expecting one of the following: select 。谢谢先。end if; user_cursor :=v_cursor;end proc_query_employee;
包中的定义为:
type mycursor is ref cursor;procedure proc_query_employee(
intype in employee_userinfo.type%type,
inbmh in employee_userinfo.bmh%type,
inmaxfee in employee_userinfo.maxfee%type,
instatus in employee_userinfo.status%type,
user_cursor out mycursor);
strsql:=strsql||' type='||intype; --把所有都改成这种形式,字符型就不用多加一对单引号了
intype in employee_userinfo.type%type,
inbmh in employee_userinfo.bmh%type,
inmaxfee in employee_userinfo.maxfee%type,
instatus in employee_userinfo.status%type,
user_cursor out mycursor)
as
tag varchar2(1);
strsql VARCHAR2(500);
v_cursor mycursor;
begin
tag :='0';if length(rtrim(intype))+length(rtrim(inbmh))+inmaxfee+length(rtrim(instatus))=0 then
open v_cursor for select svcnum,employee_name,type,bmh,status,maxfee
from employee_userinfo order by type,bmh,status,maxfee,employee_name;
elsestrsql :='select svcnum,employee_name,type,bmh,status,maxfee from employee_userinfo where ';
if length(rtrim(ltrim(intype)))>0 then
strsql :=strsql||' type='||intype;
tag :='1';
end if;if length(rtrim(ltrim(inbmh)))>0 then
if tag='1' then
strsql := strsql||' and bmh='||inbmh;
else
strsql := strsql||' bmh='||inbmh;
tag :='1';
end if;
end if;if inmaxfee>0 then
if tag='1' then
strsql := strsql||' and maxfee='||inmaxfee ;
else
strsql := strsql||' maxfee='||inmaxfee;
tag :='1';
end if;
end if;if length(rtrim(ltrim(instatus)))>0 then
if tag='1' then
strsql := strsql||' and status='||instatus;
else
strsql := strsql||' status='||instatus;
end if;
end if;
strsql := strsql||' order by type,bmh,status,maxfee,employee_name';
DBMS_OUTPUT.PUT_LINE(strsql);
OPEN v_cursor FOR strsql;
可是还是有问题,在OPEN v_cursor FOR strsql; 出错,提示
PLS-00103: Encountered the symbol "(" when expecting one of the following: select
。谢谢先。end if;
user_cursor :=v_cursor;end proc_query_employee;