Oracle 23ai New Feature: Domains

Die Anwendung von Domains soll am Beispiel der Spalte EMPLOYEES.COMMISSION_PCT (Provision) im Schema HR gezeigt werden. Diese Spalte hat den Datentyp NUMBER(2,2) und keine Prüfregeln.

desc employees

Name           Null?    Typ          
-------------- -------- ------------ 
EMPLOYEE_ID    NOT NULL NUMBER(6)    
FIRST_NAME              VARCHAR2(20) 
LAST_NAME      NOT NULL VARCHAR2(25) 
...
COMMISSION_PCT          NUMBER(2,2)  
...

Als Voraussetzung für die Definition von Domains muss das Systemprivileg CREATE DOMAIN erteilt worden sein.

Im Beispiel wird eine Domain mit folgenden Regeln erstellt:
- Wertebereich zwischen 0.01 und 0.5
- formatierte Anzeige
- Sortierung

Diese Domain wird einer Spalte zugewiesen.

-- falls die Domain COMMISSION_D bereits vorhanden ist

drop domain commission_d force;

create domain commission_d as number(2,2)
              constraint commission_valid check ( commission_d is null or commission_d between 0.01 and 0.5 )
              display 'Provision ' || to_char( nvl( commission_d, 0), '0D99')
              order nvl( commission_d, 0);

-- Domain COMMISSION_D erstellt.

alter table employees modify ( commission_pct domain commission_d);

-- Table EMPLOYEES geändert.

Der Name der Domain wird an Stelle des späteren Spaltennamens als Platzhalter verwendet.

Die Check Regel verhindert die Eingabe fehlerhafter Daten.

update employees 
set    commission_pct = 0.7
where  employee_id = 165;

-- ORA-11534: Check Constraint (HR.SYS_C009148) aufgrund von Domain-Constraint HR.COMMISSION_VALID von Domain HR.COMMISSION_D verletzt

Im nächsten Schritt sind die formatierte Ausgabe (domain_display) und Sortierung (domain_order) zu sehen.

select   last_name, domain_display( commission_pct)
from     employees
order by domain_order( commission_pct) desc;

LAST_NAME DOMAIN_DISPLAY 
--------- --------------- 
Singh     Provision  0,40
McEwen    Provision  0,35
Sully     Provision  0,35
King      Provision  0,35

Die Eigenschaften der Domains können in verschiedenen Data Dictionary Views analysiert werden.

select  *
from    user_domains
where   name = 'COMMISSION_D';

-- Prüfregeln

select  *
from    user_domain_constraints
where   domain_name = 'COMMISSION_D';

-- Spalten

select  *
from    user_domain_cols
where   domain_name = 'COMMISSION_D';

-- und es gibt auch einige built-in Domains ...

select  *
from    all_domains
where   builtin = 1;

Domains können auch geändert werden, allerdings nur die Eigenschaften Display und Order. Die Eigenschaft Constraint in Domains ist hingegen nicht änderbar. Soll also in unserem Fall die Check Clause geändert werden, so muss die Domain gelöscht (mit Force), dann neu angelegt und den betreffenden Spalten neu zugewiesen werden.

alter domain commission_d modify display to_char( commission_d, '0D99');

-- geänderte Anzeige testen

select  last_name,
        domain_display( commission_pct)
from    employees
order by
        domain_order( commission_pct) desc;

LAST_NAME DOMAIN_DISPLAY 
--------- --------------- 
Singh      0,40
McEwen     0,35
Sully      0,35
King       0,35