Oracle分页存储过程

首先在oracle中创建包，包可以比喻成篮子，存储过程好比是苹果，好多个苹果可以放在一个篮子中。
//第一步
create or replace package JT_P_page is
type type_cur is ref cursor;                    --定义游标变量用于返回记录集
procedure Pagination  (Pindex in number,        --要显示的页数索引，从0开始
                       Psql in varchar2,        --产生分页数据的查询语句
                       Psize in number,         --每页显示记录数
                       Pcount out number,       --返回的分页数
                       Prowcount out number,    --返回的记录数
                       v_cur out type_cur      --返回分页数据的游标
                       );
end JT_P_page;
--定义包主体
create or replace package body JT_P_page is
  procedure Pagination(Pindex in number, --要显示的页数索引，从0开始
                       Psql in varchar2, --产生分页数据的查询语句
                       Psize in number, --每页显示记录数
                       Pcount out number, --返回的分页数
                       Prowcount out number, --返回的记录数
                       v_cur out type_cur --返回分页数据的游标
                       ) AS
    v_sql VARCHAR2(1000);
    v_Pbegin number;
    v_Pend number;
  begin
    v_sql := 'select count(*) from (' || Psql || ')';
    execute immediate v_sql into Prowcount; --计算记录总数
    Pcount := ceil(Prowcount / Psize); --计算分页总数
    --显示任意页内容
    v_Pend := Pindex * Psize + Psize;
    v_Pbegin := v_Pend - Psize + 1;    
    v_sql :=  'select * from (' || Psql || ') where rn between ' || v_Pbegin || ' and ' || v_Pend;         
    open v_cur for v_sql;
  end Pagination; 
end JT_P_page;

/// <summary>
        /// 調用存儲過程，返回DataTable
        /// </summary>
        /// <param name="index"></param>
        /// <param name="sql"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public static DataTable ReturnDataTable(int index, string sql, int pageSize)
        {
            DataTable dt = new DataTable();
            try
            {
                OracleParameter[] param = new OracleParameter[] { new OracleParameter("Pindex", OracleType.Number), new OracleParameter("Psql", OracleType.VarChar), new OracleParameter("Psize", OracleType.Number), new OracleParameter("Pcount", OracleType.Number), new OracleParameter("Prowcount", OracleType.Number), new OracleParameter("v_cur", OracleType.Cursor) };
                param[0].Value = index;
                param[1].Value = sql;
                param[2].Value = pageSize;

                param[0].Direction = ParameterDirection.Input;
                param[1].Direction = ParameterDirection.Input;
                param[2].Direction = ParameterDirection.Input;
                param[3].Direction = ParameterDirection.Output;
                param[4].Direction = ParameterDirection.Output;
                param[5].Direction = ParameterDirection.Output;
                dt = OracleHelper.ExecuteDataset(CommandType.StoredProcedure, "JT_P_page.Pagination", param).Tables[0];
            }
            catch (OracleException on)
            {
                throw on;
            }
            return dt;
        }
//第二步
/// <summary>
        /// 調用存儲過程，返回DataTable
        /// </summary>
        /// <param name="index"></param>
        /// <param name="sql"></param>
        /// <param name="pageSize"></param>
        /// <returns></returns>
        public static DataTable ReturnDataTable(int index, string sql, int pageSize)
        {
            DataTable dt = new DataTable();
            try
            {
                OracleParameter[] param = new OracleParameter[] { new OracleParameter("Pindex", OracleType.Number), new OracleParameter("Psql", OracleType.VarChar), new OracleParameter("Psize", OracleType.Number), new OracleParameter("Pcount", OracleType.Number), new OracleParameter("Prowcount", OracleType.Number), new OracleParameter("v_cur", OracleType.Cursor) };
                param[0].Value = index;
                param[1].Value = sql;
                param[2].Value = pageSize;

                param[0].Direction = ParameterDirection.Input;
                param[1].Direction = ParameterDirection.Input;
                param[2].Direction = ParameterDirection.Input;
                param[3].Direction = ParameterDirection.Output;
                param[4].Direction = ParameterDirection.Output;
                param[5].Direction = ParameterDirection.Output;
                dt = OracleHelper.ExecuteDataset(CommandType.StoredProcedure, "JT_P_page.Pagination", param).Tables[0];
            }
            catch (OracleException on)
            {
                throw on;
            }
            return dt;
        }
//第三步，绑定表格数据
GridView1.DataSource = DemoDAO.ReturnDataTable(0, "select rownum rn,name,score1 from y1", AspNetPager1.PageSize);
GridView1.DataBind();
AspNetPager1.RecordCount = int.Parse(OracleHelper.GetSingle("select count(*) from y1").ToString());
//第四步，分页事件
GridView1.DataSource = DemoDAO.ReturnDataTable(AspNetPager1.CurrentPageIndex - 1, "select rownum rn,name,score1 from y1", AspNetPager1.PageSize);
        GridView1.DataBind();
        AspNetPager1.RecordCount = int.Parse(OracleHelper.GetSingle("select count(*) from y1").ToString());
