Oracle存储过程与游标结合的分页

网址：http://yaweidt.blog.163.com/blog/static/67364232201072510120151/
转：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也集成了存储过程调试的功能，下一章将写上这方面的知识。