Oracle通过触发器+序列实现自动增长
Oracle建立数据库表时,主键上无法加auto_increment,那么可以通过sequence和trigger来实现主键自动增长。
实现代码:
create table member( memberId number primary key, memberMail varchar2(20)not null, memberName varchar2(20) not null, memberPassword varchar2(20) ); CREATE SEQUENCE emp_sequence INCREMENT BY 1 -- 每次加几个 START WITH 1 -- 从1开始计数 NOMAXVALUE -- 不设置最大值 NOCYCLE -- 一直累加,不循环 NOCACHE; -- 不建缓冲区 create or replace trigger mem_trig before insert on member for each row when (new.memberId is null) begin select emp_sequence.nextval into:new.memberId from dual; end;
通过执行如上sql脚本,可以达到目的。
测试代码:
insert into member(memberMail, memberName, memberPassword) values('2222@163.com', 'jack', '33333')从上诉insert语句中可以看出,并没有给主键memberId赋值,那么它的值从哪里来呢?
因为前面我们为该表建立了触发器和序列,根据该触发器,当我们插入数据时,如果memberId为null,那么数据库会将序列emp_sequence.nextval的值作为主键memberId的值,最后执行插入操作。
注意问题1:
当你将上诉sql脚本拷贝到如:navicat的查询面板中执行时,千万不要执行[美化SQL]操作,如果执行了就是如下结果:
CREATE TABLE MEMBER ( memberId NUMBER PRIMARY KEY, memberMail VARCHAR2 (20) NOT NULL, memberName VARCHAR2 (20) NOT NULL, memberPassword VARCHAR2 (20) ); CREATE SEQUENCE emp_sequence INCREMENT BY 1 -- 每次加几个 START WITH 1 -- 从1开始计数 NOMAXVALUE -- 不设置最大值 NOCYCLE -- 一直累加,不循环 NOCACHE; -- 不建缓冲区 CREATE OR REPLACE TRIGGER mem_trig BEFORE INSERT ON MEMBER FOR EACH ROW WHEN (NEW .memberId IS NULL) BEGIN SELECT emp_sequence.nextval INTO : NEW .memberId FROM dual ; END ;
反复的看,发现好像没有本质区别啊。但是请注意:emp_sequence.nextval INTO : NEW .memberId
其中INTO : NEW .memberId多了空格,这是错误的。
现在执行美化SQL之后的脚本,当执行create or replace...时,会出现如下错误:
[SQL]CREATE OR REPLACE TRIGGER mem_trig BEFORE INSERT ON MEMBER FOR EACH ROW WHEN (NEW .memberId IS NULL) BEGIN SELECT emp_sequence.nextval INTO : NEW .memberId FROM dual ; END ; [Err] ORA-24344: success with compilation error
这是编译错误,而导致该错误的原因就是美化SQL之后出现的空格,因此一定要注意。
注意问题2:
对于如下脚本:
CREATE OR REPLACE TRIGGER mem_trig BEFORE INSERT ON MEMBER FOR EACH ROW WHEN (NEW .memberId IS NULL) BEGIN SELECT emp_sequence.nextval INTO : NEW .memberId FROM dual ; END;
一定要注意,END后的分号";"绝对不可以省略,否则会出编译错误,如下所示:
ORA-24344: success with compilation error
因为这是pl/sql,语法必须严格。
又有人在java代码中通过jdbc测试没有加分号时发现,执行过程并没有报错啊。
package com.ssh.model; import java.sql.Connection; import java.sql.SQLException; import java.sql.Statement; import org.junit.Test; public class TestBeginEnd { @Test public void testBeginEnd() { Connection conn = DBUtil.getConnection(); try { Statement stmt = conn.createStatement(); String sql = "CREATE OR REPLACE TRIGGER trg_test_id \n" + "BEFORE INSERT ON test \n" + "FOR EACH ROW \n" + "WHEN (NEW.id IS NULL) \n" + "BEGIN \n" + "SELECT hibernate_sequence.nextval INTO :NEW.id FROM DUAL; \n" + "END"; stmt.executeUpdate(sql); stmt.close(); } catch (SQLException e) { e.printStackTrace(); } } }
运行代码没有报任何的错误。但是这个让主键自动增长的触发器真的ok了吗?答案是否定的。
我们通过数据库客户端工具,连接上数据库,看到触发器上有红色的叹号,说明该触发器是有问题的。
怎么测试该触发器有问题呢?往test表中插入一条数据即可:test表有id, name字段,其中id number(19, 0) primary key.
insert into test(name) values('张三');
执行,报错:
[SQL]insert into test(name) values('张三')
[Err] ORA-04098: trigger 'YTKJ.TRG_TEST_ID' is invalid and failed re-validation
说明该触发器时有问题的。把分号加上,再测试ok。
综上所述,END后的分号绝对不可以少。