- 存储过程
- 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 then
v_curPage := 1;
end if;
if v_curPage > v_pageCount then
v_curPage := 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;
/
- create or replace package pkg_test as
- Sqlplus调试代码
- 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;
/
- declare
- 总结
- pl/sql developer 里的Command Window里面输入(Sqlplus调试代码)调试存储过程会出现“refcursor don't supported!“的错误,换到sqlplus里面调试一切正常。
- 参考文档