不能定义一个字段自增,但可以用序列来实现:
创建序列:
CREATE SEQUENCE seq_name
INCREMENT BY 1
START WITH 1
MINVALUE 1
MAXVALUE 999999999
CYCLE
NOORDER
CACHE 50
/
使用序列:
SELECT LTRIM(TO_CHAR(seq_name.NEXTVAL,'000000000')) INTO v_var FROM DUAL;
创建序列:
CREATE SEQUENCE seq_name
INCREMENT BY 1
START WITH 1
MINVALUE 1
MAXVALUE 999999999
CYCLE
NOORDER
CACHE 50
/
使用序列:
SELECT LTRIM(TO_CHAR(seq_name.NEXTVAL,'000000000')) INTO v_var FROM DUAL;
例子:Drop TABLE DBMTS_HISTORY;CREATE TABLE DBMTS_HISTORY
(
ID INTEGER NOT NULL,
DESCRIPTION VARCHAR2(2000 BYTE)
)
;CREATE OR REPLACE TRIGGER SET_DBMTS_HISTORY_ID
BEFORE INSERT
ON DBMTS_HISTORY
FOR EACH ROW
DECLARE
NEXT_ID NUMBER;BEGIN
SELECT DBMTS_HISTORY_ID_SEQ.NEXTVAL INTO NEXT_ID FROM dual;
:NEW.ID := NEXT_ID;END ;
/CREATE OR REPLACE TRIGGER UPDATE_DBMTS_HISTORY_ID
BEFORE UPDATE OF ID
ON DBMTS_HISTORY
FOR EACH ROW
BEGIN
RAISE_APPLICATION_ERROR(-20000, 'Updates of the ID filed are NOT allowed.');
END ;
/ALTER TABLE DBMTS_HISTORY ADD (
CONSTRAINT PK_DBMTS_HISTORY PRIMARY KEY (ID));
CREATE SEQUENCE DBMTS_HISTORY_ID_SEQ
START WITH 7
MAXVALUE 9999999999
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;
例子:Drop TABLE DBMTS_HISTORY;CREATE TABLE DBMTS_HISTORY
(
ID INTEGER NOT NULL,
DESCRIPTION VARCHAR2(2000 BYTE)
)
;CREATE OR REPLACE TRIGGER SET_DBMTS_HISTORY_ID
BEFORE INSERT
ON DBMTS_HISTORY
FOR EACH ROW
DECLARE
NEXT_ID NUMBER;BEGIN
SELECT DBMTS_HISTORY_ID_SEQ.NEXTVAL INTO NEXT_ID FROM dual;
:NEW.ID := NEXT_ID;END ;
/CREATE OR REPLACE TRIGGER UPDATE_DBMTS_HISTORY_ID
BEFORE UPDATE OF ID
ON DBMTS_HISTORY
FOR EACH ROW
BEGIN
RAISE_APPLICATION_ERROR(-20000, 'Updates of the ID filed are NOT allowed.');
END ;
/ALTER TABLE DBMTS_HISTORY ADD (
CONSTRAINT PK_DBMTS_HISTORY PRIMARY KEY (ID));
CREATE SEQUENCE DBMTS_HISTORY_ID_SEQ
START WITH 7
MAXVALUE 9999999999
MINVALUE 1
NOCYCLE
NOCACHE
NOORDER;