plsql插入，并按規(guī)則生成單號

 <insert id="insertSelective" parameterType="com.tcl.srm.vendor.vo.TQiHeader" >
    <selectKey keyProperty="qiCode" resultType="java.lang.String" order="BEFORE">
        select 'QI' || to_char(sysdate,'yyyymmdd') || lpad(seq_qi_id.nextval,4,'0') as QI_CODE from dual
    </selectKey>
    insert into T_QI_HEADER
    <trim prefix="(" suffix=")" suffixOverrides="," >
      <if test="qiCode != null" >
        QI_CODE,
      </if>
      <if test="companyCode != null" >
        COMPANY_CODE,
      </if>
      <if test="sourceType != null" >
        SOURCE_TYPE,
      </if>
      <if test="sourceCode != null" >
        SOURCE_CODE,
      </if>
      <if test="vendorCode != null" >
        VENDOR_CODE,
      </if>
      <if test="vendorName != null" >
        VENDOR_NAME,
      </if>
      <if test="vendorTel != null" >
        VENDOR_TEL,
      </if>
    </trim>
    <trim prefix="values (" suffix=")" suffixOverrides="," >
      <if test="qiCode != null" >
        #{qiCode,jdbcType=VARCHAR},
      </if>
      <if test="companyCode != null" >
        #{companyCode,jdbcType=VARCHAR},
      </if>
      <if test="sourceType != null" >
        #{sourceType,jdbcType=VARCHAR},
      </if>
      <if test="sourceCode != null" >
        #{sourceCode,jdbcType=VARCHAR},
      </if>
      <if test="vendorCode != null" >
        #{vendorCode,jdbcType=VARCHAR},
      </if>
      <if test="vendorName != null" >
        #{vendorName,jdbcType=VARCHAR},
      </if>
      <if test="vendorTel != null" >
        #{vendorTel,jdbcType=VARCHAR},
      </if>
    </trim>
  </insert>

其中：

<selectKey keyProperty="qiCode" resultType="java.lang.String" order="BEFORE"> select 'QI' || to_char(sysdate,'yyyymmdd') || lpad(seq_qi_id.nextval,4,'0') as QI_CODE from dual </selectKey>` 即為主鍵生成規(guī)則； 先創(chuàng)建一個(gè)序列seq_qi_id 寫一個(gè)存儲過程用于每天初始化序列從0開始：

create or replace procedure CLEARSEQ_QI as n_count number(8); begin select count(1) into n_count from user_sequences t where t.sequence_name = 'SEQ_QI_ID';

  if n_count > 0 then
    execute immediate 'drop sequence SEQ_QI_ID';
  end if;


  execute immediate 'create sequence SEQ_QI_ID
  minvalue 1
  maxvalue 99999999
  start with 1
  increment by 1
  NOCYCLE
  NOCACHE';
  commit;
end;

每天需要對該存儲過程計(jì)數(shù)清零：

create or replace package body PG_CLEAR_ALL_SEQ is

  PROCEDURE mainproc aS
    procedure_name varchar2(40) := 'PG_CLEAR_ALL_SEQ.mainproc';


  BEGIN


    rlog.info(procedure_name, 0, '', 'Start,iv_id is PG_CLEAR_ALL_SEQ'  );
    COMMIT;
    clearseq_qi();
    COMMIT;
    rlog.info(procedure_name, 0, '', 'Finished,iv_id is PG_CLEAR_ALL_SEQ');
    COMMIT;


  EXCEPTION
    WHEN OTHERS THEN
      rlog.except(procedure_name,
                  '1',
                  SUBSTR(SQLERRM, 1, 512),
                  SUBSTR(DBMS_UTILITY.format_error_backtrace, 1, 512));
      COMMIT;
  END;


end PG_CLEAR_ALL_SEQ;

（附加）根據(jù)主表單號生成對應(yīng)下一級明細(xì)條目號：

<selectKey keyProperty="modelItemCode" resultType="java.lang.String" order="BEFORE"> select #{modelCode} || '_' || lpad((decode(max(to_number(substr (m.MODEL_ITEM_CODE,18))), null, 0, max(to_number(substr(m.MODEL_ITEM_CODE,18)))) + 1),2,'0') from T_VD_INDICATORS_MODEL_DETAIL m where m.MODEL_CODE = #{modelCode} </selectKey>