Sqlplus中调试带cursor的存储过程

1. 存储过程
2. 1. 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;
      /
   2. 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;
      /
3. Sqlplus调试代码
   1. 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;
      /
4. 总结
   1. pl/sql developer 里的Command Window里面输入(Sqlplus调试代码)调试存储过程会出现“refcursor don't supported!“的错误，换到sqlplus里面调试一切正常。
5. 参考文档
   1. SQL*Plus User's Guide and Reference Release 9.2