一個簡單的oracle分頁存儲過程的實現和調用
2024-08-29 13:37:31
供稿：網友
在看了眾多的分頁存儲過程以后發現都是針對sqlserver的，而沒有Oracle的，因此想寫一個關于oracle的存儲過程，因為我用到的數據庫是oracle.
---------------------------------------------------------------------------------------
oracle分頁存儲過程的思路于sqlserver的思路是一樣的，但是我這里做了點改動，在因為oracle的語法和規則的不同所以，oracle分頁
存儲過程看上去有點不一樣。見笑，見笑！在oracle的存儲過程中返回記錄集，需要用到游標變量，oracle不能像sqlserver那樣可以直接返回一個記錄集。
由于設想在.net中把復雜的sql語句生成，所以在存儲過程中沒有去考慮生成sql語句的問題。
-----------------------------------------------------------------------------------
以下是在oracle中實現的分頁存儲過程。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, --產生dataset的sql語句
Psize in number, --頁面大小
Pcount out number, --返回分頁總數
v_cur out type_cur --返回當前頁數據記錄
);
procedure DotNetPageRecordsCount(
Psqlcount in varchar2, --產生dataset的sql語句
Prcount out number --返回記錄總數
);
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
------------------------------------------------------------取分頁總數
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 + 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; --返回記錄總數
end DotNetPageRecordsCount;
--**************************************************************************************
end DotNot;------------------------------------------------------------------------------------------
以下是在.net中調用oracle分頁存儲過程的步驟。(vb.net)
在.net調用返回記錄集的存儲過程，需要用到datareader，但是datareader不支持在datagrid中的分頁，因此需要利用datagrid
自定義分頁功能。
Protected WithEvents DataGrid1 As System.Web.UI.WebControls.DataGrid Dim conn As New OracleClient.OracleConnection()
Dim cmd As New OracleClient.OracleCommand()
Dim dr As OracleClient.OracleDataReaderPrivate 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("總計頁數 " & 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
