|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
|
create or replace package jssb.pck_page is
type T_Page is ref cursor; --定义游标变量用于返回记录集
procedure getpagerecord(
pindex in number, --分页索引
psql in varchar2, --产生dataset的sql语句
psize in number, --页面大小
pcount out number, --返回分页总数
v_cur out T_Page --返回当前页数据记录
); end pck_page;
[sql] view plaincopy
create or replace procedure jssb.p_app_GetCount(
p_sql in varchar2,
p_count out number
)as v_sql varchar2(1000);
v_prcount number;
begin v_sql := 'select count(*) from (' || p_sql || ')';
execute immediate v_sql into v_prcount;
p_count := v_prcount; --返回记录总数
end p_app_GetCount;
[sql] view plaincopy
create or replace procedure jssb.p_app_page(
p_pagesql in varchar2,--产生分页的sql语句 sql要求必须包含rownum字段且其别名为rn,如例子:select rownum rn,t.* from test t
p_pagesize in number, --每页显示的条数
p_pageindex in number,--显示页的索引 从0开始
p_totalcount out number,--总条数
p_pagecount out number,--总页数
p_currentpagedata out pck_page.T_Page --返回当前页的数据
)asbegin pck_page.getpagerecord(p_pageindex,p_pagesql,p_pagesize,p_pagecount,p_currentpagedata);
p_app_getcount(p_pagesql,p_totalcount);
end;
[sql] view plaincopy
create or replace package body jssb.pck_page is
procedure getpagerecord(
pindex in number,
psql in varchar2,
psize in number,
pcount out number,
v_cur out T_Page
)
as
v_sql varchar2(1000);
v_count number;
v_plow number;
v_phei number;
begin
v_sql := 'select count(*) from (' || psql || ')';
execute immediate v_sql into v_count;
pcount := ceil(v_count/psize);
v_phei := pindex * psize + psize;
v_plow := v_phei - psize + 1;
--psql := select rownum rn,t.* from test t ; --要求必须包含rownum字段
v_sql := 'select * from (' || psql || ') where rn between ' || v_plow || ' and ' || v_phei ;
open v_cur for v_sql;
end getpagerecord;
end pck_page;
|
本文转自 guwei4037 51CTO博客，原文链接:http://blog.51cto.com/csharper/1345961