Oracle中的数据分页

--数据分页脚本

--创建包含数据分页代码元素声明的包头结构create or replace package data_controlis 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_controlis /*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;

转载于:https://www.cnblogs.com/hgc-bky/p/5588430.html