整数自增列的实现
--方式一:用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开始.
*/