存儲過程： CREATE?OR?REPLACE?PACKAGE?pkg_tableType IS procedure?FY ( TableName?varchar2,?--?表名 getFields?varchar2,?--?字段名(全部字段為*) OrderField?varchar2,?--?排序字段(必須!支持多字段) whereCondition?varchar2,?--?條件語句(不用加where) pageSize?int,?--?每頁多少條記錄 pageIndex?int,?--?指定當前為第幾頁 recordCount?out?int?,?--返回總記錄條數(shù) cur_out?out?sys_refcursor?--返回的是數(shù)據(jù)集 ); END; -------------創(chuàng)建包體-------------------- CREATE?OR?REPLACE?PACKAGE?body?pkg_tableType IS procedure?FY( TableName?varchar2,?--?表名 getFields?varchar2,?--?字段名(全部字段為*) OrderField?varchar2,?--?排序字段(必須!支持多字段) whereCondition?varchar2,?--?條件語句(不用加where) pageSize?int,?--?每頁多少條記錄 pageIndex?int,?--?指定當前為第幾頁 recordCount?out?int?,?--返回總記錄條數(shù) cur_out?out?sys_refcursor?--返回的是數(shù)據(jù)集 ) i s v_sql?varchar2(500); StartRecord?int; EndRecord?int; sPageIndex?int; pageCount?int; begin if?whereCondition?is?null?then ? v_sql?:=?'select?count(*)?from?'?||?TableName; ? else ??v_sql?:=?'select?count(*)?from?'?||?TableName?||'?where?'?||?whereCondition; end?if; ? execute?immediate?v_sql?into?recordCount;?--計算總記錄數(shù) ? pageCount:=CEIL((recordCount||0.0)/PageSize);?--計算總頁數(shù) ?? sPageIndex:=pageIndex; ??--處理開始點和結(jié)束點 if(pageIndex<=0)then ??sPageIndex:=1; elsif(pageIndex>pageCount)then ??sPageIndex:=pageCount; end?if; ?? StartRecord?:=?(sPageIndex-1)*PageSize?+?1; ? EndRecord?:=?StartRecord?+pageSize?-?1; ??--合成sql ? if?whereCondition?is?not?null?then ???? v_sql:='select?*?from?(select?row_number()?over?(order?by?'||OrderField||'?)?as?rn,d.*?from?' ????||TableName||'?d?where?'||+whereCondition||')?where?rn>'||StartRecord||'?and?rn<='||EndRecord; ??else ????v_sql:='select?*?from?(select?row_number()?over?(order?by?'||OrderField||'?)?as?rn,d.*?from?' ????||TableName||'?d)?where?rn>'||StartRecord||'?and?rn<='||EndRecord; ??end?if; ? ??open?cur_out?for?v_sql; ??end?FY; END?; DAL層業(yè)務(wù)邏輯（調(diào)用存儲過程） /// 分頁存儲過程 ????? ?/// </summary> ??????? ?/// <param name="TableName">表名</param> ?? ?/// <param name="getFields">字段名(全部字段為*)</param> ? ?/// <param name="OrderField">排序字段(必須!支持多字段)</param> ??? ?/// <param name="whereCondition">條件語句(不用加where)</param> ?? ?/// <param name="pageSize">每頁多少條記錄</param> ??????? ?/// <param name="pageIndex">指定當前為第幾頁</param> ??? ?/// <returns></returns> ????? ?public static DataSet GetProductsByPagerProc(string TableName, string getFields, string OrderField, string whereCondition, int pageSize, int pageIndex, out int recordCount) ?? ?{ ?????????? ?? Database db = DBHelper.CreateDataBase(); ?? ?? StringBuilder sb = new StringBuilder(); ?sb.Append("Shop_Product_Query.Pagination"); ?DbCommand cmd = db.GetStoredProcCommand(sb.ToString()); ?db.AddInParameter(cmd, "TableName", DbType.String, TableName);//輸入?yún)?shù) ?db.AddInParameter(cmd, "getFields", DbType.String, getFields);//輸入?yún)?shù) ?db.AddInParameter(cmd, "OrderField", DbType.String, OrderField);//輸入?yún)?shù) ?db.AddInParameter(cmd, "whereCondition", DbType.String, whereCondition);//輸入?yún)?shù) ?db.AddInParameter(cmd, "pageSize", DbType.Int32, pageSize);//輸入?yún)?shù) ?db.AddInParameter(cmd, "pageIndex", DbType.Int32, pageIndex);//輸入?yún)?shù) ?db.AddOutParameter(cmd, "recordCount", DbType.Int32, Int32.MaxValue);//輸出參數(shù) ?DataSet ds = db.ExecuteDataSet(cmd); ?recordCount = int.Parse(db.GetParameterValue(cmd, "recordCount").ToString());//企業(yè)庫獲取輸出參數(shù) ?return ds; ????????}? 頁面層調(diào)用DAL： public void DataBind() ?{ ?int intPageIndex = this.AspNetPager1.CurrentPageIndex; ?int intPageSize = this.AspNetPager1.PageSize; int count = 0; ?DataSet dt = Sys_ParamService.GetProductsByPagerProc("products", "PRODUCTCODE,PRODUCTNAME,HAOBAIPRICE", "productcode", "productname like '%蒙牛%'", ?intPageSize, intPageIndex, out count); ?this.AspNetPager1.RecordCount = count; ?GridView1.DataSource = dt; ?GridView1.DataBind(); ????????} ?//分頁事件 ?protected void AspNetPager1_PageChanging(object src, Wuqi.Webdiyer.PageChangingEventArgs e) ????????{ ?this.AspNetPager1.CurrentPageIndex = e.NewPageIndex; ?DataBind(); ?} 分頁控件樣式： <table cellpadding="0" cellspacing="0" align="left" width="99%" class="border"> ?<tr> ?<td align="left"> ?<webdiyer:AspNetPager ID="AspNetPager1" CssClass="paginator" CurrentPageButtonClass="cpb" ?runat="server" AlwaysShow="True" FirstPageText="首頁" LastPageText="尾頁" NextPageText="下一頁" ????????????????????????PageSize="3" PrevPageText="上一頁" ShowCustomInfoSection="Left" CustomInfoTextAlign="Center" ????????????????????????LayoutType="Table" CustomInfoHTML="當前第%CurrentPageIndex%/%PageCount%頁?每頁%PageSize%條 共%RecordCount%條記錄" ????????????????????????OnPageChanging="AspNetPager1_PageChanging" PageIndexBoxType="DropDownList" ShowPageIndexBox="Always"> ????????????????????</webdiyer:AspNetPager> ????????????????</td> ????????????</tr> ????????</table> <style type="text/css"> ?.paginator ????????{ ?font: 12px Arial, Helvetica, sans-serif; ?padding: 10px 20px 10px 0; ?margin: 0px; ????????} ?.paginator a ????????{ ????????????border: solid 1px #ccc; ????????????color: #0063dc; ????????????cursor: pointer; ????????????text-decoration: none; ????????} ????????.paginator a:visited ????????{ ????????????padding: 1px 6px; ????????????border: solid 1px #ddd; ????????????background: #fff; ????????????text-decoration: none; ????????} ????????.paginator .cpb ????????{ ????????????border: 1px solid #F50; ????????????font-weight: 700; ????????????color: #F50; ????????????background-color: #ffeee5; ????????} ????????.paginator a:hover ????????{ ????????????border: solid 1px #F50; ????????????color: #f60; ????????????text-decoration: none; ????????} ????????.paginator a, .paginator a:visited, .paginator .cpb, .paginator a:hover ????????{ ????????????float: left; ????????????height: 16px; ????????????line-height: 16px; ????????????min-width: 10px; ????????????_width: 10px; ????????????margin-right: 5px; ????????????text-align: center; ????????????white-space: nowrap; ????????????font-size: 12px; ????????????font-family: Arial,SimSun; ????????????padding: 0 3px; ????????} ????</style>

轉(zhuǎn)載于:https://www.cnblogs.com/Chuifr/archive/2012/12/18/EnterpriseLibrary4.html

總結(jié)

以上是生活随笔為你收集整理的微软企业库调用Oracle分页存储过程的全部內(nèi)容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網(wǎng)站內(nèi)容還不錯，歡迎將生活随笔推薦給好友。