熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

oracle分頁存儲過程

2013-11-13 16:18:46  來源: Oracle 

  create or replace package mypack

  as

  type cursor testCursor is ref cursor;

  end mypack;

  tableName表名 pageSize每頁顯示的數量pageNow第幾頁rows總頁數pageCount總頁數p_cursor游標

  create or replace procedure fenye

  (tableName in varchar pageSize in number pageNow in number rows out number pageCount out number p_cursor out mypacktestCursor)

  is

  declare

  定義sql語句

  v_sql varchar();

  定義個整數記錄上一頁

  v_begin number := (pageNow ) * pageSize + ;

  v_end number := pageNow * pageSize;

  begin

  v_sql := select * from (select t* rownum r from (select * from || tableName ||) t where rownum<=|| v_end ||) where r>=|| v_begin ;

  將游標和sql語句關聯起來

  open p_cursor for v_sql;

  計算rows和pageCount;

  v_sql := select count(*) from || tableName;

  執行sql語句並將返回的值付給 rows;

  execute immediate v_sql into rows;

  pageCount := (rows + pageSize ) / pageSize; 總頁數;

  close p_cursor;

  end;


From:http://tw.wingwit.com/Article/program/Oracle/201311/18125.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.