数据库基础体系 · 第 105/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

Oracle Schema 与数据类型：NUMBER、字符、日期、LOB 和对象

---

一、先建立整体模型：Schema 到底是什么

在 Oracle 中，Schema 是由某个数据库用户拥有的数据库对象集合。最常见的规则是：

CREATE USER app_user IDENTIFIED BY "StrongPassword_1";

执行后，通常会同时存在一个名为 APP_USER 的 schema。用户和 schema 在名称上对应，但概念并不相同：

• 用户（User）：用于认证、授权和会话连接。
• Schema：该用户拥有的表、索引、视图、序列、同义词、存储过程、函数、包、触发器、类型等对象的逻辑集合。
• 表空间（Tablespace）：数据库文件之上的逻辑存储空间，不等于 schema。
• 数据库（Database）：包含控制文件、数据文件、联机日志等持久化结构。
• 实例（Instance）：内存结构和后台进程，负责访问数据库文件。

因此，下面两个对象的全限定名称不同：

APP_USER.CUSTOMERS
REPORT_USER.CUSTOMERS

它们都叫 CUSTOMERS，但属于不同 schema。

1. Schema 不是物理目录

Oracle 不会把一个 schema 简单地映射成操作系统中的一个文件夹。一个 schema 中的表可能分布在多个表空间中；一个表空间也可以存放多个 schema 的对象。

访问对象时，Oracle 通常先按当前 schema 解析未限定名称：

SELECT * FROM customers;

这近似于：

SELECT * FROM current_schema.customers;

但 CURRENT_SCHEMA 不一定等于登录用户。可以通过：

ALTER SESSION SET CURRENT_SCHEMA = APP_USER;

改变名称解析的默认 schema。这个操作不会切换身份，也不会获得 APP_USER 的权限。它只改变未限定对象名的解析起点。

更安全、可读性更高的方式是使用全限定名称：

SELECT * FROM app_user.customers;

2. Schema 对象与对象类型不是一回事

Oracle 文档中的“对象”有两层常见含义：

1. Schema object：属于 schema 的数据库对象，例如表、索引、视图、序列、过程、函数、包、触发器和类型。
2. Object type / 对象类型：Oracle 的用户定义复合数据类型，类似于带属性和方法的数据库类型。

本文后面讲的“对象”主要指第二种，但必须先区分这两个概念。一个对象类型本身也是 schema object；而使用该类型保存的值，则是对象类型实例。

---

二、数据类型为什么决定了数据的含义

列的数据类型不是存储格式的装饰，而是数据库对值执行以下操作的基础：

• 是否允许小数、负数、时间区间或字符编码；
• 比较、排序和算术运算如何进行；
• 是否允许 NULL；
• 值如何转换；
• 需要多少存储空间；
• 索引、约束和函数能否直接使用；
• 客户端驱动如何读取和绑定。

例如，下面三列表示的含义不同：

amount       NUMBER(12,2),
account_code VARCHAR2(20 CHAR),
created_at   TIMESTAMP WITH TIME ZONE

• amount 是有精度和小数位约束的数值；
• account_code 是最多 20 个字符的字符串；
• created_at 是带时区语义的时间点。

如果把金额存成字符，排序会变成字典序；如果把带时区的事件时间存成 DATE，原始时区信息就无法表达。

---

三、NUMBER：Oracle 的精确数值类型

1. NUMBER 的基本模型

Oracle NUMBER 用于存储整数和十进制数。它可以声明为：

NUMBER
NUMBER(p)
NUMBER(p, s)

其中：

• p 是 precision，精度，表示有效十进制数字的总数；
• s 是 scale，小数位尺度；
• p 的范围通常为 1 到 38；
• s 的允许范围通常为 -84 到 127；
• 不指定约束时，NUMBER 可表示的范围比固定 NUMBER(p,s) 更宽。

NUMBER(p,s) 的有效位数包括整数部分和小数部分，但不包括小数点，也不把整数部分最左侧的无意义零计入有效数字。

例如：

NUMBER(7,2)

表示：

• 总精度最多 7 位；
• 小数部分最多 2 位；
• 因而整数部分通常最多 5 位。

典型可接受范围近似为：

-99999.99 到 99999.99

但边界值还要考虑 Oracle 对输入值的舍入。

2. 插入时先舍入，再检查精度

下面的示例展示 NUMBER(7,2) 的行为：

CREATE TABLE number_demo (
    id       NUMBER PRIMARY KEY,
    amount   NUMBER(7,2),
    rounded  NUMBER(5,-1)
);

INSERT INTO number_demo VALUES (1, 123.456, 126);
INSERT INTO number_demo VALUES (2, 99999.99, 124);
COMMIT;

SELECT id, amount, rounded
FROM number_demo
ORDER BY id;

预期结果近似为：

ID  AMOUNT    ROUNDED
--  --------  -------
1   123.46    130
2   99999.99  120

这里发生了两个不同的规则：

