Implementation and invocation of a simple Oracle paging stored procedure

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;
------------------------------------------------------------------------------------------