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;