NUMBER(7,2)

输入 123.456：

1. 小数位要求为 2；
2. 123.456 按小数位舍入为 123.46；
3. 123.46 共有 5 个有效数字，小于精度 7；
4. 插入成功。

NUMBER(5,-1)

负 scale 表示在小数点左侧进行舍入。-1 表示舍入到十位：

126 -> 130
124 -> 120

负 scale 适合表达“以十为粒度保存”的值，但它不是显示格式，而是列约束的一部分。

3. 精度溢出的反例

INSERT INTO number_demo (id, amount)
VALUES (3, 999999.99);

这个值即使保留两位小数，也需要 8 个有效数字，而 amount 的精度只有 7，因此会报数值精度或表示范围相关错误，通常为 ORA-01438。

再看一个容易误解的边界：

CREATE TABLE number_rounding_demo (
    value1 NUMBER(5,2),
    value2 NUMBER(5,0)
);

INSERT INTO number_rounding_demo VALUES (999.995, 999.5);

对于 NUMBER(5,2)，Oracle 会先按照小数位进行舍入；舍入后如果整数部分导致总有效位数超过精度，仍然会失败。“会舍入”不等于“任何超范围输入都能插入”。

4. NUMBER 与整数、浮点数的选择

Oracle 没有把所有数字都当作同一种语义：

• NUMBER：十进制精确数值，适合金额、数量、比例和业务编号；
• BINARY_FLOAT：单精度二进制浮点数；
• BINARY_DOUBLE：双精度二进制浮点数。

二进制浮点数不能精确表示所有十进制小数。例如，十进制的 0.1 在二进制浮点格式中通常是近似值。这与 IEEE 浮点计算一致，适合科学计算或必须使用二进制浮点的接口，但不宜直接用于财务金额。

CREATE TABLE numeric_types_demo (
    decimal_value NUMBER(20,10),
    binary_value  BINARY_DOUBLE
);

INSERT INTO numeric_types_demo
VALUES (0.1, 0.1);

SELECT decimal_value, binary_value
FROM numeric_types_demo;

客户端显示出来的结果受驱动和格式化规则影响，但两者的核心区别是：

• NUMBER 采用十进制数值语义；
• BINARY_DOUBLE 采用二进制浮点语义。

5. NUMBER 的显示不是存储值本身

以下查询中的格式化结果受 NLS_NUMERIC_CHARACTERS 和格式模型影响：

SELECT TO_CHAR(12345.67, 'FM999G999D00') AS formatted
FROM dual;

G 和 D 分别代表本地化的千位分隔符和小数分隔符。数据库中存储的是数值，不应通过字符串格式判断数值是否相等。

错误示例：

WHERE TO_CHAR(amount) = '100.00'

这会依赖会话的 NLS 设置。更可靠的是：

WHERE amount = 100

或在确实需要格式化输出时显式指定格式和 NLS 参数。

---

四、字符类型：CHAR、VARCHAR2、NCHAR 和 NVARCHAR2

1. CHAR 与 VARCHAR2

Oracle 常用的两种数据库字符类型是：

CHAR(n [BYTE | CHAR])
VARCHAR2(n [BYTE | CHAR])

区别是：

• CHAR 是定长字符类型；
• VARCHAR2 是变长字符类型；
• CHAR 的值不足声明长度时会以空格补齐；
• VARCHAR2 不会按声明长度补齐。

示例：

CREATE TABLE character_demo (
    fixed_code   CHAR(5 CHAR),
    variable_code VARCHAR2(5 CHAR)
);

INSERT INTO character_demo
VALUES ('A', 'A');

SELECT
    '[' || fixed_code || ']' AS fixed_text,
    LENGTH(fixed_code)       AS fixed_length,
    '[' || variable_code || ']' AS variable_text,
    LENGTH(variable_code)       AS variable_length
FROM character_demo;

CHAR(5 CHAR) 的值在语义上会补足到 5 个字符；VARCHAR2(5 CHAR) 的值长度仍然是 1 个字符。

CHAR 适合真正固定长度的编码，例如固定格式的国家代码或协议字段。对普通姓名、标题、备注等变长文本，VARCHAR2 更符合数据含义。

2. BYTE 与 CHAR 长度语义

Oracle 可以按字节或字符解释长度：

VARCHAR2(20 BYTE)
VARCHAR2(20 CHAR)

• BYTE：最多 20 个字节；
• CHAR：最多 20 个字符。

在 UTF-8 等多字节字符集下，一个字符可能占用多个字节。因此：

VARCHAR2(20 BYTE)

不一定能保存 20 个中文字符，而：

VARCHAR2(20 CHAR)

表达的是字符数量限制。

数据库参数和字符集配置会影响默认长度语义。生产表结构不应依赖不明确的默认值，尤其是在多语言系统中，应根据业务约束显式选择 BYTE 或 CHAR。

3. VARCHAR2 的长度上限

