create or replace package my_pkg
as
type refcursor_type is ref cursor;
procedure get_emps(p_ename varchar2 default null,
p_deptno varchar2 default null,
p_cursor in out refcursor_type);
end;
/
create or replace package body my_pkg
as
procedure get_emps(p_ename varchar2 default null,
p_deptno varchar2 default null,
p_cursor in out refcursor_type);
is
l_query long;
l_bind varchar2(30);
begin
l_query:='select deptno,ename,job from emp';
if(p_ename is not null) then
l_query:=l_query||'where ename like :x';
l_bind:='%'||upper(p_ename)||'%';
elsif(p_deptno is not null) then
l_query:=l_query||'where deptno=to_number(:x)';
l_bind:=p_deptno;
else
raise_application_error(-20001,'Missing search criteria');
end if;
open p_cursor for l_query using l_bind;
end;
end;
/
var m refcursor
set autoprint on
exec my_pkg.get_emps(p_ename=>'a',p_cursor=>:m)
第1行出现错误:
ORA-00933:SQL命令未正确结束
ORA-06512:在"SCOTT.MY_PKG",line 20
ORA-06512:在line 1 为什么……
as
type refcursor_type is ref cursor;
procedure get_emps(p_ename varchar2 default null,
p_deptno varchar2 default null,
p_cursor in out refcursor_type);
end;
/
create or replace package body my_pkg
as
procedure get_emps(p_ename varchar2 default null,
p_deptno varchar2 default null,
p_cursor in out refcursor_type);
is
l_query long;
l_bind varchar2(30);
begin
l_query:='select deptno,ename,job from emp';
if(p_ename is not null) then
l_query:=l_query||'where ename like :x';
l_bind:='%'||upper(p_ename)||'%';
elsif(p_deptno is not null) then
l_query:=l_query||'where deptno=to_number(:x)';
l_bind:=p_deptno;
else
raise_application_error(-20001,'Missing search criteria');
end if;
open p_cursor for l_query using l_bind;
end;
end;
/
var m refcursor
set autoprint on
exec my_pkg.get_emps(p_ename=>'a',p_cursor=>:m)
第1行出现错误:
ORA-00933:SQL命令未正确结束
ORA-06512:在"SCOTT.MY_PKG",line 20
ORA-06512:在line 1 为什么……
引号里面最后加个空格试试