提取累计保额过百万的客户信息（存储过程）

在一知半解的情况下，竟也写出存储过程完成任务，不得不感叹人是逼出来的。。
虽然比较粗糙，不过也记着留作纪念，毕竟第一次嘛～

--从系统中提取上述个人累计保额超过100万元[险种为红利发两全保险（分红型）、保得利两全保险（分红型）、红利发两全保险C款(分红型)及红利宝两全保险(分红型)]业务的被保险人清单，该清单应包括如下信息：投保单号、保单号、被保险人的身份证号、投保险种、投保份数、保单生效日期、被保险人姓名、年龄，此次新保保额、累计保额

Drop procedure sp_million;
CREATE PROCEDURE sp_million(i_start_date date,i_end_date date,i_addprecount decimal(12,2))
RETURNING char (15),
          char(15),
          char(6),
          decimal(12,3),
          date,
          char(60),
          integer,
          decimal(12,2);
         
define v_pid char (18);                --身份证号
define v_appno char (15);            --投保单号
define v_policyno char(15);            --保单号
define v_classcode char(6);          --投保险种
define v_pieces decimal(12,3);        --投保分数
define v_begdate date;                --保单生效日期
define v_name char(60);                --被保人姓名
define v_age integer;                --被保人年龄
define v_addpre decimal(12,2);        --新保保额
define v_addpresum decimal(12,2);    --累计保额
define v_nowyear integer;
define v_bthyear integer;


--创建临时表temp_2
create temp table temp_2(
pid char (18),
appno char (15),
policyno char(15),
classcode char(6),
pieces decimal(12,3),
begdate date,
name char(60),
age integer,
addpre decimal(12,2)
) with no log;

--创建临时表temp_3
create temp table temp_3(
pid char (18),
appno char (15),
policyno char(15),
classcode char(6),
pieces decimal(12,3),
begdate date,
name char(60),
age integer,
addpre decimal(12,2)
) with no log;

--从riskcon表中获得被保人身份证pid?
select pid from riskcon
where begdate between i_start_date and i_end_date and polist=2 and (classcode ='EL4611' or classcode ='EL4612' or classcode ='EL4613' or classcode ='EL4614' or classcode ='EL4615' or classcode ='EL4711' or classcode ='EL4712' or classcode ='EL6102')
into temp temp_1 with no log;

--遍历pid，取累计保额超过v_addpresum的被保人投保单号、保单号、投保险种、投保分数、保单生效日期、被保人姓名、被保人年龄、新保保额、累计保额
foreach

select pid into v_pid from temp_1

--被保人姓名
select name into v_name from custmatl where id=v_pid;

--从custmatl表中取生日后计算被保人年龄
let v_nowyear=YEAR(today);
let v_bthyear=(select YEAR(bthdate) from custmatl where id=v_pid);

--如果被保人生日为空，取1900年为生日
if v_bthyear is null or v_bthyear='' then
let v_bthyear=1900;
end if

let v_age=v_nowyear-v_bthyear;

--从chkpreacct1999-chkpreacct2007表中取所需信息并存入临时表temp_2?
foreach
select appno,policyno,classcode,pieces,begdate,addpre
into v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_addpre from chkpreacct1999
where pid=v_pid and movedate is null and precode='001' and (classcode ='EL4611' or classcode ='EL4612' or classcode ='EL4613' or classcode ='EL4614' or classcode ='EL4615' or classcode ='EL4711' or classcode ='EL4712' or classcode ='EL6102')

insert into temp_2(pid,appno,policyno,classcode,pieces,begdate,name,age,addpre)
values(v_pid,v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_name,v_age,v_addpre);
end foreach

foreach
select appno,policyno,classcode,pieces,begdate,addpre
into v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_addpre from chkpreacct2000
where pid=v_pid and movedate is null and precode='001' and (classcode ='EL4611' or classcode ='EL4612' or classcode ='EL4613' or classcode ='EL4614' or classcode ='EL4615' or classcode ='EL4711' or classcode ='EL4712' or classcode ='EL6102')

insert into temp_2(pid,appno,policyno,classcode,pieces,begdate,name,age,addpre)
values(v_pid,v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_name,v_age,v_addpre);
end foreach

foreach
select appno,policyno,classcode,pieces,begdate,addpre
into v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_addpre from chkpreacct2001
where pid=v_pid and movedate is null and precode='001' and (classcode ='EL4611' or classcode ='EL4612' or classcode ='EL4613' or classcode ='EL4614' or classcode ='EL4615' or classcode ='EL4711' or classcode ='EL4712' or classcode ='EL6102')