在 SQL 中，普通配置下 VARCHAR2 的最大长度通常为 4000 字节或字符语义下相应的限制。启用 MAX_STRING_SIZE=EXTENDED 后，Oracle 可以支持更大的 SQL 字符类型上限，常见上限是 32767 字节。

这里有三个边界必须分开：

1. 数据库初始化参数 MAX_STRING_SIZE；
2. SQL 中列的最大长度；
3. PL/SQL 变量的字符串容量。

PL/SQL 的 VARCHAR2 变量可以支持到 32767 字节，但这并不意味着普通 SQL 表列都自动具有同样容量。

如果业务文本可能超过普通字符串列的可靠上限，应使用 CLOB，而不是把列长度不断扩大。

4. Oracle 对空字符串的特殊语义

在 Oracle SQL 中，长度为零的字符值会被当作 NULL 处理：

CREATE TABLE empty_string_demo (
    v VARCHAR2(10),
    c CHAR(10)
);

INSERT INTO empty_string_demo (v, c)
VALUES ('', '');

SELECT
    CASE WHEN v IS NULL THEN 'V_IS_NULL' ELSE 'V_NOT_NULL' END AS v_state,
    CASE WHEN c IS NULL THEN 'C_IS_NULL' ELSE 'C_NOT_NULL' END AS c_state
FROM empty_string_demo;

预期为：

V_IS_NULL  C_IS_NULL

这意味着 Oracle 中不能像某些数据库那样稳定地区分：

空字符串 ''
NULL

因此判断空值必须使用：

WHERE column_name IS NULL

而不是：

WHERE column_name = ''

NULL 也不等于空格。可以保存一个或多个空格的字符值，但空字符串会转化为 NULL。

5. 字符比较与尾部空格

CHAR 的定长语义会影响比较。Oracle 在字符比较中通常会考虑定长字符的空格填充，因此下面这种判断可能成立：

SELECT CASE
         WHEN CAST('A' AS CHAR(5)) = CAST('A    ' AS CHAR(5))
         THEN 'EQUAL'
         ELSE 'NOT_EQUAL'
       END AS result
FROM dual;

但是，把 CHAR 和 VARCHAR2 混合比较、使用不同函数以及通过客户端取值时，尾部空格的表现可能不同。不要把 CHAR 当作“自动去空格的字符串”。

如果业务要求忽略两端空格，应明确写出：

WHERE TRIM(code) = :input_code

但要注意，对列使用函数可能影响普通索引的使用，必要时应设计函数索引或在写入时规范化。

6. NCHAR 与 NVARCHAR2

NCHAR 和 NVARCHAR2 使用国家字符集：

NCHAR(n)
NVARCHAR2(n)

它们适合需要依赖国家字符集语义的场景。Oracle 数据库还具有数据库字符集，普通 CHAR、VARCHAR2 和 CLOB 使用数据库字符集；国家字符类型使用国家字符集。

选择时应首先确认：

• 数据库字符集是否已经能覆盖业务语言；
• 客户端驱动是否正确设置字符集；
• 是否确实需要国家字符集，而不是仅仅因为文本包含中文。

字符集问题不能只靠列类型解决。数据库、客户端、连接驱动、导入导出工具都必须使用一致的编码转换路径。

---

五、日期与时间：DATE、TIMESTAMP 和时区

1. DATE 不是只有日期

Oracle DATE 同时保存：

年、月、日、时、分、秒

但不保存小数秒，也不保存时区。

CREATE TABLE date_demo (
    event_date DATE
);

INSERT INTO date_demo
VALUES (TO_DATE('2025-03-08 14:30:45', 'YYYY-MM-DD HH24:MI:SS'));

SELECT
    TO_CHAR(event_date, 'YYYY-MM-DD HH24:MI:SS') AS event_text
FROM date_demo;

输出：

2025-03-08 14:30:45

不要依赖字符串隐式转换：

INSERT INTO date_demo VALUES ('2025-03-08');

这条语句的成功与否以及解释方式取决于会话的 NLS_DATE_FORMAT。应使用 ANSI 日期字面量：

INSERT INTO date_demo VALUES (DATE '2025-03-08');

ANSI DATE 字面量只直接表达日期部分，时间默认为午夜：

2025-03-08 00:00:00

需要时间时，使用 TO_DATE 并提供格式模型。

2. TIMESTAMP 保存小数秒

TIMESTAMP(6)

表示最多保存 6 位小数秒。精度可以在类型中显式指定，默认精度通常为 6。

CREATE TABLE timestamp_demo (
    ts TIMESTAMP(6)
);

INSERT INTO timestamp_demo
VALUES (TIMESTAMP '2025-03-08 14:30:45.123456');

SELECT TO_CHAR(
           ts,
           'YYYY-MM-DD HH24:MI:SS.FF6'
       ) AS ts_text
FROM timestamp_demo;

预期结果：

2025-03-08 14:30:45.123456

DATE 和 TIMESTAMP 的核心差异是：

