Oracle存储过程教程

一个简单的oracle分页存储过程的实现和调用。在看了众多的分页存储过程以后发现都是针对sqlserver的，而没有oracle的，因此想写一个关于oracle的存储过程，因为我用到的数据库是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分页存储过程的步骤。 
在.net调用返回记录集的存储过程，需要用到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("总计页数 " & 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