Aspnetpager用法：
AspNetPager1.CustomInfoHTML = "记录总数：<font color=\"blue\"><b>" + AspNetPager1.RecordCount.ToString() + "</b></font>";  
        AspNetPager1.CustomInfoHTML += " 总页数：<font color=\"blue\"><b>" + AspNetPager1.PageCount.ToString() + "</b></font>";  
        AspNetPager1.CustomInfoHTML += " 当前页：<font color=\"red\"><b>" + AspNetPager1.CurrentPageIndex.ToString() + "</b></font>"; 
// aspx頁面屬性設置
<webdiyer:aspnetpager runat="server" ID="DemoPager" AlwaysShow="True"
            HorizontalAlign="Right" NumericButtonCount="5"
            NumericButtonTextFormatString="-{0}-" onpagechanging="DemoPager_PageChanging"
            PageSize="5" ShowCustomInfoSection="Left" ShowPageIndexBox="Always"
            Width="600px"></webdiyer:aspnetpager>
//綁定表格數據，DemoPager為控件的ID
private void BindGridData()
    {
        GridView1.DataSource = DemoManager.GetDemoData(DemoPager.PageSize*(DemoPager.CurrentPageIndex-1),DemoPager.PageSize);
        GridView1.DataBind();
        DemoPager.RecordCount = DemoManager.GetDemoSum();
        DemoPager.CustomInfoHTML = "記錄總數：<font color=\"blue\"><b>" + DemoPager.RecordCount.ToString() + "</b></font>" +
            "  共 " + DemoPager.PageCount.ToString() + " 頁 " + "     當前為第" + DemoPager.CurrentPageIndex.ToString()+"  頁  "+"      每頁"+
            DemoPager.PageSize.ToString()+"條";    }
//獲得表格數據，寫在DAL層，傳入開始記錄的索引值和每頁最大記錄數
public static DataTable GetDemoData(int pStartIndex, int pPageSize)
        {
            DataSet vDs = new DataSet();
            using (SqlConnection vConn = new SqlConnection(mConnStr))
            {
                using (SqlDataAdapter vDa = new SqlDataAdapter("select * from demo", vConn))
                {
                    vDa.Fill(vDs, pStartIndex, pPageSize, "Demo");
                }
            }
            return vDs.Tables[0];
        }
//獲得記錄總數,寫在DAL層
public static int GetDemoSum()
        {
            int vFlag;
            using (SqlConnection vConn = new SqlConnection(mConnStr))
            {
                SqlCommand vCmd = new SqlCommand("select count(*) from demo", vConn);
                vConn.Open();
                 vFlag = int.Parse(cmd.ExecuteScalar().ToString()); //拆箱
            }
            return vFlag;
        }
//  分頁事件
protected void AspNetPager1_PageChanging(object src, Wuqi.Webdiyer.PageChangingEventArgs e)
    {
        AspNetPager1.CurrentPageIndex = e.NewPageIndex;
        BindGridData();
    }
//以上方法是在頁面寫Sql,可以利用存儲過程，
BindGridData方法只修改一句，如下
GridView1.DataSource = DemoManager.GetDemoData(DemoPager.CurrentPageIndex,DemoPager.PageSize);

public static DataTable GetDemoData(int pStartIndex, int pPageSize)
        {
            DataSet vDs = new DataSet();
            using (SqlConnection vConn = new SqlConnection(mConnStr))
            {
                SqlCommand vCmd = vConn.CreateCommand();
                vCmd.CommandType = CommandType.StoredProcedure;
                vCmd.CommandText = "SelectDemo";
                vCmd.Parameters.AddWithValue("@StartIndex",pStartIndex);
                vCmd.Parameters.AddWithValue("@Pagesize",pPageSize);
                SqlDataAdapter vDa = new SqlDataAdapter(vCmd);
                vDa.Fill(vDs);
            }
            return vDs.Tables[0];
        }



此外还有一个分页存储过程，暂且记下，有时间我要比较下，吸收优点，写个总的

