Oracle9i 分页存储过程

create or replace package pkg_Pager is
  -- Author  : aweige
  -- Created : 2008-1-26 19:54:19
  -- Purpose : 使用oracle包返回数据集
  -- Public type declarations
  type refcurosr is ref cursor;
  procedure p_GetPager(i_vBaseSql     Varchar2,
                       i_nCurrentPage integer,
                       i_nPageSize    integer,
                       o_vErroDesc    Out Varchar2,
                       o_cDataSet     Out refcurosr,
                       o_nCount       Out integer);
end pkg_Pager;

create or replace package body pkg_Pager is
  Procedure p_GetPager(i_vBaseSql     Varchar2,
                       i_nCurrentPage integer,
                       i_nPageSize    integer,
                       o_vErroDesc    Out Varchar2,
                       o_cDataSet     Out refcurosr,
                       o_nCount       Out integer) is
    /*****************************************************************
    名称   OraclePager
    功能描述:Oracle分页存储过程，根据指定条件分页
    输入：i_vBaseSql：基础SQL语句，用于组建临时表 select * from news order by adddate desc
          i_nCurrentPage：要取第几页
          i_nPageSize：每页取几条
          o_vErroDesc:错误信息,如果成功为null
          o_cDataSet:返回的记录集
          o_nCount:记录总数
    
    输出：一个数据集和数据总数
    
    *****************************************************************/
    Invalid_Input Exception;
    n_Min  number;
    n_Max  number;
    t_vSql varchar2(1000);
  begin
    begin
      n_Min  := i_nPageSize * (i_nCurrentPage - 1);
      n_Max  := i_nPageSize * i_nCurrentPage;
      t_vSql := 'SELECT *
  FROM (SELECT A.*, rownum r FROM (' || i_vBaseSql ||
                ') A WHERE rownum <=' || n_Max || ') B
    WHERE r >' || n_Min;
      Open o_cDataSet for t_vSql;
    
      execute immediate 'select count(*) from (' || i_vBaseSql || ')'
        into o_nCount;
    
    exception
      When Invalid_Input Then
        o_vErroDesc := sqlerrm;
        goto err0;
    end;
    goto end0;
    <<err0>>
    open o_cDataSet for
      select null From dual;
    <<end0>>
    null;
  end p_GetPager;
end pkg_Pager;

 

asp.net2.0中调用
 1.web.config中设置 <system.web> <trust level="Full" originUrl=".*" />

 2.代码:

using System;
using System.Collections;
using System.Configuration;
using System.Data;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.HtmlControls;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Data.OracleClient;
using ChinaSoft.Utility;

namespace WebAppTest.Admin
{
    public partial class oracleprocpage : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            if (!IsPostBack)
            {
                Gridbing();
            }
        }
        public void Gridbing()
        {
            OracleParameter[] ops = { 
               new OracleParameter("i_vBaseSql",OracleType.VarChar,500),
               new OracleParameter("i_nCurrentPage",OracleType.Int32),
               new OracleParameter("i_nPageSize",OracleType.Int32),
               new OracleParameter("o_vErroDesc",OracleType.VarChar,300),
               new OracleParameter("o_cDataSet",OracleType.Cursor),
               new OracleParameter("o_nCount",OracleType.Int32)
            };
            ops[0].Value = "select * from log order by adddate desc";
            ops[1].Value = pageAdmin.CurrentPageIndex;
            ops[2].Value = 3;
            ops[3].Direction = ParameterDirection.Output;
            ops[4].Direction = ParameterDirection.Output;
            ops[5].Direction = ParameterDirection.Output;
            DataSet ds = EOracle.RunProcedure("pkg_pager.p_GetPager", ops,"ds");
            int recordCount = Convert.ToInt32(ops[5].Value);
            pageAdmin.RecordCount = recordCount;
            this.GridView1.DataSource = ds;
            this.GridView1.DataBind();
        }
        protected void pageAdmin_PageChanged(object sender, EventArgs e)
        {
            Gridbing();
        }
    }
}

 

 3.页面代码

 

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="oracleprocpage.aspx.cs" Inherits="WebAppTest.Admin.oracleprocpage" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

<html xmlns="http://www.w3.org/1999/xhtml" >
<head runat="server">
    <title>无标题页</title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:GridView ID="GridView1" runat="server">
        </asp:GridView>
        <webdiyer:AspNetPager ID="pageAdmin" runat="server" AlwaysShow="true" CenterCurrentPageButton="True"
        CustomInfoHTML="共%PageCount%页，共%RecordCount%条数据，当前为第%CurrentPageIndex%页，每页%PageSize%条"
        FirstPageText="首页" HorizontalAlign="Center" LastPageText="尾页" NextPageText="下一页"
        OnPageChanged="pageAdmin_PageChanged" PageSize="3" PrevPageText="上一页" ShowCustomInfoSection="Left"
        Width="90%">
    </webdiyer:AspNetPager>
    </div>
    </form>
</body>
</html>