撰写过程:网上搜集测试了好多的oracle分页存储过程代码,经整理后终于通过测试,特分享给大家 测试步骤:1、运行创建包命令;2、运行创建存储过程命令;3、运行调用分页存储过程语句 测试环境:windows2003+oracle11g+plsql developer --1、创建包命令 create or r
撰写过程:网上搜集测试了好多的oracle分页存储过程代码,经整理后终于通过测试,特分享给大家
测试步骤:1、运行创建包命令;2、运行创建存储过程命令;3、运行调用分页存储过程语句
测试环境:windows2003+oracle11g+plsql developer
--1、创建包命令
create or replace package mypackage as
  type cursor_page is ref cursor;
  procedure myprocdure(
             p_tablename varchar2,              --表名
             p_fields varchar2,                 --查询列
             p_orderby varchar2,                --排序
             p_where varchar2,                  --查询条件
             p_pagesize number,                 --每页大小
             p_pageindex number,                --当前页
             p_rowcount out number,             --总条数,输出参数
             p_pagecount out number,            --总页数
             p_cursor out cursor_page);         --结果集
end mypackage;
--2、创建存储过程命令
create or replace package body mypackage
is
       --存储过程
      procedure myprocdure(
             p_tablename varchar2,              --表名
             p_fields varchar2,                 --查询列
             p_orderby varchar2,                --排序
             p_where varchar2,                  --查询条件
             p_pagesize number,                 --每页大小
             p_pageindex number,                --当前页
             p_rowcount out number,             --总条数,输出参数
             p_pagecount out number,            --总页数
             p_cursor out cursor_page           --结果集
      )
      is
            v_count_sql varchar2(2000);
            v_select_sql varchar2(2000);
s_tablename nvarchar2(255);--分页表名
begin
            --查询总条数
            v_count_sql:='select count(*) from '||p_tablename;
            --连接查询条件(''也属于is null)
            if p_where is not null  then
               v_count_sql:=v_count_sql||' where '||p_where;
            end if;
            --执行查询,查询总条数
            execute immediate v_count_sql into p_rowcount;
--dbms_output.put_line('查询总条数sql=>'||v_count_sql);
            --dbms_output.put_line('查询总条数count='||p_rowcount);
--得到总页数
             if mod(p_rowcount,p_pagesize)=0 then
                p_pagecount:=p_rowcount/p_pagesize;
             else
                p_pagecount:=p_rowcount/p_pagesize+1;
             end if;
--如果查询记录大于0则查询结果集
            if p_rowcount>0 and p_pageindex>=1 and p_pageindex
               --查询所有(只有一页)
               if p_rowcount                  v_select_sql:='select '||p_fields||' from '||p_tablename;
                  if p_where is not null then
                     v_select_sql:=v_select_sql||' where '||p_where;
                  end if;
                  if p_orderby is not null then
                      v_select_sql:=v_select_sql||' order by '||p_orderby;
                  end if;
               elsif p_pageindex=1 then  --查询第一页
                  v_select_sql:='select '||p_fields||' from '||p_tablename;
                  if p_where is not null then
                     v_select_sql:=v_select_sql||' where '||p_where||' and rownum                  else
                     v_select_sql:=v_select_sql||' where rownum                  end if;
                  if p_orderby is not null then
                      v_select_sql:=v_select_sql||' order by '||p_orderby;
                  end if;
               else      --查询指定页
                  if instr(p_tablename,')')>0 then                  
                  s_tablename:=replace(substr(p_tablename,instr(p_tablename,')')+1),' ','');
                  v_select_sql:='select * from (select '||s_tablename||'.' || p_fields ||',rownum row_num from '|| p_tablename;
                  else
                  v_select_sql:='select * from (select '|| p_tablename || '.' || p_fields ||',rownum row_num from '|| p_tablename;
                  end if;
if p_where is not null then
                     v_select_sql:=v_select_sql||' where '||p_where;
                  end if;
                  if p_orderby is not null then
                      v_select_sql:=v_select_sql||' order by '||p_orderby;
                  end if;
                  v_select_sql:=v_select_sql||') where row_num>'||((p_pageindex-1)*p_pagesize)||' and row_num               end if;
               --执行查询
               --dbms_output.put_line('查询语句=>'||v_select_sql);
               open p_cursor for v_select_sql;
            else
               --dbms_output.put_line('查询语句=>'||'select * from '||p_tablename||' where 1!=1');
               open p_cursor for 'select * from '||p_tablename||' where 1!=1';
            end if;
end myprocdure;
end mypackage;
--3、调用分页存储过程语句
declare
       p_tablename varchar2(2000);
       p_fields varchar2(2000);
       p_orderby varchar2(200);
       p_where varchar2(200);
       p_pagesize number;
       p_pageindex number;
       p_rowcount number;
       p_pagecount number;
       p_cursor mypackage.cursor_page ;
begin   
        p_tablename:='goodsdoc';
        p_fields:='*';
        p_orderby:='goodsname';
        p_where:='1=1';
        p_pagesize:=100;
        p_pageindex:=1;
        mypackage.myprocdure(p_tablename,p_fields,p_orderby,p_where,p_pagesize,p_pageindex,p_rowcount,p_pagecount,p_cursor);      
        dbms_output.put_line('记录总数'||p_rowcount||'页面总数'||p_pagecount);
end;