Data Paging in Oracle

--Data paging scripts

--Create a header structure that contains the data pagination code element declaration
Create or Replace package Data_control
Is
Type type_cursor_data is REF CURSOR;

V_totalline int; --Total number of data rows
V_totalpage int; --Total Pages
V_selectsql VARCHAR2 (500); --Cache query statements

--function pagedata (tablename varchar2,currentpage int,linecount int) return type_cursor_data; --function mode for paging query

Procedure Pagedata (tablename varchar2,currentpage int,linecount int,resultdata out type_cursor_data); --the process way to realize paging query
End Data_control;

--Creating a package structure for data pagination code elements
Create or replace package body Data_control
Is
/*function pagedata (tablename varchar2,currentpage int,linecount int) return Type_cursor_data
Is
Data type_cursor_data; --a cursor variable that caches the current page data
Begin
Execute immediate ' SELECT COUNT (*) from ' | | TableName into V_totalline;

Dbms_output.put_line (' Total rows of records: ' | | v_totalline);

If V_totalline/linecount = 0 Then
v_totalpage: = V_totalline/linecount;
Else
V_totalpage: = v_to Talline/linecount + 1;
End If;

Dbms_output.put_line (' Total pages: ' | | v_totalpage);

V_selectsql: = ' select * FROM (select Tn.*,rownum linenum from ' | | tablename | | ' TN ' t where t.linenum > ' | | (CurrentPage * linecount-linecount) | | ' and T.linenum <= ' | | (CurrentPage * linecount);

Open Data for v_selectsql;

return data;
End pagedata;*/

Procedure pagedata (tablename varchar2,currentpage int,linecount int,resultdata out Type_ Cursor_data)
is
data type_cursor_data;--the cursor variable that caches the current page information
Begin
Execute immediate ' SELECT COUNT (*) from ' | | t Ablename into V_totalline;

Dbms_output.put_line (' Total record lines: ' | | v_totalline);

If V_totalline/linecount = 0 Then
V_totalpage: = V_totalline/linecount;
Else
V_totalpage: = V_totalline/linecount + 1;
End If;

Dbms_output.put_line (' Total pages: ' | | v_totalpage);

V_selectsql: = ' select * from ' (select Tn.*,rownum linenum from ' | | tablename | | ' TN ' t where t.linenum > ' | | (CurrentPage * linecount-linecount) | | ' and T.linenum <= ' | | (CurrentPage * linecount);

Open data for v_selectsql;

Resultdata: = data;
End Pagedata;
End Data_control;

--Test code
Declare
Res_data Data_control.type_cursor_data;

Type Type_page_record is record (
Empno Emp.empno%type,
Ename Emp.ename%type,
Job Emp.job%type,
Mgr Emp.mgr%type,
HireDate Emp.hiredate%type,
Sal Emp.sal%type,
Comm Emp.comm%type,
Deptno Emp.deptno%type,
RN int
);

Rec_row Type_page_record;
Begin
--res_data: = Data_control.pagedata (' emp ', 2,5);

Data_control.pagedata (' dept ', 2,5,res_data);

Loop
Fetch res_data into Rec_row;
Exit when Res_data%notfound;
Dbms_output.put_line (Rec_row.ename);
End Loop;

Close Res_data;
End

Data Paging in Oracle