Oracle使用虚拟表dual一次插入多条记录
BEGIN
?????????? generate_tmpsht;
?????????? execute immediate
??????????????? 'insert into SHTSUPPLIERRATE (supplierid,supplierrate,supplierdialcode)
??????????????? select :var_TMPSupplier1ID,:var_TMPSupplier1Rate,:var_TMPSupplier1DialCode? from dual
??????????????? union all select :var_TMPSupplier2ID,:var_TMPSupplier2Rate,:var_TMPSupplier2DialCode from dual
??????????????? union all select :var_TMPSupplier3ID,:var_TMPSupplier3Rate,:var_TMPSupplier3DialCode from dual
??????????????? union all select :var_TMPSupplier4ID,:var_TMPSupplier4Rate,:var_TMPSupplier4DialCode from dual
??????????????? union all select :var_TMPSupplier5ID,:var_TMPSupplier5Rate,:var_TMPSupplier5DialCode from dual
??????????????? union all select :var_TMPSupplier6ID,:var_TMPSupplier6Rate,:var_TMPSupplier6DialCode from dual
??????????????? union all select :var_TMPSupplier7ID,:var_TMPSupplier7Rate,:var_TMPSupplier7DialCode from dual
??????????????? union all select :var_TMPSupplier8ID,:var_TMPSupplier8Rate,:var_TMPSupplier8DialCode from dual
??????????????? union all select :var_TMPSupplier9ID,:var_TMPSupplier9Rate,:var_TMPSupplier9DialCode from dual
??????????????? union all select :var_TMPSupplier10ID,:var_TMPSupplier10Rate,:var_TMPSupplier10DialCode from dual
??????????????? union all select :var_TMPSupplier11ID,:var_TMPSupplier11Rate,:var_TMPSupplier11DialCode from dual
?????????????? union all select :var_TMPSupplier12ID,:var_TMPSupplier12Rate,:var_TMPSupplier12DialCode from dual
??????????????? union all select :var_TMPSupplier13ID,:var_TMPSupplier13Rate,:var_TMPSupplier13DialCode from dual
??????????????? union all select :var_TMPSupplier14ID,:var_TMPSupplier14Rate,:var_TMPSupplier14DialCode from dual
??????????????? union all select :var_TMPSupplier15ID,:var_TMPSupplier15Rate,:var_TMPSupplier15DialCode from dual
??????????????? union all select :var_TMPSupplier16ID,:var_TMPSupplier16Rate,:var_TMPSupplier16DialCode from dual
??????????????? union all select :var_TMPSupplier17ID,:var_TMPSupplier17Rate,:var_TMPSupplier17DialCode from dual
??????????????? union all select :var_TMPSupplier18ID,:var_TMPSupplier18Rate,:var_TMPSupplier18DialCode from dual
??????????????? union all select :var_TMPSupplier19ID,:var_TMPSupplier19Rate,:var_TMPSupplier19DialCode from dual
??????????????? union all select :var_TMPSupplier20ID,:var_TMPSupplier20Rate,:var_TMPSupplier20DialCode from dual
??????????????? union all select :var_TMPSupplier11ID,:var_TMPSupplier21Rate,:var_TMPSupplier21DialCode from dual
?????????????? union all select :var_TMPSupplier22ID,:var_TMPSupplier22Rate,:var_TMPSupplier22DialCode from dual
??????????????? union all select :var_TMPSupplier23ID,:var_TMPSupplier23Rate,:var_TMPSupplier23DialCode from dual
??????????????? union all select :var_TMPSupplier24ID,:var_TMPSupplier24Rate,:var_TMPSupplier24DialCode from dual
??????????????? union all select :var_TMPSupplier25ID,:var_TMPSupplier25Rate,:var_TMPSupplier25DialCode from dual
??????????????? union all select :var_TMPSupplier26ID,:var_TMPSupplier26Rate,:var_TMPSupplier26DialCode from dual
??????????????? union all select :var_TMPSupplier27ID,:var_TMPSupplier27Rate,:var_TMPSupplier27DialCode from dual
??????????????? union all select :var_TMPSupplier28ID,:var_TMPSupplier28Rate,:var_TMPSupplier28DialCode from dual
??????????????? union all select :var_TMPSupplier29ID,:var_TMPSupplier29Rate,:var_TMPSupplier29DialCode from dual
??????????????? union all select :var_TMPSupplier30ID,:var_TMPSupplier30Rate,:var_TMPSupplier30DialCode from dual'
??????????????? using var_TMPSupplier1ID
??????????????????????????????????????????? ,var_TMPSupplier1Rate
??????????????????????????????????????????? ,var_TMPSupplier1DialCode
??????????????????????????????????????????? ,var_TMPSupplier2ID
??????????????????????????????????????????? ,var_TMPSupplier2Rate
??????????????????????????????????????????? ,var_TMPSupplier2DialCode
??????????????????????????????????????????? ,var_TMPSupplier3ID
??????????????????????????????????????????? ,var_TMPSupplier3Rate
??????????????????????????????????????????? ,var_TMPSupplier3DialCode
??????????????????????????????????????????? ,var_TMPSupplier4ID
??????????????????????????????????????????? ,var_TMPSupplier4Rate
??????????????????????????????????????????? ,var_TMPSupplier4DialCode
??????????????????????????????????????????? ,var_TMPSupplier5ID
??????????????????????????????????????????? ,var_TMPSupplier5Rate
??????????????????????????????????????????? ,var_TMPSupplier5DialCode
??????????????????????????????????????????? ,var_TMPSupplier6ID
??????????????????????????????????????????? ,var_TMPSupplier6Rate
??????????????????????????????????????????? ,var_TMPSupplier6DialCode
??????????????????????????????????????????? ,var_TMPSupplier7ID
??????????????????????????????????????????? ,var_TMPSupplier7Rate
??????????????????????????????????????????? ,var_TMPSupplier7DialCode
??????????????????????????????????????????? ,var_TMPSupplier8ID
??????????????????????????????????????????? ,var_TMPSupplier8Rate
??????????????????????????????????????????? ,var_TMPSupplier8DialCode
??????????????????????????????????????????? ,var_TMPSupplier9ID
??????????????????????????????????????????? ,var_TMPSupplier9Rate
??????????????????????????????????????????? ,var_TMPSupplier9DialCode
??????????????????????????????????????????? ,var_TMPSupplier10ID??
??????????????????????????????????????????? ,var_TMPSupplier10Rate
??????????????????????????????????????????? ,var_TMPSupplier10DialCode
??????????????????????????????????????????? ,var_TMPSupplier11ID??
??????????????????????????????????????????? ,var_TMPSupplier11Rate
??????????????????????????????????????????? ,var_TMPSupplier11DialCode
??????????????????????????????????????????? ,var_TMPSupplier12ID??
??????????????????????????????????????????? ,var_TMPSupplier12Rate
??????????????????????????????????????????? ,var_TMPSupplier12DialCode
??????????????????????????????????????????? ,var_TMPSupplier13ID??
??????????????????????????????????????????? ,var_TMPSupplier13Rate
??????????????????????????????????????????? ,var_TMPSupplier13DialCode
??????????????????????????????????????????? ,var_TMPSupplier14ID??
??????????????????????????????????????????? ,var_TMPSupplier14Rate
??????????????????????????????????????????? ,var_TMPSupplier14DialCode
??????????????????????????????????????????? ,var_TMPSupplier15ID??
??????????????????????????????????????????? ,var_TMPSupplier15Rate
??????????????????????????????????????????? ,var_TMPSupplier15DialCode
??????????????????????????????????????????? ,var_TMPSupplier16ID??
??????????????????????????????????????????? ,var_TMPSupplier16Rate
??????????????????????????????????????????? ,var_TMPSupplier16DialCode
??????????????????????????????????????????? ,var_TMPSupplier17ID??
??????????????????????????????????????????? ,var_TMPSupplier17Rate
??????????????????????????????????????????? ,var_TMPSupplier17DialCode
??????????????????????????????????????????? ,var_TMPSupplier18ID??
??????????????????????????????????????????? ,var_TMPSupplier18Rate
??????????????????????????????????????????? ,var_TMPSupplier18DialCode
??????????????????????????????????????????? ,var_TMPSupplier19ID??
??????????????????????????????????????????? ,var_TMPSupplier19Rate
??????????????????????????????????????????? ,var_TMPSupplier19DialCode
??????????????????????????????????????????? ,var_TMPSupplier20ID??
??????????????????????????????????????????? ,var_TMPSupplier20Rate
??????????????????????????????????????????? ,var_TMPSupplier20DialCode???
??????????????????????????????????????????? ,var_TMPSupplier21ID??
??????????????????????????????????????????? ,var_TMPSupplier21Rate
??????????????????????????????????????????? ,var_TMPSupplier21DialCode
??????????????????????????????????????????? ,var_TMPSupplier22ID??
??????????????????????????????????????????? ,var_TMPSupplier22Rate
??????????????????????????????????????????? ,var_TMPSupplier22DialCode
??????????????????????????????????????????? ,var_TMPSupplier23ID??
??????????????????????????????????????????? ,var_TMPSupplier23Rate
??????????????????????????????????????????? ,var_TMPSupplier23DialCode
??????????????????????????????????????????? ,var_TMPSupplier24ID??
??????????????????????????????????????????? ,var_TMPSupplier24Rate
??????????????????????????????????????????? ,var_TMPSupplier24DialCode
??????????????????????????????????????????? ,var_TMPSupplier25ID??
??????????????????????????????????????????? ,var_TMPSupplier25Rate
??????????????????????????????????????????? ,var_TMPSupplier25DialCode
??????????????????????????????????????????? ,var_TMPSupplier26ID??
??????????????????????????????????????????? ,var_TMPSupplier26Rate
??????????????????????????????????????????? ,var_TMPSupplier26DialCode
??????????????????????????????????????????? ,var_TMPSupplier27ID??
??????????????????????????????????????????? ,var_TMPSupplier27Rate
??????????????????????????????????????????? ,var_TMPSupplier27DialCode
??????????????????????????????????????????? ,var_TMPSupplier28ID??
??????????????????????????????????????????? ,var_TMPSupplier28Rate
??????????????????????????????????????????? ,var_TMPSupplier28DialCode
??????????????????????????????????????????? ,var_TMPSupplier29ID??
??????????????????????????????????????????? ,var_TMPSupplier29Rate
??????????????????????????????????????????? ,var_TMPSupplier29DialCode
??????????????????????????????????????????? ,var_TMPSupplier30ID??
??????????????????????????????????????????? ,var_TMPSupplier30Rate
??????????????????????????????????????????? ,var_TMPSupplier30DialCode ;
?????????????? EXCEPTION
??????????????? WHEN OTHERS THEN
??????????????? NULL;
??????? END;
?
