oracle 通用分页存储过程 asp.net,Oracle通用分页存储过程和AspNetPage结合

--包头部分

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 (SELECT a.*, ROWNUM rn FROM (' || Psql || ') a) WHERE rn >= ' || v_Pbegin || ' AND rn <= ' || v_Pend;

DBMS_OUTPUT.put_line(v_sql);

open v_cur for v_sql;

end Pagination;

end JT_P_page;

protected void Page_Load(object sender, EventArgs e)

{

if (!IsPostBack)

{

BindGridView(0, "DD_Demo", AspNetPager1.PageSize);

}

}

///

/// 数据绑定

///

/// 当前页码

/// 表名或者视图名

/// 每页显示记录数

private void BindGridView(int index, string sql, int pageSize)

{

int totalCount = 0;

int pageCount = 0;

DataTable dt = ReturnDataTable(index, sql, pageSize, out totalCount, out pageCount);

GridView1.DataSource = dt;

GridView1.DataBind();

AspNetPager1.RecordCount = totalCount;

AspNetPager1.PageSize = pageSize;

AspNetPager1.CustomInfoHTML = " 共" + AspNetPager1.RecordCount.ToString() + "条记录";

AspNetPager1.CustomInfoHTML += " 当前第" + AspNetPager1.CurrentPageIndex.ToString() + "页";

AspNetPager1.CustomInfoHTML += "/共" + AspNetPager1.PageCount.ToString() + "页";

}

///

/// 执行存储过程返回分页数据

///

/// 当前页码

/// 表名或者视图名

/// 每页显示记录数

/// 返回记录总数

/// 返回页面总数

///

public static DataTable ReturnDataTable(int index, string sql, int pageSize, out int totalCount,out int pageCount)

{

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 = Maticsoft.DBUtility.DbHelperOra.RunProcedure("JT_P_page.Pagination", param, "queryTable").Tables["queryTable"];

pageCount = int.Parse(param[3].Value.ToString());

totalCount = int.Parse(param[4].Value.ToString());

}

catch (Exception ex)

{

throw new Exception(ex.Message);

}

return dt;

}

protected void AspNetPager1_PageChanged(object sender, EventArgs e)

{

BindGridView(AspNetPager1.CurrentPageIndex - 1, "DD_Demo", AspNetPager1.PageSize);

}