使用array实现多条记录的插入

最近项目的一个功能是传入参数，插入多条记录，参数格式类似如下：

'a,a,a'

'b,c'

''

因此使用了oracle的嵌套表实现，代码如下：

create or replace type VARRAY_PART is table of varchar2(100);

/

CREATE OR REPLACE FUNCTION STRING_SEPARATOR_FNC(P_IN_INSTR IN VARCHAR2,
                                                P_IN_CHAR  IN VARCHAR2)
  RETURN VARRAY_PART IS
  V_STORE     VARRAY_PART;
  V_INSERT    VARCHAR2(3000);
  V_POS       NUMBER;
  V_COUNTER   NUMBER;
  V_LEN       NUMBER;
  v_sum       number;
  v_temp      VARRAY_PART;
  v_counter_1 number;
BEGIN
  V_STORE   := VARRAY_PART('');
  v_temp    := VARRAY_PART('');
  V_INSERT  := P_IN_INSTR || P_IN_CHAR;
  V_POS     := INSTR(V_INSERT, P_IN_CHAR, 1, 1);
  V_COUNTER := 1;
  V_LEN     := LENGTH(P_IN_CHAR);
  WHILE (V_POS <> 0) LOOP
    v_temp(V_COUNTER) := SUBSTR(V_INSERT, 1, V_POS - 1);
    V_INSERT := SUBSTR(V_INSERT, V_POS + V_LEN);
    V_POS := INSTR(V_INSERT, P_IN_CHAR, 1, 1);
    V_COUNTER := V_COUNTER + 1;
    v_temp.EXTEND(1);
  END LOOP;
  v_sum       := v_temp.count;
  v_counter_1 := 1;
  while (v_counter_1 < v_sum) loop
    v_store(v_counter_1) := v_temp(v_counter_1);
    v_counter_1 := v_counter_1 + 1;
    if (v_counter_1 = v_sum) then
      exit;
    end if;
    v_store.extend(1);
  end loop;
  RETURN V_STORE;
EXCEPTION
  WHEN OTHERS THEN
    null;

    --do sth accroding logic
END STRING_SEPARATOR_FNC;
/

create or replace procedure ctsp_rfrt_upd_weblink_prc(DOC_TYPE_NM_IN H9T_WEBLINK_DTL.DOC_TYPE_NM%TYPE,
                                                      TITLE_DESC_IN  H9T_WEBLINK_DTL.TITLE_DESC%TYPE,
                                                      LINK_TXT_IN    H9T_WEBLINK_DTL.LINK_TXT%TYPE,
                                                      ON_LINE_FLG_IN H9T_WEBLINK_DTL.ON_LINE_FLG%TYPE,
                                                      val_cd_in      lov_prpty.val_cd%TYPE,
                                                      errmsg_out     out varchar2) as
  v_row             number;
  varr_doc_type_nm  VARRAY_PART;
  varr_title_desc   VARRAY_PART;
  varr_link_txt     VARRAY_PART;
  varr_on_line_flg  VARRAY_PART;
  var_doc_type_nm_f H9T_WEBLINK_DTL.DOC_TYPE_NM%TYPE;
  var_title_desc_f  H9T_WEBLINK_DTL.TITLE_DESC%TYPE;
  var_link_txt_f    H9T_WEBLINK_DTL.LINK_TXT%TYPE;
  var_on_line_flg_f H9T_WEBLINK_DTL.ON_LINE_FLG%TYPE;
begin
  varr_doc_type_nm := STRING_SEPARATOR_FNC(DOC_TYPE_NM_IN, ',');
  varr_title_desc  := STRING_SEPARATOR_FNC(TITLE_DESC_IN, ',');
  varr_link_txt    := STRING_SEPARATOR_FNC(LINK_TXT_IN, ',');
  varr_on_line_flg := STRING_SEPARATOR_FNC(ON_LINE_FLG_IN, ',');
  v_row            := greatest(varr_doc_type_nm.count,
                               varr_title_desc.count,
                               varr_on_line_flg.count,
                               varr_link_txt.count);

  varr_doc_type_nm.extend(v_row - varr_doc_type_nm.count);

  varr_title_desc.extend(v_row - varr_title_desc.count);
 
  varr_link_txt.extend(v_row - varr_link_txt.count);
  varr_on_line_flg.extend(v_row - varr_on_line_flg.count);
  for i in 1 .. v_row loop
    insert into H9T_WEBLINK_DTL
      (H9T_WEBLINKS_DTL_ID, DOC_TYPE_NM, TITLE_DESC, LINK_TXT, ON_LINE_FLG)
    values
      (H9S_WEBLINK_REC_ID.Nextval,
       varr_doc_type_nm(i),
       varr_title_desc(i),
       varr_link_txt(i),
       varr_on_line_flg(i));
    dbms_output.put_line('sql_rowcount:' || sql%rowcount);
  end loop;
  insert into H9T_UNIT_TYPE_WLINK
    select lp.lov_prpty_id, H9S_WEBLINK_REC_ID.Currval
      from lov_prpty lp
     where lp.prpty_id = 'CTS9HDUTYP'
       and lp.val_cd LIKE '%' || val_cd_in || '%';
  commit;
  errmsg_out := 'Success';

exception
  WHEN OTHERS THEN
    ROLLBACK;
    errmsg_out := SQLCODE || substr(SQLERRM, 1, 200);
end ctsp_rfrt_upd_weblink_prc;
/