oracle 批量插入原理,请教有没有一种高效的plsql批量插入过程！多谢

多谢各位

看了网上一篇文章写了我的批量PL/SQL过程，大家看有没有更好的修改；

CREATE OR REPLACE PACKAGE qtone IS

TYPE SUBSTR_ARRAY IS TABLE OF VARCHAR2(100 CHAR) INDEX BY BINARY_INTEGER;

end;

CREATE OR REPLACE FUNCTION split_str(str in varchar2, delimiter in varchar2) RETURN qtone.SUBSTR_ARRAY is

src_str varchar2(300 char) := '';

prev_index integer := 1;

next_index integer := 1;

retval qtone.substr_array ;

cnt INTEGER := 1;

BEGIN

--去掉开头和结束的分隔符

src_str := trim(both delimiter from str);

while next_index != 0 loop

next_index := instr(src_str,delimiter,prev_index,1);

if(next_index = 0) then

retval(cnt) := substr(src_str,prev_index);

cnt := cnt + 1;

else

retval(cnt) := substr(src_str,prev_index,next_index - prev_index);

cnt := cnt + 1;

end if;

prev_index := next_index + 1;

end loop;

RETURN retval;

end;

CREATE OR REPLACE PROCEDURE p_voucher_imp(v_voucher_sn     VARCHAR2,

v_voucher_pass   VARCHAR2,

v_voucher_tag    VARCHAR2,

v_createtime     VARCHAR2,

v_creater        VARCHAR2,

v_expiretime     VARCHAR2,

v_balance        VARCHAR2,

v_agent_id       VARCHAR2,

v_tariffgroup_id VARCHAR2,

v_activated      VARCHAR2,

v_used           VARCHAR2,

v_account_name   VARCHAR2,

v_usetime        VARCHAR2,

v_discount       VARCHAR2,

v_retcode        OUT INTEGER,

v_retinfo        OUT VARCHAR2) IS

voucher_sn_arry     qtone.substr_array;

voucher_pass_arry   qtone.substr_array;

voucher_tag_arry    qtone.substr_array;

createtime_arry     qtone.substr_array;

creater_arry        qtone.substr_array;

expiretime_arry     qtone.substr_array;

balance_arry        qtone.substr_array;

agent_id_arry       qtone.substr_array;

tariffgroup_id_arry qtone.substr_array;

activated_arry      qtone.substr_array;

used_arry           qtone.substr_array;

account_name_arry   qtone.substr_array;

usetime_arry        qtone.substr_array;

discount_arry       qtone.substr_array;

v_sql               VARCHAR2(500);

v_pkg               VARCHAR2(30);

v_procname          VARCHAR2(30);

rowline             NUMBER;

BEGIN

v_pkg               := 'adm';

v_procname          := 'p_voucher_imp';

rowline             := 0;

voucher_sn_arry     := split_str(v_voucher_sn, '_');

voucher_pass_arry   := split_str(v_voucher_pass, '_');

voucher_tag_arry    := split_str(v_voucher_tag, '_');

createtime_arry     := split_str(v_createtime, '_');

creater_arry        := split_str(v_creater, '_');

expiretime_arry     := split_str(v_expiretime, '_');

balance_arry        := split_str(v_balance, '_');

agent_id_arry       := split_str(v_agent_id, '_');

tariffgroup_id_arry := split_str(v_tariffgroup_id, '_');

activated_arry      := split_str(v_activated, '_');

used_arry           := split_str(v_used, '_');

account_name_arry   := split_str(v_account_name, '_');

usetime_arry        := split_str(v_usetime, '_');

discount_arry       := split_str(v_discount, '_');

--插入日志

ods.p_insert_log(SYSDATE, v_pkg, v_procname, 'ALL', SYSDATE);

COMMIT;

v_sql := 'insert into adm_voucher(voucher_id,

voucher_sn,

voucher_pass,

voucher_tag,

createtime,

creater,

expiretime,

balance,

agent_id,

tariffgroup_id,

activated,

used,

account_name,

usetime,

discount)

valuse(seq_voucher_id.nextval,

:voucher_sn,

:voucher_pass,

:voucher_tag,

:createtime,

:creater,

:expiretime,

:balance,

:agent_id,

:tariffgroup_id,

:activated,

:used,

:account_name,

:usetime,

:discount)';

IF voucher_sn_arry.COUNT <> 0 THEN

FORALL i IN voucher_sn_arry.FIRST .. voucher_sn_arry.LAST EXECUTE

IMMEDIATE v_sql USING

voucher_sn_arry(i),

voucher_pass_arry(i),

voucher_tag_arry(i),

createtime_arry(i),

creater_arry(i),

expiretime_arry(i),

balance_arry(i), agent_id_arry(i),

tariffgroup_id_arry(i),

activated_arry(i), used_arry(i),

account_name_arry(i),

usetime_arry(i), discount_arry(i)

;

END IF;

rowline := SQL%ROWCOUNT;

COMMIT;

--过程执行成功,更新日志

v_retcode := 1;

ods.p_update_log(SYSDATE,

v_pkg,

v_procname,

'结束',

v_retcode,

SYSDATE,

rowline);

--过程执行异常,更新日志

EXCEPTION

WHEN OTHERS THEN

v_retinfo := SQLERRM;

v_retcode := 0;

ods.p_update_log(SYSDATE,

v_pkg,

v_procname,

v_retinfo,

v_retcode,

SYSDATE,

rowline);

COMMIT;

END p_voucher_imp;