类型	小数秒	时区
DATE	不支持	不支持
TIMESTAMP	支持	不支持
TIMESTAMP WITH TIME ZONE	支持	支持
TIMESTAMP WITH LOCAL TIME ZONE	支持	按会话时区显示

3. TIMESTAMP WITH TIME ZONE

它表达的是带时区信息的时间值：

CREATE TABLE zoned_time_demo (
    event_time TIMESTAMP(6) WITH TIME ZONE
);

INSERT INTO zoned_time_demo
VALUES (
    TIMESTAMP '2025-03-08 14:30:45.123456 +08:00'
);

SELECT
    TO_CHAR(
        event_time,
        'YYYY-MM-DD HH24:MI:SS.FF6 TZH:TZM'
    ) AS event_text
FROM zoned_time_demo;

它适合保存跨时区事件，例如：

• 用户操作发生时间；
• 外部系统传来的带偏移时间；
• 需要保留时区上下文的业务事件。

两个带不同时区表示但代表同一瞬间的值，在时间排序和比较时可能相等：

2025-03-08 14:00:00 +08:00
2025-03-08 06:00:00 +00:00

它们表示同一个 UTC 时间点。

4. TIMESTAMP WITH LOCAL TIME ZONE

这个类型的关键特征是：

1. 插入时把值转换到数据库时区语义；
2. 读取时根据当前会话时区显示；
3. 不保留原始时区偏移作为显示值的一部分。

示例：

CREATE TABLE local_time_demo (
    event_time TIMESTAMP(6) WITH LOCAL TIME ZONE
);

INSERT INTO local_time_demo
VALUES (
    TIMESTAMP '2025-03-08 14:30:00 +08:00'
);

COMMIT;

ALTER SESSION SET TIME_ZONE = '+00:00';

SELECT TO_CHAR(
           event_time,
           'YYYY-MM-DD HH24:MI:SS.FF6'
       ) AS utc_view
FROM local_time_demo;

如果数据库时区和会话设置正常，读取出的墙上时间会显示为对应的 UTC 时间，例如：

2025-03-08 06:30:00.000000

然后：

ALTER SESSION SET TIME_ZONE = '+08:00';

SELECT TO_CHAR(
           event_time,
           'YYYY-MM-DD HH24:MI:SS.FF6'
       ) AS local_view
FROM local_time_demo;

会显示：

2025-03-08 14:30:00.000000

这里改变的是显示，不是事件瞬间。

选择原则不是“哪个类型更高级”，而是先明确问题：

• 只需要本地日期和时间，不跨时区：DATE 或 TIMESTAMP；
• 需要保留输入时区或偏移：TIMESTAMP WITH TIME ZONE；
• 需要保存绝对时间，并让不同会话按自身时区显示：TIMESTAMP WITH LOCAL TIME ZONE。

5. 日期算术的返回类型和单位

Oracle 日期算术有明确的单位：

DATE + 数字

其中数字的单位是“天”。

SELECT
    DATE '2025-03-08' + 1 AS next_day,
    DATE '2025-03-08' + 1/24 AS next_hour
FROM dual;

• + 1：加一天；
• + 1/24：加一小时；
• + 1/(24*60)：加一分钟。

两个 DATE 相减，结果是天数：

SELECT
    (DATE '2025-03-10' - DATE '2025-03-08') AS days_between
FROM dual;

结果为：

2

对于月份，不应使用固定天数替代月份。应使用：

SELECT ADD_MONTHS(DATE '2025-01-31', 1)
FROM dual;

Oracle 会按照 ADD_MONTHS 的月份边界规则处理月末日期。需要表达“几年、几个月、几天、几小时”的间隔时，应使用 INTERVAL 类型，而不是把所有时间换算成一个小数天数。

---

六、LOB：处理大对象数据

LOB 是 Large Object 的缩写，用于保存普通字符串列不适合保存的大型值。主要类型如下：

类型	内容	是否在数据库内
CLOB	数据库字符集文本	是
NCLOB	国家字符集文本	是
BLOB	二进制数据	是
BFILE	外部文件定位器	否，文件由数据库外部系统管理

1. CLOB、NCLOB 和 BLOB

示例：

CREATE TABLE lob_demo (
    id          NUMBER PRIMARY KEY,
    description CLOB,
    metadata    NCLOB,
    payload     BLOB
);

INSERT INTO lob_demo (
    id, description, metadata, payload
)
VALUES (
    1,
    TO_CLOB('这是保存在数据库中的长文本。'),
    TO_NCLOB(N'这是国家字符集文本。'),
    HEXTORAW('01020304FF')
);

COMMIT;

这里：

• TO_CLOB 把字符表达式转换为 CLOB；
• TO_NCLOB 把字符表达式转换为 NCLOB；
• HEXTORAW 将十六进制文本转换为二进制值，便于示例写入 BLOB。

实际文件、图片或压缩包通常由客户端通过绑定变量写入，而不是拼接成 SQL 字面量。

