Oracle paging Stored Procedure compiling case

Write a stored procedure. You can enter the table name, the number of records displayed on each page, and the current page. The total number of records, the total number of pages, and the result set are returned.

-- Oracle Paging

Select T1. *, rownum rn from (select * from EMP) T1

Select T1. *, rownum rn from (select * from EMP) T1 where rn <= 10;

-- When paging, you can 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;

-- Develop a package. In this package, the test_cursor type is defined as a cursor.

Create or replace package testpackage

Type test_cursor is ref cursor;

End testpackage;

-- Start the paging Process

Create or replace procedure fenye

(Tablename in varchar2, -- table name

Pagesize in number, -- number of records displayed on one page

Pagenow in number, -- current page

Myrows out number, -- total number of records

Mypagecount out number, -- total number of pages

P_cursor out testpackage. test_cursor -- returned record set

) Is

-- Definition

-- Define the SQL statement string

V_ SQL varchar2 (1000 );

-- Define two integers

V_begin number: = (pageNow-1) * pagesize + 1;

V_end number: = pagenow * pageszie;

Begin

-- Execution part

V_ SQL: = 'select * from (select T1. *, rownum rn from (select * from' | tablename | ') T1 where rownum <=' | v_end | ') where rn> = '| v_begin;

-- Associate a cursor with an SQL statement

Open p_cursor for v_ SQL;

-- Calculate myrows and mypagecount

-- Organize an SQL statement

V_ SQL: = 'select count (*) from' | tablename;

-- Execute the SQL statement and assign the returned value to myrows;

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