After reading a large number of paging stores, it was found for SQLServer, and there was no Oracle, so I wanted to write a stored procedure about Oracle because I used the database is Oracle. -------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ---------------------- Oracle's thinking of SQL Server's idea is the same, but I have done it here, because Oracle's grammar and The rules are different, and the Oracle paging stored procedure looks a bit different. Laughing, laugh! Returns a recordset during the stored procedure of Oracle, you need to use a cursor variable, Oracle can't return a recordset directly like SQL Server. Since the complex SQL statement is envisaged in .NET, the problem of generating the SQL statement is not considered during the stored procedure. -------------------------------------------------- -------------------------------------------------- -------------------------------- The following is the paging stored procedure implemented in Oracle. Create Or Replace Package DotNet IS
- Author: good_hy - create: 2004-12-13 13:30:30 - purpose: type type_cur is ref cur; - Define cursor variables Used to return record set Procedure DotNetPagination (PINDEX IN NUMBER, - Page Index PSQL in varcha2, - Generating a DataSet SQL statement psize in number, - page size PCount Out number, - Return to the total number of pairs v_cur out type_cur - Return to the current page data record); Procedure DotNetPageRecordscount (psqlcount in varchar2,) DataSet SQL Statement Prcount Out Number - Returns the 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 varcha2 (1000); v_count number; v_PLOW Number; V_PHEI Number; Begin ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ -------------------------- Total Page Total 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'; - 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 The total number of records end dotNetPageRecordscount; - **************************************************** *********************************************************** * End dotnot; --- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------- The following is the step of calling the Oracle paging store in .NET. (VB.NET) The stored procedure of the recordset in .NET calls, you need to use DataReader, but DataReader does not support paging in DataGrid, so you need to use the DataGrid custom paging function. 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.OracleDataReaderPrivate 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 ()
'------------------------------------- ---------------------------------- 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. Value
CMD.Parameters.clear () ------------------------------------------ ------------------------------------------ CMD.commandtext = "dotNot.dotNetPagination "'------------------------------------------------ ---------------------------------- CMD.Parameters.Add ("Pindex", Data.OracleClient. Oracletype.Number) .value = pindex cmd.parameters.add ("psql", data.oraclenet.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.outputdr = cmd.executeReader ()
Me.DataGrid1.datasource = Dr Me.dataGrid1.database
Dr.close () conn.close ()
Response.write ("Total Page" & cmd.Parameters ("PCOUNT"). Value) End Sub --------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------- Private Sub Page_load (Byval E AS System.EventArgs Handles mybase.load if not page.ispostback kil 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 ---------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------- -------------------------------------------------- ------------------------------------- Do you have any heights may wish to give you a 2nd! Thank you
