軟體工程師的小事：到底要不要 Close

0

實作 Oracle 資料庫資料表擁有類似 MS-SQL IDENTITY 的功能

在 Microsoft SQL Server 建立資料表時會設定主索引鍵欄位 IDENTITY 指定遞增序號，但 Oracle 並沒有 IDENTITY 選項給使用者建立遞增序號主索引鍵

雖然沒有 IDENTITY，不過 Oracle 有序列 (sequence) 功能實現遞增序號，可透過觸發程序 (trigger) 的方式讓新增資料列時不需要刻意從序列 (sequence) 取得序號，讓觸發程序幫你自動完成從序列取值動作

Microsoft SQL Server 在特定版本後也增加此功能
文章環境

資料庫為 wnameless/oracle-xe-11g 的 Docker Image wnameless/oracle-xe-11g - Docker Hub

工具為 Oracle SQL Developer

如何使用 docker 建立 Oracle 資料庫與 Oracle SQL Developer 操作教學不在此文章的範圍
建立範例資料表與相關物件

本篇文章描述的 PL/SQL 指令碼可以在此 Github 連結中取得
實作 Oracle 資料庫的資料表擁有類似 MS-SQL IDENTITY 的功能

建立資料表主索引鍵使用的序列

CREATE SEQUENCE "SYSTEM"."EVENT_SEQ"
 START WITH 1 
 INCREMENT BY 1;

建立範例資料表

CREATE TABLE "SYSTEM"."EVENT_LOGS"
(
    "No"  INT,
    "StatusCode" INT,
    "EventTime"  TIMESTAMP WITH TIME ZONE DEFAULT(SYSDATE),
    "Content"  CLOB,
    
    CONSTRAINT "pk_EVENT_LOGS" PRIMARY KEY ("No")
);

建立會自動從序列取得序號的觸發程序

--建立當 INSERT 未指定 NO 欄位時會自動從序列取得數值的觸發程序
CREATE OR REPLACE TRIGGER "SYSTEM"."TRG_EVENT_LOGS_SEQ"
 BEFORE INSERT ON "SYSTEM"."EVENT_LOGS"
 FOR EACH ROW WHEN (new."No" IS NULL) 
BEGIN
    SELECT "SYSTEM"."EVENT_SEQ".NEXTVAL 
    INTO :new."No" FROM DUAL;
END;
未指定 No 欄位進行資料表新增作業

建立完成後使用 INSERT 指令進行資料列的新增作業，並不指定主索引鍵欄位 NO

INSERT INTO "SYSTEM"."EVENT_LOGS" ("StatusCode","Content")
    VALUES (200,'1nothing');

新增完成後，SYSTEM.EVENT_LOGS 的 No 欄位就會被 TRG_EVENT_LOGS_SEQ 觸發程序帶入目前 EVENT_SEQ 的數值

insert data without no column

透過觸發程序 (trigger) 讓取得主索引鍵數值的權責由資料庫這邊來負責

參考資料

留言

張貼留言