Oracle分页存储过程

使用建包方式编写：


create or replace package bluedot_pkg is
  TYPE type_cur IS REF CURSOR; --定义游标变量用于返回记录集
  procedure page(Pindex in number,
                 Psql   in varchar2,
                 Psize  in number,
                 Pcount out number,
                 v_cur  out bluedot_pkg.type_cur);
end bluedot_pkg;


create or replace package body bluedot_pkg is
  procedure page(Pindex in number,
                 Psql   in varchar2,
                 Psize  in number,
                 Pcount out number,
                 v_cur  out bluedot_pkg.type_cur) AS
  
    v_sql   VARCHAR2(1000);
    v_count number;
    v_Plow  number;
    v_Phei  number;
  Begin
  
--select * from users where 1=1 and name =?   ------------------------------------------------------------取分页总数
    v_sql : = 'select count(*) from (' || Psql || ')';
    execute immediate v_sql