ORACLE 自定义分页存储过程

一、创建包

CREATE OR REPLACE PACKAGE PKG_JK_LAB_BASIC
IS
    TYPE CURSOR_TYPE IS REF CURSOR; 
    
    PROCEDURE SP_GET_PAGINATION (
        pi_tableName        in  varchar2,   --表名
        pi_where            in  varchar2,   --查询条件
        pi_columns          in  varchar2,   --查询列集合
        pi_orderColumn      in  varchar2,   --排序的列 
        pio_curPage         in out Number,  --当前页
        pio_pageSize        in out Number,  --每页显示记录条数
        po_totalRecords     out Number,     --总记录数
        po_totalPages       out Number,     --总页数
        po_cur              out CURSOR_TYPE);
        
 END PKG_JK_LAB_BASIC;

 二、创建包体

CREATE OR REPLACE PACKAGE BODY PKG_JK_LAB_BASIC
IS

     PROCEDURE SP_GET_PAGINATION
       (pi_tableName        in  varchar2,   --表名
        pi_where            in  varchar2,   --查询条件
        pi_columns          in  varchar2,   --查询列集合
        pi_orderColumn      in  varchar2,   --排序的列
        pio_curPage         in out Number,  --当前页
        pio_pageSize        in out Number,  --每页显示记录条数
        po_totalRecords     out Number,     --总记录数
        po_totalPages       out Number,     --总页数
        po_cur              out CURSOR_TYPE)   --返回的结果集
      IS
       v_sql VARCHAR2(1000) := '';      --sql语句
       v_startRecord Number(4);         --开始显示的记录条数
       v_endRecord Number(4);           --结束显示的记录条数
       v_where VARCHAR2(500) := ''; 
       v_orderColumn VARCHAR2(200) := ''; 
       v_columns VARCHAR2(200) := ''; 
      BEGIN
       --记录中总记录条数
       v_sql := 'SELECT TO_NUMBER(COUNT(*)) FROM ' || pi_tableName;

       v_where := TRIM(NVL(pi_where,''));    
       IF LENGTH(v_where)>0  THEN
           v_where := ' WHERE ' || pi_where || ' ';
           v_sql := v_sql || v_where;
       END IF;
       
       v_orderColumn := TRIM(NVL(pi_orderColumn,''));
       IF LENGTH(v_orderColumn)>0 THEN
           v_orderColumn := ' ORDER BY ' || v_orderColumn  || ' ';
       END IF;

       EXECUTE IMMEDIATE v_sql INTO po_totalRecords;

       IF pio_pageSize <= 0 THEN
           pio_pageSize := 10;
       END IF;

       --根据页大小计算总页数
       po_totalPages := CEIL( po_totalRecords / pio_pageSize);

       IF pio_curPage < 1 THEN
           pio_curPage := 1;
       END IF;

       IF pio_curPage > po_totalPages THEN
           pio_curPage := po_totalPages;
       END IF;

       --实现分页查询
       v_startRecord := (pio_curPage - 1) * pio_pageSize + 1;
       v_endRecord := pio_curPage * pio_pageSize;   
       v_columns := TRIM(NVL(pi_columns,''));
       
       IF LENGTH(v_columns)>0 AND v_columns <> '*' THEN
         v_sql := 'SELECT * FROM (SELECT '|| v_columns ||', ROWNUM RowNumber FROM ' ||
                    '(SELECT '|| v_columns ||' FROM ' || pi_tableName || v_where || v_orderColumn || '))'||
                    ' WHERE RowNumber <= ' || v_endRecord || ' AND RowNumber >= ' || v_startRecord ;      
       ELSE
           v_sql := 'SELECT * FROM (SELECT A.*, ROWNUM RowNumber FROM ' ||
                    '(SELECT * FROM ' || pi_tableName || v_where || v_orderColumn || ') A )'||
                    ' WHERE RowNumber <= ' || v_endRecord || ' AND RowNumber >= ' || v_startRecord ;
       END IF;
       
       DBMS_OUTPUT.put_line(v_sql);

       OPEN po_cur FOR v_sql;

      END SP_GET_PAGINATION;

 END PKG_JK_LAB_BASIC;