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;

Oracle中的資料分頁