oracle| Stored Procedures | Paging |oracle| stored Procedures | Paging after looking at a number of paging stored procedures, it was found to be for SQL Server, without Oracle, so I wanted to write a stored procedure about Oracle because the database I used was Oracle.
--------------------------------------------------------------------------------------------------------------- -----------------------------------------------------------------------
The idea of the Oracle paging stored procedure is the same as that of SQL Server, but I have made some changes here because of the differences in Oracle's syntax and rules, Oracle paging
The stored procedure looks a little different. 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.
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. (vb.net)
A stored procedure that returns a recordset in. NET calls requires DataReader, but DataReader does not support paging in the DataGrid, so you need to take advantage of the DataGrid
Customize the paging functionality.
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.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 = Parameterdirectio
