第六十六期 SQL Domain

上一期一筆帶過了部分Oracle 23c的新特性，這一期重點(diǎn)講一下SQL Domain新特性。
【https://docs.oracle.com/en/database/oracle/oracle-database/23/sqlrf/create-domain.html】

1 基本介紹

SQL Domain是一個(gè)屬于SCHEMA的高級(jí)字典對(duì)象，包含一組可選的屬性和約束?？梢詫⒈碇械牧信cDomain進(jìn)行關(guān)聯(lián)，從而顯示的將Domain包含的可選屬性和約束應(yīng)用到這些列中。一個(gè)Domain至少必須指定一個(gè)Oracle內(nèi)建的數(shù)據(jù)類型。Domain的數(shù)據(jù)類型必須是單個(gè)的Oracle數(shù)據(jù)庫類型。對(duì)于字符數(shù)據(jù)類型，必須指定一個(gè)最大長度，即是VARCHAR2(L [CHAR|BYTE]), NVARCHAR2(L), CHAR(L [CHAR|BYTE]), or NCHAR(L)中的一個(gè)。

2 Domain的表達(dá)式和條件

Domain的表達(dá)式可以簡單的、日期時(shí)間、時(shí)間間隔，CASE、復(fù)合或者domain列表表達(dá)式之一：

• 簡單的domain表達(dá)式可以是字符串、數(shù)字、 sequence.CURRVAL、 sequence.NEXTVAL、NULL或者SCHEMA Domain。它類似于簡單表達(dá)式，只不過使用domain名稱而不是列名。它將域名引用為限定名稱、Oracle內(nèi)置的Domain名或者使用一個(gè)Domain的公有同義詞。
• 日期時(shí)間Domain表達(dá)式僅引用域表達(dá)式的日期時(shí)間表達(dá)式。
• 時(shí)間間隔表達(dá)式僅僅被定義為正則區(qū)間表達(dá)式，只是它引用了domain表達(dá)式。例如， (SYSTIMESTAMP - DayOfWeek) DAY(9) TO SECOND 是一個(gè)時(shí)間間隔表達(dá)式。
• 符合表達(dá)式是(expr), expr op expr with op +, -, *, /, ||, or expr排序collation_name，其中expr是一個(gè)domain表達(dá)式。

'email: ' || EmailAddress
DayOfWeek + INTERVAL '1' DAY
TO_CHAR(LastFour(SSN))

• CASE domain表達(dá)式與正則case表達(dá)式類似，只是它僅引用domain表達(dá)式。

CASE WHEN TO_UPPER(DOMAIN_DISPLAY(DayOfWeek)) != 'SAT' AND TO_UPER(DOMAIN_DISPLAY(DayOfWeek)) != 'SUN' THEN 'week day' ELSE 'weekend' END 

• 與SQL Domain表達(dá)式的定義類似，Domain條件就像正則SQL條件一樣，只是它只引用Domain表達(dá)式。您可以在Domain表達(dá)式中使用關(guān)鍵字值，而不是使用Domain名稱。

CREATE DOMAIN DayOfWeek AS CHAR(3 CHAR)
CONSTRAINT
CHECK DayOfWeek_C (UPPER(Substr(VALUE, 1, 3)) IN ('MON', 'WED', 'FRI', 'SAT', 'SUN') orUPPER(Substr(VALUE, 1, 2)) IN ('TU', 'TH')) 
DEFERRRABLE INITIALLY DEFERRED
COLLATE BINARY_CI
DISPLAY SUBSTR(VALUE, 1, 3);

3 語法

create_domain::=

create_single_column_domain::=

column_properties_clause::=

create_multi_column_domain::=

create_flexible_domain::=

result_expr::=

default_clause::=

constraint_clause::=

annotations_clause::=

4 語義

• IF NOT EXISTS

  • 如果domain不存在則創(chuàng)建新的domain
  • 如果domain存在則不會(huì)創(chuàng)建新的domain

  如果使用IF EXISTS則會(huì)報(bào)錯(cuò)：Incorrect IF NOT EXISTS clause for CREATE statement。

• domain_name
  domain_name遵循與任何類型名稱相同的限制，并且不能與domain schema中的任何對(duì)象的名稱、任何Oracle提供的數(shù)據(jù)類型以及任何Oracle提供的domain名稱發(fā)生沖突。
  這些限制適用于CDB環(huán)境中的PDB級(jí)別。
  請(qǐng)注意，域是schema的catalog對(duì)象，因此受schema級(jí)別對(duì)象的限制。

