1）建一张表
create table GATHER_DATA_TEMP
(
gather_id NUMBER,
insurance_policy_id VARCHAR2(100),
scale_premium NUMBER,
standard_premium NUMBER,
remark VARCHAR2(10),
operate_date DATE,
payment_method VARCHAR2(50),
payment_frequency VARCHAR2(10),
start_date DATE,
end_date DATE,
chinese_name VARCHAR2(80),
english_name VARCHAR2(100),
nationality VARCHAR2(20),
identity_type VARCHAR2(30),
identity_code VARCHAR2(30),
marital_status VARCHAR2(20),
year_income VARCHAR2(20),
is_other_insure VARCHAR2(10),
is_smoke VARCHAR2(10),
stature VARCHAR2(10),
weight VARCHAR2(10),
industry_type VARCHAR2(10),
company VARCHAR2(50),
company_address VARCHAR2(100),
company_postcode VARCHAR2(10),
position VARCHAR2(50),
birth_date VARCHAR2(30),
age NUMBER,
sex VARCHAR2(20),
email VARCHAR2(30),
phone VARCHAR2(20),
address VARCHAR2(100),
postcode VARCHAR2(30),
location VARCHAR2(100),
partner_code VARCHAR2(50),
registration_no VARCHAR2(100),
legal_representative VARCHAR2(50),
legal_code VARCHAR2(100),
partner_name VARCHAR2(100),
partner_type VARCHAR2(50),
institution_id VARCHAR2(50),
lattice_point_code VARCHAR2(100),
linkman VARCHAR2(50),
contact_phone VARCHAR2(50),
partner_address VARCHAR2(100),
partner_postcode VARCHAR2(50),
partner_state VARCHAR2(10),
teller_code VARCHAR2(50),
person_id NUMBER,
salesman_code VARCHAR2(50),
salesman_name VARCHAR2(50),
equipment_serial_number VARCHAR2(100),
flow_name VARCHAR2(100),
operator VARCHAR2(50),
flow_operation_date DATE,
institution_type VARCHAR2(50),
institution_name VARCHAR2(100),
salesman_phone VARCHAR2(30),
superior_institution_id VARCHAR2(50),
product_id VARCHAR2(50),
product_type VARCHAR2(100),
product_name VARCHAR2(100),
insure_type VARCHAR2(100),
coefficient NUMBER,
insured_amount NUMBER,
distribution_type VARCHAR2(50),
distribution_name VARCHAR2(50),
trade_date DATE,
flag VARCHAR2(10),
rownumber number
)
2）创建存储过程
create or replace procedure PRO_GATHER_DATA_TEMP(dispose_strip_count number,start_date varchar2,end_date varchar2,describe out varchar2 )
IS
draw_sql varchar2(5000);
type cur_type is ref cursor; --定义游标类型
cur_sors cur_type;--使用游标类型
sql_count number(19);
g_count number(10);
pangeNow number(10);
v_rowdata GATHER_DATA_TEMP%rowtype; --类型是表的全字段
i number;
c_count number;
i_count number;
BEGIN
c_count:=0;
i_count:=0;
select count(*) into sql_count from t_policy po
inner join t_biz_activity ba
on ba.policy_id = po.id
inner join t_policy_apply pa
on pa.id = ba.id
inner join t_product pr
on pr.id = po.main_product_id
inner join t_partner pt
on pt.id = ba.partner_id
inner join t_organization_cpic oc
on oc.id = pt.organization_id
inner join t_organization_cpic poc
on poc.id = oc.parent_id
left join t_life_policy lp
on lp.id = po.id
left join t_holder ho
on po.holder_id = ho.id
left join t_person hop
on hop.id = ho.person_id
where po.status in ('0','1')
and po.apply_date>=to_date(start_date||' 00:00:00','YYYY-MM-DD hh24:mi:ss')
and po.apply_date<=to_date(end_date||' 23:59:59','YYYY-MM-DD hh24:mi:ss');
if mod(sql_count,dispose_strip_count)=0
then pangeNow:=sql_count/dispose_strip_count;
end if;
if mod(sql_count,dispose_strip_count)!=0
then pangeNow:=sql_count/dispose_strip_count+0.5;
end if;
describe:='数据共:'||sql_count||':条';
i:=1;
for i in 1..pangeNow loop
draw_sql:='select * from(select t.* from (select NULL GATHER_ID,
po.policy_no INSURANCE_POLICY_ID,
po.policy_fee SCALE_PREMIUM,
NULL STANDARD_PREMIUM,
NULL REMARK,
ba.trans_time OPERATE_DATE,
lp.pay_mode PAYMENT_METHOD,
lp.bllng_frequency PAYMENT_FREQUENCY,
po.in_force_date START_DATE,
po.expiry_date END_DATE,
hop.name CHINESE_NAME,
hop.english_name ENGLISH_NAME,
ho.nationality NATIONALITY,
hop.govt_idtc IDENTITY_TYPE,
hop.govt_id IDENTITY_CODE,
ho.marital_status MARITAL_STATUS,
NULL YEAR_INCOME,
null IS_OTHER_INSURE,
null IS_SMOKE,
null STATURE,
null WEIGHT,
ho.occupation_code INDUSTRY_TYPE,
ho.work_unit COMPANY,
null COMPANY_ADDRESS,
null COMPANY_POSTCODE,
null POSITION,
hop.birthdate BIRTH_DATE,
NULL AGE,
hop.gender SEX,
ho.email EMAIL,
hop.telphone_number PHONE,
null ADDRESS,
ho.mail_address_zip POSTCODE,
ho.resident_erea LOCATION,
pt.code PARTNER_CODE,
null REGISTRATION_NO,
null LEGAL_REPRESENTATIVE,
null LEGAL_CODE,
pt.full_name PARTNER_NAME,
null PARTNER_TYPE,
oc.code INSTITUTION_ID,
pa.branch LATTICE_POINT_CODE,
null LINKMAN,
null CONTACT_PHONE,
null PARTNER_ADDRESS,
null PARTNER_POSTCODE,
pt.status PARTNER_STATE,
pt.teller TELLER_CODE,
hop.id PERSON_ID,
pa.teller SALESMAN_CODE,
null SALESMAN_NAME,
pa.equipment_no EQUIPMENT_SERIAL_NUMBER,
po.status FLOW_NAME,
null OPERATOR,
NULL FLOW_OPERATION_DATE,
oc.org_level INSTITUTION_TYPE,
oc.name INSTITUTION_NAME,
null SALESMAN_PHONE,
poc.code SUPERIOR_INSTITUTION_ID,
pr.code PRODUCT_ID,
pr.classes PRODUCT_TYPE,
pr.name PRODUCT_NAME,
pr.type INSURE_TYPE,
NULL COEFFICIENT,
NULL INSURED_AMOUNT,
null DISTRIBUTION_TYPE,
null DISTRIBUTION_NAME,
po.apply_date TRADE_DATE,
null FLAG,
rownum y
from t_policy po inner join t_biz_activity ba on ba.policy_id = po.id
inner join t_policy_apply pa
on pa.id = ba.id
inner join t_product pr
on pr.id = po.main_product_id
inner join t_partner pt
on pt.id = ba.partner_id
inner join t_organization_cpic oc
on oc.id = pt.organization_id
inner join t_organization_cpic poc
on poc.id = oc.parent_id
left join t_life_policy lp
on lp.id = po.id
left join t_holder ho
on po.holder_id = ho.id
left join t_person hop
on hop.id = ho.person_id
where and po.apply_date>=to_date('||''''||start_date||' 00:00:00'||''''||','||'''YYYY-MM-DD hh24:mi:ss'''||')
and po.apply_date<=to_date('||''''||end_date||' 23:59:59'||''''||','||'''YYYY-MM-DD hh24:mi:ss'''||')Order By INSURANCE_POLICY_ID)t)
where y>('||i||'-1)*'||dispose_strip_count||'
and rownum<=('||i||')*'||dispose_strip_count;
open cur_sors for draw_sql; --
savepoint sp; --定义断点
loop --事先不知道循环次数的循环
fetch cur_sors into v_rowdata;--取游标中一行数据放入v_rowdata
exit when cur_sors%notfound;
select count(*) into g_count from GATHER_DATA_TEMP where INSURANCE_POLICY_ID= v_rowdata.INSURANCE_POLICY_ID and TRADE_DATE=v_rowdata.TRADE_DATE;
if g_count>0 then
c_count:=c_count+1;
end if;
if g_count=0 then --从v_rowdata中取数据插入表
insert into GATHER_DATA_TEMP(GATHER_ID,INSURANCE_POLICY_ID,SCALE_PREMIUM,OPERATE_DATE,
PAYMENT_METHOD,PAYMENT_FREQUENCY,START_DATE,END_DATE,
CHINESE_NAME,ENGLISH_NAME,NATIONALITY,IDENTITY_TYPE,
IDENTITY_CODE,MARITAL_STATUS,INDUSTRY_TYPE,COMPANY,
BIRTH_DATE,SEX,EMAIL,PHONE,POSTCODE,LOCATION,PARTNER_CODE,
PARTNER_NAME,INSTITUTION_ID,LATTICE_POINT_CODE,
PARTNER_STATE,TELLER_CODE,PERSON_ID,SALESMAN_CODE,
EQUIPMENT_SERIAL_NUMBER,INSTITUTION_TYPE,INSTITUTION_NAME,
SUPERIOR_INSTITUTION_ID,PRODUCT_ID,PRODUCT_TYPE,PRODUCT_NAME,
INSURE_TYPE,TRADE_DATE,FLAG) values(rptds.SEQ_GATHER_DATA_TEMP.nextval,
v_rowdata.INSURANCE_POLICY_ID,v_rowdata.SCALE_PREMIUM,v_rowdata.OPERATE_DATE,
v_rowdata.PAYMENT_METHOD,v_rowdata.PAYMENT_FREQUENCY,v_rowdata.START_DATE,v_rowdata.END_DATE,
v_rowdata.CHINESE_NAME,v_rowdata.ENGLISH_NAME,v_rowdata.NATIONALITY,v_rowdata.IDENTITY_TYPE,
v_rowdata.IDENTITY_CODE,v_rowdata.MARITAL_STATUS,v_rowdata.INDUSTRY_TYPE,v_rowdata.COMPANY,
v_rowdata.BIRTH_DATE,v_rowdata.SEX,v_rowdata.EMAIL,v_rowdata.PHONE,v_rowdata.POSTCODE,v_rowdata.LOCATION,v_rowdata.PARTNER_CODE,
v_rowdata.PARTNER_NAME,v_rowdata.INSTITUTION_ID,v_rowdata.LATTICE_POINT_CODE,
v_rowdata.PARTNER_STATE,v_rowdata.TELLER_CODE,v_rowdata.PERSON_ID,v_rowdata.SALESMAN_CODE,
v_rowdata.EQUIPMENT_SERIAL_NUMBER,v_rowdata.INSTITUTION_TYPE,v_rowdata.INSTITUTION_NAME,
v_rowdata.SUPERIOR_INSTITUTION_ID,v_rowdata.PRODUCT_ID,v_rowdata.PRODUCT_TYPE,v_rowdata.PRODUCT_NAME,
v_rowdata.INSURE_TYPE,v_rowdata.TRADE_DATE,'E');
i_count:=i_count+1;
end if;
end loop;
close cur_sors;
end loop;
describe:=describe||',数据重复:'||c_count||':条,'||'已成功:'||i_count||':条';
EXCEPTION
WHEN OTHERS THEN
describe:='error-sqlcode :'||sqlcode||':error-sqlerrm:'||sqlerrm||':'||describe||',数据重复:'||c_count||':条,'||'已成功:'||i_count||':条';
commit;
END PRO_GATHER_DATA_TEMP;
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
