Java_Paging_PL/SQL

CREATE OR REPLACE 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
  ------------------------------------------------------------取分页总数
  v_sql := 'select count(*) from (' || Psql || ')';
  execute immediate v_sql
    into v_count;
  Pcount := ceil(v_count / Psize);
  ------------------------------------------------------------显示任意页内容
  v_Phei := Pindex * Psize;
  v_Plow := (Pindex - 1) * Psize + 1;
  --Psql := 'select rownum rn,t.* from cd_ssxl t' ;            --要求必须包含rownum字段
  v_sql := 'select * from (' || Psql || ') where rn between ' || v_Plow ||
           ' and ' || v_Phei;

  open v_cur for v_sql;
End page;

create or replace package bluedot_pkg is

  -- Author  : GUOZHANXIAN
  -- Created : 2007-11-26 19:06:18
  -- Purpose :

  TYPE type_cur IS REF CURSOR; --定义游标变量用于返回记录集
end bluedot_pkg;









使用建包方式编写：


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
    ------------------------------------------------------------取分页总数
    v_sql := 'select count(*) from (' || Psql || ')';
    execute immediate v_sql
      into v_count;
    Pcount := ceil(v_count / Psize);
    ------------------------------------------------------------显示任意页内容
    v_Phei := Pindex * Psize;
    v_Plow := (Pindex - 1) * Psize + 1;
    --Psql := 'select rownum rn,t.* from cd_ssxl t' ;            --要求必须包含rownum字段
    v_sql := 'select * from (' || Psql || ') where rn between ' || v_Plow ||
             ' and ' || v_Phei;
 
    open v_cur for v_sql;
  End page;
end bluedot_pkg;