Oracle9i 分页存储过程

最新推荐文章于 2024-07-26 14:20:58 发布

weixin_34318326

最新推荐文章于 2024-07-26 14:20:58 发布

阅读量68

点赞数

文章标签：数据库 xhtml ui

原文链接：http://www.cnblogs.com/lvyanyang/archive/2008/09/16/1291930.html

版权

Code
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.代码:

Code
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.页面代码

Code
<%@ 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>

转载于:https://www.cnblogs.com/lvyanyang/archive/2008/09/16/1291930.html

weixin_34318326

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Oracle9i 分页存储过程

Codecreate 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...
复制链接

扫一扫