개요
Oracle 23c에 SQL 도메인(이후 도메인) 기능이 추가되었습니다. 도메인은 데이터 타입, CHECK 제약조건, 표시 형식, 정렬 기준 등을 정의할 수 있는 오브젝트로 싱글 칼럼 도메인, 멀티 칼럼 도메인, Flexible 도메인으로 구분됩니다.
A SQL domain is a dictionary object that belongs to a schema and encapsulates a set of optional properties and constraints for common values, such as credit card numbers or email addresses. After you define a SQL domain, you can define table columns to be associated with that domain, thereby explicitly applying the domain's optional properties and constraints to those columns. With SQL domains, you can define how you intend to use data centrally. They make it easier to ensure you handle values consistently across applications and improve data quality.
싱글 칼럼 도메인
싱글 칼럼 도메인은 하나의 칼럼에 대한 도메인입니다.
테스트를 위해 아래와 같이 싱글 칼럼 도메인을 생성하겠습니다. 23.2 버전은 도메인 관련 결과 메시지가 완성되지 않은 것으로 보입니다.
-- 1
DROP DOMAIN d1;
ORA-11504: Message 11504 not found; product=RDBMS; facility=ORA
CREATE DOMAIN d1 AS NUMBER (5,2)
CONSTRAINT c1 CHECK (d1 BETWEEN 0 AND 100)
DISPLAY TO_CHAR (d1, '990.99') || '%'
ORDER d1 * -1;
조작 0 성공입니다.
도메인은 칼럼 속성이나 DOMAIN 키워드로 선언할 수 있습니다. 아래 t1 테이블은 칼럼 속성으로 도메인을 선언합니다. INSERT 문을 수행하면 도메인에 의해 세 번째 INSERT 문에서 에러가 발생합니다.
-- 2
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 NUMBER, c2 d1);
INSERT INTO t1 VALUES (1, 12.34);
INSERT INTO t1 VALUES (2, 56.78);
INSERT INTO t1 VALUES (3, 100.01); -- ORA-11534
ORA-11534: check constraint (TUNA.SYS_C######) due to domain constraint TUNA.C1 of domain TUNA.D1 violated
COMMIT;
도메인과 관련하여 DOMAIN_DISPLAY, DOMAIN_ORDER, DOMAIN_CHECK 등의 도메인 함수와 CAST 함수에 DOMAIN 키워드를 사용할 수 있습니다. DOMAIN_CHECK 함수는 23.2 버전에서 제대로 동작하지 않는 것으로 보입니다.
-- 3-1
SELECT c1, c2, DOMAIN_DISPLAY (c2) AS dd, DOMAIN_ORDER (c2) AS do
FROM t1
ORDER BY DOMAIN_ORDER (c2);
C1 C2 DD DO
-- ----- -------- ------
2 56.78 56.78% -56.78
1 12.34 12.34% -12.34
2 행이 선택되었습니다.
-- 3-2
SELECT DOMAIN_CHECK ('D1', 12.34) AS c1, DOMAIN_CHECK ('D1', 100.01) AS c2
FROM DUAL;
C1 C2
-- --
0 0
1개의 행이 선택되었습니다.
-- 3-3
SELECT CAST (100.01 AS DOMAIN d1) FROM DUAL;
ORA-11513: 도메인 제약 조건으로 인해 CAST AS DOMAIN을 실패했습니다.
멀티 칼럼 도메인
멀티 칼럼 도메인은 다수의 칼럼에 대한 도메인입니다.
테스트를 위해 아래와 같이 멀티 칼럼 도메인을 생성하겠습니다.
-- 4
DROP DOMAIN d2;
CREATE DOMAIN d2 AS (v1 AS VARCHAR2(2), v2 AS VARCHAR2(4)) CONSTRAINT c2 CHECK (v2 LIKE v1 || '%');
아래 t2 테이블은 DOMAIN 키워드로 도메인을 선언합니다. INSERT 문을 수행하면 도메인에 의해 세 번째 INSERT 문에서 에러가 발생합니다.
-- 5
DROP TABLE t2 PURGE;
CREATE TABLE t2 (c1 NUMBER, c2 VARCHAR2(2), c3 VARCHAR2(4), DOMAIN d2 (c2, c3));
INSERT INTO t2 VALUES (1, '10', '1010');
INSERT INTO t2 VALUES (2, '10', '1020');
INSERT INTO t2 VALUES (3, '10', '2010'); -- ORA-11534
COMMIT;
Flexible 도메인
Flexible 도메인은 DECODE 문이나 CASE 문의 조건에 따라 다른 SQL 도메인을 선택할 수 있는 도메인입니다.
테스트를 위해 아래와 같이 Flexible 도메인을 생성하겠습니다.
-- 6
DROP DOMAIN d4;
DROP DOMAIN d3;
CREATE DOMAIN d3 AS NUMBER NOT NULL;
CREATE FLEXIBLE DOMAIN d4 (v1, v2)
CHOOSE DOMAIN USING (c1 VARCHAR2(1))
FROM CASE c1 WHEN 'A' THEN d3 (v1) WHEN 'B' THEN d3 (v2) END;
아래와 같이 t3 테이블에 d4 도메인을 선언하고 INSERT 문을 수행하면 두 번째, 세 번째 INSERT 문에서 에러가 발생합니다.
-- 7-1
DROP TABLE t3 PURGE;
CREATE TABLE t3 (c1 NUMBER, c2 VARCHAR2(2), c3 NUMBER, c4 NUMBER, DOMAIN d4 (c3, c4) USING (c2));
INSERT INTO t3 VALUES (1, 'A', 1, NULL);
INSERT INTO t3 VALUES (2, 'A', NULL, 2); -- ORA-11534
INSERT INTO t3 VALUES (3, 'B', 3, NULL); -- ORA-11534
INSERT INTO t3 VALUES (4, 'B', NULL, 4);
INSERT INTO t3 VALUES (5, 'C', NULL, NULL);
-- 7-2
SELECT * FROM t3;
C1 C2 C3 C4
-- -- -- --
1 A 1
4 B 4
5 C
3 행이 선택되었습니다.
정보 조회
도메인은 *_OBJECTS 뷰의 object_type 칼럼 값이 DOMAIN으로 표시됩니다. *_TAB_COLUMNS 뷰에서 칼럼에 사용된 도메인을 확인할 수 있습니다.
-- 8-1
SELECT object_name
FROM user_objects
WHERE object_type = 'DOMAIN';
OBJECT_NAME
-----------
D1
D2
D3
D4
4 행이 선택되었습니다.
-- 8-2
SELECT table_name, column_name, data_type, domain_name
FROM user_tab_columns
WHERE table_name IN ('T1', 'T2', 'T3')
AND domain_name IS NOT NULL;
TABLE_NAME COLUMN_NAME DATA_TYPE DOMAIN_NAME
---------- ----------- --------- -----------
T1 C2 NUMBER D1
T2 C2 VARCHAR2 D2
T2 C3 VARCHAR2 D2
T3 C2 VARCHAR2 D3
T3 C3 NUMBER D3
T3 C4 NUMBER D3
6 행이 선택되었습니다.
*_DOAMINS 뷰에서 도메인 정보, *_DOMAIN_COLS 뷰에서 도메인을 사용한 칼럼 정보, *_DOMAIN_CONSTRAINTS 뷰에서 도메인 제약조건 정보를 조회할 수 있습니다. 참고로 도메인 제약조건은 별도의 제약조건으로 자동 생성됩니다. *_CONSTRANTS 뷰의 domain_name, domain_constraint_name 칼럼에서 제약조건과 연관된 도메인 제약 조건을 조회할 수 있습니다.
-- 9-1
SELECT name, cols, discriminantcols, builtin, data_display, data_order, selector
FROM user_domains
WHERE name IN ('D1', 'D2', 'D3', 'D4');
NAME COLS DISCRIMINANTCOLS BUILTIN DATA_DISPLAY DATA_ORDER SELECTOR
---- ---- ---------------- ------- ------------------------------ ---------- --------
D1 1 0 NO TO_CHAR (d1, '990.99') || '%' d1 * -1
D2 2 0 YES
D3 1 0 NO
D4 2 1 NO CASE ...
4 행이 선택되었습니다.
-- 9-2
SELECT domain_name, column_name, column_id, data_type, nullable, data_default, discriminant
FROM user_domain_cols
WHERE domain_name IN ('D1', 'D2', 'D3', 'D4');
DOMAIN_NAME COLUMN_NAME DATA_TYPE NULLABLE DATA_DEFAULT DISCRIMINANT
----------- ----------- --------- -------- ------------ ------------
D1 D1 NUMBER Y NO
D2 V1 VARCHAR2 Y NO
D2 V2 VARCHAR2 Y NO
D3 D3 NUMBER N NO
D4 V1 NUMBER N NO
D4 V2 NUMBER N NO
D4 C1 VARCHAR2 Y YES
7 행이 선택되었습니다.
-- 9-3
SELECT name, domain_name, search_condition, validated
FROM user_domain_constraints
WHERE domain_name IN ('D1', 'D2', 'D3', 'D4');
NAME DOMAIN_NAME SEARCH_CONDITION VALIDATED
---------------- ----------- ------------------------------------------- -------------
C1 D1 d1 BETWEEN 0 AND 100 VALIDATED
C2 D2 v2 LIKE v1 || '%' VALIDATED
SYS_DOMAIN_C#### D3 "D3" IS NOT NULL VALIDATED
SYS_DOMAIN_C#### D4 ("C1"='A' OR "C1"<>'B' OR "V2" IS NOT NULL) NOT VALIDATED
SYS_DOMAIN_C#### D4 ("C1"<>'A' OR "V1" IS NOT NULL) NOT VALIDATED
5 행이 선택되었습니다.
-- 9-4
SELECT constraint_name, table_name, search_condition_vc, domain_name, domain_constraint_name
FROM user_constraints
WHERE domain_constraint_name IS NOT NULL;
CONSTRAINT_NAME TABLE_NAME SEARCH_CONDITION_VC DOMAIN_NAME DOMAIN_CONSTRAINT_NAME
--------------- ---------- ------------------------------------------- ----------- ----------------------
SYS_C###### T1 "C2">=0 AND "C2"<=100 D1 C1
SYS_C###### T2 "C3" LIKE "C2"||'%' D2 C2
SYS_C###### T3 ("C2"<>'A' OR "C3" IS NOT NULL) D4 SYS_DOMAIN_C####
SYS_C###### T3 ("C2"='A' OR "C2"<>'B' OR "C4" IS NOT NULL) D4 SYS_DOMAIN_C####
5 행이 선택되었습니다.
속성 변경
커서 무효화를 테스트하기 위해 아래 쿼리를 먼저 수행하겠습니다.
-- 10
SELECT /* TEST */ * FROM t1;
SELECT loads, invalidations, executions FROM v$sql WHERE sql_text LIKE 'SELECT /* TEST */ * FROM t1';
LOADS INVALIDATIONS EXECUTIONS
----- ------------- ----------
1 0 1
1개의 행이 선택되었습니다.
도메인의 표시 형식과 정렬 기준은 추가, 변경, 삭제가 가능하지만 데이터 타입과 제약 조건은 변경할 수 없습니다. 아울러 사용중인 도메인은 삭제할 수 없습니다.
-- 11-1
ALTER DOMAIN d1 MODIFY DISPLAY TO_CHAR (d1, 'FM990.99') || '%';
-- 11-2
ALTER DOMAIN d1 DROP ORDER;
-- 11-3
DROP DOMAIN d1;
ORA-11502: Message 11502 not found; product=RDBMS; facility=ORA
테스트 쿼리를 다시 수행하면 커서가 무효화되지 않은 것을 확인할 수 있습니다.
-- 12-1
SELECT /* TEST */ * FROM t1;
-- 12-2
SELECT loads, invalidations, executions FROM v$sql WHERE sql_text LIKE 'SELECT /* TEST */ * FROM t1';
LOADS INVALIDATIONS EXECUTIONS
----- ------------- ----------
1 0 2
1개의 행이 선택되었습니다.
도메인의 데이터 타입과 제약 조건을 변경을 변경하려면 테이블의 칼럼 속성에서 도메인을 삭제하고 도메인을 다시 생성한 후 테이블의 칼럼 속성에 생성한 도메인을 추가해야 합니다.
-- 13-1
ALTER TABLE t1 MODIFY (c2) DROP DOMAIN;
-- 13-2
DROP DOMAIN d1;
CREATE DOMAIN d1 AS NUMBER (5,2) CONSTRAINT c1 CHECK (d1 BETWEEN 1 AND 100);
-- 13-3
ALTER TABLE t1 MODIFY (c2) ADD DOMAIN d1;
테스트 쿼리를 다시 수행하면 커서가 무효화된 것을 확인할 수 있습니다. 데이터 타입 또는 제약조건이 변경된 경우 정상적인 동작으로 볼 수 있습니다.
-- 14-1
SELECT /* TEST */ * FROM t1;
-- 14-2
SELECT loads, invalidations, executions FROM v$sql WHERE sql_text LIKE 'SELECT /* TEST */ * FROM t1';
LOADS INVALIDATIONS EXECUTIONS
----- ------------- ----------
2 1 1
1개의 행이 선택되었습니다.