存储过程
create or replace package pkg_test as
type t_cursor is ref cursor;
procedure sp_get_catalogs(p_userid in number, cur_catalogs out t_cursor);
end;
create or replace package body pkg_test as
procedure sp_get_catalogs(p_userid in number, cur_catalogs out t_cursor) is
v_sql varchar2(500);
begin
if p_userid = 0 then
open cur_catalogs for
select userid, loginid, name from tuser;
else
v_sql := 'select userid,loginid,name from tuser where userid=:w_userid';
open cur_catalogs for v_sql
using p_userid;
end if;
exception
when others then
dbms_output.put_line('other exception');
end sp_get_catalogs;
end pkg_test;
Sqlplus调试代码
variable v_cur refcursor;
variable v_userid number;
set autoprint off;
begin
:v_userid := -9222229;
end;
/
execute pkg_test.sp_get_catalogs(:v_userid, :v_cur);
print v_cur;
总结
pl/sql developer 里的Command Window里面输入(Sqlplus调试代码)调试存储过程会出现“refcursor don't supported!“的错误，换到sqlplus里面调试一切正常。
参考文档