2. LOB 定位器不是完整数据

查询 LOB 列时，客户端常常先得到一个 LOB locator（定位器），而不是把所有内容立即复制到普通字符串或字节数组中。

SELECT description
FROM lob_demo
WHERE id = 1;

定位器用于访问 LOB 数据。客户端是否立即读取全部数据，取决于驱动的取值方式和配置。

这带来几个重要边界：

• LOB 读取可能是流式的；
• 过早关闭连接或事务，可能导致定位器无法继续使用；
• 将巨大 LOB 一次性加载到应用内存，可能造成内存压力；
• LOB 的写入通常需要绑定变量或 DBMS_LOB 等 API，而不是字符串拼接。

3. LOB 的基本操作

Oracle 提供 DBMS_LOB 处理 LOB 的长度、截取和追加等操作。例如：

SELECT
    DBMS_LOB.GETLENGTH(description) AS char_length,
    DBMS_LOB.SUBSTR(description, 20, 1) AS preview
FROM lob_demo
WHERE id = 1;

对于 CLOB，DBMS_LOB.GETLENGTH 的长度单位是字符；对于 BLOB，通常是字节。

将大文本直接转换为 VARCHAR2 时，可能受到 SQL 表达式长度上限限制：

SELECT DBMS_LOB.SUBSTR(description, 100, 1)
FROM lob_demo
WHERE id = 1;

这里明确只取前 100 个字符，比试图把整个 CLOB 隐式转成普通字符串更安全。

4. LOB 与事务

LOB 数据属于数据库数据，其持久化仍受事务控制。典型流程是：

1. 开启或加入当前事务；
2. 插入或更新 LOB；
3. 执行 COMMIT 后对其他事务可见；
4. ROLLBACK 可以撤销尚未提交的修改。

但 LOB 定位器的生命周期和普通标量值不同。应用必须遵守驱动对事务、连接和流的要求，不能在连接关闭后继续假定定位器可读取。

此外，LOB 存储还涉及：

• 表内存储还是独立 LOB 段；
• BASICFILE 还是 SECUREFILE；
• 是否压缩、去重或加密；
• LOB 索引和空间回收。

这些是存储实现和运维层面的选择，不改变 CLOB、BLOB 的逻辑数据类型语义。较新的 Oracle 版本通常推荐使用 SecureFiles，但具体可用能力还受数据库版本、表空间和授权条件影响。

5. BFILE 的边界

BFILE 只保存外部文件的定位信息，不把文件内容作为普通数据库 LOB 存入数据库。文件通常位于数据库服务器可访问的操作系统目录中，并通过 Oracle DIRECTORY 对象映射。

它有几个明确限制：

• 数据库不负责外部文件内容的事务回滚；
• 文件删除、替换和权限由操作系统或外部部署管理；
• 数据库备份不一定包含对应的外部文件；
• BFILE 通常是只读的数据库外部 LOB。

因此，如果文件必须与业务事务一起备份、恢复和回滚，应优先评估使用 BLOB；如果文件体积巨大且由外部文件系统统一管理，BFILE 才可能符合边界。

---

七、Oracle 对象类型：属性、方法与对象实例

1. 对象类型的结构

Oracle 对象类型可以定义：

• 属性（attribute）；
• 成员方法（member method）；
• 静态方法（static method）；
• 构造函数；
• 继承关系和可替代类型能力。

最小示例：

CREATE OR REPLACE TYPE address_type AS OBJECT (
    city        VARCHAR2(50),
    postal_code VARCHAR2(20)
);
/

address_type 是一个 schema object，也是一种可用于列、变量、集合元素的用户定义数据类型。

2. 对象列

可以把对象类型作为表列：

CREATE TABLE customer_object_demo (
    customer_id NUMBER PRIMARY KEY,
    name        VARCHAR2(100),
    address     address_type
);

INSERT INTO customer_object_demo (
    customer_id, name, address
)
VALUES (
    1,
    'Alice',
    address_type('Shanghai', '200000')
);

COMMIT;

查询对象属性：

SELECT
    customer_id,
    name,
    address.city        AS city,
    address.postal_code AS postal_code
FROM customer_object_demo;

预期结果：

CUSTOMER_ID  NAME   CITY      POSTAL_CODE
-----------  -----  --------  -----------
1            Alice  Shanghai  200000

address_type('Shanghai', '200000') 是对象类型的构造调用，用属性值创建一个对象实例。

也可以直接更新对象属性：

UPDATE customer_object_demo
SET address.city = 'Beijing'
WHERE customer_id = 1;

或者替换整个对象：

UPDATE customer_object_demo
SET address = address_type('Shenzhen', '518000')
WHERE customer_id = 1;

3. 对象方法

对象类型可以声明并实现成员方法：

CREATE OR REPLACE TYPE person_type AS OBJECT (
    first_name VARCHAR2(50),
    last_name  VARCHAR2(50),
    MEMBER FUNCTION full_name RETURN VARCHAR2
);
/

