一個簡單的oracle分頁存儲過程的實現(xiàn)和調(diào)用。在看了眾多的分頁存儲過程以后發(fā)現(xiàn)都是針對sqlserver的，而沒有oracle的，因此想寫一個關于oracle的存儲過程，因為我用到的數(shù)據(jù)庫是oracle。
Oracle分頁存儲過程的思路于sqlserver的思路是一樣的，但是我這里做了點改動，在因為Oracle的語法和規(guī)則的不同所以，Oracle分頁存儲過程看上去有點不一樣。見笑，見笑！
在Oracle的存儲過程中返回記錄集，需要用到游標變量，Oracle不能像sqlserver那樣可以直接返回一個記錄集。
由于設想在.net中把復雜的sql語句生成，所以在存儲過程中沒有去考慮生成sql語句的問題。
以下是在Oracle中實現(xiàn)的分頁存儲過程。
代碼如下:
create or replace package DotNet is
— Author : good_hy
— Created : 2004-12-13 13:30:30
— Purpose :
TYPE type_cur IS REF CURSOR; –定義游標變量用于返回記錄集
PROCEDURE DotNetPagination(
Pindex in number, –分頁索引
Psql in varchar2, –產(chǎn)生dataset的sql語句
Psize in number, –頁面大小
Pcount out number, –返回分頁總數(shù)
v_cur out type_cur –返回當前頁數(shù)據(jù)記錄
);
procedure DotNetPageRecordsCount(
Psqlcount in varchar2, –產(chǎn)生dataset的sql語句
Prcount out number –返回記錄總數(shù)
);
end DotNot;
create or replace package body DotNet is
–***************************************************************************************
PROCEDURE DotNetPagination(
Pindex in number,
Psql in varchar2,
Psize in number,
Pcount out number,
v_cur out type_cur
)
AS
v_sql VARCHAR2(1000);
v_count number;
v_Plow number;
v_Phei number;
Begin
————————————————————取分頁總數(shù)
v_sql := ‘select count(*) from (‘ || Psql || ‘)’;
execute immediate v_sql into v_count;
Pcount := ceil(v_count/Psize);
————————————————————顯示任意頁內(nèi)容
v_Phei := Pindex * Psize + Psize;
v_Plow := v_Phei – Psize + 1;
–Psql := ‘select rownum rn,t.* 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 varchar2,
Prcount out number
)
as
v_sql varchar2(1000);
v_prcount number;
begin
v_sql := ‘select count(*) from (‘ || Psqlcount || ‘)’;
execute immediate v_sql into v_prcount;
Prcount := v_prcount; –返回記錄總數(shù)
end DotNetPageRecordsCount;
–**************************************************************************************
end DotNot;
以下是在.net中調(diào)用Oracle分頁存儲過程的步驟。
在.net調(diào)用返回記錄集的存儲過程，需要用到datareader，但是datareader不支持在datagrid中的分頁，因此需要利用datagrid自定義分頁功能。
代碼如下:
rotected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid
Dim conn As New OracleClient.OracleConnection()
Dim cmd As New OracleClient.OracleCommand()
Dim dr As OracleClient.OracleDataReader
Private Sub gridbind(ByVal pindex As Integer, ByVal psql As String, Optional ByVal psize As Integer = 10)
conn.ConnectionString = “Password=gzdlgis;User ID=gzdlgis;Data Source=gzgis”
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
conn.Open()
‘————————————————————————————
cmd.CommandText = “DotNot.DotNetPageRecordsCount”
‘————————————————————————————
cmd.Parameters.Add(“psqlcount”, OracleType.VarChar).Value = psql
cmd.Parameters.Add(“prcount”, OracleType.Number).Direction = ParameterDirection.Output
cmd.ExecuteNonQuery()
Me.DataGrid1.AllowPaging = True
Me.DataGrid1.AllowCustomPaging = True
Me.DataGrid1.PageSize = psize
Me.DataGrid1.VirtualItemCount = cmd.Parameters(“prcount”).Value
cmd.Parameters.Clear()
‘————————————————————————————
cmd.CommandText = “DotNot.DotNetPagination”
‘————————————————————————————
cmd.Parameters.Add(“pindex”, Data.OracleClient.OracleType.Number).Value = pindex
cmd.Parameters.Add(“psql”, Data.OracleClient.OracleType.VarChar).Value = psql ‘”select rownum rn,t.* from cd_ssxl t”
cmd.Parameters.Add(“psize”, Data.OracleClient.OracleType.Number).Value = psize
cmd.Parameters.Add(“v_cur”, Data.OracleClient.OracleType.Cursor).Direction = ParameterDirection.Output
cmd.Parameters.Add(“pcount”, Data.OracleClient.OracleType.Number).Direction = ParameterDirection.Output
dr = cmd.ExecuteReader()
Me.DataGrid1.DataSource = dr
Me.DataGrid1.DataBind()
dr.Close()
conn.Close()
Response.Write(“總計頁數(shù) ” & cmd.Parameters(“pcount”).Value)
End Sub
—————————————————————————————-
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
If Not Page.IsPostBack Then
Dim psql As String = “select rownum rn,t.* from cd_ssxl t”
gridbind(0, psql, 20)
End If
End Sub
—————————————————————————————
Private Sub DataGrid1_PageIndexChanged(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridPageChangedEventArgs) Handles DataGrid1.PageIndexChanged
Dim psql As String = “select rownum rn,t.* from cd_ssxl t”
Me.DataGrid1.CurrentPageIndex = e.NewPageIndex
gridbind(e.NewPageIndex, psql, 20)
End Sub