• datatype
  datatype必須是Oracle內(nèi)建的數(shù)據(jù)類型：

  • CHAR(L [CHAR|BYTE]), NCHAR(L), VARCHAR(L [CHAR|BYTE]), VARCHAR2(L [CHAR|BYTE]), NVARCHAR2(L), LONG
  • NUMBER[p, [s]], FLOAT, BINARY_FLOAT, BINARY_DOUBLE
  • RAW, LONG RAW (extended included)
  • DATE, TIMESTAMP (WITH (LOCAL) TIME ZONE), INTERVAL
  • BFILE, BLOB, CLOB, NCLOB
  • JSON native datatype
  • BOOLEAN

• default_expression
  default_expression必須是一個(gè)域表達(dá)式，并且必須符合給定數(shù)據(jù)類型的默認(rèn)列表達(dá)式的所有限制：

  • default_expression不能包含返回域引用或嵌套函數(shù)調(diào)用的SQL函數(shù)，它不能是子查詢表達(dá)式。
  • default_expression的數(shù)據(jù)類型必須于domain指定的數(shù)據(jù)類型匹配。
  • 作為domain的表達(dá)式，default_expression不能引用任何表或列以及任何其他domain名。
  • default_expression可以指一個(gè)序列的NEXTVAL和CURRVAL。不能引用PL/SQL函數(shù)。

• constraint_clause
  請(qǐng)注意，domain約束可以有可選的名稱。它們NOT NULL、NULL或CHECK約束?？梢栽诹屑?jí)別和domain級(jí)別上同時(shí)指定多個(gè)這樣的約束子句。
  CHECK條件以及ALTER DOMAIN中的檢查條件和表達(dá)式只能引用domain的列。如果domain有單個(gè)列，則列名是domain名或關(guān)鍵字VALUE，但相同的表達(dá)式不能同時(shí)包含domain名和VALUE作為列名。
  constraint_name是可選。當(dāng)指定時(shí)，它不能與schema中的任何其他約束的名稱發(fā)生沖突（如果在CDB環(huán)境中，則在給定的PDB中）。當(dāng)未指定時(shí)，將使用系統(tǒng)生成的名稱。domain約束遵循與表級(jí)約束和列級(jí)約束相同的規(guī)則：一個(gè)已命名的表或列級(jí)約束不能與同一schema中的任何其他約束的名稱相一致。即使是在相同的schema中，domain約束可以與表名相同。它們可以與列名相同，并且約束可以與它在上面定義的表或列使用相同的名稱。
  CHECK條件必須是一個(gè)domain的邏輯條件，同時(shí)必須符合對(duì)轉(zhuǎn)換為domain表達(dá)式的檢查約束的所有限制：

  • 它只能引用domain名，就像列上的檢查約束只能引用列一樣。它不能引用任何表或視圖中的任何列，甚至在domain schema中也不能。
  • 不能使用子查詢或標(biāo)量查詢表達(dá)式。
  • 條件不能引用非確定性函數(shù)（如CURRENT_DATE），或用戶定義的PL/SQL函數(shù)。
  • 允許CHECK IS JSON（STRICT）約束。
  • CHECK約束條件一次應(yīng)用于一個(gè)值，如果值替換為domain_name的CHECK條件計(jì)算結(jié)果為TRUE或UNKNOWN，則滿足該條件。

  Domain約束可以按任意順序強(qiáng)制執(zhí)行。
  NULL約束意味著允許domain的值為NULL，并且是默認(rèn)值。
  如果未指定constraint_state，則約束為NOT DEFERABLE INITIALLY IMMEDIATE。

• COLLATE
  當(dāng)指定排序規(guī)則時(shí)，它符合列級(jí)別或SCHEMA級(jí)別排序規(guī)則的所有限制。如果指定了排序規(guī)則，數(shù)據(jù)類型必須是字符數(shù)據(jù)類型。
  當(dāng)創(chuàng)建一個(gè)表，其中的列標(biāo)記為一個(gè)排序規(guī)則不同于該列的排序規(guī)則的domain時(shí)，將引發(fā)錯(cuò)誤。
  將列更改為具有與列domain的排序規(guī)則不同的排序規(guī)則時(shí)，將引發(fā)錯(cuò)誤。
  這應(yīng)該確保具有指定排序規(guī)則的域的所有列具有與其域相同的排序規(guī)則的不變性。如果未指定排序規(guī)則且數(shù)據(jù)類型是可排序的，則將使用列的排序規(guī)則（如果已指定），否則將使用域模式中的基礎(chǔ)默認(rèn)數(shù)據(jù)類型排序規(guī)則。
  如果未指定排序規(guī)則且數(shù)據(jù)類型是可排序的，則將使用列的排序規(guī)則（如果已指定）。

