一個簡單的oracle分頁存儲過程的實現和調用

　　在看了眾多的分頁存儲過程以後發現都是針對sqlserver的而沒有oracle的因此想寫一個關於oracle的存儲過程因為我用到的數據庫是oracle

oracle分頁存儲過程的思路於sqlserver的思路是一樣的但是我這裡做了點改動在因為oracle的語法和規則的不同所以oracle分頁
存儲過程看上去有點不一樣見笑見笑！

　　在oracle的存儲過程中返回記錄集需要用到游標變量oracle不能像sqlserver那樣可以直接返回一個記錄集
由於設想中把復雜的sql語句生成所以在存儲過程中沒有去考慮生成sql語句的問題

以下是在oracle中實現的分頁存儲過程

　　create or replace package DotNet is

　　 Author  : good_hy
  Created : ::
  Purpose :
 
  TYPE type_cur IS REF CURSOR;     定義游標變量用於返回記錄集
   
  PROCEDURE DotNetPagination(     
  Pindex in number                分頁索引  
  Psql in varchar                產生dataset的sql語句
  Psize in number                 頁面大小
  Pcount out number               返回分頁總數
  v_cur out type_cur               返回當前頁數據記錄
  ); 
 
  procedure DotNetPageRecordsCount(
  Psqlcount in varchar           產生dataset的sql語句                          
  Prcount   out number             返回記錄總數
  );
 
end DotNot;

　　

　　create or replace package body DotNet is

　　***************************************************************************************
 
 PROCEDURE DotNetPagination(
  Pindex in number
  Psql in varchar
  Psize in number 
  Pcount out number
  v_cur out type_cur
 )
 AS

　　v_sql VARCHAR();
  v_count number; 
  v_Plow number;
  v_Phei number;
 Begin
  取分頁總數
  v_sql := select count(*) from ( || Psql || );
  execute immediate v_sql into v_count;
  Pcount := ceil(v_count/Psize);
  顯示任意頁內容
  v_Phei := Pindex * Psize + Psize;
  v_Plow := v_Phei Psize + ;
  Psql := select rownum rnt* from cd_ssxl t ;            要求必須包含rownum字段
  v_sql := select * from ( || Psql || ) where rn between || v_Plow || and || v_Phei ;

　　open v_cur for v_sql;
 
 End DotNetPagination;
 
 **************************************************************************************
 
 procedure DotNetPageRecordsCount(
  Psqlcount in varchar
  Prcount   out number
  )
  as
 
   v_sql varchar();
   v_prcount number;
  
  begin
 
   v_sql := select count(*) from ( || Psqlcount || );
   execute immediate v_sql into v_prcount;
   Prcount := v_prcount;                  返回記錄總數                                                     
  
  end DotNetPageRecordsCount;
 
 **************************************************************************************
 
end DotNot;

　　
以下是中調用oracle分頁存儲過程的步驟()
調用返回記錄集的存儲過程需要用到datareader但是datareader不支持在datagrid中的分頁因此需要利用datagrid
自定義分頁功能

　　Protected WithEvents DataGrid As SystemWebUIWebControlsDataGrid

　　Dim conn As New OracleClientOracleConnection()
    Dim cmd As New OracleClientOracleCommand()
    Dim dr As OracleClientOracleDataReader

　　Private Sub gridbind(ByVal pindex As Integer ByVal psql As String Optional ByVal psize As Integer = )

　　connConnectionString = Password=gzdlgis;User ID=gzdlgis;Data Source=gzgis
        cmdConnection = conn
        cmdCommandType = CommandTypeStoredProcedure
        connOpen()

　　
        cmdCommandText = DotNotDotNetPageRecordsCount
       
        cmdParametersAdd(psqlcount OracleTypeVarChar)Value = psql
        cmdParametersAdd(prcount OracleTypeNumber)Direction = ParameterDirectionOutput

　　cmdExecuteNonQuery()

　　MeDataGridAllowPaging = True
        MeDataGridAllowCustomPaging = True
        MeDataGridPageSize = psize
        MeDataGridVirtualItemCount = cmdParameters(prcount)Value

　　cmdParametersClear()
       
        cmdCommandText = DotNotDotNetPagination
       
        cmdParametersAdd(pindex DataOracleClientOracleTypeNumber)Value = pindex
        cmdParametersAdd(psql DataOracleClientOracleTypeVarChar)Value = psql select rownum rnt* from cd_ssxl t
        cmdParametersAdd(psize DataOracleClientOracleTypeNumber)Value = psize
        cmdParametersAdd(v_cur DataOracleClientOracleTypeCursor)Direction = ParameterDirectionOutput
        cmdParametersAdd(pcount DataOracleClientOracleTypeNumber)Direction = ParameterDirectionOutput

　　dr = cmdExecuteReader()

　　MeDataGridDataSource = dr
        MeDataGridDataBind()

　　drClose()
        connClose()

　　ResponseWrite(總計頁數 & cmdParameters(pcount)Value)
    End Sub

　　

　　Private Sub Page_Load(ByVal sender As SystemObject ByVal e As SystemEventArgs) Handles MyBaseLoad
        If Not PageIsPostBack Then
            Dim psql As String = select rownum rnt* from cd_ssxl t
            gridbind( psql )
           
        End If

　　End Sub

　　

　　Private Sub DataGrid_PageIndexChanged(ByVal source As Object ByVal e As SystemWebUIWebControlsDataGridPageChangedEventArgs) Handles DataGridPageIndexChanged
        Dim psql As String = select rownum rnt* from cd_ssxl t

　　MeDataGridCurrentPageIndex = eNewPageIndex
        gridbind(eNewPageIndex psql )
    End Sub

From:http://tw.wingwit.com/Article/program/Oracle/201311/17152.html