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
