Domains 使用
此章节为译文。
原文链接：ORACLE-BASE - Domains in Oracle Database 23c
什么是域
域是定义数据格式的一种方式，使我们在整个应用程序中保持一致。我们定义一次与列关联的属性和约束，并在整个应用程序中使用该定义。
域被描述为“application usage domains”。只是强调域为了帮助开发应用程序。在早期的一些资源中，域被描述为SQL域，因为它们是SQL标准的一部分。
环境准备
我们连接到一个特权用户并创建一个新的测试用户。请注意，我们将CREATE DOMAIN授予测试用户。然后，我们连接到此测试用户来运行示例。
conn sys/SysPassword1@//localhost:1521/zspdb as sysdba
drop user if exists zs cascade;
create user zs identified by 123456 quota unlimited on users;
grant connect, resource to zs;
grant create domain to zs;
conn zs/123456@//localhost:1521/zspdb
域类型
域有三种基本的类型：
- 单列域
- 多列域
- 灵活域
域是由几个特定的表达式和条件组成：
- 简单域表达式 可以是string, number, sequence.CURRVAL, sequence.NEXTVAL, NULL, or schema.domain
- 日期时域表达式 这是一个普通的日期时间表达式，但它只引用其他域表达式
- 区间域表达式 这是一个正常的区间表达式，但它只引用其他域表达式
- 复合域表达式 这只是一个更复杂的域表达式，通常包含额外的操作
根据域的类型，语法可能相当复杂，因此我们不打算在本文中全面介绍。您可以在CREATE DOMAIN文档中获得完整的详细信息。
创建域
大多数重点将放在单列域上，但也有多列和弹性域的例子。
单列域
假设我们希望在整个应用程序中使电子邮件地址的定义保持一致。我们创建了一个名为EMAIL_DOM的单列域，它指定了域的数据类型，并添加了一个检查约束，以确保内容遵循基本的电子邮件模式。
drop domain if exists email_dom;
create domain email_dom as varchar2(100)
constraint email_chk check (regexp_like (email_dom, '^(\S+)\@(\S+)\.(\S+)$'));
我们可以添加DISPLAY和ORDER引用，以解释我们希望数据在默认情况下如何显示和排序，还可以添加注释。
drop domain if exists email_dom;
create domain email_dom as varchar2(100)
constraint email_chk check (regexp_like (email_dom, '^(\S+)\@(\S+)\.(\S+)$'))
display lower(email_dom)
order lower(email_dom)
annotations (Description 'Domain for Emails');
如果我们使用扩展数据类型（max_string_size=extended），我们可以为不区分大小写的搜索添加排序规则。
drop domain if exists email_dom;
create domain email_dom as varchar2(100)
constraint email_chk check (regexp_like (email_dom, '^(\S+)\@(\S+)\.(\S+)$'))
collate binary_ci;
下面示例将使用此域。
drop domain if exists email_dom;
create domain email_dom as varchar2(100)
constraint email_chk check (regexp_like (email_dom, '^(\S+)\@(\S+)\.(\S+)$'))
display lower(email_dom)
order lower(email_dom)
annotations (Description 'Domain for Emails');
现在，我们可以通过以下几种方式之一在表列定义中引用该域。
-- Domain reference.
drop table if exists t1 purge;
create table t1 (
id number,
email email_dom
);
-- Domain reference with domain keyword.
drop table if exists t1 purge;
create table t1 (
id number,
email domain email_dom
);
-- Data type and domain reference.
drop table if exists t1 purge;
# 执行提示语法错误
create table t1 (
id number,
email varchar2(100) email_dom
);
-- Data type and domain reference with domain keyword.
drop table if exists t1 purge;
create table t1 (
id number,
email varchar2(100) domain email_dom
);
当我们查看表时，我们会看到数据类型定义以及引用的域。
SQL> desc t1;
Name Null? Type
----------------------------------------- -------- ---------------------------------
ID NUMBER
EMAIL VARCHAR2(100) TESTUSER1.EMAIL_DOM
SQL>
我们可以将数据插入到列中，前提是它不违反关联的检查约束。
-- Insert correctly.
insert into t1 values (1, 'Banana@fruit.com');
insert into t1 values (2, 'apple@fruit.com');
-- Doesn't pass check constraint.
insert into t1 values (3, 'banana');
*
ERROR at line 1:
ORA-11534: check constraint (TESTUSER1.SYS_C0012510) due to domain constraint TESTUSER1.EMAIL_CHK of
domain TESTUSER1.EMAIL_DOM violated
SQL>
多列域
我们创建一个多列域来表示地址
drop domain if exists address_dom;
create domain address_dom as (
address_line_1 as varchar2(50),
address_line_2 as varchar2(50),
city as varchar2(50),
country_code as varchar2(5),
postcode as varchar2(10)
)
constraint address_chk check (address_line_1 is not null and
city is not null and
country_code is not null and
postcode is not null)
display address_line_1||','||address_line_2||','||city||','||country_code||','||postcode;
我们创建了一个名为addresses的表，并引用这些列来将它们与域相关联。在这种情况下，我们不能使用域直接定义列数据类型和精度，因此我们必须包含列规范。
drop table if exists addresses purge;
create table addresses (
id number,
address_line_1 varchar2(50),
address_line_2 varchar2(50),
city varchar2(50),
country_code varchar2(5),
postcode varchar2(10),
domain address_dom(address_line_1, address_line_2, city, country_code, postcode)
);
我们查看这个表，可以看到域与列相关联。
SQL> desc addresses
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
ADDRESS_LINE_1 VARCHAR2(100) TESTUSER1.ADDRESS_DOM
ADDRESS_LINE_2 VARCHAR2(50) TESTUSER1.ADDRESS_DOM
CITY VARCHAR2(50) TESTUSER1.ADDRESS_DOM
COUNTRY_CODE VARCHAR2(5) TESTUSER1.ADDRESS_DOM
POSTCODE VARCHAR2(10) TESTUSER1.ADDRESS_DOM
SQL>
清理创建的临时表
drop table if exists addresses purge;
drop domain if exists address_dom;
灵活域
灵活域允许我们根据数据的上下文应用几个域中的一个。我们创建了三个地址域，分别代表英国、美国和“其他”地址。英国地址需要6到8个字符的邮政编码。美国地址需要5或9个字符的邮政编码/邮政编码。默认地址不检查邮政编码的长度。这些长度检查被添加到域中的相关检查约束中。
-- UK address.
create domain address_uk_dom as (
address_line_1 as varchar2(50),
address_line_2 as varchar2(50),
city as varchar2(50),
postcode as varchar2(10)
)
constraint address_uk_chk check (address_line_1 is not null and
city is not null and
postcode is not null and
length(postcode) between 6 and 8);
-- US address.
create domain address_us_dom as (
address_line_1 as varchar2(50),
address_line_2 as varchar2(50),
city as varchar2(50),
postcode as varchar2(10)
)
constraint address_us_chk check (address_line_1 is not null and
city is not null and
postcode is not null and
(length(postcode) = 5 or length(postcode) = 9));
-- Default address.
create domain address_dom as (
address_line_1 as varchar2(50),
address_line_2 as varchar2(50),
city as varchar2(50),
postcode as varchar2(10)
)
constraint address_chk check (address_line_1 is not null and
city is not null and
postcode is not null);
我们创建了一个灵活的域，它根据行的上下文应用这些域。我们必须指定一个列作为决策的基础。这就是判断列。然后，我们可以使用DECODE或CASE表达式来决定应用哪个域。在这个例子中，我们使用了一个CASE表达式。
create flexible domain address_flex_dom (address_line_1, address_line_2, city, postcode)
choose domain using (country_code varchar2(5))
from case
when country_code in ('GB','GBR') then address_uk_dom(address_line_1, address_line_2, city, postcode)
when country_code in ('US','USA') then address_us_dom(address_line_1, address_line_2, city, postcode)
else address_dom(address_line_1, address_line_2, city, postcode)
end;
我们使用灵活域重新创建地址表。我们用USING关键字指定判断列。
create table addresses (
id number,
address_line_1 varchar2(50),
address_line_2 varchar2(50),
city varchar2(50),
country_code varchar2(5),
postcode varchar2(10),
domain address_flex_dom(address_line_1, address_line_2, city, postcode) using (country_code)
);
现在可以通过插入一些数据来测试灵活的域。以下行插入正确，因为它们都遵守与其国家/地区代码关联的域。
insert into addresses (id, address_line_1, address_line_2, city, country_code, postcode)
values (1, '1 my street', null, 'birmingham', 'GB', 'A12 BCD');
1 row created.
SQL>
insert into addresses (id, address_line_1, address_line_2, city, country_code, postcode)
values (2, '2 my street', null, 'boston', 'US', '12345');
1 row created.
SQL>
insert into addresses (id, address_line_1, address_line_2, city, country_code, postcode)
values (3, '3 my street', null, 'dublin', 'IRE', '1234567890');
1 row created.
SQL>
在这些插入中，我们试图在英国地址中使用美国邮政编码，在美国地址中使用英国邮政编码。正如预期的那样，两者都会失败，但请注意，对于不同的域和检查约束，它们都会失败。
insert into addresses (id, address_line_1, address_line_2, city, country_code, postcode)
values (4, '4 my street', null, 'birmingham', 'GB', '12345');
*
ERROR at line 1:
ORA-11534: check constraint (TESTUSER1.SYS_C0012512) due to domain constraint
TESTUSER1.SYS_DOMAIN_C0063 of domain TESTUSER1.ADDRESS_FLEX_DOM violated
SQL>
insert into addresses (id, address_line_1, address_line_2, city, country_code, postcode)
values (5, '5 my street', null, 'boston', 'US', 'A12 BCD');
*
ERROR at line 1:
ORA-11534: check constraint (TESTUSER1.SYS_C0012513) due to domain constraint
TESTUSER1.SYS_DOMAIN_C0062 of domain TESTUSER1.ADDRESS_FLEX_DOM violated
SQL>
清理对象
drop table if exists addresses purge;
drop domain if exists address_flex_dom;
drop domain if exists address_dom;
drop domain if exists address_uk_dom;
drop domain if exists address_us_dom;
域的相关函数
一些域函数可以让我们利用域功能。如果我们查询数据，我们可以看到它是以正常方式显示和排序的。显示了大小写的差异，ASCII排序将“B”置于“a”之前
select email from t1 order by email;
EMAIL
--------------------------------------------------------------------------------
Banana@fruit.com
apple@fruit.com
SQL>
DOMAIN_DISPLAY函数允许我们使用DISPLAY特性。数据现在以小写显示，如域中所定义，但请注意，如果我们按名称引用列，这不会影响排序。如果我们在ORDERBY子句中使用列位置或别名，则会出现这种情况。
select domain_display(email) from t1 order by email;
DOMAIN_DISPLAY(EMAIL)
--------------------------------------------------------------------------------
banana@fruit.com
apple@fruit.com
SQL>
DOMAIN_ORDER函数允许我们使用ORDER特性。请注意，数据是以混合大小写表示的，但排序是基于小写比较。
select email from t1 order by domain_order(email);
EMAIL
--------------------------------------------------------------------------------
apple@fruit.com
Banana@fruit.com
SQL>
DOMAIN_NAME函数允许我们返回与列关联的域。
select domain_name(email) from t1 where rownum = 1;
DOMAIN_NAME(EMAIL)
----------------------------------------
TESTUSER1.EMAIL_DOM
SQL>
域可以用作CAST操作的一部分。
select cast('grape@fruit.com' as domain email_dom);
CAST('GRAPE@FRUIT.COM'ASDOMAINEMAIL_DOM)
----------------------------------------------------------------------------------------------------
grape@fruit.com
SQL>
DOMAIN_CHECK函数允许我们根据域检查约束检查某些数据。它似乎不适用于将字符串作为参数，因此我们需要将数据强制转换为适合检查的类型.
-- Using strings.
select domain_check('email_dom', 'grape@fruit.com');
DOMAIN_CHECK('EMAIL_DOM','GRAPE@FRUIT.COM')
-------------------------------------------
0
SQL>
select domain_check('email_dom', 'grape');
DOMAIN_CHECK('EMAIL_DOM','GRAPE')
---------------------------------
0
SQL>
-- Using cast.
select domain_check('email_dom', cast('grape@fruit.com' as varchar2(100)));
DOMAIN_CHECK('EMAIL_DOM',CAST('GRAPE@FRUIT.COM'ASVARCHAR2(100)))
----------------------------------------------------------------
1
SQL>
select domain_check('email_dom', cast('grape' as varchar2(100)));
DOMAIN_CHECK('EMAIL_DOM',CAST('GRAPE'ASVARCHAR2(100)))
------------------------------------------------------
0
SQL>
修改域
在编写本文时，不能更改域的数据类型定义和检查约束。对于那些用作表列的域来说，这可能是一个问题，因为不能对列大小的进行扩展。
域的显示、顺序和注释属性可以更改。
-- Display
alter domain email_dom
add display lower(email_dom);
alter domain email_dom
modify display upper(email_dom);
alter domain email_dom
drop display;
-- Order
alter domain email_dom
add order lower(email_dom);
alter domain email_dom
modify order upper(email_dom);
alter domain email_dom
drop order;
-- Annotation
alter domain email_dom
annotations (Usage 'Use this for email columns.');
alter domain email_dom
annotations (drop Usage);
删除域
我们的域有依赖项，因此尝试删除它会导致错误
drop domain email_dom;
*
ERROR at line 1:
ORA-11502: The domain to be dropped has dependent objects.
SQL>
我们可以使用FORCE关键字将域与列分离，并删除域，但我们失去了对依赖列的约束。我们检查表上的约束条件。
column constraint_name format a20
column search_condition format a50
select constraint_name, constraint_type, search_condition
from user_constraints
where table_name = 'T1';
CONSTRAINT_NAME C SEARCH_CONDITION
-------------------- - --------------------------------------------------
SYS_C0012349 C REGEXP_LIKE ("EMAIL",'^(\S+)\@(\S+)\.(\S+)$')
SQL>
我们使用FORCE选项删除域，可以看到约束已经消失，并且在描述表时不再显示域。
drop domain email_dom force;
Domain dropped.
SQL>
select constraint_name, constraint_type, search_condition
from user_constraints
where table_name = 'T1';
no rows selected
SQL>
desc t1
Name Null? Type
----------------------------------------- -------- ----------------------------
ID NUMBER
EMAIL VARCHAR2(100)
SQL>
FORCE PRESERVE选项在删除域时保留相关表列的默认值和约束。
-- Recreate the test domain and table.
drop table t1 purge;
create domain email_dom as varchar2(100)
constraint email_chk check (regexp_like (email_dom, '^(\S+)\@(\S+)\.(\S+)$'));
create table t1 (
id number,
email domain email_dom
);
-- Check the constraints.
select constraint_name, constraint_type, search_condition
from user_constraints
where table_name = 'T1';
CONSTRAINT_NAME C SEARCH_CONDITION
-------------------- - --------------------------------------------------
SYS_C0012351 C REGEXP_LIKE ("EMAIL",'^(\S+)\@(\S+)\.(\S+)$')
SQL>
-- Drop the domain with FORCE PRESERVE option.
drop domain email_dom force preserve;
-- Check the constraints.
select constraint_name, constraint_type, search_condition
from user_constraints
where table_name = 'T1';
CONSTRAINT_NAME C SEARCH_CONDITION
-------------------- - --------------------------------------------------
SYS_C0012351 C REGEXP_LIKE ("EMAIL",'^(\S+)\@(\S+)\.(\S+)$')
SQL>
域的演变
我们的列定义可能会随着应用程序的使用而变化。让我们看看如何管理域的演变。
首先，我们重新创建域和表来表示我们的起点。
drop table if exists t1 purge;
drop domain if exists email_dom;
create domain email_dom as varchar2(100)
constraint email_chk check (regexp_like (email_dom, '^(\S+)\@(\S+)\.(\S+)$'));
create table t1 (
id number,
email domain email_dom
);
假设我们的需求发生了变化，我们需要将字符串长度扩展到200。我们使用USER_TAB_COLUMNS视图来列出与域关联的表列。
column table_name format a30
column column_name format a30
select table_name, column_name
from user_tab_columns
where domain_name = 'EMAIL_DOM';
TABLE_NAME COLUMN_NAME
------------------------------ ------------------------------
T1 EMAIL
SQL>
我们使用FORCE PRESERVE选项删除域。
drop domain email_dom force preserve;
我们重新创建域，将列大小扩展到200。
create domain email_dom as varchar2(200)
constraint email_chk check (regexp_like (email_dom, '^(\S+)\@(\S+)\.(\S+)$'));
如果我们试图重新应用该域，希望这能扩展该列，我们将被阻止。
alter table t1 modify email domain email_dom;
*
ERROR at line 1:
ORA-11517: the column data type does not match the domain column
SQL>
要重新关联域，我们必须使基列定义与域匹配，然后应用域。我们需要对最初引用域的任何列重复此操作。
alter table t1 modify email varchar2(200);
alter table t1 modify email domain email_dom;
desc t1
Name Null? Type
----------------------------------------- -------- ---------------------------------
ID NUMBER
EMAIL VARCHAR2(200) TESTUSER1.EMAIL_DOM
SQL>
域的相关视图
我们看到域和表都列在USER_OBJECTS视图中。
column object_name format A20
column object_type format A20
select object_name, object_type
from user_objects
order by 1;
OBJECT_NAME OBJECT_TYPE
-------------------- --------------------
EMAIL_DOM DOMAIN
T1 TABLE
SQL>
DOMAIN_OWNER和DOMAIN_NAME列已添加到USER_TAB_COLUMNS视图中。
column column_name format A20
column domain_owner format A20
column domain_name format A20
select column_id,
column_name,
domain_owner,
domain_name
from user_tab_columns
where table_name = 'T1'
order by 1;
COLUMN_ID COLUMN_NAME DOMAIN_OWNER DOMAIN_NAME
---------- -------------------- -------------------- --------------------
1 ID
2 EMAIL TESTUSER1 EMAIL_DOM
SQL>
USER_DOMAINS、USER_DOMAIN_COLS和USER_DOMAIN _CONSTRAINTS视图提供有关域定义的信息。
SQL> desc user_domains
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
OWNER NOT NULL VARCHAR2(128)
NAME NOT NULL VARCHAR2(128)
COLS NOT NULL NUMBER
DISCRIMINANTCOLS NUMBER
FLAGS NUMBER
BUILTIN VARCHAR2(3)
DATA_DISPLAY CLOB
DISPLAY_LENGTH NUMBER
DATA_ORDER CLOB
ORDER_LENGTH NUMBER
SELECTOR CLOB
SELECTOR_LENGTH NUMBER
SQL>
SQL> desc user_domain_cols;
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
OWNER NOT NULL VARCHAR2(128)
DOMAIN_NAME NOT NULL VARCHAR2(128)
COLUMN_NAME NOT NULL VARCHAR2(128)
COLUMN_ID NOT NULL NUMBER
DATA_TYPE_ID NOT NULL NUMBER
DATA_TYPE VARCHAR2(106)
DATA_LENGTH NUMBER
DATA_PRECISION NUMBER
DATA_SCALE NUMBER
NULLABLE VARCHAR2(1)
DATA_DEFAULT CLOB
DEFAULT_LENGTH NUMBER
DEFAULT_ON_NULL VARCHAR2(3)
DEFAULT_ON_NULL_UPD VARCHAR2(3)
CHARACTER_SET_NAME VARCHAR2(44)
CHAR_COL_DECL_LENGTH NUMBER
COLLATION VARCHAR2(100)
EXACT VARCHAR2(3)
CHAR_LENGTH NUMBER
DISCRIMINANT VARCHAR2(3)
SQL>
SQL> desc user_domain_constraints
Name Null? Type
----------------------------------------------------- -------- ------------------------------------
NAME NOT NULL VARCHAR2(128)
DOMAIN_OWNER NOT NULL VARCHAR2(128)
DOMAIN_NAME NOT NULL VARCHAR2(128)
CONSTRAINT_TYPE VARCHAR2(1)
SEARCH_CONDITION CLOB
STATUS VARCHAR2(8)
DEFERRABLE VARCHAR2(14)
DEFERRED VARCHAR2(9)
VALIDATED VARCHAR2(13)
GENERATED VARCHAR2(14)
BAD VARCHAR2(3)
RELY VARCHAR2(4)
INVALID VARCHAR2(7)
ORIGIN_CON_ID NUMBER
SQL>
PL/SQL支持
在编写本文时，似乎没有任何对PL/SQL中构建的域的支持。
重新创建域和测试表。
drop table t1 purge;
drop domain email_dom;
create domain email_dom as varchar2(100)
constraint email_chk check (regexp_like (email_dom, '^(\S+)\@(\S+)\.(\S+)$'));
create table t1 (
id number,
email domain email_dom
);
我们不能直接基于域定义变量。
declare
l_email domain email_dom;
begin
null;
end;
/
l_email domain email_dom;
*
ERROR at line 2:
ORA-06550: line 2, column 18:
PLS-00103: Encountered the symbol "EMAIL_DOM" when expecting one of the following:
:= . ( @ % ; not null range default character
The symbol ":=" was substituted for "EMAIL_DOM" to continue.
SQL>
我们根据列类型声明了一个变量，但我们可以看到在变量赋值过程中没有检查约束。
declare
l_email t1.email%type;
begin
l_email := 'banana@fruit.com';
l_email := 'banana';
end;
/
PL/SQL procedure successfully completed.
SQL>
也许这个功能将包含在以后的版本中。
域的严谨性
在前面的例子中，我们没有在域定义中包含STRICT关键字。因此，表列可能具有与域定义不同的列精度。
在本例中，我们创建了具有三列不同大小的表。我们尝试将域应用于每一列。如果列的大小大于或等于域定义，则域关联起作用。如果该列小于域定义，则该域无法应用于它。
drop table if exists t1 purge;
drop domain if exists email_dom;
create domain email_dom as varchar2(100)
constraint email_chk check (regexp_like (email_dom, '^(\S+)\@(\S+)\.(\S+)$'));
create table t1 (
col1 varchar2(50),
col2 varchar2(100),
col3 varchar2(200)
);
alter table t1 modify col1 domain email_dom;
*
ERROR at line 1:
ORA-11517: the column data type does not match the domain column
SQL>
alter table t1 modify col2 domain email_dom;
Table altered.
SQL>
alter table t1 modify col3 domain email_dom;
Table altered.
SQL>
这一次我们重复前面的例子，但要严格定义列。现在，域只能与具有相同精度的列相关联。
drop table if exists t1 purge;
drop domain if exists email_dom;
create domain email_dom as varchar2(100) strict
constraint email_chk check (regexp_like (email_dom, '^(\S+)\@(\S+)\.(\S+)$'));
create table t1 (
col1 varchar2(50),
col2 varchar2(100),
col3 varchar2(200)
);
alter table t1 modify col1 domain email_dom;
*
ERROR at line 1:
ORA-11517: the column data type does not match the domain column
SQL>
alter table t1 modify col2 domain email_dom;
Table altered.
SQL>
alter table t1 modify col3 domain email_dom;
*
ERROR at line 1:
ORA-11517: the column data type does not match the domain column
SQL>
域的一些思考
以下是关于此功能的一些想法。
-
随着模式的发展，缺乏轻松更改表列定义的能力是一个令人担忧的问题。
-
缺乏PL/SQL支持意味着域实际上不是一个定义点。
-
感觉像是未完成的功能。
-
有很多不同的功能，但我并不认为自己会使用其中的大部分。也许随着功能的成熟，我的观点会随着时间的推移而改变，它可以从PL/SQL中获得。
-
文档需要更多真实的用例，完整的实现显示域的演变，而不仅仅是一个独立的域创建。
最后修改时间：2023-08-25 14:07:13
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
