Oracle stored Procedures tutorial

The Oracle paged procedure is the same as the sqlserver paged procedure, but I've changed it a little bit here because the syntax and rules of Oracle are different, so the Oracle paged procedure looks a little different. Laugh, laugh!
Oracle does not return a recordset directly as sqlserver does when it returns a recordset in its stored procedure, which requires cursor variables.
Because the complex sql statements are supposed to be generated in.ES9en, the generation of sql statements is not considered in the stored procedure.
The following is the paging stored procedure implemented in Oracle.

 
create or replace package DotNet is 
-- Author : good_hy 
-- Created : 2004-12-13 13:30:30 
-- Purpose : 
TYPE type_cur IS REF CURSOR; -- Defines a cursor variable to return a recordset  
PROCEDURE DotNetPagination( 
Pindex in number, -- Paging index  
Psql in varchar2, -- produce dataset the sql statements  
Psize in number, -- The page size  
Pcount out number, -- Return total number of pages  
v_cur out type_cur -- Returns the current page data record  
); 
procedure DotNetPageRecordsCount( 
Psqlcount in varchar2, -- produce dataset the sql statements  
Prcount out number -- Return total number of records  
); 
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 
------------------------------------------------------------ Fetch page total  
v_sql := 'select count(*) from (' || Psql || ')'; 
execute immediate v_sql into v_count; 
Pcount := ceil(v_count/Psize); 
------------------------------------------------------------ Displays the contents of any page  
v_Phei := Pindex * Psize + Psize; 
v_Plow := v_Phei - Psize + 1; 
--Psql := 'select rownum rn,t.* from cd_ssxl t' ; -- Requirements must include rownum field  
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; -- Return total number of records  
end DotNetPageRecordsCount; 
--************************************************************************************** 
end DotNot; 

Here are the steps to call the Oracle paging stored procedure in.net.
datareader is required to call a stored procedure that returns a recordset in.net, but datareader does not support paging in datagrid, so custom paging from datagrid is required.

 
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(" Total number of pages  " & 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