数据页 oracle,Oracle中的数据分页

--数据分页脚本

--创建包含数据分页代码元素声明的包头结构

create or replace package data_control

is

type type_cursor_data is ref cursor;

v_totalline int; --总数据行数

v_totalpage int; --总页数

v_selectsql varchar2(500); --缓存查询语句

--function pagedata(tablename varchar2,currentpage int,linecount int) return type_cursor_data; --函数方式实现分页查询

procedure pagedata(tablename varchar2,currentpage int,linecount int,resultdata out type_cursor_data); --过程方式实现分页查询

end data_control;

--创建针对数据分页代码元素实现的包体结构

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; --缓存当前页数据的游标变量

begin

execute immediate ‘select count(*) from ‘ || tablename into v_totalline;

dbms_output.put_line(‘总记录行数: ‘ || 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(‘总页数: ‘ || 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; --缓存当前页数据的游标变量

begin

execute immediate ‘select count(*) from ‘ || tablename into v_totalline;

dbms_output.put_line(‘总记录行数: ‘ || 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(‘总页数: ‘ || 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;

--测试代码

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;

原文：http://www.cnblogs.com/hgc-bky/p/5588430.html