设置ID自增

一、使用触发器和序列

1、整数自增

设置主键自增：
CREATE SEQUENCE FOODBORNE_DISEASE_RESULT_ID_SEQ MINVALUE 1 NOMAXVALUE INCREMENT BY 1 START WITH 1 NOCACHE;
-- 为Insert操作创建触发器，无需在SQL语句里写NEXTVAL，名称为表名_INS_TRG
CREATE OR REPLACE TRIGGER FOODBORNE_DISEASE_RESULT_INS_TRG  BEFORE INSERT ON FOODBORNE_DISEASE_RESULT FOR EACH ROW WHEN(NEW.ID IS NULL)
BEGIN
    SELECT FOODBORNE_DISEASE_RESULT_ID_SEQ.NEXTVAL INTO :NEW.ID FROM DUAL;
END;

2、UUID自增

设置主键自增：
CREATE SEQUENCE BD_DATA_TIME_ID_SEQ MINVALUE 1 NOMAXVALUE INCREMENT BY 1 START WITH 1 NOCACHE;

-- 为Insert操作创建触发器，无需在SQL语句里写NEXTVAL，名称为表名_INS_TRG
CREATE OR REPLACE TRIGGER BD_DATA_TIME_INS_TRG  BEFORE INSERT ON BD_DATA_TIME FOR EACH ROW WHEN(NEW.ID IS NULL)
BEGIN
 
     IF :NEW.ID IS NULL THEN
    :NEW.ID:=SYS_GUID();
  END IF; END BD_DATA_TIME_ID_SEQ;

二、创建序列和sql限制（插入语句不写Id）

CREATE SEQUENCE SEQ_DC_TEST_LIST_ID INCREMENT BY 1 MINVALUE 1 MAXVALUE 9999999999999999999999999999 NOCYCLE NOCACHE NOORDER

1、批量插入

<insert id="insertBatch" parameterType="java.util.List">
    insert into DC_MEDICAL_ADVICE(ID, NAME, ID_CARD, PAT_TYPE, PAT_NUM, SEX,
                                  AGE, TEL, LINK_MAN, ADDRESS, HAPPEN_TIME,
                                  DUTY_DOCTOR_NAME, DUTY_DOCTOR_ID, ORG_NAME,
                                  ORG_CODE, DEP_NAME, DEP_CODE, SYSTEMTIME, HOSPITAL_CODE)
    select SEQ_DC_MEDICAL_ADVICE_ID.NEXTVAL, A.* from (
    <foreach collection ="entities" item="entity"  index="index" separator="union all">
        select
        #{entity.name,jdbcType=VARCHAR} as NAME,
        #{entity.idCard,jdbcType=VARCHAR} as ID_CARD,
        #{entity.patType,jdbcType=VARCHAR} as PAT_TYPE,
        #{entity.patNum,jdbcType=VARCHAR} as PAT_NUM,
        #{entity.sex,jdbcType=VARCHAR} as SEX,
        #{entity.age,jdbcType=VARCHAR} as AGE,
        #{entity.tel,jdbcType=VARCHAR} as TEL,
        #{entity.linkMan,jdbcType=VARCHAR} as LINK_MAN,
        #{entity.address,jdbcType=VARCHAR} as ADDRESS,
        #{entity.happenTime,jdbcType=TIMESTAMP} as HAPPEN_TIME,
        #{entity.dutyDoctorName,jdbcType=VARCHAR} as DUTY_DOCTOR_NAME,
        #{entity.dutyDoctorId,jdbcType=VARCHAR} as DUTY_DOCTOR_ID,
        #{entity.orgName,jdbcType=VARCHAR} as ORG_NAME,
        #{entity.orgCode,jdbcType=VARCHAR} as ORG_CODE,
        #{entity.depName,jdbcType=VARCHAR} as DEP_NAME,
        #{entity.depCode,jdbcType=VARCHAR} as DEP_CODE,
        SYSDATE AS SYSTEMTIME,
        #{entity.hospitalCode,jdbcType=VARCHAR} as HOSPITAL_CODE
        from dual
    </foreach>
    ) A

</insert>

2、单条插入

<insert id="insertDisease" keyProperty="true" useGeneratedKeys="true">
    <selectKey keyProperty="id" resultType="int" order="BEFORE">
        select SEQ_BD_DD_INFO_ID.nextval from dual
    </selectKey>
    INSERT INTO BD_DD_INFO
    <trim prefix="(" suffix=")" suffixOverrides=",">
        ID,
        <if test="datanum != null and datanum != ''">
            DATANUM,
        </if>
        <if test="datainfo != null and datainfo != ''">
            DATAINFO,
        </if>
        <if test="parentid != null and parentid != ''">
            PARENTID,
        </if>
        <if test="memo != null and memo != ''">
            MEMO,
        </if>
        <if test="sort != null and sort != ''">
            SORT,
        </if>
        <if test="invalid != null and invalid != ''">
            INVALID
        </if>
    </trim>
    VALUES
    <trim prefix="(" suffix=")" suffixOverrides=",">
        #{id,jdbcType=INTEGER},
        <if test="datanum != null and datanum != ''">
            #{datanum,jdbcType=VARCHAR},
        </if>
        <if test="datainfo != null and datainfo != ''">
            #{datainfo,jdbcType=VARCHAR},
        </if>
        <if test="parentid != null and parentid != ''">
            #{parentid,jdbcType=VARCHAR},
        </if>
        <if test="memo != null and memo != ''">
            #{memo,jdbcType=VARCHAR},
        </if>
        <if test="sort != null and sort != ''">
            #{sort,jdbcType=VARCHAR},
        </if>
        <if test="invalid != null and invalid != ''">
            #{invalid,jdbcType=VARCHAR}
        </if>
    </trim>
</insert>

三、创建表时设置ID自增

CREATE TABLE "BD_DATA_TIME"
(
    "ID" VARCHAR2(40) DEFAULT sys_guid() NOT NULL ENABLE,
     "TIME"DATE NULL,
    "USE_TYPE" CHAR(1)
);
COMMENT ON TABLE BD_DATA_TIME IS '数据时间戳保存';
COMMENT ON COLUMN BD_DATA_TIME.TIME IS '时间';
COMMENT ON COLUMN BD_DATA_TIME.USE_TYPE IS '0:筛查病例；1:分类分级；2:判断逾期'