資源描述：
《ibatis調(diào)用oracle分頁存儲過程》由會員上傳分享，免費(fèi)在線閱讀，更多相關(guān)內(nèi)容在工程資料-天天文庫。
1、oracle分頁：createorreplaceprocedureP_QuerySplit(sqlscriptvarchar2,--表名/SQL語句pageSizeinteger,--每頁記錄數(shù)pageIndexinteger,--當(dāng)前頁totalCountoutnumber,--總記錄數(shù)totalPageoutnumber,--總頁數(shù)v_curoutsys_refcursor--返回游標(biāo))is/***bychenjianxin2008-5-3**/v_PageSizenumber;v_PageIndexnumber;v_SQL_Countvarchar2(4000);v_SQLvarchar
2、2(4000);v_StartIndexnumber;v_EndIndexnumber;beginv_PageSize:=pageSize;ifv_PageSize=0thenv_PageSize:=1;endif;--統(tǒng)計(jì)記錄數(shù)量v_SQL_Count:='selectcount(*)from('||sqlscript||')a';executeimmediatev_SQL_CountintototalCount;--計(jì)算總頁數(shù)totalPage:=CEIL(totalCount/v_PageSize);--驗(yàn)證頁號如果頁號大余了最大頁數(shù)，返回最后一頁v_PageIndex:=pageInd
3、ex;ifv_PageIndex>totalPagethenv_PageIndex:=totalPage;endif;--計(jì)算開始的Index和結(jié)束的Indexv_StartIndex:=(v_PageIndex-1)*v_PageSize1;v_EndIndex:=v_PageIndex*v_PageSize;v_SQL:='SELECT/*FIRST_ROWS*/*FROM(';v_SQL:=v_SQL||'SELECTA.*,ROWNUMRN';v_SQL:=v_SQL||'FROM('||sqlscript||')A';v_SQL:=v_SQL||'WHEREROWNUM<='||v_
4、EndIndex;v_SQL:=v_SQL||')WHERERN>='||v_StartIndex;openv_curforv_SQL;endP_QuerySplit;帶排序的分頁存儲過程CREATEORREPLACEPROCEDURETABLEPAGE_SELECT(v_page_size int,--thesizeofapageoflist v_current_pageint,--thecurrentpageoflist v_table_namevarchar2,--thetalbename
5、 v_order_field varchar2,--theorderfield v_order_sequencevarchar2,--theordersequenceshouldby"_desc"or"_asc",_isblank. --v_sql_select varchar2,--theselectsqlforprocedure --v_sql_count varchar2,--thecountsqlforprocedure
6、 --v_out_recordcountOUTint,--thenumofreturnrows p_cursorOUTrefcursor_pkg.return_cursor)as v_sql varchar2(3000);--thesqlforselectallrowsoflist v_sql_count varchar2(3000);--thecountsqlforprocedure v_sql_order varchar2(2000);--theorderoflist v_count int;--theamo
7、untrowsfooriginallist v_endrownum int;--theendrownumofthecurrentpage v_startrownumint;--thestartrownumofthecurrentpageBEGIN ----settheorderoflist if v_order_field!='NO'then v_sql_order:='ORDERBY'
8、
9、v_
