CREATE OR REPLACE PACKAGE BODY lbexchgPACKAGE as
/** 说明:* 用于记录总页数和当前页的最小和最大记录号**/
procedure Selectlbexchg
(
pageIndex in number , --页码,以1开始
pageSize in number , --每页记录数
pageCount out number , --总页数
cppsrpList out t_cursor, --当前页记录值
wtcode_in in char , --工种
cpname_in in char , --公司
psname_in in char , --姓名
iscode_in in char , --身份证号码
opdate1_in in date , --查询的条件
opdate2_in in date , --查询的条件
agcode_in in char , --介绍所
btbz_in in char , --优惠券 0全部1有
epflag_in in char --介绍结果
)
as
recordCount number ;
pageMinRownum number ;
pageMaxRownum number ;
begin
if btbz_in='0' and epflag_in='0' then--所有
select count( * ) into recordCount from ( select psseno
from READ_EMPDA_LBEXCHG_ALL
where agcode = agcode_in and wtcode like wtcode_in and iscode like iscode_in
and cpname like cpname_in and psname like psname_in
and opdate>=opdate1_in and opdate<=opdate2_in
);
else if btbz_in='0' and epflag_in='1' then--所有介绍成功,
select count( * ) into recordCount from ( select psseno
from READ_EMPDA_LBEXCHG_ALL
where agcode = agcode_in and wtcode like wtcode_in and iscode like iscode_in
and cpname like cpname_in and psname like psname_in
and opdate>=opdate1_in and opdate<=opdate2_in
and epflag_in='1'
);
else if btbz_in='1' and epflag_in='0' then--所有 有优惠券
select count( * ) into recordCount from ( select psseno
from READ_EMPDA_LBEXCHG_ALL
where agcode = agcode_in and wtcode like wtcode_in and iscode like iscode_in
and cpname like cpname_in and psname like psname_in
and opdate>=opdate1_in and opdate<=opdate2_in
and btbz_in='1'
) ;
else if btbz_in='1' and epflag_in='1' then--介绍成功,有优惠券
select count( * ) into recordCount from ( select psseno
from READ_EMPDA_LBEXCHG_ALL
where agcode = agcode_in and wtcode like wtcode_in and iscode like iscode_in
and cpname like cpname_in and psname like psname_in
and opdate>=opdate1_in and opdate<=opdate2_in
and btbz_in='1' and epflag_in='1'
) ;
end if;
end if;
end if;
end if;
DIVPAGE.getPageRownum( recordCount , pageSize , pageIndex ,
pageCount , pageMinRownum , pageMaxRownum ) ;
--所以需要外一级的查询,同时直接使用zip_in参数,可防止SQL注入
--order by opdate oracle 9.2.0.3.0 能通过
if btbz_in='0' and epflag_in='0' then--所有
open cppsrpList for select * from (
select rownum rn,psname,decode(sex,'0','女','1','男','2','/') sex,
substr(iscode,1,15)||'***' iscode,certno,cpname,wtcode,Opdate,lcno,
decode(Epflag,'0','失败','1','成功') Epflag,
decode(btbz,'0','无','1','有') btbz,psseno,rpseno,cpseno
from read_empda_lbexchg_valid
where agcode = agcode_in and wtcode like wtcode_in and iscode like iscode_in
and cpname like cpname_in and psname like psname_in
and opdate>=opdate1_in and opdate<=opdate2_in
)where rn between pageMinRownum and pageMaxRownum;
else if btbz_in='0' and epflag_in='1' then--所有介绍成功,
open cppsrpList for select * from (
select rownum rn,psname,decode(sex,'0','女','1','男','2','/') sex,
substr(iscode,1,15)||'***' iscode,certno,cpname,wtcode,Opdate,lcno,
decode(Epflag,'0','失败','1','成功') Epflag,
decode(btbz,'0','无','1','有') btbz,psseno,rpseno,cpseno
from read_empda_lbexchg_valid
where agcode = agcode_in and wtcode like wtcode_in and iscode like iscode_in
and cpname like cpname_in and psname like psname_in
and opdate>=opdate1_in and opdate<=opdate2_in
and epflag_in='1'
)where rn between pageMinRownum and pageMaxRownum;
else if btbz_in='1' and epflag_in='0' then--所有 有优惠券
open cppsrpList for select * from (
select rownum rn,psname,decode(sex,'0','女','1','男','2','/') sex,
substr(iscode,1,15)||'***' iscode,certno,cpname,wtcode,Opdate,lcno,
decode(Epflag,'0','失败','1','成功') Epflag,
decode(btbz,'0','无','1','有') btbz,psseno,rpseno,cpseno
from read_empda_lbexchg_valid
where agcode = agcode_in and wtcode like wtcode_in and iscode like iscode_in
and cpname like cpname_in and psname like psname_in
and opdate>=opdate1_in and opdate<=opdate2_in
and btbz_in='1'
) where rn between pageMinRownum and pageMaxRownum;
else if btbz_in='1' and epflag_in='1' then--介绍成功,有优惠券
open cppsrpList for select * from (
select rownum rn,psname,decode(sex,'0','女','1','男','2','/') sex,
substr(iscode,1,15)||'***' iscode,certno,cpname,wtcode,Opdate,lcno,
decode(Epflag,'0','失败','1','成功') Epflag,
decode(btbz,'0','无','1','有') btbz,psseno,rpseno,cpseno
from read_empda_lbexchg_valid
where agcode = agcode_in and wtcode like wtcode_in and iscode like iscode_in
and cpname like cpname_in and psname like psname_in
and opdate>=opdate1_in and opdate<=opdate2_in
and btbz_in='1' and epflag_in='1'
) where rn between pageMinRownum and pageMaxRownum;
end if;
end if;
end if;
end if;
end;
end lbexchgPACKAGE ;
/** 说明:* 用于记录总页数和当前页的最小和最大记录号**/
procedure Selectlbexchg
(
pageIndex in number , --页码,以1开始
pageSize in number , --每页记录数
pageCount out number , --总页数
cppsrpList out t_cursor, --当前页记录值
wtcode_in in char , --工种
cpname_in in char , --公司
psname_in in char , --姓名
iscode_in in char , --身份证号码
opdate1_in in date , --查询的条件
opdate2_in in date , --查询的条件
agcode_in in char , --介绍所
btbz_in in char , --优惠券 0全部1有
epflag_in in char --介绍结果
)
as
recordCount number ;
pageMinRownum number ;
pageMaxRownum number ;
begin
if btbz_in='0' and epflag_in='0' then--所有
select count( * ) into recordCount from ( select psseno
from READ_EMPDA_LBEXCHG_ALL
where agcode = agcode_in and wtcode like wtcode_in and iscode like iscode_in
and cpname like cpname_in and psname like psname_in
and opdate>=opdate1_in and opdate<=opdate2_in
);
else if btbz_in='0' and epflag_in='1' then--所有介绍成功,
select count( * ) into recordCount from ( select psseno
from READ_EMPDA_LBEXCHG_ALL
where agcode = agcode_in and wtcode like wtcode_in and iscode like iscode_in
and cpname like cpname_in and psname like psname_in
and opdate>=opdate1_in and opdate<=opdate2_in
and epflag_in='1'
);
else if btbz_in='1' and epflag_in='0' then--所有 有优惠券
select count( * ) into recordCount from ( select psseno
from READ_EMPDA_LBEXCHG_ALL
where agcode = agcode_in and wtcode like wtcode_in and iscode like iscode_in
and cpname like cpname_in and psname like psname_in
and opdate>=opdate1_in and opdate<=opdate2_in
and btbz_in='1'
) ;
else if btbz_in='1' and epflag_in='1' then--介绍成功,有优惠券
select count( * ) into recordCount from ( select psseno
from READ_EMPDA_LBEXCHG_ALL
where agcode = agcode_in and wtcode like wtcode_in and iscode like iscode_in
and cpname like cpname_in and psname like psname_in
and opdate>=opdate1_in and opdate<=opdate2_in
and btbz_in='1' and epflag_in='1'
) ;
end if;
end if;
end if;
end if;
DIVPAGE.getPageRownum( recordCount , pageSize , pageIndex ,
pageCount , pageMinRownum , pageMaxRownum ) ;
--所以需要外一级的查询,同时直接使用zip_in参数,可防止SQL注入
--order by opdate oracle 9.2.0.3.0 能通过
if btbz_in='0' and epflag_in='0' then--所有
open cppsrpList for select * from (
select rownum rn,psname,decode(sex,'0','女','1','男','2','/') sex,
substr(iscode,1,15)||'***' iscode,certno,cpname,wtcode,Opdate,lcno,
decode(Epflag,'0','失败','1','成功') Epflag,
decode(btbz,'0','无','1','有') btbz,psseno,rpseno,cpseno
from read_empda_lbexchg_valid
where agcode = agcode_in and wtcode like wtcode_in and iscode like iscode_in
and cpname like cpname_in and psname like psname_in
and opdate>=opdate1_in and opdate<=opdate2_in
)where rn between pageMinRownum and pageMaxRownum;
else if btbz_in='0' and epflag_in='1' then--所有介绍成功,
open cppsrpList for select * from (
select rownum rn,psname,decode(sex,'0','女','1','男','2','/') sex,
substr(iscode,1,15)||'***' iscode,certno,cpname,wtcode,Opdate,lcno,
decode(Epflag,'0','失败','1','成功') Epflag,
decode(btbz,'0','无','1','有') btbz,psseno,rpseno,cpseno
from read_empda_lbexchg_valid
where agcode = agcode_in and wtcode like wtcode_in and iscode like iscode_in
and cpname like cpname_in and psname like psname_in
and opdate>=opdate1_in and opdate<=opdate2_in
and epflag_in='1'
)where rn between pageMinRownum and pageMaxRownum;
else if btbz_in='1' and epflag_in='0' then--所有 有优惠券
open cppsrpList for select * from (
select rownum rn,psname,decode(sex,'0','女','1','男','2','/') sex,
substr(iscode,1,15)||'***' iscode,certno,cpname,wtcode,Opdate,lcno,
decode(Epflag,'0','失败','1','成功') Epflag,
decode(btbz,'0','无','1','有') btbz,psseno,rpseno,cpseno
from read_empda_lbexchg_valid
where agcode = agcode_in and wtcode like wtcode_in and iscode like iscode_in
and cpname like cpname_in and psname like psname_in
and opdate>=opdate1_in and opdate<=opdate2_in
and btbz_in='1'
) where rn between pageMinRownum and pageMaxRownum;
else if btbz_in='1' and epflag_in='1' then--介绍成功,有优惠券
open cppsrpList for select * from (
select rownum rn,psname,decode(sex,'0','女','1','男','2','/') sex,
substr(iscode,1,15)||'***' iscode,certno,cpname,wtcode,Opdate,lcno,
decode(Epflag,'0','失败','1','成功') Epflag,
decode(btbz,'0','无','1','有') btbz,psseno,rpseno,cpseno
from read_empda_lbexchg_valid
where agcode = agcode_in and wtcode like wtcode_in and iscode like iscode_in
and cpname like cpname_in and psname like psname_in
and opdate>=opdate1_in and opdate<=opdate2_in
and btbz_in='1' and epflag_in='1'
) where rn between pageMinRownum and pageMaxRownum;
end if;
end if;
end if;
end if;
end;
end lbexchgPACKAGE ;
v_epflag_in CHAR(1):=epflag_in;
v_btbz_in CHAR(1):=btbz_in;
BEGIN
SELECT COUNT(*)
INTO recordCount
FROM (SELECT psseno
FROM READ_EMPDA_LBEXCHG_ALL
WHERE agcode = agcode_in
AND wtcode LIKE wtcode_in
AND iscode LIKE iscode_in
AND cpname LIKE cpname_in
AND psname LIKE psname_in
AND opdate >= opdate1_in
AND opdate <= opdate2_in
AND epflag_in=decode(v_epflag_in,'1','1',epflag_in)
AND btbz_in=decode(v_btbz_in,'1','1',btbz_in);
下面的SQL同理
AND btbz_in=decode(v_btbz_in,'1','1',btbz_in);应 改为AND epflag=decode(v_epflag_in,'1','1',epflag)
AND btbz=decode(v_btbz_in,'1','1',btbz);