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;