CREATE OR REPLACE TYPE BODY person_type AS
    MEMBER FUNCTION full_name RETURN VARCHAR2 IS
    BEGIN
        RETURN first_name || ' ' || last_name;
    END;
END;
/

创建表并调用方法：

CREATE TABLE person_object_demo (
    id   NUMBER PRIMARY KEY,
    data person_type
);

INSERT INTO person_object_demo
VALUES (1, person_type('Ada', 'Lovelace'));

SELECT data.full_name()
FROM person_object_demo;

预期结果：

Ada Lovelace

这里的调用链是：

1. person_type('Ada', 'Lovelace') 创建对象实例；
2. 实例存入 data 对象列；
3. data.full_name() 调用该实例的方法；
4. 方法读取自身属性并返回字符串。

对象方法仍在数据库事务和权限体系中执行，不会绕过 SQL 或 PL/SQL 的错误处理机制。

4. 对象表与对象列的区别

可以直接创建对象表：

CREATE TABLE person_object_table OF person_type (
    CONSTRAINT person_object_table_pk PRIMARY KEY (first_name, last_name)
);

此时表的每一行本身就是一个 person_type 对象，而不是某个普通表中的一个对象列。

两种设计的结构不同：

普通关系表 + 对象列：
customer_id | data(person_type)

对象表：
每一行本身就是 person_type

对象表可以使用对象属性进行查询，也可以使用对象方法。对象表还可以涉及对象标识、REF、继承和可替代类型等更复杂语义。

5. 对象类型不是 JSON，也不是普通记录别名

对象类型具有数据库类型身份、构造函数和方法；它不是简单的 JSON 文档，也不是只在 PL/SQL 过程中存在的临时记录。

例如，下面是三个不同层次：

-- 普通关系列
name VARCHAR2(100)

-- 对象列
address address_type

-- JSON 文档列（版本和数据库能力相关）
document JSON 或 CLOB/BLOB 上的 JSON 约束

选择对象类型通常意味着需要：

• 多个相关属性的稳定结构；
• 数据库侧方法；
• 类型复用；
• 与对象表、对象引用或嵌套集合配合。

如果只是保存结构经常变化的文档，JSON 语义可能更适合；如果只是把几个字段放在同一张关系表中，普通列通常更简单。

---

八、集合类型：对象设计中容易遗漏的基础工具

对象类型经常与 Oracle 集合类型一起使用。两种主要集合是：

• VARRAY：有最大元素数量限制，通常保持元素顺序；
• Nested Table：嵌套表，元素集合可单独存储，逻辑上不应依赖自然顺序。

定义一个地址对象和地址集合：

CREATE OR REPLACE TYPE simple_address_type AS OBJECT (
    city VARCHAR2(50)
);
/

CREATE OR REPLACE TYPE address_list_type
AS TABLE OF simple_address_type;
/

将集合用于表列：

CREATE TABLE account_with_addresses (
    account_id NUMBER PRIMARY KEY,
    addresses  address_list_type
)
NESTED TABLE addresses STORE AS account_addresses_nt;

插入集合值：

INSERT INTO account_with_addresses
VALUES (
    1,
    address_list_type(
        simple_address_type('Shanghai'),
        simple_address_type('Beijing')
    )
);

COMMIT;

查询集合元素：

SELECT a.account_id, x.city
FROM account_with_addresses a,
     TABLE(a.addresses) x
ORDER BY a.account_id, x.city;

TABLE(a.addresses) 把集合展开为可查询的行源。

这里有一个重要反例：如果业务需要严格的列表顺序，不能仅凭嵌套表的物理返回顺序推断顺序。应在元素对象中增加显式序号：

CREATE OR REPLACE TYPE ordered_address_type AS OBJECT (
    position_no NUMBER,
    city        VARCHAR2(50)
);
/

然后使用：

ORDER BY x.position_no

数据库表、嵌套表和 SQL 查询都不应把“未写 ORDER BY 时恰好看到的顺序”当作规范保证。

---

九、Schema、权限与类型解析

创建对象类型或表时，对象属于当前 schema：

CREATE TYPE address_type AS OBJECT (...);
CREATE TABLE customer_object_demo (...);

如果另一个用户要使用它，需要对象权限。例如由 APP_USER 执行：

GRANT EXECUTE ON address_type TO REPORT_USER;
GRANT SELECT ON customer_object_demo TO REPORT_USER;

REPORT_USER 可以这样访问：

SELECT customer_id, address.city
FROM app_user.customer_object_demo;

但仅有表的 SELECT 权限，不代表可以创建或修改 APP_USER 的对象；仅有对象类型的 EXECUTE 权限，也不代表可以查询存储该类型的表。

跨 schema 使用用户定义类型时，最好显式写出类型所属 schema，避免同名类型导致解析歧义：

app_user.address_type(...)

