The idea of the Oracle paging stored procedure is the same as in SQL Server, but I have made some changes here, and the Oracle paging stored procedure looks a little different because of the differences in Oracle's syntax and rules. Laughed, laughed!
Returning a recordset in Oracle's stored procedure requires a cursor variable, and Oracle cannot return a recordset directly as SQL Server can.
Due to the idea of generating complex SQL statements in. NET, the problem of generating SQL statements is not considered in the stored procedure.
The following are the paging stored procedures implemented in Oracle.
Copy Code code 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; --Define a cursor variable to return the recordset
PROCEDURE Dotnetpagination (
Pindex in number--Paging index
Psql in VARCHAR2--SQL statements that produce a dataset
Psize in number--page size
Pcount out number--returns total paging
V_cur out Type_cur--Returns the current page data record
);
Procedure Dotnetpagerecordscount (
Psqlcount in VARCHAR2--SQL statements that produce a dataset
Prcount out number--returns total 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
Total------------------------------------------------------------Paging
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 '; --Requires that rownum fields 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; --Returns the total number of records
End Dotnetpagerecordscount;
--**************************************************************************************
End Dotnot;
The following are steps to invoke the Oracle paging stored procedure in. Net.
DataReader is needed for the stored procedure that returns the recordset in. NET calls, but DataReader does not support paging in the DataGrid, so you need to use the DataGrid to customize the paging functionality.
Copy Code code 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 ()
'------------------------------------------------------------------------------------
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 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
