oracle大数据量分页存储过程

最近开始在项目中使用oracle数据库，针对项目中的大数据量搜索效率变得奇慢无比，学习采用分页方式来解决，综合网上的资料最终搞定。

oracle存储过程需要写在包里，代码如下：

包定义：

  CREATE OR REPLACE PACKAGE DotNet is
--****************************************************************
  TYPE type_cur IS REF CURSOR;     --定义游标变量用于返回记录集
  PROCEDURE DotNetPagination
  (
  Pindex in number,                --分页索引
  Psql in varchar2,                --产生dataset的sql语句
  Psize in number,                 --页面大小
  Pcount out number,               --返回分页总数
  v_cur out type_cur               --返回当前页数据记录
  );
  procedure DotNetPageRecordsCount
  (
  Psqlcount in varchar2,           --产生dataset的sql语句
  Prcount   out number             --返回记录总数
  );
end DotNet;
--****************************************************************

包体定义：REATE OR REPLACE PACKAGE Body DotNet is
--***************************************************************************************
PROCEDURE DotNetPagination
(
Pindex in number,
Psql in varchar2,
Psize in number,
Pcount out number,
v_cur out 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 + Psize;
v_Plow := v_Phei - 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 ;

v_sql := 'SELECT *
FROM (
SELECT A.*, rownum rn
FROM ('|| Psql ||') A
WHERE rownum <= '|| to_char(v_Phei) || '
) B
WHERE rn >= ' || to_char(v_Plow) ;
--注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn


open v_cur for v_sql;
End DotNetPagination;

--**************************************************************************************
procedure DotNetPageRecordsCount
(
Psqlcount in varchar2,
Prcount out number
)
as
v_sql varchar2(1000);
v_prcount number;
begin
v_sql := 'select count(*) from (' || Psqlcount || ')';
execute immediate v_sql into v_prcount;
Prcount := v_prcount; --返回记录总数
end DotNetPageRecordsCount;
--**************************************************************************************
end DotNet;

CREATE OR REPLACE PACKAGE Body DotNet is
 --***************************************************************************************
  PROCEDURE DotNetPagination
  (
  Pindex in number,
  Psql in varchar2,
  Psize in number,
  Pcount out number,
  v_cur out 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 + Psize;
  v_Plow := v_Phei - Psize + 1;
  
  v_sql := 'SELECT * 
            FROM (
                  SELECT A.*, rownum rn 
                  FROM  ('|| Psql ||') A
                  WHERE rownum <= '|| to_char(v_Phei) || '
                 ) B
            WHERE rn >= ' || to_char(v_Plow) ;
            --注意对rownum别名的使用,第一次直接用rownum,第二次一定要用别名rn

  
  open v_cur for v_sql;
  End DotNetPagination;

 --**************************************************************************************
  procedure DotNetPageRecordsCount
  (
  Psqlcount in varchar2,
  Prcount   out number
  )
  as
  v_sql varchar2(1000);
  v_prcount number;
  begin
  v_sql := 'select count(*) from (' || Psqlcount || ')';
  execute immediate v_sql into v_prcount;
  Prcount := v_prcount;                  --返回记录总数
  end DotNetPageRecordsCount;
 --**************************************************************************************
end DotNet;

实际运用发现查询的速度还是不快（因为我的查询时联合4个表，其中还有图片表），所以仍在考虑怎么对性能调优，下一步再总结。