Sqlplus中调试带cursor的存储过程
2024-07-21 02:41:05
供稿：网友
存储过程 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里面调试一切正常。参考文档 SQL*Plus User's Guide and Reference Release 9.2