insert into temp_2(pid,appno,policyno,classcode,pieces,begdate,name,age,addpre)
values(v_pid,v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_name,v_age,v_addpre);
end foreach

foreach
select appno,policyno,classcode,pieces,begdate,addpre
into v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_addpre from chkpreacct2002
where pid=v_pid and movedate is null and precode='001' and (classcode ='EL4611' or classcode ='EL4612' or classcode ='EL4613' or classcode ='EL4614' or classcode ='EL4615' or classcode ='EL4711' or classcode ='EL4712' or classcode ='EL6102')

insert into temp_2(pid,appno,policyno,classcode,pieces,begdate,name,age,addpre)
values(v_pid,v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_name,v_age,v_addpre);
end foreach

foreach
select appno,policyno,classcode,pieces,begdate,addpre
into v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_addpre from chkpreacct2003
where pid=v_pid and movedate is null and precode='001' and (classcode ='EL4611' or classcode ='EL4612' or classcode ='EL4613' or classcode ='EL4614' or classcode ='EL4615' or classcode ='EL4711' or classcode ='EL4712' or classcode ='EL6102')

insert into temp_2(pid,appno,policyno,classcode,pieces,begdate,name,age,addpre)
values(v_pid,v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_name,v_age,v_addpre);
end foreach

foreach
select appno,policyno,classcode,pieces,begdate,addpre
into v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_addpre from chkpreacct2004
where pid=v_pid and movedate is null and precode='001' and (classcode ='EL4611' or classcode ='EL4612' or classcode ='EL4613' or classcode ='EL4614' or classcode ='EL4615' or classcode ='EL4711' or classcode ='EL4712' or classcode ='EL6102')

insert into temp_2(pid,appno,policyno,classcode,pieces,begdate,name,age,addpre)
values(v_pid,v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_name,v_age,v_addpre);
end foreach

foreach
select appno,policyno,classcode,pieces,begdate,addpre
into v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_addpre from chkpreacct2005
where pid=v_pid and movedate is null and precode='001' and (classcode ='EL4611' or classcode ='EL4612' or classcode ='EL4613' or classcode ='EL4614' or classcode ='EL4615' or classcode ='EL4711' or classcode ='EL4712' or classcode ='EL6102')

insert into temp_2(pid,appno,policyno,classcode,pieces,begdate,name,age,addpre)
values(v_pid,v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_name,v_age,v_addpre);
end foreach

foreach
select appno,policyno,classcode,pieces,begdate,addpre
into v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_addpre from chkpreacct2006
where pid=v_pid and movedate is null and precode='001' and (classcode ='EL4611' or classcode ='EL4612' or classcode ='EL4613' or classcode ='EL4614' or classcode ='EL4615' or classcode ='EL4711' or classcode ='EL4712' or classcode ='EL6102')

insert into temp_2(pid,appno,policyno,classcode,pieces,begdate,name,age,addpre)
values(v_pid,v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_name,v_age,v_addpre);
end foreach

foreach
select appno,policyno,classcode,pieces,begdate,addpre
into v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_addpre from chkpreacct2007
where pid=v_pid and movedate is null and precode='001' and (classcode ='EL4611' or classcode ='EL4612' or classcode ='EL4613' or classcode ='EL4614' or classcode ='EL4615' or classcode ='EL4711' or classcode ='EL4712' or classcode ='EL6102')

insert into temp_2(pid,appno,policyno,classcode,pieces,begdate,name,age,addpre)
values(v_pid,v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_name,v_age,v_addpre);
end foreach

--从temp_2表中取保额超过i_addprecount的信息存入temp_3
select sum(addpre) into v_addpresum from temp_2;

if v_addpresum>i_addprecount then
insert into temp_3 select * from temp_2;
end if

--清空temp_2
delete from temp_2 where 1=1;

end foreach

foreach

--取所需字段
select appno,policyno,classcode,pieces,begdate,name,age,addpre
into v_appno,v_policyno,v_classcode,v_pieces,v_begdate,v_name,v_age,v_addpre
from temp_3

return v_appno,
        v_policyno,
        v_classcode,
        v_pieces,
        v_begdate,
        v_name,
        v_age,
        v_addpre with resume;

end foreach

drop table temp_1;
drop table temp_2;
drop table temp_3;

END PROCEDURE;