17,086
社区成员
发帖
与我相关
我的任务
分享
create or replace package PKG_Biz_Claim is
type type_cur is ref cursor; -- 定义游标变量返回记录集
procedure ClaimList
(
Pindex in number, --页码 从1开始
Pfield in varchar2, -- 查询字段
Psql in varchar2, --查询SQL语句
Psize in number , --每页记录数
PpageCount out number, --总页数
PRowCount out number, --总记录数
v_cur out type_cur --分页数据游标
);
end PKG_Biz_Claim;
Create or replace package body PKG_Biz_Claim is
procedure ClaimList
(
Pindex in number, --页码 从1开始
Pfield in varchar2, -- 查询字段
Psql in varchar2, --查询SQL语句
Psize in number , --每页记录数
PpageCount out number, --总页数
PRowCount out number, --总记录数
v_cur out type_cur --分页数据游标
) is
v_sql varchar2(2000);
v_Pstart number;
v_Pend number;
begin
-- 取记录总数
v_sql:= 'select count(1) from (' || Psql || ')';
execute immediate v_sql into PRowCount;
-- 当前页第一条
v_Pstart = (pindex -1)*Psize +1;
-- 当前页最后一条
v_Pend = Pindex * Psize;
v_sql := 'SELECT ' || Pfield || ' from
(SELECT ' || Pfield || ',rownum rn FROM( ' || Psql || ' ) WHERE rownum <= '|| to_char(v_Pend)||')WHERE rn >= ' || to_char(v_Pstart);
OPEN v_cur FOR v_sql;
end ClaimList;
end PKG_Biz_Claim;
create or replace package PKG_Biz_Claim is
procedure UT_ClaimList
(
Pindex in number, --页码 从1开始
pfilter in varchar2, -- 查询字段
Psize in number , --每页记录数
PpageCount out number, --总页数
PRowCount out number, --总记录数
v_cur out sys_refcursor --分页数据游标
);
end PKG_Biz_Claim;
再执行
create or replace package body PKG_Biz_Claim is
procedure UT_ClaimList
(
Pindex in number, --页码 从1开始
pfilter in varchar2, -- 查询字段
Psize in number , --每页记录数
PpageCount out number, --总页数
PRowCount out number, --总记录数
v_cur out sys_refcursor --分页数据游标
) as
v_dynamicSql varchar2(2000);
v_sql varchar2(2000);
v_Pstart number;
v_Pend number;
begin
v_dynamicSql := '
select A.NAME,
A.CERTIFICATE_NUM,
A.MEMBER_CARD_ID,
E.PRODUCTNAME,
E.AllSum,
E.payType,
E.Reportdate,
E.AUDITDATE,
E.DATEEFFECTED,
E.BACKREASON,
E.Bankaddress,
F.MC,
C.OrgFullCode,
C.OrgName,
C.OrgName as CurrentOrgName
from M_B_JKPERSONINFO A,
M_B_ORG_BINDEDRELATION C,
M_B_JKCLAIMINFO E,
M_B_DIC_CONTENT F,
M_B_DIC_CLASS G
where
E.JKCREATORORG = C.JK_ORGID and
E.JK_ORG_CODE = C.JK_ORGID and
E.STATUS = F.BM and
F.ZDFLBH = G.ZDFLBH and
A.CERTIFICATE_NUM = E.CERTIFICATENUM
where F.ZDFLBH=' || 'D_BUSINESS_STATUS' || pfilter ;
-- 取记录总数
v_sql:= 'select count(1) from (' || v_dynamicSql || ')';
execute immediate v_sql into PRowCount;
-- 当前页第一条
v_Pstart := (pindex -1)*Psize +1;
-- 当前页最后一条
v_Pend := Pindex * Psize;
--返回总页数
PpageCount := ceil( PRowCount / Psize );
v_sql := 'SELECT * from
(SELECT * ,rownum rn FROM( ' || v_dynamicSql || ' ) WHERE rownum <= '|| to_char(v_Pend)||')WHERE rn >= ' || to_char(v_Pstart);
OPEN v_cur FOR v_sql;
end UT_ClaimList;
end PKG_Biz_Claim;