实际可用的授权还受到角色、直接授权、定义者权限和调用者权限等 PL/SQL 规则影响。数据库对象创建和运行时访问是两个不同的权限阶段。

---

十、类型转换：显式转换优于隐式转换

Oracle 会在部分表达式中执行隐式类型转换，例如把字符串转换为数字或日期。但隐式转换依赖：

• NLS_DATE_FORMAT；
• NLS_NUMERIC_CHARACTERS；
• 客户端绑定类型；
• 列和表达式的数据类型优先级。

反例：

WHERE order_date = '2025-03-08'

更明确的写法：

WHERE order_date >= DATE '2025-03-08'
  AND order_date <  DATE '2025-03-09'

这个范围条件还避免了把 DATE 列转换成字符串，因此通常比下面的写法更容易使用普通索引：

WHERE TO_CHAR(order_date, 'YYYY-MM-DD') = '2025-03-08'

数字转换也应显式指定格式：

SELECT TO_NUMBER(
           '1.234,56',
           '9G999D99',
           'NLS_NUMERIC_CHARACTERS='',.'''
       )
FROM dual;

其含义是：

• G 代表千位分隔符；
• D 代表小数点；
• 显式指定本例中逗号为小数点、句号为千位分隔符。

显式转换不是为了让 SQL 更长，而是把数据解释规则从会话环境中拿出来，固定在语句本身。

---

十一、NULL 与各类数据类型的交互

NULL 表示未知或不存在的值，而不是：

• 0；
• 空字符串；
• 空格；
• 1970 年；
• 默认值。

对于任何类型，以下判断都不成立：

column_name = NULL

正确判断：

column_name IS NULL
column_name IS NOT NULL

算术也会传播 NULL：

SELECT 10 + NULL AS result
FROM dual;

结果为 NULL。

日期示例：

SELECT order_date + NULL
FROM orders;

结果同样是 NULL。

LOB、对象列也可以为 NULL。需要区分：

• 对象列本身为 NULL；
• 对象不为 NULL，但某个属性为 NULL；
• LOB 列为 NULL；
• LOB 存在但长度为 0。

在 Oracle 的字符语义下，空字符串会转成 NULL，因此“长度为零的普通字符串”不能作为稳定的独立状态保存。

---

十二、事务、DDL 与数据类型变更的边界

Oracle DDL 通常会隐式提交事务。下面的过程不能按某些数据库的直觉理解：

INSERT INTO number_demo (id, amount)
VALUES (10, 1.23);

CREATE TABLE ddl_boundary_demo (id NUMBER);

ROLLBACK;

CREATE TABLE 前的插入通常已经因为 DDL 的隐式提交而无法通过后面的 ROLLBACK 撤销。

因此，以下操作应当在独立的部署阶段执行，而不是混入业务事务：

• CREATE TABLE；
• ALTER TABLE；
• CREATE TYPE；
• CREATE INDEX；
• GRANT。

数据写入、LOB 更新和对象实例更新则遵循普通事务边界：

UPDATE customer_object_demo
SET address.city = 'Nanjing'
WHERE customer_id = 1;

-- 此时当前会话可见，其他会话通常尚不可见
COMMIT;

如果 LOB 或对象数据写入失败，应由应用根据异常决定回滚、重试或记录失败，而不是假设数据库会自动将大对象写入拆成若干独立事务。

---

十三、常见失败表现与诊断方式

1. 数值插入失败

常见原因：

• NUMBER(p,s) 精度不足；
• 字符串无法转换为数字；
• 小数舍入后溢出；
• 应用绑定类型与列类型不匹配。

检查列定义：

SELECT
    column_name,
    data_type,
    data_length,
    data_precision,
    data_scale
FROM user_tab_columns
WHERE table_name = 'NUMBER_DEMO';

其中：

• DATA_PRECISION 表示精度；
• DATA_SCALE 表示尺度；
• DATA_LENGTH 主要反映字节长度，对字符语义不能单独作为字符数判断。

2. 日期在不同环境显示不同

常见原因不是数据库“改变了日期”，而是：

• 查询工具使用了不同的 NLS_DATE_FORMAT；
• DATE 没有时区；
• TIMESTAMP WITH LOCAL TIME ZONE 按不同会话时区显示；
• 应用把数据库值转换成了本地时间。

诊断当前会话环境：

SELECT
    SESSIONTIMEZONE,
    DBTIMEZONE,
    SYS_CONTEXT('USERENV', 'CURRENT_SCHEMA') AS current_schema
FROM dual;

显示时显式使用 TO_CHAR，不要根据客户端默认格式判断底层值。

3. 字符串长度错误

检查列定义：

SELECT
    column_name,
    data_type,
    char_length,
    char_used,
    data_length
FROM user_tab_columns
WHERE table_name = 'CHARACTER_DEMO';

• CHAR_LENGTH：字符长度；
• CHAR_USED：长度语义通常为 B（BYTE）或 C（CHAR）；
• DATA_LENGTH：字节长度。

