下面的是我的存储过程,我的目的是根据query_type的值来确定用哪种查询方式,所以要用到if--elseif--else结构,但总是不能通过编译,请高手帮我看看问题出在哪里:
--根据条件来查询actiontable
Create or Replace package ref_types
AS
TYPE ref_cursor is REF CURSOR;
End;
/
Create or Replace package QUERY_ACTION_BY_CONDITION as
procedure Get_useraction (
username_str in USERACTION.USERNAME%TYPE,
query_type in integer,
start_time_str in USERACTION.OPERATIME%TYPE,
end_time_str in USERACTION.OPERATIME%TYPE,
useraction out ref_types.ref_cursor
);
End;
/ Create or Replace package body QUERY_ACTION_BY_CONDITION as
procedure Get_useraction (
username_str in USERACTION.USERNAME%TYPE,
query_type in integer,
start_time_str in USERACTION.OPERATIME%TYPE,
end_time_str in USERACTION.OPERATIME%TYPE,
useraction out ref_types.ref_cursor
) is
begin
if (query_type=0) --如果是按用户名查询
then
open useraction FOR select rownum as columid,useraction.* from useraction
where username=username_str order by columid asc;
elseif (query_type=1 ) --如果是按时间查询
then
open useraction FOR select rownum as columid,useraction.* from useraction
where OPERATIME >= start_time_str
and OPERATIME <= end_time_str
order by columid asc;
else --显示所有数据
open useraction FOR select rownum as columid,useraction.* from
useraction order by columid asc;
end if;
end;
end;
/创建游标和包的语句都没问题,但实现包体的语句有问题,我错误信息截图如下:
--根据条件来查询actiontable
Create or Replace package ref_types
AS
TYPE ref_cursor is REF CURSOR;
End;
/
Create or Replace package QUERY_ACTION_BY_CONDITION as
procedure Get_useraction (
username_str in USERACTION.USERNAME%TYPE,
query_type in integer,
start_time_str in USERACTION.OPERATIME%TYPE,
end_time_str in USERACTION.OPERATIME%TYPE,
useraction out ref_types.ref_cursor
);
End;
/ Create or Replace package body QUERY_ACTION_BY_CONDITION as
procedure Get_useraction (
username_str in USERACTION.USERNAME%TYPE,
query_type in integer,
start_time_str in USERACTION.OPERATIME%TYPE,
end_time_str in USERACTION.OPERATIME%TYPE,
useraction out ref_types.ref_cursor
) is
begin
if (query_type=0) --如果是按用户名查询
then
open useraction FOR select rownum as columid,useraction.* from useraction
where username=username_str order by columid asc;
elseif (query_type=1 ) --如果是按时间查询
then
open useraction FOR select rownum as columid,useraction.* from useraction
where OPERATIME >= start_time_str
and OPERATIME <= end_time_str
order by columid asc;
else --显示所有数据
open useraction FOR select rownum as columid,useraction.* from
useraction order by columid asc;
end if;
end;
end;
/创建游标和包的语句都没问题,但实现包体的语句有问题,我错误信息截图如下:
Errors for PACKAGE BODY QUERY_ACTION_BY_CONDITION:LINE/COL ERROR
-------- -----------------------------------------------------------------
15/1 PLS-00103: Encountered the symbol "THEN" when expecting one of
the following:
:= . ( % ;24/4 PLS-00103: Encountered the symbol ";" when expecting one of the
following:
if如果图片不能正常显示,下面是文字版的错误说明
elsif(query_type = 1) ---------------这里做了修改,你看看
then open useraction FOR
select rownum as columid, useraction.*
from useraction
where OPERATIME >= start_time_str
and OPERATIME <= end_time_str
order by columid asc;