oracle动态游标分页排序问题,[转载]转：Oracle存储过程与游标结合的分页

参考了网上各种Oracle的分页存储过程，今天贴上我自己实现的集分页、查询与排序等功能的存储过程。只要以分页为主，这个分页结合了游标、存储

过程、程序包与子程序、游标与存储过程的声明或定义等！(本人用的Oracle版本为：10g)

1、打开PL/SQL

Developer客户端，用有足够权限的用户登录，因为在Oracle数据库里，权限是处理得很严格，很多操作要授权才能操作的。

在PL/SQL

Developer左边找到Packages右键创建一个包，用于定义游标与存储过程的声明。单纯创建一个带游标的存储过程的分页其实不需要程序包的，建

这些包无非就是在大项目里，较多的存储过程下便于管理与调用而已，在这里只是为了学习这方便的知识而用的。

创建一个包：PACK_PAGE

在包里写以下语句：

create or replace package pack_page is

type page_cursor is ref

cursor ;--定义游标

end pack_page;

2、在Procedures里右键创建一个存储过程proc_pageFull，语句以下：

create or replace procedure proc_pageFull

(

p_tableName varchar2,--表名

p_pageIndex number,--当前页码

p_pageSize number,--每页记录数

p_pageCount out number,--总页数

p_totalCount out number,--总记录数

v_cursor out pack_page.page_cursor,--返回的结果集

p_where varchar2,--查询条件

p_order_key varchar2,--排序关键字(asc desc)

p_order_column varchar2--排序字段

)

is

v_pageIndex number;

--当前页

v_pageSize number;--每页记录数

v_startCurs number;--游标开始

v_endCurs number;--游标结束

v_sql

varchar2(2000);--保存SQL语句的变量

--输出参数可以对其进行运算操作，所以声明为out的参数在此不用定义变量来操作

begin

v_sql:='select count(*) from

'|| p_tableName;-- ||'where 1=1';

if p_where is not null or

p_where <>'' then

v_sql:=v_sql||'

where '||p_where;

end

if;

execute

immediate v_sql into p_totalCount;--查询总记录数

p_pageCount:=ceil(p_totalCount/p_pageSize);--计算总页数

v_pageSize:= p_pageSize;

if v_pageSize<0

then

v_pageSize:=0;

end if;

v_pageIndex:=p_pageIndex;

if v_pageIndex<0

then --判断当前页是否小于0

v_pageIndex:=1;

end if;

if

v_pageIndex>p_pageCount then --判断当前页是否超过最大页

v_pageIndex:=p_pageCount;

end if;

v_startCurs:=(v_pageIndex-1)*v_pageSize+1;--计算游标的开始位置

v_endCurs:=v_pageIndex*v_pageSize;中心--计算游标的结尾位置

v_sql:='select * from (select

rownum num,t.* from (select * from

'||p_tableName;--SQL语句的开始部分

if p_where is not null or

p_where<>''

then --判断是否有查询的条件传进来，有则加上

v_sql:=v_sql||'

where '||p_where;

end

if;

if

p_order_column is not null or

p_order_column<>''

then --判断是否有排序条件传进来，有则加上

v_sql:=v_sql||'

order by '||p_order_column||' '||p_order_key;

end

if;

v_sql:=v_sql||') t where

rownum<='||v_endCurs||')where

num>='||v_startCurs;--SQL语句的结尾部分

dbms_output.put_line(v_sql);

--打印输出一整条SQL语句

open v_cursor for v_sql;

--打开游标并动态执行v_sql里保存的语句

end proc_pageFull;

至此，整个分页的存储过程就结束了。如果要把这个存储过程再封装的话，就要将它放在子程序包里了。

3、将存储过程封装，便于在项目程序里调用：

将刚才创建并定义有游标那个包打开，添加以下语句(即定义存储过程的声明)，完整的语句如下：

create or replace package pack_page is --子程序包

type page_cursor is ref

cursor ;--游标的声明

procedure proc_pageFull

--分页的存储过程声明

(

p_tableName varchar2,--表名

p_pageIndex number,--当前页码

p_pageSize number,--每页记录数

p_pageCount out number,--总页数

p_totalCount out number,--总记录数

v_cursor out pack_page.page_cursor,--返回的结果集

p_where varchar2,--查询条件

p_order_key varchar2,--排序关键字(asc desc)

p_order_column varchar2--排序字段

);

end pack_page;

PL/SQL Developer左边找到Package

bodies右键创建一个子程序包：pack_page(注意这里的命名要以刚才那个包名相同)完整语句如下：

create or replace package body pack_page is

procedure proc_pageFull

(

p_tableName varchar2,--表名

p_pageIndex number,--当前页码

p_pageSize number,--每页记录数

p_pageCount out number,--总页数

p_totalCount out number,--总记录数

v_cursor out pack_page.page_cursor,--返回的结果集

p_where varchar2,--查询条件

p_order_key varchar2,--排序关键字(asc desc)

p_order_column varchar2--排序字段

)

is

v_pageIndex number;

v_pageSize number;

v_startCurs number;

v_endCurs number;

v_sql varchar2(2000);

--输出参数可以对其进行运算操作，所以声明为out的参数在此不用定义变量来操作

begin

v_sql:='select count(*) from

'|| p_tableName;-- ||'where 1=1';

if p_where is not null or

p_where <>'' then

v_sql:=v_sql||'

where '||p_where;

end

if;

execute

immediate v_sql into p_totalCount;--查询总记录数

p_pageCount:=ceil(p_totalCount/p_pageSize);--计算总页数

v_pageSize:= p_pageSize;

if v_pageSize<0

then

v_pageSize:=0;

end if;

v_pageIndex:=p_pageIndex;

if v_pageIndex<0

then

v_pageIndex:=1;

end if;

if

v_pageIndex>p_pageCount then

v_pageIndex:=p_pageCount;

end if;

v_startCurs:=(v_pageIndex-1)*v_pageSize+1;

v_endCurs:=v_pageIndex*v_pageSize;

v_sql:='select * from (select

rownum num,t.* from (select * from '||p_tableName;

if p_where is not null or

p_where<>'' then

v_sql:=v_sql||'

where '||p_where;

end

if;

if

p_order_column is not null or

p_order_column<>'' then

v_sql:=v_sql||'

order by '||p_order_column||' '||p_order_key;

end

if;

v_sql:=v_sql||') t where

rownum<='||v_endCurs||')where

num>='||v_startCurs;

dbms_output.put_line(v_sql);

open v_cursor for v_sql;

end proc_pageFull;

end pack_page;

至此封装在子程序包里的分页存储过程也写好了，以后要调用时，用包名.存储过程名即可。

写好的存储过程除了在PL/SQL

Developer验证没有语法错误外，怎么知道自己写的存储过程是否被执行成功呢？其实PL/SQL

Developer也集成了存储过程调试的功能，下一章将写上这方面的知识。

From：http://hi.baidu.com

/comrd/blog/item/9afb79fa865415d4b48f3199.html