ORACLE 自增序列

-- 1、在PLSQL下先建立一個專用的使用者
-- Create the user
create user user1
  identified by \"user1\"
  default tablespace USERS
  temporary tablespace TEMP
  profile DEFAULT;
-- Grant/Revoke role privileges
grant dba to user1;
-- Grant/Revoke system privileges
grant unlimited tablespace to user1;
------------------------------------

------------------------------------
-- 建表
create table AAA (
oid NUMBER NOT NULL,
TYPE NUMBER(3)
);

 

以下部分處理自增序列問題

--自增序列
--第一步：建一個隊列
Create Sequence AAA_OID_sequence
INCREMENT BY 1  --每次加幾個
START WITH 1      --從1開始計數
NOMAXVALUE       --不設定最大值
NOCYCLE              --一直累加，不迴圈
NOCACHE ;

--第二步：建立觸發器
CREATE OR REPLACE TRIGGER AAA_OID_TRIGGER
BEFORE INSERT ON AAA  FOR EACH ROW
DECLARE
next_id NUMBER;
BEGIN
  --Get the next id number from the sequence
   SELECT AAA_OID_sequence.NEXTVAL INTO next_id FROM dual;
  --Use the sequence number as the primarykey
  --for there cord being inserted.
  :new.oid:=next_id;
END;

 

測試：

INSERT INTO AAA (TYPE) VALUES (2);

SELECT * FROM aaa;