测试多字节文本时，分别使用：

SELECT
    LENGTH(:value)  AS character_count,
    LENGTHB(:value) AS byte_count
FROM dual;

LENGTH 和 LENGTHB 不一定相等，这正是多字节字符集下需要明确长度语义的原因。

4. LOB 读取内存过大

如果应用把 CLOB 全部读取为一个普通字符串，可能出现：

• 客户端内存增长；
• 驱动转换失败；
• 超过普通字符串变量上限；
• 响应超时。

诊断时应确认：

1. 驱动是否以流式方式读取；
2. 是否只需读取前 N 个字符；
3. 是否在读取期间保持连接和事务；
4. 是否将 BLOB 错误地按字符集转换；
5. 是否把大字段加入了不必要的列表查询。

5. 对象类型无法删除或修改

如果对象类型被表列、对象表、集合或其他类型依赖，直接执行：

DROP TYPE address_type;

可能失败，因为仍存在依赖对象。

可以先查看依赖：

SELECT
    name,
    type,
    referenced_name,
    referenced_type
FROM user_dependencies
WHERE referenced_name = 'ADDRESS_TYPE';

对象类型的修改需要考虑依赖关系、数据兼容性和应用编译顺序。生产变更应先在相同版本和相同字符集配置的环境中验证，而不是直接删除重建。

---

十四、类型选择的推导方法

面对一个新字段，可以按以下顺序推导，而不是先凭习惯选类型。

1. 先确定数学或语义域

如果值需要精确十进制运算：

金额、税率、计费数量 -> NUMBER

如果值是二进制浮点科学计算结果：

测量值、模拟结果 -> BINARY_FLOAT 或 BINARY_DOUBLE

如果值是文本：

普通短文本 -> VARCHAR2
真正定长代码 -> CHAR
超长文本 -> CLOB

如果值是二进制：

普通二进制或文件内容 -> BLOB
外部文件引用 -> BFILE

如果值是时间：

本地日期时间 -> DATE / TIMESTAMP
跨时区时间点 -> TIMESTAMP WITH TIME ZONE
按会话时区展示的绝对时间 -> TIMESTAMP WITH LOCAL TIME ZONE

如果值由多个有明确结构的属性组成，并且需要类型级方法或复用：

对象类型

2. 再确定约束是否属于类型

例如，金额通常不只是 NUMBER，还需要：

amount NUMBER(18,2) NOT NULL

这里有两个独立约束：

• NUMBER(18,2) 限制表示范围和小数位；
• NOT NULL 限制是否允许缺失。

不要把“业务校验”全部寄托在类型本身。例如金额不能为负数，还需要：

CHECK (amount >= 0)

完整示例：

CREATE TABLE invoice_line (
    line_id       NUMBER PRIMARY KEY,
    unit_price    NUMBER(18,2) NOT NULL,
    quantity      NUMBER(18,4) NOT NULL,
    total_amount  NUMBER(20,2)
        GENERATED ALWAYS AS (ROUND(unit_price * quantity, 2)),
    CONSTRAINT invoice_line_price_ck CHECK (unit_price >= 0),
    CONSTRAINT invoice_line_qty_ck   CHECK (quantity > 0)
);

这里的总金额由数据库表达式计算，避免应用层和数据库层使用不同的舍入规则。实际部署时仍要根据目标 Oracle 版本验证生成列表达式、精度和业务舍入策略。

3. 最后验证边界值和跨环境行为

至少应测试：

• 最大值、最小值和舍入边界；
• 多字节字符；
• NULL、空字符串和空格；
• 夏令时切换点；
• 不同时区会话；
• LOB 大小和流式读取；
• 对象类型依赖和升级顺序。

类型设计完成不等于测试完成。很多问题只在 NLS、时区、字符集或客户端驱动改变后出现。

---

Oracle Schema 提供的是对象命名、权限和依赖的逻辑边界；数据类型则定义了值的数学、字符、时间、存储和对象语义。理解两者的关系，不能只记住“NUMBER 存数字、DATE 存日期”这样的简称，而要继续追问：精度如何计算、时区是否保留、空字符串是否等于 NULL、LOB 是数据还是定位器、对象类型与对象列如何依赖，以及 DDL 和事务在什么边界上发生变化。只有这些规则都明确，表结构才具有可验证的含义。

---

系列导航与关联阅读

• 系列入口：数据库完整学习路线：从关系模型、事务索引到分布式与向量检索
• 上一篇：PostgreSQL PostGIS：空间类型、坐标系、空间索引和查询优化
• 下一篇：Oracle 存储管理：Tablespace、Segment、Extent、Block 和 ASM
• 延伸：Oracle 数据库架构：Instance、SGA、PGA、数据文件与后台进程
• 延伸：Oracle SQL 与 PL/SQL：数据类型、包、过程、异常和批处理

官方资料

• Oracle Database Documentation
• Oracle Database Concepts

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。