我们建表的时候通常会有些ID,这个ID既不能重复,又很重要,那么它的auto_increment是怎么实现的呢?
oracle通常用sequence(序列)和trigger(触发器)来实现:
实现步骤如下:
1.首先先建立相关的表格:
declare
num number;
begin
select count(1) into num from user_tables where table_name='T_USER';
if num>0 then
execute immediate 'drop table T_USER';
end if;
end;
CREATE TABLE t_user (
user_id NUMBER(20) NOT NULL,
username VARCHAR(50) DEFAULT '' UNIQUE,
password VARCHAR(255) DEFAULT '' UNIQUE,
password_hint VARCHAR(255) DEFAULT NULL,
first_name VARCHAR(50) DEFAULT '',
last_name VARCHAR(50) DEFAULT '',
gender VARCHAR(1) DEFAULT 'N',
certi_type NUMBER(2) DEFAULT 9,
certi_code VARCHAR(50) DEFAULT '',
email VARCHAR(255) DEFAULT '',
phone_number VARCHAR(255) DEFAULT NULL,
mobile_phone VARCHAR(255) DEFAULT NULL,
website VARCHAR(255) DEFAULT NULL,
province VARCHAR(100) DEFAULT NULL,
city VARCHAR(50) DEFAULT '',
county VARCHAR(100) DEFAULT '',
area VARCHAR(100) DEFAULT '',
zip VARCHAR(15) DEFAULT '',
direct_city NUMBER(1) DEFAULT 0,
version NUMBER(11) DEFAULT 0,
account_enabled NUMBER(1) DEFAULT 0,
account_expired NUMBER(1) DEFAULT 0,
account_locked NUMBER(1) DEFAULT 1,
credentials_expired NUMBER(1) DEFAULT 0,
PRIMARY KEY (user_id)
);
2.然后建立相关的序列:
CREATE SEQUENCE SEQ_T_USER
INCREMENT BY 1 --
MINVALUE 0 --最小值
MAXVALUE 99999999 --最大值由NUMBER(8)
NOCYCLE --不打环
NOCACHE --不缓存
ORDER;
COMMIT;
3.最后建立相应的触发器将序列生效
CREATE OR REPLACE TRIGGER TRG_ADD_TUSER
BEFORE INSERT ON T_USER
FOR EACH ROW
BEGIN
SELECT SEQ_T_USER.NEXTVAL INTO :NEW.UID FROM DUAL; --:NEW.ID是新的值
END TRG_ADD_TUSER;