CREATE DOMAIN
目的
CREATE DOMAINを使用して、データ・ユース・ケース・ドメインを作成します。ユース・ケース・ドメインは、スキーマに属している高レベルのディクショナリ・オブジェクトであり、オプションのプロパティと制約のセットをカプセル化します。
ドメインに関連付ける表の列を定義できるため、ドメインのオプションのプロパティと制約を明示的に列に適用できます。
少なくとも、ドメインでは組込みのOracleデータ型を指定する必要があります。修飾ドメイン名は、修飾ユーザー定義データ型またはOracle組込みデータ型と競合しないようにしてください。
ドメインのデータ型は、単一のOracleデータ型である必要があります。Oracle文字データ型の場合は、最大長と、VARCHAR2(L [CHAR|BYTE])、NVARCHAR2(L)、CHAR(L [CHAR|BYTE])またはNCHAR(L)のいずれかを指定する必要があります。
ドメイン固有の式および条件の定義
ドメイン式には、単純、日時、間隔、CASE、複合またはリスト・ドメイン式のいずれかを指定できます。
-
単純ドメイン式は、
string、number、sequence.CURRVAL、sequence.NEXTVAL、NULLまたはschema.domainのいずれかになります。単純な式と同様ですが、列名ではなくドメイン名を参照する点が異なります。ドメイン名を修飾名、Oracle組込みドメインの名前として参照するか、ドメインに対してPUBLICシノニムを使用します。 -
日時ドメイン式は、ドメイン式のみを参照する日時式です。
-
間隔ドメイン式は、ドメイン式を参照する点を除いて、通常の間隔式として定義されます。たとえば、
(SYSTIMESTAMP - day_of_week) DAY(9) TO SECONDは、間隔ドメイン式です。 -
複合ドメイン式は、
(expr)、expr op expr(opは+, -, *, /, ||,)またはexpr COLLATE collation_nameのいずれかです(exprはドメイン式)。有効な複合ドメイン式の例
'email: ' || EmailAddress
day_of_week + INTERVAL '1' DAY
TO_CHAR(LastFour(SSN))
-
caseドメイン式は、ドメイン式のみを参照することを除いて、正規表現と同様のものです。
有効なcaseドメイン式の例
CASE WHEN UPPER(DOMAIN_DISPLAY(day_of_week)) IN ('SAT','SUN') THEN 'weekend' ELSE 'week day' END -
ユース・ケース・ドメイン式の定義と同様に、ドメイン条件は通常のSQL条件と同様に、ドメイン式のみを参照します。ドメイン式では、ドメイン名のかわりにキーワード
VALUEを使用できます。次に例を示します。CREATE DOMAIN day_of_week AS CHAR(3 CHAR) CONSTRAINT day_of_week_c CHECK (UPPER(VALUE) IN ('MON', 'TUE', 'WED', 'THU', 'FRI', 'SAT', 'SUN')) DEFERRABLE INITIALLY DEFERRED DISPLAY SUBSTR(VALUE, 1, 2);
前提条件
自分のスキーマ内にドメインを作成するには、CREATE DOMAINシステム権限が必要です。
別のユーザーのスキーマにドメインを作成するには、CREATE ANY DOMAINシステム権限が必要です。
構文
create_domain::=
create_single_column_domain::=
(datatype::=、enum_list::=、column_properties_clause::=、annotations_clause)
enum_list::=
enum_item_list::=
enum_alias_list::=
column_properties_clause::=
create_multi_column_domain::=
(datatype::=、column_properties_clause::=、annotations_clause)
result_expr::=
default_clause::=
constraint_clause::=
annotations_clause::=
annotations_clause の完全な構文およびセマンティクスについては、「annotations_clause」を参照してください。
セマンティクス
USECASE
このキーワードは省略可能であり、セマンティクスを明確にするために使用されます。ドメインがデータのユース・ケースを記述することを示します。
IF NOT EXISTS
IF NOT EXISTSを指定すると、次の効果が得られます。
-
ドメインが存在していない場合は、文の最後に新しいドメインが作成されます。
-
ドメインが存在する場合、古いドメインが検出されるため、新しいドメインは作成されません。
CREATEとともにIF EXISTSを使用すると、Incorrect IF NOT EXISTS clause for CREATE statementというが発生します。
domain_name
domain_nameは、すべての型名と同じ制限に従います。また、ドメイン・スキーマ内のオブジェクトの名前、Oracle提供のデータ型の名前およびOracle提供のドメインの名前と競合しないようにする必要があります。
このような制限は、CDB環境内のPDBレベルで適用されます。
ドメインはスキーマ・レベルのカタログ・オブジェクトであるため、スキーマ・レベルのオブジェクト制限の対象になる点に注意してください。
datatype
datatypeは、次に示すようなOracle組込みデータ型であることが必要です。
-
CHAR(L [CHAR|BYTE]), NCHAR(L), VARCHAR(L [CHAR|BYTE]), VARCHAR2(L [CHAR|BYTE]), NVARCHAR2(L) -
NUMBER[p, [s]]、FLOAT、BINARY_FLOAT、BINARY_DOUBLE -
RAW、LONG RAW(拡張を含む) -
DATE、TIMESTAMP (WITH (LOCAL) TIME ZONE)、INTERVAL -
BFILE、BLOB、CLOB、NCLOB -
JSONネイティブ・データ型 -
BOOLEAN
ENUM
列挙ドメインを作成するには、ENUMを指定します。列挙ドメインは、一連の名前と、オプションで名前に対応する値で構成されます。名前は有効なSQL識別子である必要があり、指定される値はすべてリテラルまたは定数式である必要があります。それらの値は、データ・ユースケース・ドメインでサポートされている任意のデータ型にできますが、それらすべてがそのデータ型に一致している必要があります。
列挙ドメインには次のデフォルトがあります:
-
表示式: 各値に関連付けられた名を返します。引用符で囲まれていない名前は大文字で返され、引用符で囲まれている名前は、定義されているのと同じ大/小文字の区別で返されます。
-
順序式: 列挙内の値でソートします。
-
チェック制約: 入力が列挙内の値リストにあることを検証します。このチェック制約は、
ALTER DOMAINを使用して削除できません
列挙ドメイン内の名前は、スカラーSQL式でリテラルが許可される任意の場所で使用でき、ドメイン自体は、表であるかのようにSELECT文のFROM句で使用できます。
列挙ドメイン内の名前のコレクション(name)は一意である必要があります。Oracle SQLにすでに存在する制限に加えて、列挙内の名前(またはその別名)の数に制限はありません。
すべての値(value)のデータ型が一致している必要があります。値を指定しない場合、名のデフォルト値は1で、その他すべての未指定の名前の値は以前の値より1つ大きい値になります。
デフォルトを定義する式は、それ以外の式を含まない列挙名の1つである必要があります。
STRICT
STRICTを指定する場合、ドメインにリンクされた表の列のデータ型制限は、対応するドメイン列と同じである必要があります。たとえば、ドメイン列のデータ型がNUMBER(10)の場合、NUMBER(10)として宣言された列にのみ関連付けることができます。NUMBER(9)またはNUMBER(11)の列にドメインを適用すると、型のエラーが発生します。
STRICTを省略すると、ドメインの制限以上の型の制限を持つ列にドメインをリンクできます。たとえば、データ型がNUMBER(10)の厳密でないドメインをデータ型がNUMBER(20)の列に適用できます。
列のデータ型を指定せずに列をドメインに関連付けると、STRICTセマンティクスが使用されます。
default_clause
ON NULL句を指定すると、暗黙的なNOT NULL制約が追加されます。
default_expression
default_expressionは、ドメイン式である必要があり、ドメイン式への適用時は、指定されたデータ型のデフォルト列式に関する制限すべてに従っている必要があります。
-
default_expressionには、ドメイン参照を返すSQLファンクションやネストされたファンクション起動を含めることができません。また、副問合せ式にすることもできません。 -
default_expressionのデータ型は、ドメインの、指定されているデータ型と一致する必要があります。 -
ドメイン式としての
default_expressionは、表または列を参照できません。別のドメイン名は参照できません。 -
default_expressionは、シーケンスのNEXTVALおよびCURRVALを参照できます。PL/SQLファンクションは参照できません。
constraint_clause
ドメイン制約には、オプションの名前を指定できます。これらは、NOT NULL制約、NULL制約またはCHECK制約です。このような制約句は、列レベルとドメイン・レベルどちらでも複数指定できます。
ALTER DOMAINのCHECK条件と式では、ドメイン列のみを参照できます。ドメインに単一の列がある場合、列名はドメイン名またはキーワードVALUEのどちらかになりますが、同じ式に列名としてドメイン名とVALUEの両方を含めることはできません。
constraint_nameはオプションです。指定する場合は、ドメインのスキーマ内(CDB環境の場合は指定のPDB内)の別の制約の名前と競合しないようにしてください。指定しない場合は、システム生成の名前が使用されます。ドメイン制約は、表および列レベルの制約と同じルールに従います。名前付きの表または列レベルの制約は、同じスキーマ内の別の制約の名前と一致させることはできません。ドメイン制約は、その名前を同じスキーマ内であっても表と共有できます。名前を列と共有でき、制約が定義されている表または列と同じ名前を持つことが可能です。
CHECK条件は、ドメイン論理条件であることが必要で、ドメイン式に変換されるCHECK制約に対するすべての制限に準拠する必要があります。
-
列に対する
CHECK制約は列のみを参照できるように、domain_nameのみを参照できます。ドメイン・スキーマ内であっても、表またはビューの列を参照できません。 -
副問合せ式またはスカラー問合せ式は使用できません。
-
条件では非決定ファンクション(
CURRENT_DATEなど)やユーザー定義PL/SQLファンクションを参照できません。 -
CHECK IS JSON (STRICT)制約は使用できます。CHECK IS JSON(VALIDATE USING schema_constant_text)を使用できます。JSONスキーマを指定し、これを使用して、JSON列が指定されたスキーマ定義に従うことを検証できます。schema_constant_textには、JSONスキーマ・テキストを使用した定数リテラル、またはJSONスキーマ・テキストのバインド変数を指定できます。バインドはランタイム定数である必要があります。ドメインにすることはできません。VALIDATE USING schema_constraintを指定せずにIS JSON制約を使用すると、すべてのJSON値が受け入れられます。ただし、VALIDATE USING schema_constraintを使用してJSONスキーマを指定し、表の列に入力された入力データがスキーマに従っていない場合は、JSONスキーマ検証エラーが発生します。短縮構文を使用して、
VALIDATE USING schema_constant_textでJSONスキーマを指定できます。表レベルおよび列レベルの制約と同様に、特定の表の列に対して指定できるJSON制約は1つのみです
-
CHECK制約条件は一度に1つの値に適用され、domain_nameに値を代入したCHECK条件がTRUEまたはUNKNOWNと評価された場合に、その制約条件が満たされます。
ドメイン制約は、任意の順序で適用できます。
NULL制約は、ドメインの値にNULLを許容することを意味します。これはデフォルトです。
constraint_stateが指定されていない場合、その制約はNOT DEFERABLE INITIALLY IMMEDIATEになります。
COLLATE
照合を指定すると、列レベルまたはスキーマ・レベルの照合のすべての制限に準拠するようになります。このデータ型は、照合が指定されている場合は文字データ型である必要があります。
必ず、照合が指定されているドメインのすべての列に、そのドメインと同じ照合がある必要があります。
照合が指定されておらず、そのデータ型が照合可能である場合は、列の照合が指定されていればそれが使用されます。それ以外の場合は、ドメインのスキーマ内の、基にあるデフォルト・データ型照合が使用されます。
次の場合は、エラーが発生します:
-
列の照合とは異なる照合があるドメイン内にその列を含む表を作成した場合。
-
列を、その列のドメインの照合とは異なる照合に変更した場合。
-
ドメインを変更して、そのドメインの照合を、指定したドメインのマーク付けされた列の照合とは異なる値で追加または変更した場合。
COLLATE句を指定できるのは、COMPATIBLE初期化パラメータが12.2以上に設定され、かつMAX_STRING_SIZE初期化パラメータがEXTENDEDに設定されている場合のみです。
display_expression
display_expressionは、ドメインの仕様に従ってデータを書式設定する場合に使用します。ドメイン・データ型として許容されている任意のデータ型にできます。display_expressionは、表またはビューの列、副問合せ、非決定ファンクションまたはPL/SQLファンクションが含まれていないドメイン式であることが必要です。domain_nameを参照できます。式の照合を指定しない場合、display_expressionは、ドメインの照合を使用します(指定されている場合)。
order_expression
order_expressionは、ドメイン仕様の値を順序付けして比較する場合に使用します。
order_expressionは、display_expressionsと同じ制限に従う必要があり、さらにバイトまたは文字に相当するデータ型であることも必要です。式のドメインにorder_expressionが指定されている場合は、domain_nameを式に置き換えたorder_expressionを返し、それ以外の場合は式を返します
annotations_clause
annotations_clauseの例については、末尾の例を参照してください。
注釈句のセマンティクスの詳細は、「annotations_clause」を参照してください。
CREATE FLEXIBLE DOMAINのFROM句
exprおよびcomparison_exprは、リストdomain_discriminant_column内のドメインの判別式の列を参照します。
フレキシブル・ドメインのFROM句はDECODEまたはCASE式であり、検索式では判別式の列名(CHOOSE DOMAIN USINGに続くリスト内)のみを参照し、結果式にはドメイン名とその後に続く列のリストのみが含まれます。結果式に含まれる列は、ドメイン列リスト(CREATE FLEXIBLE DOMAINに続くもの)の列のみである必要があります。
例
ドメインyear_of_birthの作成
次の例では、単一列ドメインyear_of_birthを作成します。CHECK制約によって、列の値が1900以上の整数であることを確認します。display句は、domain_displayへのコールの出力を19-YYまたは20-YYに書式設定します。ここで、YYは値の下2桁です。order句は、domain_orderへのコールを列値から1900を引いた値順にソートします。
CREATE DOMAIN year_of_birth AS NUMBER(4)
CONSTRAINT CHECK ( (trunc(year_of_birth) = year_of_birth) and (year_of_birth >= 1900) )
DISPLAY (CASE WHEN year_of_birth < 2000 THEN '19-' ELSE '20-' END) || MOD(year_of_birth, 100)
ORDER year_of_birth-1900 ;
ドメインday_of_weekの作成
次の文は、単一列ドメインday_of_weekを作成します。CHECK制約によって、その値がMON、TUE、WED、THU、FRI、SAT、SUNのいずれかであることが確認されます。最初に遅延した句は、コミット時までこれらの値の検証を遅延します。order句を使用すると、domain_orderをコールするときに、値がアルファベット順ではなく曜日でソートされます。
CREATE DOMAIN day_of_week AS CHAR(3 CHAR)
CONSTRAINT day_of_week_c
CHECK (day_of_week IN ('MON', 'TUE', 'WED', 'THU', 'FRI', 'SAT', 'SUN'))
INITIALLY DEFERRED
ORDER CASE day_of_week
WHEN 'MON' THEN 0
WHEN 'TUE' THEN 1
WHEN 'WED' THEN 2
WHEN 'THU' THEN 3
WHEN 'FRI' THEN 4
WHEN 'SAT' THEN 5
WHEN 'SUN' THEN 6
ELSE 7
END;23.3以降、データ型CHAR(L CHAR)の列を、Lの値を持つドメインに関連付けることができます。
ドメインemailの作成
次の例では、順序email_seqを作成します。次に、単一列ドメインemailを作成します。これにより、このドメインを持つ列にnullを挿入すると、順序を使用してnnn@domain.comという形式で電子メール・アドレスが生成されます。nnnは、順序によって生成される番号です。制約により、電子メール・アドレスの形式はsss@sss.sssとなり、sssは空白以外の文字になります。
display句は、domain_displayへのコールの出力を---sss.sssに書式設定します。sssは、@記号の後の空白以外の文字です。
CREATE SEQUENCE IF NOT EXISTS email_seq;
CREATE DOMAIN email AS VARCHAR2(30)
DEFAULT ON NULL email_seq.NEXTVAL || '@domain.com'
CONSTRAINT EMAIL_C CHECK (REGEXP_LIKE (email, '^(\S+)\@(\S+)\.(\S+)$'))
DISPLAY '---' || SUBSTR(email, INSTR(email, '@') + 1);
厳密なドメインdept_codesの作成
次の文は、ドメインdept_codesを作成します。CHECK制約によって、その値が200を除いた99より大きい値になります。値が"Domain Annotation"の注釈Titleを追加します。このドメインは、NUMBER(3)型の列でのみリンクできます。
CREATE DOMAIN dept_codes AS NUMBER(3) STRICT
CONSTRAINT dept_chk CHECK (dept_codes > 99 AND dept_codes != 200)
ANNOTATIONS (Title 'Domain Annotation');ドメインhourly_wagesの作成
次の文は、単一列ドメインhourly_wagesを作成します。このドメインを持つ列にnullを挿入する場合、デフォルトで15に設定されます。CHECK制約によって、値が7から1,000の間であることが確認されます。
display句は、domain_displayをコールするときに$999.99の形式で値を戻します。order句の値に負の1を乗算するため、domain_orderでソートすると高から低の順にソートされます。これには値が"Domain Annotation"の注釈Titleが含まれています。
CREATE DOMAIN hourly_wages AS NUMBER(10)
DEFAULT ON NULL 15
CONSTRAINT minimal_wage_c
CHECK (hourly_wages >= 7 and hourly_wages <=1000) ENABLE
DISPLAY TO_CHAR(hourly_wages, '$999.99')
ORDER ( -1*hourly_wages )
ANNOTATIONS (Title 'Domain Annotation');
列およびドメイン・レベルでの複数列ドメインUS_Cityへの注釈の追加
次の文は、複数列ドメインUS_cityを作成します。これには、name、stateおよびzipの3つの列があります。すべての列にAddress注釈があります。
CHECK制約により、州に対して許可される値がCA、AZおよびTXで、zipが100000より小さいことが確認されます。display句は、||', '|| state ||', '||TO_CHAR(zip)という書式名でdomain_displayへのコールを戻します。
order句は、domain_orderへのコールを、州、郵便番号、名前を連結してソートします。ドメインには、値が"Domain Annotation"のオブジェクト・レベルの注釈Titleと、値なしの3つの列レベルの注釈Addressが列ごとに1つずつあります。
CREATE DOMAIN US_city AS
(
name AS VARCHAR2(30) ANNOTATIONS (Address),
state AS VARCHAR2(2) ANNOTATIONS (Address),
zip AS NUMBER ANNOTATIONS (Address)
)
CONSTRAINT City_CK CHECK(state in ('CA','AZ','TX') and zip < 100000)
DISPLAY name||', '|| state ||', '||TO_CHAR(zip)
ORDER state||', '||TO_CHAR(zip)||', '||name
ANNOTATIONS (Title 'Domain Annotation');
フレキシブル・ドメインの作成
次の例では、フレキシブル・ドメインexpense_detailsを作成します。これを実行するには、まずドメインflight_details、meals_detailsおよびlodging_detailsを作成する必要があります。これらはCHECK制約がある複数列ドメインであり、ドメイン列に経費タイプの適切な値が格納されるようにします。
flight_detailsの場合、これはflight_numがダッシュで区切られた2つの文字列であり、出発地と目的地の両方が3つの文字列であることを意味します。
meals_detailsの場合、レストランは必須で、meal_typeはBreakfast、LunchまたはDinnerのいずれかであり、diner_countはnullではありません。
lodging_detailsの場合、hotelはnull以外で、nights_countはゼロより大きい必要があります。
フレキシブル・ドメインexpense_detailsは、typ列の値に基づいてこれらを選択します。
FROM DECODEの例の場合:
-
typ=Flightの場合、flight_detailsドメインが使用されます。フレキシブル・ドメイン列val1、val2およびval3は、flight_detailsドメインのflight_num、originおよびdestinationにマップされます。 -
typ=Mealsの場合、meals_detailsドメインが使用されます。フレキシブル・ドメイン列val1、val2およびval4は、meals_detailsドメインのrestaurant、meal_typeおよびdiner_countにそれぞれマップされます。 -
typ=Lodgingの場合、lodging_detailsドメインが使用されます。フレキシブル・ドメイン列val1およびval4は、それぞれlodging_detailsドメインのhotelおよびnights_countにマップされます。
FROM CASEフレキシブル・ドメインの場合:
-
typが文字A-Gで始まる場合、flight_detailsドメインが使用されます。 -
typ=Mealsの場合、meals_detailsドメインが使用されます。 -
typが
Lodgで始まる場合、lodging_detailsドメインが使用されます。
列マッピングは、FROM DECODEの例と同じです。
ドメインflight_detailsの作成
CREATE DOMAIN flight_details AS
(
flight_num AS VARCHAR2(100) NOT NULL,
origin AS VARCHAR2(200)
CONSTRAINT origin_3_char_c CHECK (LENGTH(origin) = 3),
destination AS VARCHAR2(200)
CONSTRAINT dest_3_char_c CHECK (LENGTH(destination) = 3)
)
CONSTRAINT flight_c
CHECK
(
flight_num LIKE '%-%' AND
origin IS NOT NULL AND
destination IS NOT NULL
)
CONSTRAINT origin_dest_different_c
CHECK (origin <> destination)
DISPLAY flight_num||', '||origin||', '||destination
ORDER flight_num||destination;ドメインmeals_detailsの作成
CREATE DOMAIN meals_details AS
(
restaurant AS VARCHAR2(100) NOT NULL,
meal_type AS VARCHAR2(200),
diner_count AS NUMBER
)
CONSTRAINT meals_c
CHECK
(
restaurant IS NOT NULL AND
meal_type IN ('Breakfast', 'Lunch', 'Dinner') AND
diner_count IS NOT NULL
)
DISPLAY meal_type||', '||restaurant||', '||diner_count;ドメインlodging_detailsの作成
CREATE DOMAIN lodging_details AS
(
hotel AS VARCHAR2(100) NOT NULL,
nights_count AS NUMBER
)
CONSTRAINT lodging_c
CHECK (hotel IS NOT NULL AND nights_count > 0)
DISPLAY hotel||', '||nights_count;FROM DECODEを使用したフレキシブル・ドメインexpense_detailsの作成
CREATE FLEXIBLE DOMAIN expense_details (val1, val2, val3, val4)
CHOOSE DOMAIN USING (typ VARCHAR2(10))
FROM DECODE(typ,
'Flight', flight_details(val1, val2, val3),
'Meals', meals_details(val1, val2, val4),
'Lodging', lodging_details(val1, val4));FROM CASEを使用したフレキシブル・ドメインexpense_detailsの作成
CREATE FLEXIBLE DOMAIN expense_details (val1, val2, val3, val4)
CHOOSE DOMAIN USING(typ VARCHAR2(10))
FROM CASE
WHEN typ BETWEEN 'A' AND 'G' THEN flight_details(val1, val2, val3)
WHEN typ = 'Meals' THEN meals_details(val1, val2, val4)
WHEN typ LIKE 'Lodg%' THEN lodging_details(val1, val4)
END;検証用のJSONスキーマを指定するドメインの作成
次の例では、値が、指定されたJSONスキーマに準拠するJSONオブジェクトであることをチェックする制約を使用して、タイプJSONのドメインw2_formを作成します。CHECK制約では、IS JSON VALIDATE USING schema_constant_textを使用します:
CREATE DOMAIN w2_form AS JSON
CONSTRAINT CHECK (VALUE IS JSON VALIDATE USING '{
"title": "W2_form",
"type": "object",
"properties": {
"social_security_number": {
"type": "string",
"description": "The person social security number."
},
"wages": {
"description": "total wages",
"type": "number",
"minimum": 0
},
"social_security_wages": {
"type": "number",
"description": "wages subject to social security tax"
},
"Federal Income Tax Withheld": {
"type": "number",
"description": "withheld of tax to federal income tax"
},
"Social Security Tax Withheld": {
"type": "number",
"description": "withheld of social security tax"
}
},
"required": [
"social_security_number",
"wages",
"Federal Income Tax Withheld"
]
}'
);
次の文では、そのコンテンツがドメインw2_formで定義されたスキーマに準拠するように、列w2_formがドメインw2_formに関連付けられている表tax_reportを作成します:
CREATE TABLE tax_report(id NUMBER, income JSON DOMAIN w2_form);
データがincome列に挿入される前に、JSONスキーマに対してチェックされます。データが有効でない場合、エラーが発生します。
有効なデータの例:
INSERT INTO tax_report VALUES
(1, '{"wages": 100, "social_security_number": "111", "Federal Income Tax Withheld":10}'
);
1 row created無効なデータの例:
INSERT INTO tax_report VALUES
(2, '{"wages": 100}'
);
ERROR at line 1:
ORA-40875: JSON schema validation error短縮構文を使用したJSONスキーマを指定するドメインの作成
CREATE DOMAIN w2_form AS JSON VALIDATE USING '{
"title": "W2_form",
"type": "object",
"properties": {
"social_security_number": {
"type": "string",
"description": "The person social security number."
},
"wages": {
"description": "total wages",
"type": "number",
"minimum": 0
},
"social_security_wages": {
"type": "number",
"description": "wages subject to social security tax"
},
"Federal Income Tax Withheld": {
"type": "number",
"description": "withheld of tax to federal income tax"
},
"Social Security Tax Withheld": {
"type": "number",
"description": "withheld of social security tax"
}
},
"required": [
"social_security_number",
"wages",
"Federal Income Tax Withheld"
]
}';例: JSONとして格納された注釈を使用してドメインを作成し、その値を問い合せる
次の例では、ネストされたJSONオブジェクトを含むJSON文字列として指定された注釈allowed_operationsを使用してドメインを作成します:
CREATE DOMAIN email AS VARCHAR2(30)
CONSTRAINT EMAIL_C CHECK (REGEXP_LIKE (email, '^(\S+)\@(\S+)\.(\S+)$'))
DISPLAY '---' || SUBSTR(email, INSTR(email, '@') + 1)
ANNOTATIONS(allowed_operations
'{
"allowed_operations": {
"title": "Allowed operations",
"operations": [
"Sort",
"Group By",
"Picklist"
]
}
}');
これで、次の問合せを実行して、注釈allowed_operationsの値を取得できます:
SELECT jt.* FROM user_annotations_usage a,
JSON_TABLE (annotation_value,
'$.allowed_operations.operations[*]'
COLUMNS (value VARCHAR2(50 CHAR) PATH '$')) jt
WHERE annotation_name = 'ALLOWED_OPERATIONS'
AND object_name = 'EMAIL' ;
出力は次のとおりです:
VALUE ---------------------------------------- Sort Group By Picklist
例: JSONとして格納された注釈を使用してドメインを作成し、その値を問い合せる
次の例では、使用可能な表示単位を格納するために配列を含むJSON文字列として指定された注釈display_unitsを使用してドメインを作成します:
CREATE DOMAIN temperature AS NUMBER(3)
ANNOTATIONS (display_units '{ "units": ["celsius", "fahrenheit"] }');
これで、注釈の値を問い合せることができます:
SELECT jt.* FROM user_annotations_usage,
JSON_TABLE(annotation_value, '$.units[*]'
COLUMNS (value VARCHAR2(30 CHAR) PATH '$')) jt
WHERE annotation_name = 'DISPLAY_UNITS'
AND object_name = 'TEMPERATURE';出力は次のとおりです:
VALUE -------------------------------------------- celsius fahrenheit
例: ユースケース・ドメインを使用するJSONスキーマ
JSONスキーマをユース・ケース・ドメインとして登録できます。
次の例では、ドメインdj5をJSONスキーマ・オブジェクトとして作成します:
CREATE DOMAIN dj5 AS JSON CONSTRAINT dj5chk
CHECK (dj5 IS JSON validate
'{
"type": "object",
"properties": {
"a": {
"type": "number"
}
}
}'
);
その後、表jtabを作成し、列jcolをドメインdj5に関連付けることができます:
CREATE TABLE jtab(
id NUMBER PRIMARY KEY,
jcol JSON DOMAIN dj5
);
ENUMドメインの例
例: ENUMドメインorder_statusの作成
次の例では、New、Open、Shipped、Closed、Cancelledという名前のコレクションを持つ列挙ドメインorder_statusを作成します。
CREATE DOMAIN order_status AS
ENUM (
New ,
Open ,
Shipped ,
Closed ,
Cancelled
);
例: 問合せ列挙ドメインorder_status
通常のドメインとは異なり、列挙ドメインorder_statusは、次のように表として扱われ、SELECTを介して問い合せることができます。
SELECT * FROM order_status;
結果は次のとおりです。
ENUM_NAME ENUM_VALUE - - - - - - - - - - - NEW 1 OPEN 2 SHIPPED 3 CLOSED 4 CANCELLED 5
例: 列のデータ型としての列挙ドメインorder_status:
通常の単一列ドメインと同様に、列挙ドメインを使用して、表内の列のデータ型を定義できます。次の例では、列挙ドメインorder_statusが、表ordersの列statusのデータ型として使用されています。
CREATE TABLE orders (
id NUMBER,
cust VARCHAR2(100),
status ORDER_STATUS
);orders表でDESCRIBEを使用すると、単一の列ドメインorder_statusを持つ数値列としてstatus列が表示されます。
DESCRIBE orders;
結果は次のとおりです。
Name Null ? Type ---- ------ ---- ID NUMBER CUST VARCHAR2 (100) STATUS NUMBER SCOTT.ORDER_STATUS
各行を作成して、適切なorder_statusを使用してorders表に挿入できます。
INSERT INTO orders VALUES (1, 'Costco', order_status.open ), (2, 'BMW', order_status.closed ), (3, 'Nestle', order_status.shipped ); 3 rows created .
domain_display関数を使用して、orders表の行をリストします。
SELECT ID, DOMAIN_DISPLAY(STATUS) FROM orders;
結果は次のとおりです。
ID STATUS --- ------ 1 OPEN 2 CLOSED 3 SHIPPED
ステータス列に格納される実際の値は、列挙ドメインorder_statusの作成時にステータスに関連付けた値、OPENの場合は2、CLOSEDの場合は4、SHIPPEDの場合は3です。
SELECT ID, STATUS FROM orders;
結果は次のとおりです。
ID STATUS --- ------ 1 2 2 4 3 3
ステータス列の、基にあるデータ型は数値であるため、ドメインcheck-constraintに合格していれば、その列を数値で直接更新できます:
UPDATE orders SET STATUS = 2 WHERE STATUS = 5; 1 ROW UPDATED.
列挙名はリテラル値のプレースホルダであるため、SQLでリテラルが許可されている任意の場所で使用できます。
SELECT 2 * ORDER_STATUS.CANCELLED;
結果は次のとおりです。
2*ORDER_STATUS.CANCELLED
–-----------------------
10例: ENUMドメインdays_of_weekの作成
次の例では、曜日で構成される名前のコレクションを含む列挙ドメインdays_of_weekを作成します。
各値には名前のペアがあり、そのペアの最初の名前のみに、割り当てられた値があります。各値には2つの名前(完全な曜日名と、その曜日の名前の先頭2文字)があります。名前SundayとSuは両方とも値がゼロです。その後、値は、名前のペアごとに1増加します。したがって、MondayとMoの値は1、TuesdayとTuの値は2となり、値が6のSaturdayとSaまで続きます。
CREATE DOMAIN days_of_week AS
ENUM (
Sunday = Su = 0,
Monday = Mo,
Tuesday = Tu,
Wednesday = We,
Thursday = Th,
Friday = Fr,
Saturday = Sa
);