Oralce writes a paging Stored Procedure

Stored Procedure with returned values (list result set)
Case: Write a process, enter the Department number, and return the information of all employees of the Department. The analysis of this question is as follows:
Since Oracle stored procedures do not return values, all their return values are replaced by the out parameter, and the list is no exception. However, because it is a set, general parameters cannot be used, you must use a package. Therefore, there are two parts:

1) create a package. As follows:

Create or replace package testpackage as type test_cursor

Is ref cursor;
End testpackage;

In this package, I defined a cursor type test_cursor.

The following is the write creation process.
Create or replace procedure chenchuang_pro
(ChenNo in number p_cursor out testpackage. test_cursor) is
Begin
Open p_cursor for select * from emp wheredeptno-chenNo;
End;

---------------------------------------------------------
Required: Write a stored procedure. You can enter the table name, the number of records displayed on each page, the current page, the total number of returned records, the total number of pages, and the returned result set. (That is, write

Paging stored procedures)

Oracle paging:
When paging, use the following SQL statement as a template

Select * from (select t1. *, rownum rn from (select * from

Emp) t1 where rn <= 10) where rn> = 6

-- Start the paging Process

Write a package first
Create or replace package testpackage as type test_cursor

Is ref cursor;
End testpackage;

 

Created or replace procedure fenye
(TableName in varchar2,
PageSize in number; -- the data volume displayed on each page
PageNow in number; -- current page number
MyRows out number; -- total number of records
MyPageCount out number; -- number of pages in the middle
P_cursor out testpackage. test_cursor -- returned record set
) Is
-- Definition
--- Define SQL statements
V_ SQL varchar2 (1000 );
V_begin number: = (PageNow-1) * PageSize + 1; -- paging algorithm
V_end number: = PageNow * PageSize;
Begin
-- Execution part
V_ SQL: = 'select * from (select t1. *, rownum rn from (select * from' | tableName | ') t1 where rn <=' | v_end | ') where rn> = '| v_begin;
-- Associate a cursor with an SQL statement
Open p_cursor for v_ SQL;
-- Calculate MyRows and MyJpageCount
-- Organize an SQL statement
V_ SQL: = 'select count (*) from' | tableName
-- Execute the SQL statement and assign the returned value to MyRows.
Excute immediate v_ SQL into MyRows;
-- Calculate myPageCount
If mod (myRows, PageSize) = 0 then
MyPageCount: = myRows/PageSize;
Else myPageCount: = myRows/PageSize + 1;
End if;
-- Close the cursor
Close p_cursor;

End