Oracle Stored Procedure tutorial

The idea of Oracle paging stored procedures is the same as that of SQL Server, but I have made some changes here because of the differences in Oracle syntax and rules, the Oracle paging Stored Procedure looks a little different. Smile!
Cursor variables are required to return record sets during Oracle storage. Oracle cannot directly return a record set as SQL Server does.
Because it is assumed that complicated SQL statements are generated in. net, the problem of generating SQL statements is not considered in the stored procedure.
The following is a paging stored procedure in Oracle.
Copy codeThe Code is as follows:
Create or replace package DotNet is
-- Author: good_hy
-- Created: 2004-12-13 13:30:30
-- Purpose:
TYPE type_cur is ref cursor; -- defines the CURSOR variable used to return the record set
PROCEDURE DotNetPagination (
Pindex in number, -- paging Index
Psql in varchar2, -- SQL statement that generates dataset
Psize in number, -- page size
Pcount out number, -- total number of returned pages
V_cur out type_cur -- returns the current page data record
);
Procedure DotNetPageRecordsCount (
Psqlcount in varchar2, -- generate the dataset SQL statement
Prcount out number -- total number of returned 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
------------------------------------------------------------ Retrieve the total number of pages
V_ SQL: = 'select count (*) from ('| Psql | ')';
Execute immediate v_ SQL into v_count;
Pcount: = ceil (v_count/Psize );
-------------------------------------------------------------- Display any page content
V_Phei: = Pindex * Psize + Psize;
V_Plow: = v_Phei-Psize + 1;
-- Psql: = 'select rownum rn, t. * from cd_ssxl T'; -- The rownum field must be included.
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; -- total number of returned records
End DotNetPageRecordsCount;
--*************************************** **************************************** *******
End DotNot;

The following describes how to call the Oracle paging stored procedure in. net.
Datareader is required to call the stored procedure of the returned record set in. net. However, datareader does not support paging in the datagrid. Therefore, you must use the datagrid custom paging function.
Copy codeThe Code is as follows:
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 ()
'Timeout '------------------------------------------------------------------------------------
Cmd. CommandText = "DotNot. DotNetPageRecordsCount"
'Timeout '------------------------------------------------------------------------------------
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 ()
'Timeout '------------------------------------------------------------------------------------
Cmd. CommandText = "DotNot. DotNetPagination"
'Timeout '------------------------------------------------------------------------------------
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 pages" & cmd. Parameters ("pcount"). Value)
End Sub
Bytes ----------------------------------------------------------------------------------------
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
Bytes ---------------------------------------------------------------------------------------
Private Sub maid (ByVal source As Object, ByVal e As System. Web. UI. WebControls. DataGridPageChangedEventArgs) Handles maid
Dim psql As String = "select rownum rn, t. * from cd_ssxl t"
Me. Maid = e. NewPageIndex
Gridbind (e. NewPageIndex, psql, 20)
End Sub