寫個簡單的存儲過程權當練習
create or replace package MAHANPACK as
type my_cursor is ref cursor;
end MAHANPACK;
-------------------------------------
create or replace package body PAGEUTIL is
procedure getResults(
tableName in varchar2,
colsStr in varchar2,
pageSize in number,
currentPage in number,
pageCount out number,
p_cursor out MAHANPACK.my_cursor
) is
v_sql varchar2(2000);
v_begin number:= (currentPage-1)*pageSize+1;
v_end number := currentPage*pageSize;
begin v_sql:='select * from (
select t.*,rownum rn from
(select '||colsStr ||' from '
||tableName||' ) t where rownum <='||v_end||') where rn>='||v_begin;
open p_cursor for v_sql;
v_sql :=' select count(*) from '||tableName;
execute immediate v_sql into pageCount;
if mod(pageCount,pageSize)=0 then
pageCount:= pageCount/pageSize ;
else
pageCount:= pageCount/pageSize+1;
end if;
end;
end PAGEUTIL;
SQL> ed
已寫入 file afiedt.buf
1 begin
2 PAGEUTIL.getResults(tableName=>'emp',
3 colsStr=>'empno , ename ',
4 pageSize=>20,
5 currentPage=>1,
6 pageCount=>:count,
7 p_cursor=>:c1);
8* end;
SQL> /
PL/SQL 過程已成功完成。
SQL> print count;
COUNT
----------
1.7
SQL> print c1;
EMPNO ENAME RN
---------- ---------- ----------
7369 SMITH 1
7499 ALLEN 2
7521 WARD 3
7566 JONES 4
7654 MARTIN 5
7698 BLAKE 6
7782 CLARK 7
7788 SCOTT 8
7839 KING 9
7844 TURNER 10
7876 ADAMS 11
EMPNO ENAME RN
---------- ---------- ----------
7900 JAMES 12
7902 FORD 13
7934 MILLER 14
已選擇14行。
