Heim > Datenbank > MySQL-Tutorial > Oracle分页存储过程及PLSQL中的调用脚本

Oracle分页存储过程及PLSQL中的调用脚本

WBOY
Freigeben: 2016-06-07 15:02:45
Original
1102 Leute haben es durchsucht

撰写过程:网上搜集测试了好多的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;
Verwandte Etiketten:
Quelle:php.cn
Erklärung dieser Website
Der Inhalt dieses Artikels wird freiwillig von Internetnutzern beigesteuert und das Urheberrecht liegt beim ursprünglichen Autor. Diese Website übernimmt keine entsprechende rechtliche Verantwortung. Wenn Sie Inhalte finden, bei denen der Verdacht eines Plagiats oder einer Rechtsverletzung besteht, wenden Sie sich bitte an admin@php.cn
Beliebte Tutorials
Mehr>
Neueste Downloads
Mehr>
Web-Effekte
Quellcode der Website
Website-Materialien
Frontend-Vorlage