Oracle存储过程实现Spring MVC + MyBatis + ExtJS 3.4权限分页管理

create or replace procedure pageGrid
    (
       parm_pageID      in number,--页索引，从1开始
       parm_pageSize    in number,--每页大小
       parm_tblName     in varchar2,--表名
       parm_strWhere    in varchar2,--where条件
       parm_orderBy     in varchar2, --排序条件
       parm_search      in varchar2, --查询的列
        p_rowcount    out number, --总条数,输出参数
       parm_ref_cur2    out parm_fenye_package.parm_ref_cursor2 --结果集
    )
   IS
      v_sql1       VARCHAR2 (2000);                       --获取数据的sql语句
      v_sql2       VARCHAR2 (4000);
      v_pagecount    NUMBER;                                 --该条件下记录页数
      v_row_start    NUMBER;                                         --开始记录
      v_row_end      NUMBER;                                         --结束记录
      v_count        number;
   --分页获取数据
   BEGIN
      v_sql1 := 'select count(1) from ' || parm_tblName;

      IF parm_strWhere is not NULL OR parm_strWhere <> ''
      THEN
         v_sql1 := v_sql1 || ' where 1=1 ' || parm_strWhere;
      END IF;

      execute immediate v_sql1
     into p_rowcount;
     

     

      --计算数据记录开始和结束
      v_pageCount := v_count / parm_pageSize + 1;
      v_row_start := (parm_pageID - 1) * parm_pageSize + 1;
      v_row_end := parm_pageID * parm_pageSize;
      v_sql2 := 'select rownum ro, '||parm_search||' from ' || parm_tblName || ' t';
      v_sql2 := v_sql2 || ' where rownum<=' || v_row_end;

      IF parm_strWhere is not NULL OR parm_strWhere <> ''
      THEN
         v_sql2 := v_sql2  || parm_strWhere;
      END IF;

      IF parm_orderBy is not null and  parm_orderBy <> ''
      THEN
         v_sql2 := v_sql2 || ' order by ' || parm_orderBy;
      END IF;


      v_sql2 := 'select '||parm_search||' from (' || v_sql2 || ') where ro >=' || v_row_start ;

 

      OPEN parm_ref_cur2 FOR v_sql2;
   END pageGrid;
----------------------------------------------------------------------------------------------
create or replace package parm_fenye_package is
   type parm_ref_cursor1 is ref cursor;
    type parm_ref_cursor2 is ref cursor;