︿
Top

2020年12月28日 星期一

Oracle: Empty string and NOT NULL column


0. 前言


最近的專案又回到 Oracle, 印象中, 在作空字串與 Null 的處理時, 有一些要注意的地方. 所以, 就整理了本篇及 上一篇 的文章.

茲區分以下幾個部份作說明.




1. SQL 指令 (NOT NULL)


?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
DROP TABLE USER1.TESTNOTNULL;
/
CREATE TABLE USER1.TESTNOTNULL (
    ID    NUMBER(3,0)     DEFAULT 0 NOT NULL,
    NAME  CHAR(10)    DEFAULT ' ' NOT NULL,
    ADDRESS   NVARCHAR2(20)     DEFAULT ' ' NOT NULL,
   CONSTRAINT PK_TESTNOTNULL PRIMARY KEY ( ID )
);
/
INSERT INTO TESTNOTNULL(ID, NAME, ADDRESS) 
VALUES (1, 'JASPER', 'TAIPEI');
-- NAME 及 ADDRESS 給預設值.
INSERT INTO TESTNOTNULL(ID)
VALUES (2);
-- NAME 給預設值, ADDRESS 填入 5  個 bytes 的空白
INSERT INTO TESTNOTNULL(ID, ADDRESS)
VALUES (3, LPAD(' ', 5, ' '));
/
-- 情境1 -------------------------
PROMPT -- [情境1] -------------------------;
SELECT ID
    , '[' || NAME || ']' AS NAME1
    , '[' || ADDRESS || ']' AS ADDRESS1
FROM TESTNOTNULL;
-- 情境2 -------------------------
PROMPT -- [情境2] -------------------------;
-- 3 筆資料的 NAME 長度都是 10 --> 因為 CHAR(10), 會被補空白.
-- 3 筆資料的 ADDRESS 長度不同 --> 因為 VARCHAR2(20), 不會被補空白. 以實際資料值為準
SELECT ID
    , LENGTH(NAME) AS LEN1
    , LENGTH(ADDRESS) AS LEN2
FROM TESTNOTNULL;
/
-- INSERT 失敗情境_A -------------------------
PROMPT -- [INSERT 失敗情境_A] -------------------------;
-- NAME 填入空字串, ADDRESS  填入 3  個 bytes 的空白 --> 會失敗
INSERT INTO TESTNOTNULL(ID, NAME, ADDRESS)
VALUES (4, '', LPAD(' ', 3, ' ') );
-- INSERT 失敗情境_B -------------------------
PROMPT -- [INSERT 失敗情境_B] -------------------------;
-- NAME 給預設值, ADDRESS 填入空字串 --> 會失敗
INSERT INTO TESTNOTNULL(ID,  ADDRESS)
VALUES (5, '' );
/


2. 輸出結果 (NOT NULL)


?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
Table USER1.TESTNOTNULL 已刪除.
Table USER1.TESTNOTNULL 已建立.
 
1 資料列 已插入.
1 資料列 已插入.
1 資料列 已插入.
 
-- [情境1] -------------------------
        ID NAME1        ADDRESS1             
---------- ------------ ----------------------
         1 [JASPER    ] [TAIPEI]             
         2 [          ] [ ]                  
         3 [          ] [     ]              
 
-- [情境2] -------------------------
        ID       LEN1       LEN2
---------- ---------- ----------
         1         10          6
         2         10          1
         3         10          5
 
-- [INSERT 失敗情境_A] -------------------------
命令的第 37 行開始發生錯誤 -
INSERT INTO TESTNOTNULL(ID, NAME, ADDRESS)
VALUES (4, '', LPAD(' ', 3, ' ') )
錯誤報告 -
ORA-01400: 無法將空值插入 ("USER1"."TESTNOTNULL"."NAME")
 
-- [INSERT 失敗情境_B] -------------------------
命令的第 42 行開始發生錯誤 -
INSERT INTO TESTNOTNULL(ID,  ADDRESS)
VALUES (5, '' )
錯誤報告 -
ORA-01400: 無法將空值插入 ("USER1"."TESTNOTNULL"."ADDRESS")

3. 個別執行 SELECT 指令的截圖 (NOT NULL)


[情境1]


[情境2]


4. 結論 (NOT NULL)


不論是 CHAR or VARCHAR2 只要欄位是 不允許 NULL, 就不能在 INSERT, UPDATE 時, 填入空字串, 因為空字串被視為 NULL 值.


5. 參考文件


張貼者： 於
標籤： , ,

沒有留言:

張貼留言

訂閱： 張貼留言 (Atom)