create or replace
package DotNet is -- Author : XiangXi
-- Created : 2008-12-13 13:30:30
-- Purpose : 分页
TYPE type_cur IS REF CURSOR; --定义游标变量用于返回记录集
PROCEDURE DotNetPagination(
Pindex in number, --分页索引
Psql in varchar2, --产生dataset的sql语句
Psize in number, --页面大小
Pcount out number, --返回分页总数
v_cur out type_cur --返回当前页数据记录
);
procedure DotNetPageRecordsCount(
Psqlcount in varchar2, --产生dataset的sql语句
Prcount out number --返回记录总数
);
end DotNet;
procedure DotNetPagination(
Pindex in number,
Psql in varchar2,
Psize in number,
Pcount out number,
v_cur out type_cur
)
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 cd_ssxl t' ; --要求必须包含rownum字段
v_sql := 'select * from (' || Psql || ') where rn between ' || v_Plow || ' and ' || v_Phei ; open v_cur for v_sql; 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 cd_ssxl t' ; --要求必须包含rownum字段
v_sql := 'select * from (' || Psql || ') where rn between ' || v_Plow || ' and ' || v_Phei ; open v_cur for v_sql;
End DotNetPagination;
procedure DotNetPageRecordsCount(
Psqlcount in varchar2,
Prcount out number
)
as
v_sql varchar2(1000);
v_prcount number;
begin
v_sql := 'select count(*) from (' || Psqlcount || ')';
execute immediate v_sql into v_prcount;
Prcount := v_prcount; --返回记录总数
end DotNetPageRecordsCount;end DotNot;
End DotNetPagination;
procedure DotNetPageRecordsCount(
Psqlcount in varchar2,
Prcount out number
)
as
v_sql varchar2(1000);
v_prcount number;
begin
v_sql := 'select count(*) from (' || Psqlcount || ')';
execute immediate v_sql into v_prcount;
Prcount := v_prcount; --返回记录总数
end DotNetPageRecordsCount;end DotNot;
--有两个错误总过不去 使用 Oracle SQL Developer 工具Error(24,2): PLS-00103: 出现符号 "PROCEDURE"
Error(81,1): PLS-00103: 出现符号 "END"在需要下列之一时: begin function package pragma procedure form 符号 "begin在 "END" 继续之前已插入。 大家帮忙在机器上运行下,看能通过不? 谢谢了。在线等。
或者有更好的存储过程分页给在下一个。
package DotNet is -- Author : XiangXi
-- Created : 2008-12-13 13:30:30
-- Purpose : 分页
TYPE type_cur IS REF CURSOR; --定义游标变量用于返回记录集
PROCEDURE DotNetPagination(
Pindex in number, --分页索引
Psql in varchar2, --产生dataset的sql语句
Psize in number, --页面大小
Pcount out number, --返回分页总数
v_cur out type_cur --返回当前页数据记录
);
procedure DotNetPageRecordsCount(
Psqlcount in varchar2, --产生dataset的sql语句
Prcount out number --返回记录总数
);
end DotNet;
procedure DotNetPagination(
Pindex in number,
Psql in varchar2,
Psize in number,
Pcount out number,
v_cur out type_cur
)
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 cd_ssxl t' ; --要求必须包含rownum字段
v_sql := 'select * from (' || Psql || ') where rn between ' || v_Plow || ' and ' || v_Phei ; open v_cur for v_sql; 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 cd_ssxl t' ; --要求必须包含rownum字段
v_sql := 'select * from (' || Psql || ') where rn between ' || v_Plow || ' and ' || v_Phei ; open v_cur for v_sql;
End DotNetPagination;
procedure DotNetPageRecordsCount(
Psqlcount in varchar2,
Prcount out number
)
as
v_sql varchar2(1000);
v_prcount number;
begin
v_sql := 'select count(*) from (' || Psqlcount || ')';
execute immediate v_sql into v_prcount;
Prcount := v_prcount; --返回记录总数
end DotNetPageRecordsCount;end DotNot;
End DotNetPagination;
procedure DotNetPageRecordsCount(
Psqlcount in varchar2,
Prcount out number
)
as
v_sql varchar2(1000);
v_prcount number;
begin
v_sql := 'select count(*) from (' || Psqlcount || ')';
execute immediate v_sql into v_prcount;
Prcount := v_prcount; --返回记录总数
end DotNetPageRecordsCount;end DotNot;
--有两个错误总过不去 使用 Oracle SQL Developer 工具Error(24,2): PLS-00103: 出现符号 "PROCEDURE"
Error(81,1): PLS-00103: 出现符号 "END"在需要下列之一时: begin function package pragma procedure form 符号 "begin在 "END" 继续之前已插入。 大家帮忙在机器上运行下,看能通过不? 谢谢了。在线等。
或者有更好的存储过程分页给在下一个。
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货