整数自增列的实现

--方式一:用SEQUENCE实现,插入时使用NEXTVAL获取自增整数的值
CREATE TABLE TEMPA2(
SID NUMBER NOT NULL,--自增整数列
SNAME VARCHAR2(200),
CONSTRAINT PK_TEMPA2 PRIMARY KEY(SID) );

CREATE SEQUENCE SEQ_TEMPA2 INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE;--TEMPA2.SID

INSERT INTO TEMPA2(SID,SNAME) VALUES(SEQ_TEMPA2.NEXTVAL,'A1');
INSERT INTO TEMPA2(SID,SNAME) VALUES(SEQ_TEMPA2.NEXTVAL,'A1');

/*
SQL> SELECT * FROM TEMPA2;
 
       SID SNAME
---------- --------------------------------------------------------------------------------
         2 A1
         3 A1
*/

--方式二:SEQUENCE+TRIGGER,插入时不用管自增列
CREATE TABLE TEMPA3(
SID NUMBER NOT NULL,--自增整数列
SNAME VARCHAR2(200),
CONSTRAINT PK_TEMPA3 PRIMARY KEY(SID) );

CREATE SEQUENCE SEQ_TEMPA3 INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE;--TEMPA3.SID

CREATE OR REPLACE TRIGGER TRIGGER_TEMPA3 BEFORE INSERT ON TEMPA3 FOR EACH ROW
DECLARE
BEGIN
  SELECT SEQ_TEMPA3.NEXTVAL INTO :NEW.SID FROM DUAL;
END;

INSERT INTO TEMPA3(SNAME) VALUES('A1');
INSERT INTO TEMPA3(SNAME) VALUES('A2');

/*
SQL> SELECT * FROM TEMPA3;
 
       SID SNAME
---------- --------------------------------------------------------------------------------
         8 A1
        10 A2
*/

--方式三:保证多表自动整数的一致性
CREATE TABLE TEMPA4(
SID NUMBER NOT NULL,--自增整数列
SNAME VARCHAR2(200),
CONSTRAINT PK_TEMPA4 PRIMARY KEY(SID) );

CREATE TABLE TEMPA5(
SID NUMBER NOT NULL,--自增整数列
XID NUMBER NOT NULL,--自增整数列
XNAME VARCHAR2(200),
CONSTRAINT PK_TEMPA5 PRIMARY KEY(XID) );

CREATE SEQUENCE SEQ_TEMPA4 INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE;--TEMPA4.SID
CREATE SEQUENCE SEQ_TEMPA5 INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE;--TEMPA5.XID

DECLARE SEQVAL NUMBER;
BEGIN
  SELECT SEQ_TEMPA4.NEXTVAL INTO SEQVAL FROM DUAL;
  INSERT INTO TEMPA4(SID,SNAME) VALUES (SEQVAL,'A1');
  INSERT INTO TEMPA5(SID,XID,XNAME) VALUES (SEQVAL,SEQ_TEMPA5.NEXTVAL,'B1');--这样就保证了两张表SID的一致性!
  INSERT INTO TEMPA5(SID,XID,XNAME) VALUES (SEQVAL,SEQ_TEMPA5.NEXTVAL,'B2');
END;

/*
SQL> SELECT * FROM TEMPA4;
 
       SID SNAME
---------- --------------------------------------------------------------------------------
         1 A1
 
SQL> SELECT * FROM TEMPA5;
 
       SID        XID XNAME
---------- ---------- --------------------------------------------------------------------------------
         1          2 B1
         1          3 B2
*/

/*SEQUENCE补充:
CREATE SEQUENCE SEQ_TEMPA4 INCREMENT BY 1 START WITH 1 NOMAXVALUE NOCYCLE;
采用SEQ_TEMPA5.NEXTVAL插入表,是从2开始.
SELECT SEQ_TEMPA2.NEXTVAL FROM DUAL;是从1开始.
*/