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;