Oracle分頁預存程序

--建立包規範
create or replace package package_page as
  type cursor_page is ref cursor;
  Procedure proc_page(
             p_curpage Number,                     --當前頁
             p_pagesize Number,                   --每頁大小
             p_tablename varchar2,                --表名emp e
             p_where varchar2,                       --查詢條件e.ename like '%S%'
             p_tablecolumn varchar2,             --查詢列e.id,e.ename,e.job
             p_order varchar2,                         --排序e.ename desc
             p_rowcount out Number,             --總條數,輸出參數
             p_pagecount out number,            --總頁數
             p_cursor out cursor_page);        --結果集
end package_page;

--建立包主休
Create Or Replace Package Body package_page
Is
       --預存程序
      Procedure proc_page(

　　　　p_curpage Number,                     --當前頁
             p_pagesize Number,                   --每頁大小
             p_tablename varchar2,                --表名emp e
             p_where varchar2,                       --查詢條件e.ename like '%S%'
             p_tablecolumn varchar2,             --查詢列e.id,e.ename,e.job
             p_order varchar2,                         --排序e.ename desc
             p_rowcount out Number,             --總條數,輸出參數
             p_pagecount out number,            --總頁數
             p_cursor out cursor_page　　　　--結果集
      )
      is
            v_count_sql varchar2(2000);
            v_select_sql varchar2(2000);
      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_curpage>=1 and p_curpage<=p_pagecount then
              
               --查詢所有(只有一頁)
               if p_rowcount<=p_pagesize then
                  v_select_sql:='select '||p_tablecolumn||' from '||p_tablename;
                  if p_where is not null then
                     v_select_sql:=v_select_sql||' where '||p_where;
                  end if;
                  if p_order is not null then
                      v_select_sql:=v_select_sql||' order by '||p_order;
                  end if;
               elsif p_curpage=1 then  --查詢第一頁
                  v_select_sql:='select '||p_tablecolumn||' from '||p_tablename;
                  if p_where is not null then
                     v_select_sql:=v_select_sql||' where '||p_where||' and rownum<='||p_pagesize;
                  else
                     v_select_sql:=v_select_sql||' where rownum<='||p_pagesize;
                  end if;
                  if p_order is not null then
                      v_select_sql:=v_select_sql||' order by '||p_order;
                  end if;
               else      --查詢指定頁
                  v_select_sql:='select * from (select '|| p_tablecolumn ||',rownum row_num from '|| p_tablename;
                  if p_where is not null then
                     v_select_sql:=v_select_sql||' where '||p_where;
                  end if;
                  if p_order is not null then
                      v_select_sql:=v_select_sql||' order by '||p_order;
                  end if;
                  v_select_sql:=v_select_sql||') where row_num>'||((p_curpage-1)*p_pagesize)||' and row_num<='||(p_curpage*p_pagesize);
               end if;
               --執行查詢
               dbms_output.put_line('查詢語句=>'||v_select_sql);
               open p_cursor for v_select_sql;
            end if;
           
      end proc_page;
end package_page;

----------------測試----------------
declare
       v_rowcount number(5,0);
       v_pagecount number;
       v_cursor package_page.cursor_page;
begin 
       package_page.proc_page(2,2,'emp e','ename like ''%S%''','e.*','ename desc',v_rowcount,v_pagecount,v_cursor);
       dbms_output.put_line(v_rowcount);
       dbms_output.put_line(v_pagecount);

 

 

 

 

 

--freedom修改後--

 

 

--建立包規範
create or replace package package_page as
  type cursor_page is ref cursor;
  Procedure proc_page(
             p_select_sql varchar2,           --查詢語句
             p_count_sql varchar2,            --查詢總記錄數語句
             p_rowcount out Number,           --總行數
             cursor_page out package_page.cursor_page);       --結果集
end package_page;

--建立包主休

CREATE OR REPLACE Package Body package_page Is
  --預存程序
  Procedure proc_page(p_select_sql varchar2,
                      p_count_sql  varchar2,
                      p_rowcount   out Number,
                      cursor_page  out package_page.cursor_page) is

  begin
    --查詢總條數
    --執行查詢,查詢總條數
    execute immediate p_count_sql
      into p_rowcount;

    --如果查詢記錄大於0則查詢結果集
    if p_rowcount > 0 then
      --執行查詢
      open cursor_page for p_select_sql;
    else
      open cursor_page for select * from dual;
    end if;
  end proc_page;
end package_page;