oracle 数据库创建序列
CREATE SEQUENCE seq_employee START WITH 1 MAXVALUE 9999999999999999999999999999;
- CREATE SEQUENCE: 这是一个SQL命令,用于创建一个新的序列。
- seq_employee: 这是新序列的名称。
- START WITH 1: 这意味着序列将从数字1开始生成数字。
- MAXVALUE 9999999999999999999999999999: 这定义了序列生成的最大值。在此例中,序列的最大值是9999999999999999999999999999。当序列达到这个值时,它将回滚到其MINVALUE(如果定义了的话),否则将回滚到其START值。
创建这个序列之后,你可以使用它来生成唯一的数字,例如,作为主键值。例如,如果你有一个名为employees的表,并且想要为主键employee_id使用这个序列,你可以这样做:
sql
INSERT INTO employees (employee_id, name)
VALUES (seq_employee.NEXTVAL, 'John Doe');
从BUSINESSDATA.SEQ_EMPLOYEE序列中获取下一个值,并将其作为id返回。
- FROM DUAL: 在Oracle数据库中,DUAL是一个特殊的单行、单列表,通常用于选择一个常量、执行一个数学运算或执行一个不涉及实际表的查询。在这里,它是用来从SEQ_EMPLOYEE序列中获取下一个值。
SELECT BUSINESSDATA.SEQ_EMPLOYEE.NEXTVAL as id FROM DUAL
mybatis插入数据 单笔数据插入 2种方式 插入序列号
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ruoyi.project.monitor.mapper.ManufacturerClassDateMapper">
<insert id="createManufacturer" >
insert into BUSINESSDATA.manufacturer_class_date (
id,
<if test="classCode != null and classCode != ''">class_code,</if>
<if test="classType != null and classType != ''">class_type,</if>
<if test="startTime != null and startTime != ''">start_time,</if>
<if test="entTime != null and entTime != ''">ent_time,</if>
<if test="restOneStartTime != null and restOneStartTime != ''">rest_one_start_time,</if>
<if test="restOneEntTime != null and restOneEntTime != ''">rest_one_ent_time,</if>
<if test="restTwoStartTime != null and restTwoStartTime != ''">rest_two_start_time,</if>
<if test="restTwoEntTime != null and restTwoEntTime != ''">rest_two_ent_time,</if>
<if test="createBy != null and createBy != ''">create_by,</if>
<if test="updateBy != null and updateBy != ''">update_by,</if>
create_time,update_time
)values(
BUSINESSDATA.SEQ_EMPLOYEE.NEXTVAL,
<if test="classCode != null and classCode != ''">#{classCode},</if>
<if test="classType != null and classType != ''">#{classType},</if>
<if test="startTime != null and startTime != ''">#{startTime},</if>
<if test="entTime != null and entTime != ''">#{entTime},</if>
<if test="restOneStartTime != null and restOneStartTime != ''">#{restOneStartTime},</if>
<if test="restOneEntTime != null and restOneEntTime != ''">#{restOneEntTime},</if>
<if test="restTwoStartTime != null and restTwoStartTime != ''">#{restTwoStartTime},</if>
<if test="restTwoEntTime != null and restTwoEntTime != ''">#{restTwoEntTime},</if>
<if test="createBy != null and createBy != ''">#{createBy},</if>
<if test="updateBy != null and updateBy != ''">#{updateBy},</if>
sysdate,sysdate
)
</insert>
<insert id="createManufacturer1" >
<selectKey keyProperty="id" resultType="long" order="BEFORE">
SELECT BUSINESSDATA.SEQ_EMPLOYEE.NEXTVAL as id FROM DUAL
</selectKey>
insert into BUSINESSDATA.manufacturer_class_date (
<if test="id != null and id != ''">id,</if>
<if test="classCode != null and classCode != ''">class_code,</if>
<if test="classType != null and classType != ''">class_type,</if>
<if test="startTime != null and startTime != ''">start_time,</if>
<if test="entTime != null and entTime != ''">ent_time,</if>
<if test="restOneStartTime != null and restOneStartTime != ''">rest_one_start_time,</if>
<if test="restOneEntTime != null and restOneEntTime != ''">rest_one_ent_time,</if>
<if test="restTwoStartTime != null and restTwoStartTime != ''">rest_two_start_time,</if>
<if test="restTwoEntTime != null and restTwoEntTime != ''">rest_two_ent_time,</if>
<if test="createBy != null and createBy != ''">create_by,</if>
<if test="updateBy != null and updateBy != ''">update_by,</if>
create_time,update_time
)values(
<if test="id != null and id != ''">#{id},</if>
<if test="classCode != null and classCode != ''">#{classCode},</if>
<if test="classType != null and classType != ''">#{classType},</if>
<if test="startTime != null and startTime != ''">#{startTime},</if>
<if test="entTime != null and entTime != ''">#{entTime},</if>
<if test="restOneStartTime != null and restOneStartTime != ''">#{restOneStartTime},</if>
<if test="restOneEntTime != null and restOneEntTime != ''">#{restOneEntTime},</if>
<if test="restTwoStartTime != null and restTwoStartTime != ''">#{restTwoStartTime},</if>
<if test="restTwoEntTime != null and restTwoEntTime != ''">#{restTwoEntTime},</if>
<if test="createBy != null and createBy != ''">#{createBy},</if>
<if test="updateBy != null and updateBy != ''">#{updateBy},</if>
sysdate,sysdate
)
</insert>
</mapper>
批量插入数据 序列号
<?xml version="1.0" encoding="UTF-8" ?>
<!DOCTYPE mapper
PUBLIC "-//mybatis.org//DTD Mapper 3.0//EN"
"http://mybatis.org/dtd/mybatis-3-mapper.dtd">
<mapper namespace="com.ruoyi.project.monitor.mapper.ManufacturerClassDateMapper">
<insert id="createAll">
insert into BUSINESSDATA.manufacturer_class_date (id,class_code,class_type,start_time,ent_time,rest_one_start_time,rest_one_ent_time,rest_two_start_time,rest_two_ent_time,create_by,update_by)
select BUSINESSDATA.SEQ_EMPLOYEE.NEXTVAL as id,a.* from(
<foreach collection="list" item="df" separator="union all" index="" close=")" open="(" >select
#{df.classCode},
#{df.classType},
#{df.startTime},
#{df.entTime},
#{df.restOneStartTime},
#{df.restOneEntTime},
#{df.restTwoStartTime},
#{df.restTwoEntTime},
#{df.createBy},
#{df.updateBy}
from dual
</foreach>
) a
</insert>
</mapper>