那么如何让oracle数据库插入数据像mysql一样实现主键自增呢?解决方法是:序列+触发器
案例:
plsql图示:
1.创建表:
2.创建序列:
3.创建触发器:
sql实现:
1.创建表
- -- Create table
- create table SJK_BAR_CODE
- (
- id NUMBER(12) not null,
- rule VARCHAR2(255),
- esp_no VARCHAR2(100),
- table_name VARCHAR2(10),
- esp_id NUMBER(12),
- is_del NUMBER(1),
- create_time DATE,
- update_time DATE,
- esp_content VARCHAR2(100)
- )
- tablespace USERS
- pctfree 10
- initrans 1
- maxtrans 255
- storage
- (
- initial 64K
- minextents 1
- maxextents unlimited
- );
- -- Add comments to the columns
- comment on column SJK_BAR_CODE.is_del
- is '1:否,2:是';
- comment on column SJK_BAR_CODE.esp_content
- is '档案内容';
- -- Create/Recreate primary, unique and foreign key constraints
- alter table SJK_BAR_CODE
- add constraint PK_BAR_CODE primary key (ID)
- using index
- tablespace USERS
- pctfree 10
- initrans 2
- maxtrans 255
- storage
- (
- initial 64K
- minextents 1
- maxextents unlimited
- );
2.创建序列
- -- Create sequence
- create sequence BAR_CODE_SEQUENCE
- minvalue 1
- maxvalue 999999999999999999999999999
- start with 1
- increment by 1
- cache 20;
备注:
minvalue 1 -----最小值
maxvalue 9999999999999999999999999999 ------最大值
increment by 1 -----每次加几个
start with 1 -----从几开始
cache 20 -----缓存值多少
noorder -----一直累加,不排序
nocycle ; --一直累加不循环
3.创建触发器
- CREATE OR REPLACE TRIGGER BAR_CODE_TG
- before insert on SJK_BAR_CODE for each row
- begin
- select BAR_CODE_SEQUENCE.nextval into :new.ID from dual;
- end;
4.插入记录
- INSERT INTO SJK_BAR_CODE(rule,esp_no)VALUES ('00-100-2016-0013','100')
5.查询记录
- select * from SJK_BAR_CODE
查询结果集:
到此这里发现插入数据主键自动递增,实现像mysql一样的小若。
总结:
1.在实际开发中,具体情况具体分析,也不一定都要创建触发器。
2.若是没有创建触发器,只创建了序列,则插入数据必须插入主键id,主键id值为序列如:
- <insert id="addSonDeptOp" parameterType="com.suwei.sysMng.bean.PtDept">
- insert into
- PT_DEPT(DEPT_ID,DEPT_NAME,DEPT_LNAME,DEPT_CODE,DEPT_ADDR,DEPT_PHONE,DEPT_TYPE,DEPT_LEVEL,DEPT_MANAGER,USE_FLAG,FDEPT_ID)
- values(PT_SEQ.nextval,#{deptName},#{deptLname},#{deptCode},#{deptAddr},#{deptPhone},#{deptType},#{deptLevel},#{deptManager},#{useFlag},#{fdeptId})
- </insert>
- select * from all_sequences
结果图:
4.oracle查询当前用户所有序列(常用)
- select * from user_sequences
5.oracle查询所有触发器
- select object_name from dba_objects where object_type='TRIGGER'
6.oracle普通用户的权限查询所有触发器
查询显示所有列:select * from user_source where type='TRIGGER'
7.oracle查询当用用户的所有触发器
则:
- select object_name from dba_objects where object_type='TRIGGER' and owner='SJKNEW'
如触发器名称"BAR_CODE_TG"
- select text from user_source where type='TRIGGER' and name='BAR_CODE_TG';