欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页

Oracle通过触发器+序列实现自动增长

程序员文章站 2022-05-07 14:09:27
...

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了吗?答案是否定的。

我们通过数据库客户端工具,连接上数据库,看到触发器上有红色的叹号,说明该触发器是有问题的。

Oracle通过触发器+序列实现自动增长
            
    
    博客分类: Oracle oracletriggersequenceauto_increment 

怎么测试该触发器有问题呢?往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后的分号绝对不可以少。

  • Oracle通过触发器+序列实现自动增长
            
    
    博客分类: Oracle oracletriggersequenceauto_increment 
  • 大小: 4.5 KB