【oracle11g自增主鍵】,oracle11g主鍵

來源:互聯網
上載者:User

【oracle11g自增主鍵】,oracle11g主鍵

oracle的自增主鍵需要用序列和觸發器來實現,我們首先建立一張表:主鍵為int,實現自增
createtableTEST
(
  ID intPRIMARYKEY,
  test1 varchar2(20),
  test2 varchar2(20),
  test3 varchar2(20))

建立一個序列:
create sequence SEQ_TEST
minvalue 1    --最小值
maxvalue 999999999999999999999999999 --最大值
start with 1 ---從1開始
increment by 1 ---每次加1
nocycle        --一直累加,不迴圈       nocache;

建立一個觸發器實現資料插入時主鍵自增:

CREATE OR REPLACE TRIGGER tg_test
//test表名,id主鍵名,seq_test.nextval序列
BEFORE INSERT ON testFOR EACH ROW WHEN (new.id is null)
     beginselect seq_test.nextval into:new.id from dual;
end;
可以看出當插入資料主鍵為空白時,會使用序列產生自增主鍵
//------------------觸發器樣式
CREATE OR REPLACE TRIGGER TRI_SCORE
BEFORE INSERT OR UPDATE ON XUANKE
FOR EACH ROW
BEGIN
//如果score為空白,則令score=0
  IF :new.SCORE IS NULL THEN :new.SCORE:= 0;
    END IF;
END;

相關文章

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.