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;