create or replace procedure InsertBankInfo is
v_legalentityid ba_entity.legalentityid%type;
v_BankId fi_bankaccount.bankid%type;
v_Bankaccountid fi_bankaccount.bankaccountid%type;
v_InsertCount number;
v_FalseCount number;
begin
v_InsertCount := 0;
v_FalseCount := 0;
for v_test in (Select t.tname,
t.tcode,
t.bankname,
t.bankname1,
t.bankno,
t.linenumber
From test_temp t) loop
begin
Select be.legalentityid
into v_legalentityid
From ba_entity be
Where be.entitycode = v_test.tcode;
Exception
when no_data_found then
v_legalentityid := 0;
end;
if v_legalentityid = 0 then
dbms_output.put_line(v_test.tcode || '这个T代码不存在');
v_FalseCount := v_FalseCount + 1;
else
--查找银行ID
begin
Select fbm.bankid
into v_BankId
From fi_bankmapping fbm
Where fbm.financecompanybankname = v_test.bankname;
Exception
when no_data_found then
v_BankId := 0;
dbms_output.put_line(v_test.tcode || v_test.bankname ||
'这个银行不存在');
end;
--获取Id
v_Bankaccountid := fi_bankaccount_seq.nextval;
--插入数据
insert into fi_bankaccount
(bankaccountid,
legalentityid,
bankname,
bankid,
bankaccountno,
currencycode,
opendate,
status,
createddate,
updateddate,
createduserid,
updateduserid,
isflush,
isbasic,
linenumber)
values
(v_Bankaccountid,
v_legalentityid,
v_test.bankname1,
v_BankId,
v_test.bankno,
'CNY',
sysdate,
41,
sysdate,
sysdate,
100021,
100021,
1,
'N',
v_test.linenumber);
dbms_output.put_line('插入ID' || v_Bankaccountid || v_test.tname ||
v_test.tcode || '----' || v_legalentityid ||
'银行ID' || v_BankId);
v_InsertCount := v_InsertCount + 1;
end if;
end loop;
dbms_output.put_line('成功添加了:' || v_InsertCount);
dbms_output.put_line('失败了:' || v_FalseCount || '由于T代码不存在');
end InsertBankInfo;