PL/SQL存儲過程-中國電力基本檔案存儲功能-僅供參考，pl存儲過程
CREATE OR REPLACE PACKAGE BODY BASE_INFO AS
PROCEDURE insert_lapc(lapc In lapc_array) AS
v_max number;
v_result number;
v_status number;
BEGIN
delete from TMP_LAPC;
v_result := -10;
--數據插入臨時表
FOR i IN lapc.FIRST .. lapc.LAST LOOP
BEGIN
insert into tmp_lapc
(lapc_id,
customer_id,
lapc_name,
sort,
lapc_addr,
tel,
cons_sort_code,
ms_mode,
trade_code,
volt_code,
lode_attr_code,
contract_cap,
status_code,
vip_code,
line_id,
powersupplyofficeid,
townid)
values
(lapc(i).lapc_id,
lapc(i).customer_id,
lapc(i).lapc_name,
lapc(i).sort,
lapc(i).lapc_addr,
lapc(i).tel,
lapc(i).cons_sort_code,
lapc(i).ms_mode,
lapc(i).trade_code,
lapc(i).volt_code,
lapc(i).lode_attr_code,
lapc(i).contract_cap,
lapc(i).status_code,
lapc(i).vip_code,
lapc(i).line_id,
lapc(i).powersupplyofficeid,
lapc(i).townid);
END;
END LOOP;
v_result := -20;
--代碼轉換
update tmp_lapc a
set a.line_id_new =
(select b.line_id
from g_line b
where a.line_id = b.guid
and rownum = 1);
update tmp_lapc a
set a.townid_new =
(select b.org_no
from O_ORG b
where a.townid = b.guid
and rownum = 1);
update tmp_lapc a
set a.group_id =
(select b.org_no
from O_ORG b
where a.powersupplyofficeid = b.guid
and rownum = 1);
update tmp_lapc a
set a.consid =
(select b.cons_id
from C_CONS b
where a.lapc_id = b.guid
and rownum = 1);
v_result := -30;
-- delete from tmp_lapc a where a.townid_new is null;
-- delete from tmp_lapc a where a.line_id_new is null;
--更新正式表
update C_CONS a
set (a.cons_id,
a.cons_name,
a.cons_no,
a.cons_addr,
a.tel1,
a.CONS_SORT_CODE,
a.meas_mode,
a.trade_code,
a.volt_code,
a.lode_attr_code,
a.contract_cap,
a.status_code,
a.vip_code,
a.line_id,
a.org_no,
a.area_code,
a.savedatetime,
a.power_type_code) =
(select b.consid,
b.lapc_name,
b.customer_id,
b.lapc_addr,
b.tel,
'01',
b.MS_MODE,
b.trade_code,
b.volt_code,
b.lode_attr_code,
b.contract_cap,
b.status_code,
b.vip_code,
b.line_id_new,
b.group_id,
b.townid_new,
sysdate,
b.cons_sort_code
from tmp_lapc b
where a.cons_id = b.consid
and rownum = 1)
where exists (select 1 from tmp_lapc b where a.cons_id = b.consid);
v_result := -40;
--刪除更新完的數據
delete from tmp_lapc a where a.consid is not null;
--插入正式表(只支持單線程)
select nvl(max(cons_id), 0) into v_max from c_cons;
insert into c_cons a
(a.cons_id,
a.cons_name,
a.cons_no,
a.cons_addr,
a.tel1,
a.CONS_SORT_CODE,
a.meas_mode,
a.trade_code,
a.volt_code,
a.lode_attr_code,
a.contract_cap,
a.status_code,
a.vip_code,
a.line_id,
a.org_no,
a.area_code,
a.guid,
a.savedatetime)
select v_max + rownum,
b.lapc_name,
b.customer_id,
b.lapc_addr,
b.tel,
b.cons_sort_code,
b.MS_MODE,
b.trade_code,
b.volt_code,
b.lode_attr_code,
b.contract_cap,
b.status_code,
b.vip_code,
b.line_id_new,
b.group_id,
b.townid_new,
b.lapc_id,
sysdate
from tmp_lapc b;
commit;
EXCEPTION
WHEN OTHERS THEN
begin
ROLLBACK;
v_status := ERROR_LOG(v_result, SQLCODE, SQLERRM, 'insert_lapc');
end;
END insert_lapc;
PROCEDURE insert_amsm(amsm In amsm_array) AS
v_max number;
v_result number;
v_status number;
BEGIN
delete from TMP_AMSM;
--數據插入臨時表
v_result := -10;
FOR i IN amsm.FIRST .. amsm.LAST LOOP
BEGIN
insert into tmp_amsm
(mped_id,
mped_name,
volt_code,
cons_id,
tgid,
subs_id,
mp_type,
trade_code,
side_code,
switch_no,
total_flag,
meas_mode,
wiring_mode,
m_mped_id,
status_code,
pt_value,
ct_value,
t_factor,
mp_calc_mode,
cons_mp_calc_mode,
powersupplyofficeid,
groupid)
values
(amsm(i).mped_id,
amsm(i) . mped_name,
amsm(i).volt_code,
amsm(i).cons_id,
amsm(i).tgid,
amsm(i).subs_id,
decode(amsm(i).mp_type,'02','03','03','02',amsm(i).mp_type),
amsm(i).trade_code,
amsm(i).side_code,
amsm(i).switch_no,
amsm(i).total_flag,
amsm(i).meas_mode,
amsm(i).wiring_mode,
amsm(i).m_mped_id,
amsm(i).status_code,
amsm(i).pt_value,
amsm(i).ct_value,
amsm(i).t_factor,
amsm(i).mp_calc_mode,
amsm(i).cons_mp_calc_mode,
amsm(i).powersupplyofficeid,
amsm(i).groupid);
END;
END LOOP;
--代碼轉換
v_result := -20;
update tmp_amsm a
set a.SUBS_ID_NEW =
(select b.subs_id
from g_subs b
where b.guid = a.subs_id
and rownum = 1);
update tmp_amsm a
set a.mped_id_new =
(select b.mped_id
from p_mped b
where b.guid = a.mped_id
and rownum = 1);
update tmp_amsm a
set a.m_mped_id_new =
(select b.mped_id
from p_mped b
where b.guid = a.m_mped_id
and rownum = 1);
update tmp_amsm a
set a.org_no =
(select b.org_no
from o_org b
where b.guid = a.powersupplyofficeid
and rownum = 1);
update tmp_amsm a
set a.area_code =
(select b.org_no
from o_org b
where b.guid = a.groupid
and rownum = 1);
update tmp_amsm a
set a.cons_id_new =
(select b.cons_id
from c_cons b
where b.guid = a.cons_id
and rownum = 1);
v_result := -30;
--更新正式表
update P_MPED a
set (MPED_NAME,
CT_VALUE,
PT_VALUE,
M_MPED_ID,
VOLT_TYPE,
SIDE_CODE,
SUBS_ID,
STATUS_CODE,
CONS_ID,
MP_TYPE,
TRADE_CODE,
SWITCH_NO,
MAIN_FLAG,
MEAS_MODE,
WIRING_MODE,
MP_CALC_MODE,
MP_CALC_TYPE,
ORG_NO,
AREA_CODE,
SAVEDATETIME,
Mped_Prop) =
(select b.mped_name,
b.ct_value,
b.pt_value,
b.m_mped_id_new,
b.volt_code,
b.side_code,
b.subs_id_new,
b.status_code,
b.cons_id_new,
b.mp_type,
b.trade_code,
b.switch_no,
b.total_flag,
b.meas_mode,
b.wiring_mode,
b.mp_calc_mode,
b.CONS_MP_CALC_MODE,
b.org_no,
b.area_code,
sysdate,
'02'
from tmp_amsm b
where a.mped_id = b.mped_id_new
and rownum = 1)
where exists
(select 1 from tmp_amsm b where a.mped_id = b.mped_id_new);
--刪除更新完的數據
v_result := -40;
delete from tmp_amsm a where a.mped_id_new is not null;
--插入正式表(只支持單線程)
select nvl(max(a.mped_id), 0) into v_max from p_mped a;
insert into P_MPED a
(MPED_ID,
MPED_NAME,
CT_VALUE,
PT_VALUE,
M_MPED_ID,
VOLT_TYPE,
SIDE_CODE,
SUBS_ID,
STATUS_CODE,
CONS_ID,
MP_TYPE,
TRADE_CODE,
SWITCH_NO,
MAIN_FLAG,
MEAS_MODE,
WIRING_MODE,
MP_CALC_MODE,
MP_CALC_TYPE,
ORG_NO,
AREA_CODE,
SAVEDATETIME,
guid,
Mped_Prop)
select v_max + rownum,
b.mped_name,
b.ct_value,
b.pt_value,
b.m_mped_id_new,
b.volt_code,
b.side_code,
b.subs_id_new,
b.status_code,
b.cons_id_new,
b.mp_type,
b.trade_code,
b.switch_no,
b.total_flag,
b.meas_mode,
b.wiring_mode,
b.mp_calc_mode,
b.CONS_MP_CALC_MODE,
b.org_no,
b.area_code,
sysdate,
b.mped_id,
'02'
from tmp_amsm b;
commit;
EXCEPTION
WHEN OTHERS THEN
begin
ROLLBACK;
v_status := ERROR_LOG(v_result, SQLCODE, SQLERRM, 'insert_amsm');
end;
END insert_amsm;
PROCEDURE insert_amso(amso In amso_array) AS
v_max number;
v_result number;
v_status number;
BEGIN
delete from TMP_AMSO;
v_result := -10;
--數據插入臨時表
FOR i IN amso.FIRST .. amso.LAST LOOP
BEGIN
insert into TMP_AMSO
(POWERSUPPLYOFFICEID, POWERSUPPLYOFFICENAME, COUNTYID)
values
(amso(i).POWERSUPPLYOFFICEID,
amso(i).POWERSUPPLYOFFICENAME,
amso(i).COUNTYID);
END;
END LOOP;
v_result := -20;
--代碼轉換
update TMP_AMSO a
set a.org_no =
(select b.org_no
from o_org b
where b.guid = a.powersupplyofficeid
and rownum = 1);
update TMP_AMSO a
set a.p_org_no =
(select b.org_no
from o_org b
where b.guid = a.countyid
and rownum = 1);
v_result := -30;
--更新正式表
update o_org a
set (a.org_name, a.p_org_no, a.org_type, a.savedatetime) =
(select b.powersupplyofficename, b.p_org_no, '06', sysdate
from TMP_AMSO b
where a.org_no = b.org_no)
where exists (select 1 from TMP_AMSO b where a.org_no = b.org_no);
update o_org a
set a.org_path =
(select b.org_path || '\' || a.org_no
from o_org b
where a.p_org_no = b.org_no)
where exists (select 1 from TMP_AMSO b where a.org_no = b.org_no);
v_result := -40;
--刪除更新完的數據
delete from TMP_AMSO a where a.org_no is not null;
--插入正式表(只支持單線程)
select nvl(max(to_number(a.org_no)), 0) into v_max from o_org a;
insert into o_org a
(a.org_no,
a.org_name,
a.p_org_no,
a.org_type,
a.savedatetime,
a.guid)
(select v_max + rownum,
b.powersupplyofficename,
b.p_org_no,
'06',
sysdate,
b.powersupplyofficeid
from TMP_AMSO b);
update o_org a
set a.org_path =
(select b.org_path || '\' || a.org_no
from o_org b
where a.p_org_no = b.org_no)
where exists
(select 1 from TMP_AMSO b where a.GUID = b.powersupplyofficeid);
commit;
EXCEPTION
WHEN OTHERS THEN
begin
ROLLBACK;
v_status := ERROR_LOG(v_result, SQLCODE, SQLERRM, 'insert_amso');
end;
END insert_amso;
PROCEDURE insert_tmrs(tmrs In tmrs_array) AS
v_max number;
--v_str varchar2(1000);
v_result number;
v_status number;
BEGIN
delete from tmp_tmrs;
v_result := -10;
--數據插入臨時表
FOR i IN tmrs.FIRST .. tmrs.LAST LOOP
BEGIN
insert into tmp_tmrs
(stationid,
stationname,
stationaddress,
voltagegrade,
zoneid,
status,
stationtype,
prop,
losstarget,
theoryloss,
product_company_id)
values
(tmrs(i).stationid,
tmrs(i).stationname,
tmrs(i).stationaddress,
tmrs(i).voltagegrade,
tmrs(i).zoneid,
tmrs(i).status,
tmrs(i).stationtype,
tmrs(i).prop,
tmrs(i).losstarget,
tmrs(i).theoryloss,
tmrs(i).product_company_id);
END;
END LOOP;
v_result := -20;
--代碼轉換
update tmp_tmrs a
set a.org_no =
(select b.org_no
from o_org b
where b.guid = a.zoneid
and rownum = 1);
update tmp_tmrs a
set a.area_code =
(select b.org_no
from o_org b
where b.guid = a.groupid
and rownum = 1);
update tmp_tmrs a
set a.subs_id =
(select b.subs_id
from g_subs b
where b.guid = a.stationid
and rownum = 1);
v_result := -30;
--更新正式表
update G_SUBS a
set (SUBS_NAME,
SUBS_ADDR,
ORG_NO,
VOLT_CODE,
RUN_STATUS_CODE,
SUBS_TYPE,
SUBS_PROP,
SAVEDATETIME) =
(select b.stationname,
b.stationaddress,
b.org_no,
b.voltagegrade,
b.status,
b.stationtype,
b.prop,
sysdate
from tmp_tmrs b
where a.subs_id = b.subs_id
and rownum = 1)
where exists (select 1 from tmp_tmrs b where a.subs_id = b.subs_id);
v_result := -40;
--刪除更新完的數據
delete from tmp_tmrs a where a.subs_id is not null;
--插入正式表(只支持單線程)
select nvl(max(a.subs_id), 0) into v_max from G_SUBS a;
insert into G_SUBS a
(SUBS_ID,
SUBS_NAME,
SUBS_ADDR,
ORG_NO,
VOLT_CODE,
RUN_STATUS_CODE,
SUBS_TYPE,
SUBS_PROP,
SAVEDATETIME,
GUID)
(select v_max + rownum,
b.stationname,
b.stationaddress,
b.org_no,
b.voltagegrade,
b.status,
b.stationtype,
b.prop,
sysdate,
b.stationid
from tmp_tmrs b);
commit;
EXCEPTION
WHEN OTHERS THEN
begin
ROLLBACK;
v_status := ERROR_LOG(v_result, SQLCODE, SQLERRM, 'insert_tmrs');
end;
END insert_tmrs;
PROCEDURE insert_amsl(amsl In amsl_array) AS
v_max number;
v_max1 number;
v_result number;
v_status number;
BEGIN
delete from tmp_amsl;
v_result := -10;
--數據插入臨時表
FOR i IN amsl.FIRST .. amsl.LAST LOOP
BEGIN
insert into tmp_amsl
(lineid,
linename,
linetype,
voltagegrade,
stationid,
losstarget,
theoryloss,
statusflag,
zoneid,
groupid)
values
(amsl(i).lineid,
amsl(i).linename,
amsl(i).linetype,
amsl(i).voltagegrade,
amsl(i).stationid,
amsl(i).losstarget,
amsl(i).theoryloss,
amsl(i).statusflag,
amsl(i).zoneid,
amsl(i).groupid);
END;
END LOOP;
v_result := -20;
--代碼轉換
update tmp_amsl a
set a.org_no =
(select b.org_no
from o_org b
where b.guid = a.zoneid
and rownum = 1);
update tmp_amsl a
set a.area_code =
(select b.org_no
from o_org b
where b.guid = a.groupid
and rownum = 1);
update tmp_amsl a
set a.subs_id =
(select b.subs_id
from g_subs b
where b.guid = a.stationid
and rownum = 1);
update tmp_amsl a
set a.line_id =
(select b.line_id
from g_line b
where b.guid = a.lineid
and rownum = 1);
v_result := -30;
--更新正式表
update G_LINE a
set (LINE_NAME,
LINE_TYPE,
VOLT_CODE,
RUN_STATUS_CODE,
ORG_NO,
AREA_CODE,
SAVEDATETIME) =
(select b.linename,
decode(b.linetype, '1', '01', '2', '02', '03'),
b.voltagegrade,
b.statusflag,
b.org_no,
b.area_code,
sysdate
from tmp_amsl b
where a.line_id = b.line_id
and rownum = 1)
where exists (select 1 from tmp_amsl b where a.line_id = b.line_id);
delete from g_subs_line_rela a
where exists (select 1
from tmp_amsl b
where a.line_id = b.line_id);
/* update g_subs_line_rela a
set (a.line_id, a.subs_id, a.savedatetime) =
(select b.line_id, b.subs_id, sysdate
from tmp_amsl b
where a.line_id = b.line_id
and a.subs_id = b.subs_id
and rownum = 1)
where exists (select 1
from tmp_amsl b
where a.line_id = b.line_id
and a.subs_id = b.subs_id);*/
select nvl(max(a.rela_id), 0) into v_max1 from g_subs_line_rela a;
insert into g_subs_line_rela
(rela_id,
line_id,
subs_id,
in_out_flag,
rela_flag,
run_status_code,
savedatetime)
(select v_max1 + rownum, b.line_id, b.subs_id, '', '', '01', sysdate
from tmp_amsl b);
v_result := -40;
--刪除更新完的數據
delete from tmp_amsl a where a.line_id is not null;
--插入正式表(只支持單線程)
select nvl(max(a.line_id), 0) into v_max from g_line a;
select nvl(max(a.rela_id), 0) into v_max1 from g_subs_line_rela a;
insert into g_line a
(line_id,
LINE_NAME,
LINE_TYPE,
VOLT_CODE,
RUN_STATUS_CODE,
ORG_NO,
AREA_CODE,
SAVEDATETIME,
GUID)
(select v_max + rownum,
b.linename,
decode(b.linetype, '1', '01', '2', '02', '03'),
b.voltagegrade,
b.statusflag,
b.org_no,
b.area_code,
sysdate,
b.lineid
from tmp_amsl b);
update tmp_amsl a
set a.line_id =
(select b.line_id
from g_line b
where b.guid = a.lineid
and rownum = 1);
insert into g_subs_line_rela
(rela_id,
line_id,
subs_id,
in_out_flag,
rela_flag,
run_status_code,
savedatetime)
(select v_max1 + rownum, b.line_id, b.subs_id, '', '', '01', sysdate
from tmp_amsl b);
commit;
EXCEPTION
WHEN OTHERS THEN
begin
ROLLBACK;
v_status := ERROR_LOG(v_result, SQLCODE, SQLERRM, 'insert_amsl');
end;
END insert_amsl;
PROCEDURE insert_tmrz(tmrz In tmrz_array) AS
v_max number;
v_result number;
v_status number;
BEGIN
delete from tmp_tmrz;
v_result := -10;
--數據插入臨時表
FOR i IN tmrz.FIRST .. tmrz.LAST LOOP
BEGIN
insert into tmp_tmrz
(transformerid,
transformername,
transformertype,
capacity,
stationid,
voltagegrade,
zoneid,
losstarget,
theoryloss,
statusflag,
groupid)
values
(tmrz(i).transformerid,
tmrz(i).transformername,
tmrz(i).transformertype,
tmrz(i).capacity,
tmrz(i).stationid,
tmrz(i).voltagegrade,
tmrz(i).zoneid,
tmrz(i).losstarget,
tmrz(i).theoryloss,
tmrz(i).statusflag,
tmrz(i).groupid);
END;
END LOOP;
/* insert into test2 (select count(1) from tmp_tmrz);
commit;*/
v_result := -20;
--代碼轉換
update tmp_tmrz a
set a.org_no =
(select b.org_no
from o_org b
where b.guid = a.zoneid
and rownum = 1);
update tmp_tmrz a
set a.area_code =
(select b.org_no
from o_org b
where b.guid = a.groupid
and rownum = 1);
update tmp_tmrz a
set a.subs_id =
(select b.subs_id
from g_subs b
where b.guid = a.stationid
and rownum = 1);
update tmp_tmrz a
set a.tran_id =
(select b.tran_id
from g_tran b
where b.guid = a.transformerid
and rownum = 1);
v_result := -30;
--更新正式表
update G_TRAN a
set (TRAN_NAME,
SUBS_ID,
ORG_NO,
RATED_VOLT,
PLATE_CAP,
PUB_PRIV_FLAG,
area_code,
SAVEDATETIME) =
(select b.transformername,
b.subs_id,
b.org_no,
b.voltagegrade,
b.capacity,
'1',
b.area_code,
sysdate
from tmp_tmrz b
where a.tran_id = b.tran_id
and rownum = 1)
where exists (select 1 from tmp_tmrz b where a.tran_id = b.tran_id);
v_result := -40;
--刪除更新完的數據
delete from tmp_tmrz a where a.tran_id is not null;
--插入正式表(只支持單線程)
select nvl(max(a.tran_id), 0) into v_max from G_TRAN a;
insert into G_TRAN a
(tran_id,
TRAN_NAME,
SUBS_ID,
ORG_NO,
RATED_VOLT,
PLATE_CAP,
PUB_PRIV_FLAG,
area_code,
SAVEDATETIME,
guid)
(select v_max + rownum,
b.transformername,
b.subs_id,
b.org_no,
b.voltagegrade,
b.capacity,
'2',
b.area_code,
sysdate,
b.transformerid
from tmp_tmrz b);
commit;
EXCEPTION
WHEN OTHERS THEN
begin
ROLLBACK;
v_status := ERROR_LOG(v_result, SQLCODE, SQLERRM, 'insert_tmrz');
end;
END insert_tmrz;
PROCEDURE insert_lapt(lapt In lapt_array) AS
v_max number;
v_result number;
v_status number;
BEGIN
delete from tmp_lapt;
v_result := -10;
--數據插入臨時表
FOR i IN lapt.FIRST .. lapt.LAST LOOP
BEGIN
insert into tmp_lapt
(transformerid,
consumerid,
transformertype,
tgid,
capacity,
address,
longitude,
latitude,
supplylineno,
zoneid,
groupid)
values
(lapt(i).transformerid,
lapt(i).consumerid,
lapt(i).transformertype,
lapt(i).tgid,
lapt(i).capacity,
lapt(i).address,
lapt(i).longitude,
lapt(i).latitude,
lapt(i).supplylineno,
lapt(i).zoneid,
lapt(i).groupid);
END;
END LOOP;
v_result := -20;
--代碼轉換
update tmp_lapt a
set a.org_no =
(select b.org_no
from o_org b
where b.guid = a.zoneid
and rownum = 1);
update tmp_lapt a
set a.area_code =
(select b.org_no
from o_org b
where b.guid = a.groupid
and rownum = 1);
update tmp_lapt a
set a.tg_id =
(select b.tg_id
from g_tg b
where b.guid = a.tgid
and rownum = 1);
update tmp_lapt a
set a.cons_id =
(select b.cons_id
from c_cons b
where b.guid = a.consumerid
and rownum = 1);
update tmp_lapt a
set a.tran_id =
(select b.tran_id
from g_tran b
where b.guid = a.transformerid
and rownum = 1);
v_result := -30;
--更新正式表
update G_TRAN a
set (TG_ID,
ORG_NO,
PLATE_CAP,
PUB_PRIV_FLAG,
CONS_ID,
INST_ADDR,
area_code,
SAVEDATETIME) =
(select b.tg_id,
b.org_no,
b.capacity,
b.transformertype,
b.cons_id,
b.address,
b.area_code,
sysdate
from tmp_lapt b
where a.tran_id = b.tran_id
and rownum = 1)
where exists (select 1 from tmp_lapt b where a.tran_id = b.tran_id);
v_result := -40;
--刪除更新完的數據
delete from tmp_lapt a where a.tran_id is not null;
--插入正式表(只支持單線程)
select nvl(max(a.tran_id), 0) into v_max from G_TRAN a;
insert into G_TRAN a
(tran_id,
TG_ID,
ORG_NO,
PLATE_CAP,
PUB_PRIV_FLAG,
CONS_ID,
INST_ADDR,
area_code,
SAVEDATETIME,
guid)
(select v_max + rownum,
b.tg_id,
b.org_no,
b.capacity,
b.transformertype,
b.cons_id,
b.address,
b.area_code,
sysdate,
b.transformerid
from tmp_lapt b);
commit;
EXCEPTION
WHEN OTHERS THEN
begin
ROLLBACK;
v_status := ERROR_LOG(v_result, SQLCODE, SQLERRM, 'insert_tmrz');
end;
END insert_lapt;
PROCEDURE insert_dmrt(dmrt In dmrt_array) AS
v_max number;
v_result number;
v_status number;
v_max1 number;
BEGIN
delete from tmp_dmrt;
v_result := -10;
--數據插入臨時表
FOR i IN dmrt.FIRST .. dmrt.LAST LOOP
BEGIN
insert into tmp_dmrt
(TGID, TGNAME, CONSNUM, ZONEID, GROUPID, SUPPLYLINENO)
values
(dmrt(i).TGID,
dmrt(i).TGNAME,
dmrt(i).CONSNUM,
dmrt(i).ZONEID,
dmrt(i).GROUPID,
dmrt(i).SUPPLYLINENO);
END;
END LOOP;
v_result := -20;
--代碼轉換
update tmp_dmrt a
set a.org_no =
(select b.org_no
from o_org b
where b.guid = a.zoneid
and rownum = 1);
update tmp_dmrt a
set a.area_code =
(select b.org_no
from o_org b
where b.guid = a.groupid
and rownum = 1);
update tmp_dmrt a
set a.line_id =
(select b.line_id
from g_line b
where b.guid = a.supplylineno
and rownum = 1);
update tmp_dmrt a
set a.tg_id =
(select b.tg_id
from g_tg b
where b.guid = a.tgid
and rownum = 1);
v_result := -30;
--更新正式表
update g_tg a
set (a.tg_name,
a.cons_num,
ORG_NO,
area_code,
SAVEDATETIME,
PUB_PRIV_FLAG,
RUN_STATUS_CODE) =
(select b.tgname,
b.consnum,
b.org_no,
b.area_code,
sysdate,
'7400',
'01'
from tmp_dmrt b
where a.tg_id = b.tg_id
and rownum = 1)
where exists (select 1 from tmp_dmrt b where a.tg_id = b.tg_id);
update g_line_tg_rela a
set (a.line_id, a.tg_id, a.savedatetime) =
(select b.line_id, b.tg_id, sysdate
from tmp_dmrt b
where a.line_id = b.line_id
and a.tg_id = b.tg_id
and rownum = 1)
where exists (select 1
from tmp_dmrt b
where a.line_id = b.line_id
and a.tg_id = b.tg_id);
v_result := -40;
--刪除更新完的數據
delete from tmp_dmrt a where a.tg_id is not null;
--插入正式表(只支持單線程)
select nvl(max(a.tg_id), 0) into v_max from g_tg a;
select nvl(max(a.line_tg_id), 0) into v_max1 from g_line_tg_rela a;
insert into g_line_tg_rela
(line_tg_id, line_id, tg_id, run_status_code, savedatetime)
(select v_max1 + rownum, b.line_id, b.tg_id, '01', sysdate
from tmp_dmrt b);
insert into g_tg a
(tg_id,
a.tg_name,
a.cons_num,
ORG_NO,
area_code,
SAVEDATETIME,
guid,
PUB_PRIV_FLAG,
RUN_STATUS_CODE)
(select v_max + rownum,
b.tgname,
b.consnum,
b.org_no,
b.area_code,
sysdate,
b.tgid,
'7400',
'01'
from tmp_dmrt b);
commit;
EXCEPTION
WHEN OTHERS THEN
begin
ROLLBACK;
v_status := ERROR_LOG(v_result, SQLCODE, SQLERRM, 'insert_dmrt');
end;
END insert_dmrt;
END BASE_INFO;