• display_expression
  使用 display_expression 根據(jù)domain規(guī)范格式化數(shù)據(jù)。它可以是任何允許作為domain數(shù)據(jù)類型的數(shù)據(jù)類型。 display_expression 必須是不包含表或視圖列、子查詢、非確定性函數(shù)或 PL/SQL 函數(shù)的域表達(dá)式。它可以引用 domain_name。如果您沒有為表達(dá)式指定排序規(guī)則，則 display_expression 將使用domain的排序規(guī)則（如果已指定）。

• order_expression
  使用 order_expression 對(duì)domain規(guī)范的值進(jìn)行排序和比較。
  order_expression 必須符合與 display_expressions 相同的限制，并且還必須是字節(jié)或字符可比較數(shù)據(jù)類型。如果為表達(dá)式的domain指定了order_expression，則返回的有domain_nameorder_expression而不是表達(dá)式，否則返回表達(dá)式。

• annotations_clause
  annotation_name是一個(gè)最多可以包含4000個(gè)字符的標(biāo)識(shí)符。如果注釋名稱是保留字，則必須用雙引號(hào)提供。當(dāng)使用雙引號(hào)標(biāo)識(shí)符時(shí)，該標(biāo)識(shí)符還可以包含空白字符。但是，不接受僅包含空白字符的標(biāo)識(shí)符。
  有關(guān)annotations_clause的示例，請(qǐng)參見末尾的示例。
  有關(guān)annotations子句的完整語義，請(qǐng)參閱CREATE TABLE的annotations_clause。

• FROM Clause of Create Flexible Domain
  expr和comparison_expr引用了domain_disperiment_column列表中的domain判別列。
  靈活東面的FROM子句是DECODE或CASE表達(dá)式，它只引用搜索表達(dá)式中的判別式列名（在CHOOSE DOMIAN USING后面的列表中），并且在結(jié)果表達(dá)式中只有domain名后面跟著列列表。結(jié)果表達(dá)式中的列必須僅為東面列列表中的列（在CREATE FLEXILE DOMAIN之后）。

5 示例

從CREATE TABLE章節(jié)中找到了建表是SQL DOMAIN的使用方法。

CREATE TABLE [owner.]name (   colname [DOMAIN][domain_owner.]domain_name [<column_def_clause>] [, colname [DOMAIN] [domain_owner.]domain_name [<column_def_clause>]])CREATE TABLE [owner.]name (column_list_def_clause [, DOMAIN  [domain_owner.]domain_name (column_name_list)])

首先在嘗試創(chuàng)建domain時(shí)，報(bào)了個(gè)錯(cuò)：

ORA-43929: Collation cannot be specified if parameter MAX_STRING_SIZE=STANDARD is set

需要MAX_STRING_SIZE=EXTENDED，詳情建四十七期。

• 示例1
  
  這里domain dn1是數(shù)字類型；domain dn2兩個(gè)值均為數(shù)字類型，第一個(gè)不能為空，第二個(gè)默認(rèn)為1；domain dm1包含四個(gè)值：兩個(gè)值均為數(shù)字類型，第一個(gè)不能為空，第二個(gè)不能為空切必須大于0，第三個(gè)為默認(rèn)值為abc的長度為10的varhar2類型，第四個(gè)值也為數(shù)字僅為空值時(shí)插入默認(rèn)0，同時(shí)要求前兩個(gè)值相加小于等于100，同時(shí)第三個(gè)值長度大于第二個(gè)數(shù)值。
  對(duì)應(yīng)的tm1表，c1-c4滿足domain dm1，c5-c6滿足domain dn2，c7滿足domain dn1。
  下面我們來嘗試插入數(shù)據(jù)：
  
  這里c4因?yàn)椴迦霝镹ULL所以值為10，c6獲取默認(rèn)值1。
  
  這里插入的c2為20，而c3默認(rèn)值abc長度為3，不滿足大于c2的條件，因此插入失敗。
  
  將上一條語句c3改為2即可成功插入。
  
  這里又因?yàn)閏1+c2>100，不滿足domain dm1。
  
  這里c1插入的不是數(shù)字類型（domain dm1）。
  
  這里c5不能為空（domain tm1）。
• 示例2
  
  
  創(chuàng)建表tm2，遵循domain email限制，插入空值時(shí)拼接序列t_seq.NEXTVAL和字符串‘gmail’，插入內(nèi)容必須包含@和. 且@不在首位。
  
  插入空值時(shí)，自動(dòng)填充。
  正確格式的郵箱地址能正確插入。
  
  錯(cuò)誤格式的郵箱地址就無法插入。
  
  使用domain_display還可以通過domain email對(duì)數(shù)據(jù)顯示進(jìn)行脫敏。

總結(jié)

SQL Domain還有很多功能，繼續(xù)探索中。
老規(guī)矩，知道寫了些啥。