--create or replace procedure proc_card_operate is 工作模式用其替换declare
declare
cursor vcursor_pay is select * from jn1_pay where tgflag = '1' and gencardflag ='0' and risktype<>'C' order by id;
type vtype_pay is table of jn1_pay%rowtype index by binary_integer;
--type vtype_amount is table of jn1_pay.amount%type;
vrow_card jn_card%rowtype;
v_flag jn_card.flag%type;
v_max_tmount jn1_pay.tmount%type;
v_allamt jn_card.allamt%type;
v_update_count integer := 0;
--以上五个为升降级变量
vrow_pay jn1_pay%rowtype;
vtable_pay vtype_pay;
v_risktype jn1_pay.risktype%type;
v_paycode jn1_pay.paycode%type;
v_max_amount jn1_pay.amount%type;
v_a_amount jn1_pay.amount%type;
v_e_amount jn1_pay.amount%type;
v_f_amount jn1_pay.amount%type;
v_cardno cardno.newcardno%type;
v_cardtype jn_card.cardtype%type;
v_date jn_card.recdate%type;
v_xztype jn_card.xztype%type;
v_jftype jn_card.jftype%type;
v_check_id boolean;
v_islast boolean;
v_pay_count integer;
v_count integer;
v_s_type_count integer := 0;
v_j_type_count integer := 0;
c_factor constant number := 0.05;
begin
savepoint p_full;
open vcursor_pay;
loop
fetch vcursor_pay bulk collect into vtable_pay limit 1000;
if v_islast then
v_pay_count:=1;
else
v_pay_count:=vtable_pay.count;
end if;
for i in 1..v_pay_count loop
savepoint p_part;
--vrow_pay.id is null用于第一条记录,v_islast用于最后一条记录
if( vrow_pay.id is null or v_islast or vtable_pay(i).id<>vrow_pay.id) then
if(v_check_id) then
--缴费方式 A-年缴 B-趸缴 C-其它(不定期)
v_jftype := 'A';
if(v_a_amount>=v_e_amount and v_a_amount>=v_f_amount) then
v_max_amount:=v_a_amount;
v_xztype := 'A';
elsif(v_e_amount>=v_f_amount) then
v_max_amount:=v_e_amount;
v_xztype := 'E';
v_jftype := 'C';
else
v_max_amount:=v_f_amount;
v_xztype := 'F';
end if;
--判断是否有卡
select count(id) into v_count from jn_card where cardflag='1' and ompno=vrow_pay.ompno and id=vrow_pay.id;
select sysdate into v_date from dual;
if v_count=0 then
if(v_max_amount>=60000) then
if(v_max_amount>=100000) then
v_cardtype:='S';
v_s_type_count := v_s_type_count+1;
else
v_cardtype:='J';
v_j_type_count := v_j_type_count+1;
end if;
select replace(lpad(max(newcardno)+1,11,'0'),'4','5') into v_cardno from cardno;
--flag发卡标志 0-新 1-升降 2-已制卡 3-失效后不能制卡
--status领卡状态 Y-已领卡 N-未领卡
insert into jn_card(ompno,id,cardflag,grpcode,cardtype,cardno,xztype,jftype,allamt,recdate,flag,operno,operdate,empno,deptno,frecdate,status)
values(vrow_pay.ompno,vrow_pay.id,'1',null,v_cardtype,v_cardno,v_xztype,v_jftype,v_max_amount,v_date,'0',null,vrow_pay.otime,vrow_pay.empno,vrow_pay.deptno,v_date,'N');
update jn1_pay set gencardflag='1' where id =vrow_pay.id;
insert into cardno (newcardno) values (v_cardno);
else
update jn1_pay set gencardflag='2',otime=sysdate where id =vrow_pay.id;
end if;
else
--新标准升降级
if(v_max_amount>=60000) then
if(v_max_amount>=100000) then
v_cardtype:='S';
else
v_cardtype:='J';
end if;
else
v_cardtype:='D';
end if;
select * into vrow_card from jn_card where cardflag='1' and ompno=vrow_pay.ompno and id=vrow_pay.id;
--A、B需要结合新老两种标准
if(vrow_card.cardtype='B' and vrow_card.xztype<>'E') then
--cardtype='B'和xztype<>'E'的情况用tmount计算
v_allamt:=v_max_tmount;
else
v_allamt:=v_max_amount;
end if;
if((vrow_card.cardtype='A' or vrow_card.cardtype='B') and v_cardtype='D' and v_allamt>=vrow_card.allamt) then
v_cardtype:=vrow_card.cardtype;
end if;
if(vrow_card.cardtype<>v_cardtype) then
v_flag:=1;
select replace(lpad(max(newcardno)+1,11,'0'),'4','5') into v_cardno from cardno;
insert into jn_chgcard(ompno,id,oldcardno,oldcardtype,newcardno,newcardtype,begdate,empno,deptno,operno,operdate)
values (vrow_card.ompno,vrow_card.id,vrow_card.cardno,vrow_card.cardtype,v_cardno,v_cardtype,vrow_card.frecdate,vrow_card.empno,vrow_card.deptno,vrow_card.operno,v_date);
insert into cardno (newcardno) values (v_cardno);
v_update_count:=v_update_count+1;
dbms_output.put_line(vrow_card.cardtype||','||vrow_card.allamt||'===>'||v_cardtype||','||v_allamt);
else
v_flag:=0;
v_cardno:=vrow_card.cardno;
end if;
update jn_card set cardno=v_cardno,cardtype=v_cardtype,xztype=v_xztype,jftype=v_jftype,allamt=v_allamt,operdate=v_date,flag=v_flag,recdate=v_date
where cardflag='1' and ompno=vrow_card.ompno and id=vrow_card.id;
update jn1_pay set gencardflag='1' where id =vrow_card.id;
end if;
end if;
--退出当前循环
exit when v_islast;
select count(id) into v_count from jn1_custmatl where id not in
(select id from jn_dummy) and id=vtable_pay(i).id;
if v_count=0 then
v_check_id := false;
update jn1_pay set gencardflag='2',otime=sysdate where id =vtable_pay(i).id;
else
v_check_id := true;
v_a_amount:=0;
v_e_amount:=0;
v_f_amount:=0;
v_max_tmount:=0;
end if;
end if;
if(v_check_id) then
v_risktype := vtable_pay(i).risktype;
v_paycode := vtable_pay(i).paycode;
case
when v_risktype='A'then
if(v_paycode='01') then
v_a_amount := v_a_amount+(vtable_pay(i).amount*c_factor*2);
elsif(v_paycode='13') then
v_a_amount := v_a_amount+(vtable_pay(i).amount*c_factor*2*10);
end if;
when v_risktype='E'then
v_e_amount := v_e_amount+(vtable_pay(i).amount*c_factor);
when v_risktype='F'then
if(v_paycode='01') then
v_f_amount := v_f_amount+(vtable_pay(i).amount*c_factor);
elsif(v_paycode='13') then
v_f_amount := v_f_amount+(vtable_pay(i).amount*c_factor*10);
end if;
else null;
end case;
--老银卡需要计算tmount,E类账户险种除外
if(vtable_pay(i).tmount>v_max_tmount) then
v_max_tmount := vtable_pay(i).tmount;
end if;
vrow_pay:=vtable_pay(i);
end if;
end loop;
--exit when vcursor_pay%notfound;
--在最后一条记录后面再循环一次
exit when v_islast;
if vcursor_pay%notfound then
v_islast := true;
end if;
end loop;
close vcursor_pay;
insert into ids values('J:'||v_j_type_count||' S:'||v_s_type_count||' U:'||v_update_count,sysdate);
exception when others then
rollback to savepoint p_full;
end;
分享到:
相关推荐
create or replace procedure qdy_pro2(p_cursor out qdy_package.qdy_cursor) is begin open p_cursor for select * from emp; end; --函数 create function qdy_fun1(name varchar2) return number is yearsal ...
赠送jar包:hbase-procedure-1.4.3.jar; 赠送原API文档:hbase-procedure-1.4.3-javadoc.jar; 赠送源代码:hbase-procedure-1.4.3-sources.jar; 赠送Maven依赖信息文件:hbase-procedure-1.4.3.pom; 包含翻译后...
oracle procedureoracle procedureoracle procedure
存储过程(Stored Procedure)是一组为了完 成特定功能的 SQL 语句集,经编译后存储在数据库中。用户通过指定存储过程的名字并给 出参数(如果该存储过程带有参数)来执行它。存储过程是数据库中的一个重要对象,...
赠送jar包:hbase-procedure-1.1.3.jar; 赠送原API文档:hbase-procedure-1.1.3-javadoc.jar; 赠送源代码:hbase-procedure-1.1.3-sources.jar; 赠送Maven依赖信息文件:hbase-procedure-1.1.3.pom; 包含翻译后...
sybase stored procedure
Using Informatica Stored Procedure Transformation
Delphi procedure function,delphi函数与过程详细说明,pdf文档。
Procedure Call Standard for the ARM® Architecture
CREATE PROCEDURE DB2ADMIN.doc CREATE PROCEDURE DB2ADMIN.doc
这个是本人在学习MySQL数据库时的一个小的总结,希望对大家有用。主要是关于如何在MySQL中创建procedure、function、trigger.
Asynchronous Procedure Calls ,也就是异步过程调用,这份文档是内核中APC的详尽介绍,值得一看
UL FUS procedure file
SAP_MRP_PROCEDURE_TYP.pdf
sql server procedure
1.50% Manikin seat at the middle lowest point as the crash test situation 2.IP and door styling released recently 3.Floor console styling released ...Knee impact zone is defined by using the procedure.
ISO 27001 Corrective action procedure
A curve fitting procedure to derive inter-annual phenologies from time series of noisy satellite NDVI data
oracle 包 procedureoracle 包 procedure
Procedure Call Standard for the ARM 64-bit Architecture (AArch64)