/* Formatted on 2010/08/04 14:29 (Formatter Plus v4.8.8) */
CREATE OR REPLACE PROCEDURE prc_query (
   p_tablename      IN       VARCHAR2,
   p_curpage        IN OUT   NUMBER,
   p_begin_time     IN       VARCHAR2,
   p_end_time       IN       VARCHAR2,
   p_pagesize       IN OUT   NUMBER,
   p_totalrecords   OUT      NUMBER,
   p_totalpages     OUT      NUMBER,
   v_cur            OUT      pkg_query.cur_query
)
IS
   v_sql           VARCHAR2 (10000) := '';                     --SQL language
   v_startrecord   NUMBER (4);                            ---開始顯示的記錄數
   v_endrecord     NUMBER (4);                           --結束顯示的記錄條件
   p_tablename     VARCHAR2 (10000)
      := 'select distinct (select p.family_name from product_family_info p)  product_family_name,rownum,
   '' '' product_family_desc,decode(rsg.group_level, ''ROA'', ''Main Board'', ''Core Product'') product_type_name,
    '' '' product_type_desc,rh.product_name,
            rh.product_rstate as product_revision,
            decode(rsg.group_level,
                   ''ROA'',
                   ''ROA Products'',
                   ''KRH and 1/KRH Products'') product_desc,
           
            ''1'' product_status,
            ''0'' product_clf_switch,
            '' '' ProductFamily_Id,
            '' '' ProductBase_Id,
            '' '' ProductType_Id,
            '' '' Product_Id
       from routing_history rh, routing_station_group rsg
      where rh.station_group = rsg.group_id and rsg.table_name is not null
        and rh.time_end >= to_date('''
         || p_begin_time
         || ''', ''yyyy/mm/dd HH24:MI:SS'')
    and rh.time_end <= to_date('''
         || p_end_time
         || ''', ''yyyy/mm/dd HH24:MI:SS'')';
BEGIN
   v_sql :=
         'select to_number(count(*)) from '
      || '('
      || p_tablename
      || ')'
      || 'where 1=1';

   EXECUTE IMMEDIATE v_sql
                INTO p_totalrecords;

   IF p_pagesize < 0
   THEN
      p_pagesize := 0;
   END IF;

   IF MOD (p_totalrecords, p_pagesize) = 0
   THEN
      p_totalpages := p_totalrecords / p_pagesize;
   ELSE
      p_totalpages := p_totalrecords / p_pagesize + 1;
   END IF;

   IF p_curpage < 1
   THEN
      p_curpage := 1;
   END IF;

   IF p_curpage > p_totalpages
   THEN
      p_curpage := p_totalpages;
   END IF;

   v_startrecord := (p_curpage - 1) * p_pagesize + 1;
   v_endrecord := p_curpage * p_pagesize;
   v_sql :=
         'select * from (select A.*, rownum r from '
      || '(select * from '
      || '('
      || p_tablename
      || ')'
      || 'where 1=1';
   v_sql :=
         v_sql
      || ')A where rownum<='
      || v_endrecord
      || ') B where r>='
      || v_startrecord;
   DBMS_OUTPUT.put_line (v_sql);

   OPEN v_cur FOR v_sql;
END prc_query;

//分頁存儲過程

CREATE OR REPLACE PACKAGE NEWHR.pkg_page
/*****************************************************************
*功能描述: 大數據量分頁通用存儲過程
*創 建 人: 丁樂進
*創建時間: 2010-08-11
*****************************************************************/
IS
   TYPE t_cursor IS REF CURSOR;

   PROCEDURE prog_page (
      p_pagesize               INT,                              --每頁記錄數
      p_pageno                 INT,                      --當前頁碼,從 1 開始
      p_sqlselect              VARCHAR2,                    --查詢語句,含排序部分
      --p_outrecordcount   OUT   INT,                            --返回總記錄數
      p_outcursor        OUT   t_cursor
   );
END;
CREATE OR REPLACE PACKAGE BODY NEWHR.pkg_page
/*****************************************************************
*功能描述: 大數據量分頁通用存儲過程
*創 建 人: 丁樂進
*創建時間: 2010-08-11
*****************************************************************/
IS
   PROCEDURE prog_page (
      p_pagesize               INT,                              --每頁記錄數
      p_pageno                 INT,                      --當前頁碼,從 1 開始
      p_sqlselect              VARCHAR2,                --查詢語句,含排序部分
      --p_outrecordcount   OUT   INT,                            --返回總記錄數
      p_outcursor        OUT   t_cursor
   )
   AS
      v_sql         VARCHAR2 (32767);
      v_count       INT;
      v_heirownum   INT;
      v_lowrownum   INT;
   BEGIN
      ----取記錄總數

      --v_sql := 'select count(1) from (' || p_sqlcount || ')';
      --EXECUTE IMMEDIATE v_sql INTO v_count;
      --p_outrecordcount := v_count;
      ----執行分頁查詢
      v_heirownum := p_pageno * p_pagesize;
      v_lowrownum := v_heirownum - p_pagesize + 1;
      v_sql := 'WITH Temp AS (' || p_sqlselect || ')SELECT * FROM (SELECT ROWNUM rn,T.*,(SELECT COUNT(1) FROM Temp) AS Record_Count FROM Temp T WHERE rownum <= '
         || TO_CHAR (v_heirownum)
         || '
                 ) B
            WHERE rn >= '
         || TO_CHAR (v_lowrownum);

      --注意對rownum別名的使用,第一次直接用rownum,第二次一定要用別名rn
      OPEN p_outcursor
       FOR v_sql;
   END prog_page;
END;