Stored procedure create or replace package pkg_test as type t_cursor is ref cursor; procedure sp_getInfoCatalog (p_userId in varchar2, p_privilege in number, p_rowsPerPage in number, p_curPage in number, p_rowCount out number, cur_catalogs out t_cursor); end; / create or replace package body pkg_test as procedure sp_getInfoCatalog (p_userId in varchar2, p_privilege in number, p_rowsPerPage in number, p_curPage in number, p_rowCount out number, cur_catalogs out t_cursor) is v_sqlstr varchar2 (1024); v_sqlstrpage varchar2 (1024); v_rowsPerPage number (18); v_curPage number (18); v_pagecount number (18); v_pagemod number (1); v_rowbegin number (18); v_ rowEnd number (18); begin v_sqlstr: = 'SELECT COUNT (1) FROM TUSER'; execute immediate v_sqlstr into p_rowCount; v_sqlstr: = 'SELECT USERID, NAME, LOGINID FROM TUSER'; v_rowsPerPage: = p_rowsPerPage; if nvl (v_rowsPerPage, 0) <1 then v_rowsPerPage: = 10; end if; if p_rowCount> v_rowsPerPage then v_pageMod: = p_rowCount mod v_rowsPerPage; v_pageCount: = p_rowCount / v_rowsPerPage; if v_pageMod> 0 then v_pageCount: = v_pageCount 1; end if; v_curPage: = P_curpage; if v_curpage: = 1; end if; if v_curpage> v_pagecount kilpage: = v_pagecount;
end if; v_rowBegin: = (v_curPage - 1) * v_rowsPerPage 1; v_rowEnd: = v_rowBegin v_rowsPerPage; v_sqlstrpage: = '. SELECT USERID, NAME, LOGINID FROM (SELECT ORIGINTABLE *, ROWNUM RM FROM (' || v_sqlstr || ') ORIGINTABLE WHERE ROWNUM <: W_ROWEND) WHERE RM> =: W_ROWBEGIN'; open cur_catalogs for v_sqlstrpage using v_rowEnd, v_rowBegin; else open cur_catalogs for v_sqlstr; end if; end sp_getInfoCatalog; end pkg_test; / Sqlplus debugging code
declare v_userId tuser.userid% type; v_userName tuser.name% type; v_userLoginId tuser.loginid% type; v_rowsPerPage number (4) not null: = 2; v_curPage number (18) not null: = 4; v_rowsCount number (18); cur_info pkg_test.t_cursor; begin v_userId: = '-9999'; pkg_test.sp_getInfoCatalog (v_userid, 0, v_rowsPerPage, v_curPage, v_rowsCount, cur_info); dbms_output.put_line ( 'rowsCount =' || v_rowsCount); loop fetch cur_info into v_userId, v_username, v_userloginid; exit when cur_info% notfound; dbms_output.put_line ('userid =' || v_userid || 'name =' || v_username); end loop; close cur_info; end; / summary
In the Command WINDOPER in PL / SQL Developer, enter (SQLPLUS debug code) debug stored procedure The "Refcursor Don't supported!" error will appear, change everything in SQLPLUS to debug everything. Reference documentation
SQL * Plus User's Guide and Reference Release 9.2
