---
title: "ORACLE高效分页存储过程代码"
author: "winzenghua"
date: "2009-04-03T21:44:00Z"
tags: []
slug: "oracle"
draft: false
lastmod: "2009-04-03T21:44:00Z"
id: 987886
url: "https://java4gis.iteye.com/blog/987886"
---

ORACLE高效分页存储过程代码  
create or replace procedure P\_Pagination  
(  
Pindex in number, --页号 从1开始  
Psql in varchar2, --查询语句  
Psize in number, --每页分多少行  
Pcount out number, --共有多少页  
v\_cur out Sys\_Refcursor --返回数据集  
)  
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 := v\_Phei - Psize + 1;  
v\_sql := 'select * from (select rownum rn,t.* from (' || Psql || ')t) where rn between ' || v\_Plow || ' and ' || v\_Phei ;

open v\_cur for v\_sql;

End P\_Pagination;