第三章 PL/SQL基础（续）

PL/SQL数据类型

一、预定义数据类型

Oracle 的数据类型可以分为四类，分别是 标量类型，复合类型，引用类型和LOB类型。标量类型没有内部组件；而复合类型包含了能够被单独操作的内部组件；引用类型类似于3G语言中的指针，能够引用一个值；LOB类型的值就是一个lob定位器，能够指示出大对象(如图像)的存储位置。

下图是在PL/SQL中可以使用的预定义类型

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41

PL/SQL 预定义数据类型
├─ 1. 标量类型（无内部组件，不可再分）
│  ├─ 1.1 数字类型（存储数值）
│  │  ├─ NUMBER（通用数值，支持整数/小数）
│  │  ├─ INTEGER（整数子类型，仅存整数）
│  │  ├─ PLS_INTEGER（PL/SQL 专用整数，性能更高）
│  │  └─ BINARY_FLOAT / BINARY_DOUBLE（二进制浮点数，适合科学计算）
│  │
│  ├─ 1.2 字符类型（存储字符/字符串）
│  │  ├─ CHAR（固定长度字符串，不足补空格）
│  │  ├─ VARCHAR2（可变长度字符串，按实际长度存储）
│  │  ├─ NCHAR / NVARCHAR2（基于国家字符集，支持多语言）
│  │  └─ CLOB（虽属 LOB 类型，但此处标量仅含基础字符类型，CLOB 归为单独 LOB 类）
│  │
│  ├─ 1.3 布尔类型（仅 PL/SQL 支持，存储逻辑值）
│  │  └─ BOOLEAN（仅取值：TRUE / FALSE / NULL）
│  │
│  └─ 1.4 日期/时间类型（存储日期和时间）
│     ├─ DATE（存储年月日+时分秒，精度到秒）
│     ├─ TIMESTAMP（DATE 扩展，支持小数秒，精度到纳秒）
│     ├─ TIMESTAMP WITH TIME ZONE（含时区信息的时间戳）
│     └─ TIMESTAMP WITH LOCAL TIME ZONE（自动转换为数据库时区的时间戳）
│
├─ 2. 复合类型（含内部组件，组件可单独操作）
│  ├─ 2.1 记录类型（类似“结构体”，存储不同类型的字段）
│  │  ├─ 自定义记录（用 TYPE ... IS RECORD 定义）
│  │  └─ %ROWTYPE（基于表/视图/游标结构的“行记录”，字段与原对象一致）
│  │
│  └─ 2.2 集合类型（存储同类型的多个元素，类似“数组”）
│     ├─ 索引表（PL/SQL TABLE，无固定长度，下标可正负）
│     ├─ 嵌套表（NESTED TABLE，可存储在数据库表中，支持动态扩展）
│     └─ 变长数组（VARRAY，固定最大长度，下标从1开始）
│
├─ 3. 引用类型（类似指针，存储值的引用地址）
│  └─ REF 类型（仅用于对象类型，存储对象表中某行对象的引用，如 REF person）
│
└─ 4. LOB 类型（存储大对象，实际数据存于外部，字段存“定位器”）
   ├─ CLOB（字符型大对象，存储长文本，如 XML/JSON）
   ├─ BLOB（二进制大对象，存储非文本数据，如图片/音频/视频）
   ├─ NCLOB（基于国家字符集的字符大对象，支持多语言长文本）
   └─ BFILE（外部二进制文件，数据存于数据库外，字段存文件路径和名称）

其中标量类型又分为四类：数字、字符、布尔和日期/时间。

1. 数字型

数字类型可以存储整数、实数和浮点数，可以表示数值的大小，参与计算。

1.1 BINARY_INTEGER

我们可以使用 BINARY_INTEGER 数据类型来存储有符号整数。它的范围是 -231至231。跟 PLS_INTEGER 一样， BINARY_INTEGER 所需的存储空间也要小于NUMBER。但是，大多数的 BINARY_INTEGER 操作要比 PLS_INTEGER 操作慢。

BINARY_INTEGER子类型

所谓的基类型，就是有子类型继承于它。子类型在基类型的基础上添加一些约束限制，也可能重新定义数值范围。为了使用方便，PL/SQL 预定义了下面几个 BINARY_INTEGER 的子类。

NATURAL（非负整数，允许 NULL）

限制变量只能存储 0 及正整数，可赋值 NULL。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

DECLARE
  num NATURAL

  -- 正确：0 和正整数
  num := 0;    -- 合法
  num := 100;  -- 合法
  
  -- 错误：负数（违反非负约束）
  -- num := -5;  -- 运行时会报错（VALUE_ERROR）
  
  -- 允许 NULL
  num := NULL;  -- 合法（不报错）
END;

NATURALN（非负整数，不允许 NULL）

与 NATURAL 类似，但禁止赋值 NULL，仅允许 0 及正整数。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

DECLARE
  num NATURALN;  -- 子类型：非负整数，不可为NULL

  -- 正确：0 和正整数
  num := 0;    -- 合法
  num := 200;  -- 合法
  
  -- 错误1：负数
  -- num := -3;  -- 报错（VALUE_ERROR）
  
  -- 错误2：赋值NULL（核心差异）
  -- num := NULL;  -- 报错（VALUE_ERROR）
END;

POSITIVE（正整数，允许 NULL）

限制变量只能存储 大于 0 的正整数，可赋值 NULL（与 NATURAL 的区别：不包含 0）。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

DECLARE
  num POSITIVE;  -- 子类型：正整数（>0）

  -- 正确：大于0的整数
  num := 1;    -- 合法
  num := 300;  -- 合法
  
  -- 错误1：0或负数
  -- num := 0;    -- 报错（VALUE_ERROR）
  -- num := -10;  -- 报错（VALUE_ERROR）
  
  -- 允许 NULL
  num := NULL;  -- 合法
END;

POSITIVEN（正整数，不允许 NULL）

与 POSITIVE 类似，但禁止赋值 NULL，仅允许大于 0 的正整数。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

DECLARE
  num POSITIVEN;  -- 子类型：正整数（>0），不可为NULL

  -- 正确：大于0的整数
  num := 5;    -- 合法
  num := 1000; -- 合法
  
  -- 错误1：0或负数
  -- num := 0;    -- 报错
  -- num := -2;   -- 报错
  
  -- 错误2：赋值NULL（核心差异）
  -- num := NULL;  -- 报错
END;

SIGNTYPE（三态整数：-1、0、1，允许 NULL）

限制变量只能存储 -1、0、1 三个值，常用于表示“负/零/正”三态逻辑。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

DECLARE
  flag SIGNTYPE;  -- 子类型：仅允许-1、0、1

  -- 正确：三态值
  flag := -1;  -- 表示“负”或“假”
  flag := 0;   -- 表示“零”或“无”
  flag := 1;   -- 表示“正”或“真”
  
  -- 错误：其他值
  -- flag := 2;   -- 报错（VALUE_ERROR）
  -- flag := -5;  -- 报错
  
  -- 允许 NULL
  flag := NULL;  -- 合法
END;

子类型NATURAL和POSITIVE能让我们将一个整数变量的取值范围分别限制在非负数和正整数之内。NATURALN和POSITIVEN不允许为整数类型变量赋空值。SIGNTYPE把整数的取值范围限定在-1，0，1，这在编程中很适合表示三态逻辑(tri-state logic)。

1.2 NUMBER

我们可以使用NUMBER数据类型来存储定点或浮点数。它的范围是1E-130至10E125。如果表达式的值超过这个范围，我们就会得到数字溢出错误(a numeric overflow or underflow error)。我们可以为要存储的数字指定精度，包括数字的全长和小数长度。语法如下：

NUMBER[(precision,scale)]

其中precision表示数字的总长度，scale代表可以有几位小数。如果要使用浮点数的话，就不能指定长度和精度，像下面这样声明就可以了：

NUMBER

声明整数直接使用下面的语法：

1

NUMBER(precision)   -- same as NUMBER(precision,0)

不可以用常量或变量指定NUMBER的长度和精度。NUMBER类型最大的长度是38位。如果不指定NUMBER类型的最大长度，就会默认采用这个长度或是使用系统所支持的最大长度。

scale的范围从-84到127，能够决定舍入规则。例如，一个scale值为2的数字，舍入后的小数部分将是最接原小数部分的百分位数 (3.456舍入为3.46)。如果scale是负数，它就会从小数点左边开始进行舍入操作。如scale值为-3的数字舍入后的结果将是最接近原值的千位数(3456舍入为3000)。当数字本身是整数时，scale 为零的数字舍入后结果还是本身；若数字含小数部分，scale=0 会舍入到整数（可能改变原值）。如果不指定 scale 的值，默认就为 0。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32

DECLARE
  -- 测试数据
  v_num1 NUMBER := 3.456;       -- 用于scale=2的测试
  v_num2 NUMBER := 3456;        -- 用于scale=-3的测试
  v_num3 NUMBER := 123.456;     -- 用于scale=0的测试
  v_num4 NUMBER := 98765.4321;  -- 综合测试
  
  -- 结果变量
  v_result1 NUMBER;
  v_result2 NUMBER;
  v_result3 NUMBER;
  v_result4 NUMBER;
BEGIN
  -- 1. scale=2：保留2位小数（四舍五入到百分位）
  v_result1 := ROUND(v_num1, 2);
  DBMS_OUTPUT.PUT_LINE('scale=2，3.456舍入结果：' || v_result1);  -- 输出：3.46
  
  -- 2. scale=-3：舍入到千位（小数点左边第3位）
  v_result2 := ROUND(v_num2, -3);
  DBMS_OUTPUT.PUT_LINE('scale=-3，3456舍入结果：' || v_result2);  -- 输出：3000
  
  -- 3. scale=0：默认值，舍入到整数（与原值整数部分一致）
  v_result3 := ROUND(v_num3, 0);  -- 不指定scale时默认也是0，即ROUND(v_num3)
  DBMS_OUTPUT.PUT_LINE('scale=0，123.456舍入结果：' || v_result3);  -- 输出：123
  
  -- 4. 更多测试：scale=1（保留1位小数）、scale=-2（舍入到百位）
  v_result4 := ROUND(v_num4, 1);  -- 98765.4321 → 98765.4
  DBMS_OUTPUT.PUT_LINE('scale=1，98765.4321舍入结果：' || v_result4);
  
  v_result4 := ROUND(v_num4, -2);  -- 98765.4321 → 98800
  DBMS_OUTPUT.PUT_LINE('scale=-2，98765.4321舍入结果：' || v_result4);
END;

NUMBER子类型

为了能和ANSI/ISO和IBM类型兼容或是想使用一个更加有描述性意义的名字，我们就可以使用下面的NUMBER子类型。

  • DEC
  • DECIMAL
  • DOUBLE PRECISION
  • FLOAT
  • INTEGER
  • INT
  • NUMERIC
  • REAL
  • SMALLINT

对定点数（DEC/DECIMAL/NUMERIC）：“精度”= 整数位数 + 小数位数（固定位数，如DEC(8,2)即 8 位总位数）。

对浮点数（DOUBLE PRECISION/FLOAT/REAL）：“精度”= 十进制有效数字个数（如 126 位二进制≈38 位有效数字，示例需控制有效数字≤38 位）。例如：1.2345678901234567890123456789012345678×10³⁴，因为浮点数仅需存储 “有效数字部分（1.2345…）” 和 “指数（34）”，但指数不算做精度，因此精度以 “有效数字个数” 衡量，而非 “整数 + 小数的总位数”。

有效数字包含小数点前后的有意义数字，从第一个非 0 数字开始计数（前面的 0 是 “占位符”，不算有效数字，如00123.45的有效数字是 5 位：1、2、3、4、5）；小数点后的数字，只要在第一个非 0 数字之后，均算有效数字（如123.45的有效数字是 5 位，0.0123的有效数字是 3 位）；末尾的 0 是否算有效数字，取决于是否有意义（如123.00的有效数字是 5 位，代表精确到百分位；12300若未标注，则可能是 3 位有效数字，末尾 0 为占位符）。

DEC（定点数，同DECIMAL）

DEC是NUMBER的子类型，用于声明定点数，默认精度为10，可指定精度和小数位数（DEC(精度, 小数位)），最大精度38位。

1
2
3
4
5
6
7
8

DECLARE
  dec_num DEC(8, 2); -- 声明精度8位（整数6位+小数2位）的定点数
BEGIN
  dec_num := 123456.78; -- 合法：总位数8，小数2位
  DBMS_OUTPUT.PUT_LINE('DEC类型值：' || dec_num); -- 输出：123456.78
  -- dec_num := 1234567.89; -- 非法：总位数超过8位
END;
/

DECIMAL（定点数，同DEC）

与DEC完全等价，用于兼容ANSI标准，同样支持定点数声明。

1
2
3
4
5
6
7

DECLARE
  decimal_num DECIMAL(10, 4); -- 精度10位（整数6位+小数4位）
BEGIN
  decimal_num := 123456.7890; -- 合法
  DBMS_OUTPUT.PUT_LINE('DECIMAL类型值：' || decimal_num); -- 输出：123456.7890
END;
/

DOUBLE PRECISION（浮点数）

用于声明高精度浮点数，最大精度为126位二进制（约38位十进制），适合需要高浮点精度的场景。

1
2
3
4
5
6
7

DECLARE
  double_num DOUBLE PRECISION; -- 浮点数，精度约38位十进制
BEGIN
  double_num := 12345678901234567890123456789012345.12345; -- 高精度浮点数
  DBMS_OUTPUT.PUT_LINE('DOUBLE PRECISION类型值：' || double_num);
END;
/

FLOAT（浮点数）

可指定二进制精度（FLOAT(b)，b范围1-126），默认精度126位二进制（约38位十进制），与DOUBLE PRECISION类似。

1
2
3
4
5
6
7

DECLARE
  float_num FLOAT(60); -- 60位二进制精度（约18位十进制）
BEGIN
  float_num := 987654321012345.789; -- 符合精度范围
  DBMS_OUTPUT.PUT_LINE('FLOAT类型值：' || float_num); -- 输出：987654321012345.789
END;
/

INTEGER（整数）

用于声明整数，精度最大38位十进制，无小数部分，等价于NUMBER(38, 0)。

1
2
3
4
5
6
7
8

DECLARE
  int_num INTEGER; -- 整数类型
BEGIN
  int_num := 12345678901234567890; -- 合法整数
  DBMS_OUTPUT.PUT_LINE('INTEGER类型值：' || int_num); -- 输出：12345678901234567890
  -- int_num := 123.45; -- 非法：包含小数
END;
/

INT（整数，同INTEGER）

INT是INTEGER的简写，功能完全一致，用于声明整数。

1
2
3
4
5
6
7

DECLARE
  int_short INT; -- 等价于INTEGER
BEGIN
  int_short := -987654321; -- 负整数
  DBMS_OUTPUT.PUT_LINE('INT类型值：' || int_short); -- 输出：-987654321
END;
/

NUMERIC（定点数，同DEC/DECIMAL）

与DEC、DECIMAL功能相同，用于声明定点数，强调“数值型”以兼容ANSI标准。

1
2
3
4
5
6
7

DECLARE
  numeric_num NUMERIC(5, 2); -- 精度5位（整数3位+小数2位）
BEGIN
  numeric_num := 123.45; -- 合法
  DBMS_OUTPUT.PUT_LINE('NUMERIC类型值：' || numeric_num); -- 输出：123.45
END;
/

REAL（浮点数）

精度为63位二进制（约18位十进制），是FLOAT(63)的简写，适合中等精度浮点场景。

1
2
3
4
5
6
7

DECLARE
  real_num REAL; -- 约18位十进制精度的浮点数
BEGIN
  real_num := 123456789012345.678; -- 符合精度范围
  DBMS_OUTPUT.PUT_LINE('REAL类型值：' || real_num); -- 输出：123456789012345.678
END;
/

SMALLINT（短整数）

本质仍是NUMBER子类型，通常用于表示较小的整数（精度约等同于NUMBER(38, 0)，但语义上强调“短”以兼容其他数据库）。

1
2
3
4
5
6
7

DECLARE
  small_int SMALLINT; -- 短整数类型
BEGIN
  small_int := 32767; -- 典型的小整数范围（语义上的“短”）
  DBMS_OUTPUT.PUT_LINE('SMALLINT类型值：' || small_int); -- 输出：32767
END;
/

使用DEC、DECIMAL和NUMBERIC可以声明最大精度为38位十进制数字的定点数字。而使用DOUBLE PRECISION和FLOAT可以声明最大精度为126位二进制数字的浮点数字，大约相当于38位十进制数字。或是使用REAL声明最大精度为63位二进制数字的浮点数字，大约相当于18位十进制数字。INTEGER、INT和SMALLINT可以声明最大精度为38位十进制数字的整数。

PLS_INTEGER

我们可以使用PLS_INTEGER数据类型来存储有符号整数。它的取值范围在 -231至231 之间。PLS_INTEGER所需的存储空间要比NUMBER少，运算的速度要高于NUMBER和BINARY_INTEGER。虽然PLS_INTEGER和BINARY_INTEGER的取值范围一样，但它们不完全兼容。PLS_INTEGER在运算时如果有溢出，则会有异常抛出，而BIANRY_INTEGER发生溢出时，如果结果是要赋给一个NUMBER类型的变量时，就不会有异常抛出。为了考虑兼容性，我们仍可以在旧的应用程序中使用BINARY_INTEGER；但在新的应用程序中， PLS_INTEGER会带来更好的性能。

2. 字符型

字符类型可以存放字符和数字混合的数据，表现词和文章，操作字符串。

2.1 CHAR

我们可以使用CHAR类型来存储定长的字符数据。但该数据的内部表现形式是取决于数据库字符集的。CHAR类型有一个用于指定最大长度的可选参数，长度范围在1到32767字节之间。我们可以采用字节或字符的形式来设置该参数。语法如下：

CHAR[(maximum_size [CHAR | BYTE] )]

maximum_size不能是常量或变量，只能是范围在1到32767之间的整数文字。

如果我们不指定最大值，它默认是1。如果我们用字节形式指定最大值，有时就会出现空间不足的问题(多字节字符会占用多于一个字节的空间)。为了避免这样的问题发生，我们可以采用按照字符的方式指定其最大值，这样，即使是那些包含多个字节的参数可以被灵活地存储下来。按照字符指定长度的方式，上限大小仍旧是32767字节，所以，对于双字节和多字节的字符集，我们可以使用字节最大长度的一半或三分之一作为最大字符个数。

虽然PL/SQL字符变量的长度相对来说比较长，但CHAR类型在数据库的字段中最大存储长度为2000个字节，所以，我们不能往数据库CHAR类型字段中插入超过2000个字节的字符。但是，我们可以把任意CHAR(n)插入LONG类型的字段中，因为LONG的最大长度是2**31字节或是2G (gigabyte)。如果我们不使用CHAR或BYTE来对字符类型长度进行限制的话，初始化参数NLS_LENGTH_SEMANTICS会决定默认长度大小的。CHAR的子类型CHARACTER和CHAR有着相同的取值范围。也就是说，CHARACTER只是CHAR的一个别名而已。这个子类型能与ANSI/ISO和IBM类型相兼容。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

DECLARE
  -- 1. 正确：maximum_size用“整数文字”（10、20、32767都是直接写的整数）
  char1 CHAR(10);          -- 正确：10是整数文字，默认按字节算（最多存10个字节）
  char2 CHAR(20 CHAR);     -- 正确：20是整数文字，按字符算（最多存20个字符，不管每个字符占多少字节）
  char3 CHAR(32767 BYTE);  -- 正确：32767是整数文字（PL/SQL中CHAR的最大长度上限）

  -- 2. 错误：maximum_size用“常量/变量”（违反“只能是整数文字”的规则）
  c_len CONSTANT NUMBER := 10;  -- 定义一个常量c_len=10
  v_len NUMBER := 20;            -- 定义一个变量v_len=20
  -- char4 CHAR(c_len);        -- 错误：用常量c_len代替整数文字
  -- char5 CHAR(v_len);        -- 错误：用变量v_len代替整数文字
BEGIN
  NULL;
END;

2.2 LONG和LONG RAW

我们可以使用LONG类型来存储变长的字符串。除了LONG类型的最大长度是32760字节（约32KB）之外，LONG类型和VARCHAR2很相像。我们还可以使用LONG RAW类型来存储二进制数据或二进制字符串。LONG RAW和LONG类似，但是它不会被PL/SQL解析。LONG RAW的最大长度也是32760字节。从9i开始，LOB类型变量可以与LONG和LONG RAW类型交换使用。Oracle推荐将LONG和LONG RAW都对应的转换成COLB和BLOB类型。

我们可以将LONG类型的值插入字段类型为LONG的数据库中，因为在数据库中LONG的长度是2^31 字节既即 2GB；但是，不可以从LONG字段中检索超过32760字节（约 32KB）的字符放到LONG类型变量中去。同样，对于LONG RAW类型来说，这条规则同样适用，在数据库中它的最大长度也是2^31字节，而变量的长度在32760字节（约 32KB）以内。LONG类型字段可以存储文本、字符数组或短文等。我们可以对LONG字段进行UPDATE、INSERT和SELECT操作，但不能在表达式（如 LONG字段 + ‘abc’）、SQL函数调用（如 UPPER(LONG字段)、或某个SQL子句（如 WHERE LONG字段 = ‘xxx’、GROUP BY LONG字段）中使用它。

注意：在SQL语句中，PL/SQL会将LONG类型的值绑定成VARCHAR2类型，而不是LONG。但是，如果被绑定的VARCHAR2值超过 4000 个字节，Oracle会自动地将绑定类型转成LONG，但LONG并不能应用在SQL函数中，所以，这时我们就会得到一个错误消息。

第一步：创建含LONG字段的表

先在数据库中创建一张表，其中content字段为LONG类型（用于存大文本）：

1
2
3
4
5
6

-- 创建表：id（主键）、content（LONG类型，存大文本）
CREATE TABLE long_demo (
  id NUMBER PRIMARY KEY,
  content LONG  -- 数据库LONG字段，最大2GB
);
/

第二步：插入数据（INSERT操作，支持）

用PL/SQL向LONG字段插入文本（注意：PL/SQL的LONG变量最大32760字节，所以插入的文本不能超过这个长度，否则需用其他方式如DBMS_LOB）：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

DECLARE
  v_long_content LONG;  -- PL/SQL的LONG变量，最大32760字节
BEGIN
  -- 给变量赋值：一段文本（这里用重复字符模拟“较长文本”，确保不超过32760字节）
  v_long_content := '这是一段用于测试LONG类型的文本' 
                   || RPAD('（重复内容）', 1000, '（重复内容）');  -- RPAD函数生成重复字符，控制长度
  
  -- 插入数据到LONG字段（INSERT支持）
  INSERT INTO long_demo (id, content)
  VALUES (1, v_long_content);  -- 合法：变量长度≤32760字节
  
  COMMIT;  -- 提交事务
  DBMS_OUTPUT.PUT_LINE('数据插入成功！');
END;
/

第三步：查询数据（SELECT操作，支持，但要避坑）

从LONG字段查询数据时，若内容≤32760字节，可存到PL/SQL的LONG变量；若超过，则会报错（后面说解决办法）：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

DECLARE
  v_query_content LONG;  -- PL/SQL的LONG变量
BEGIN
  -- 从LONG字段查询数据（SELECT支持）
  SELECT content 
  INTO v_query_content
  FROM long_demo
  WHERE id = 1;  -- WHERE子句用的是id（非LONG字段），合法
  
  -- 输出查询结果（截取前50个字符，避免输出过长）
  DBMS_OUTPUT.PUT_LINE('查询到的内容（前50字符）：' || SUBSTR(v_query_content, 1, 50));
END;
/

输出结果（类似）：

1

查询到的内容（前50字符）：这是一段用于测试LONG类型的文本（重复内容）（重复内容）（重复

坑点验证：若数据库LONG字段内容超过32760字节，用PL/SQL变量接收会报错：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

-- 模拟“插入超过32760字节的内容”（实际需用其他工具，这里仅展示报错场景）
-- 执行下面代码会报错：ORA-06502: PL/SQL: 数字或值错误 :  字符串缓冲区太小
DECLARE
  v_too_big LONG;
BEGIN
  -- 故意生成超过32760字节的文本（RPAD生成10000个字符，每个中文2字节，共20000字节？不，这里需注意：中文占多字节，实际需计算总字节数）
  -- 正确模拟：生成超过32760字节的内容（假设每个字符1字节，生成32761个字符）
  v_too_big := RPAD('a', 32761, 'a');  -- 长度32761字节，超过PL/SQL变量上限
  
  INSERT INTO long_demo (id, content)
  VALUES (2, v_too_big);  -- 报错：字符串缓冲区太小
END;
/

第四步：更新数据（UPDATE操作，支持）

更新LONG字段的内容，同样需注意PL/SQL变量的长度限制：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15

DECLARE
  v_update_content LONG;
BEGIN
  -- 准备更新后的文本
  v_update_content := '这是更新后的LONG文本' || RPAD('（更新内容）', 500, '（更新内容）');
  
  -- 更新LONG字段（UPDATE支持）
  UPDATE long_demo
  SET content = v_update_content
  WHERE id = 1;  -- WHERE用非LONG字段，合法
  
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('数据更新成功！');
END;
/

第五步：验证操作限制（禁止的操作）

下面是错误示例，展示LONG字段不能做的操作（执行会报错）：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11

-- 错误1：在WHERE子句中使用LONG字段（禁止）
-- 执行报错：ORA-00997: 非法使用 LONG 类型
SELECT * FROM long_demo WHERE content LIKE '%测试%';

-- 错误2：在SQL函数中使用LONG字段（禁止，如UPPER函数）
-- 执行报错：ORA-00997: 非法使用 LONG 类型
SELECT id, UPPER(content) FROM long_demo;

-- 错误3：在表达式中使用LONG字段（禁止，如拼接字符串）
-- 执行报错：ORA-00997: 非法使用 LONG 类型
UPDATE long_demo SET content = content || '（追加内容）' WHERE id = 1;

补充：如何解决“PL/SQL变量存不下大LONG数据”的问题？ 如果数据库LONG字段的内容超过32760字节（比如存了一篇100KB的文章），直接用PL/SQL的LONG变量接收会报错，此时需用 DBMS_LOB包（Oracle提供的处理大对象的工具），步骤如下：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

DECLARE
  v_clob CLOB;  -- 用CLOB类型接收（CLOB变量最大支持4GB，比LONG变量大得多）
BEGIN
  -- 1. 将LONG字段转换为CLOB类型（通过中间表或直接转换）
  SELECT TO_LOB(content)  -- TO_LOB函数：将LONG转为CLOB
  INTO v_clob
  FROM long_demo
  WHERE id = 1;
  
  -- 2. 操作CLOB（比如输出长度、截取内容）
  DBMS_OUTPUT.PUT_LINE('CLOB内容长度（字节）：' || DBMS_LOB.GETLENGTH(v_clob));
  DBMS_OUTPUT.PUT_LINE('CLOB前100字符：' || DBMS_LOB.SUBSTR(v_clob, 100, 1));
END;
/

RAW

我们可以使用RAW数据类型来存储二进制数据或字节串。例如，一个RAW类型的变量可以存储一个数字化图形。RAW类型数据和VARCHAR2类型数据类似，只是PL/SQL不对其进行解析而已。同样，在我们把RAW数据从一个系统传到另一个系统时，Oracle Net也不会对它做字符集转换。RAW类型包含了一个可以让我们指定最大长度的可选参数，上限为32767字节，语法如下：

RAW(maximum_size)

我们不能使用常量或变量来指定这个参数；而且参数的范围必须是在1到32767范围内。在数据库中RAW类型字段的最大长度是2000个字节，所以，不可以把超过2000字节的内容放到RAW类型字段中。我们可以把任何RAW类型插入到LONG RAW类型的数据库字段中，因为LONG RAW在数据库中是2^31字节既2G，但是不能把超过32767字节的LONG RAW类型放入RAW变量中。

2.3 ROWID和UROWID

在Oracle内部，每个数据表都有一个伪列ROWID，用于存放被称为ROWID的二进制值。每个ROWID代表了一行数据的存储地址。物理 ROWID能够标识普通数据表中的一行信息，而逻辑ROWID能够标识索引组织表(index-organized table)中的一行信息。其中ROWID类型只能存储物理内容，而UROWID(universal rowid)类型可以存储物理，逻辑或外来(non-Oracle)ROWID。

建议：只有在旧的应用程序中，为了兼容性我们才使用ROWID数据类型。对于新的应用程序，应该使用UROWID数据类型。

当我们把查询出来的ROWID放到ROWID变量时，可以使用内置函数ROWIDTOCHAR，这个函数能把二进制内容转换成18个字节的字符串；还有一个与之对应的CHARTOROWID函数，可以对该过程进行反操作，如果转换过程中发现字符串并不是一个有效的ROWID时，PL/SQL就会抛出预定义异常SYS_INVALID_ROWID。UROWID变量和字符串之间进行转换也可以直接使用赋值操作符。这时，系统会隐式地实现UROWID和字符类型之间的转换。

物理ROWID(Physical Rowid)可以让我们快速的访问某些特定的行。只要行存在，它的物理ROWID就不会改变。高效稳定的物理ROWID在查询行集合、操作整个集合和更新子集是很有用的。例如，我们可以在UPDATE或DELETE语句的WHERE子句中比较UROWID变量和ROWID伪列来找出最近一次从游标中取出的行数据。

物理ROWID有两种形式。10字节扩展ROWID格式(10-byte extended rowid format)支持相对表空间块地址并能辨识分区表和非分区表中的行记录。6字节限定ROWID格式支持向后兼容。

扩展ROWID使用检索出来的每一行记录的物理地址的base-64编码。例如，在SQL*Plus(隐式地将ROWID转换成字符串)中的查询：

1
2
3
4
5
6

SQL> SELECT ROWID, ename
SQL>  FROM emp
SQL> WHERE empno = 7788;
ROWID ENAME
------------------ ----------
AAAAqcAABAAADFNAAH SCOTT

OOOOOOFFFBBBBBBRRR这样的形式有四部分组成:

“000000"代表数据对象号(data object number)，如上例中的"AAAAqc”，能够辨识数据库段。同一段中的模式对象，都有着相同的数据对象号。

“FFF"代表文件号(file number)，如上例中的"AAB”，能辨识出包含行的数据文件。在数据库中，文件号是唯一的。

“BBBBBB"代表块号(block number)，如上例中的"AAADFN”，能辨识出包含行的数据块。块号是与它们所在的数据文件相关，而不是表空间。所以，两个在同一表空间的行数据，如果它们处于不同的数据文件中，也可能有着相同的块号。

“RRR"代表了行号(row number)，如上例中的"AAH”，可以辨识块中的行数据。

逻辑ROWID为访问特定行提供了最快的访问速度。Oracle在索引组织表基础上使用它们构建二级索引。逻辑ROWID没有持久的物理地址，当新数据被插入时，它的值就会在数据块上偏移。但是，如果一个行数据的物理位置发生变化，它的逻辑ROWID就无效了。

2.4 VARCHAR2

我们可以使用VARCHAR2类型来存储变长的字符数据。至于数据在数据库中的内部表现形式要取决于数据库的字符集。VARCHAR2类型需要指明数据长度，这个参数的上限是32767字节。语法如下：

1

VARCHAR2(maximum_size [CHAR | BYTE])

我们不能使用常量或变量来指定maximum_size值，maximum_size值的有效范围在1到32767之间。

对于长度不同的VARCHAR2类型数据，PL/SQL对它们的处理方式也是不同的。值小的PL/SQL会优先考虑到它的性能问题，而对于值大的 PL/SQL会优先考虑到内存的使用效率问题。截止点 (cutoff point) 为2000个字节。在2000字节以下，PL/SQL会一次性分配我们声明时所指定大小的空间容纳实际的值；2000字节或2000字节以上时，PL/SQL会动态的分配VARCHAR2的存储容量。比如我们声明两个VARCHAR2类型的变量，一个长度是1999字节，一个是2000字节，如果我们把长度为500字节的值分别分配给这两个变量，那么前者占用的空间就是1999字节而后者只需500字节。

如果我们采用字节形式而非字符形式指定最大值时，VARCHAR2(n)变量就有可能太小而不能容纳n个多字节字符。为了避免这个问题，就要使用 VARCHAR2(n CHAR)形式进行定义，这样，即使字符中包含多字节字符也不会出问题。所以，对于双字节或多字节字符集，我们可以指定单字节字符集中字符个数的 1/2或 1/3。

虽然PL/SQL字符变量相对比较长，但VARCHAR2类型的数据库字段最大长度为4000个字节。所以，不能把字节超过4000的VARCHAR2类型值插入VARCHAR2类型的数据库字段中。

我们可以把任何VARCHAR2(n)值插入一个LONG类型的数据库字段，因为LONG字段最大长度为2**31字节。但是，不能把LONG字段中检索出来的长度超过32767字节的值放到VARCHAR2(n)变量中。

如果声明时不使用CHAR或BYTE限定修饰词，初始化参数NLS_LENGTH_SEMANTICS会决定默认的设置。当PL/SQL过程编译时，这个参数的设置就会被记录下来，这样，当过程失效后被重新编译时就会被重新使用。

VARCHAR2的子类型

下面VARCHAR2的子类型的范围与VARCHAR2完全相同，它们只是VARCHAR2的一个别名而已。

  • STRING
  • VARCHAR

我们可以使用这些子类型来与ANSI/ISO和IBM类型兼容。注意：目前，VARCHAR和VARCHAR2有着相同意义，但是在以后的 PL/SQL版本中，为了符合SQL标准，VARCHAR有可能会作为一个单独的类型出现。所以最好使用VARCHAR2，而不是VARCHAR。

3. 本地字符型

被广泛使用的单字节ASCII和EBCDIC字符集很适合表现罗马字符，但有些亚洲语言，如汉语、日语等包含了成千上万个字符，这些语言中的一个字符就需要用两个或三个字节来表示。为了处理这些语言，Oracle提供了全球化支持，允许我们处理单字节和多字节字符数据，并在字符集之间进行数据转换。 Oracle还能让我们的应用程序运行在不同的语言环境中。

有了全球化支持，数字和日期格式会根据用户会话中所指定的语言约定(language convention)而自动进行调节。因此，全世界的用户可以使用他们母语来使用Oracle。Oracle支持一种数据库字符集和一种国家特有字符集两种，前者用于标识符和源代码，后者用于国家特有语言数据。NCHAR和NVARCHAR2类型用于存储本地字符集。

对比维度 数据库字符集（Database Character Set） 国家字符集（National Character Set）
核心存储内容 标识符（表名、列名）、源代码、普通文本 国家特有语言数据（中文、日文、阿拉伯文等）
支持的数据类型 CHAR、VARCHAR2、CLOB、LONG NCHAR、NVARCHAR2、NCLOB
数据库创建时是否必须指定 是（不指定则用默认值，如AL32UTF8） 否（但推荐指定，默认与数据库字符集关联）
多语言支持能力 有限（若用单字节字符集如US7ASCII，无法存中文） 强（默认用UTF-16/UTF-8，支持所有语言）
典型使用示例 表名EMP、列名EMP_ID、文本'Salary' 中文'员工姓名'、日文'社員番号'

创建含两种字符集类型的表

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

-- 表中同时包含：
-- 1. VARCHAR2（依赖数据库字符集）：存普通文本/标识符关联数据
-- 2. NVARCHAR2（依赖国家字符集）：存多语言数据
CREATE TABLE global_demo (
  emp_id NUMBER PRIMARY KEY,
  emp_name VARCHAR2(50),  -- 依赖数据库字符集（如存英文姓名）
  emp_name_cn NVARCHAR2(50),  -- 依赖国家字符集（存中文姓名）
  emp_name_jp NVARCHAR2(50)   -- 依赖国家字符集（存日文姓名）
);
/

插入多语言数据

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

BEGIN
  -- 插入数据：普通文本用VARCHAR2，多语言用NVARCHAR2
  INSERT INTO global_demo (emp_id, emp_name, emp_name_cn, emp_name_jp)
  VALUES (1, 'Tanaka Taro', '田中太郎', '田中 太郎');  -- 中文、日文由国家字符集存储
  
  INSERT INTO global_demo (emp_id, emp_name, emp_name_cn, emp_name_jp)
  VALUES (2, 'Wang Wei', '王伟', '王 偉');
  
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('多语言数据插入成功！');
END;
/

查询数据（验证字符集正常显示）

1
2
3

-- 查询时，多语言数据（NVARCHAR2）能正常显示，依赖国家字符集的支持
SELECT emp_id, emp_name, emp_name_cn, emp_name_jp
FROM global_demo;

查询结果（正常显示多语言）：

emp_id emp_name emp_name_cn emp_name_jp
1 Tanaka Taro 田中太郎 田中 太郎
2 Wang Wei 王伟 王 偉

注意，当在数据库的不同字符集之间转换CHAR或VARCHAR2数据时，要确保数据保持良好的形式(well-formed)。

比较 UTF8 vs AL16UTF16

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

国家字符集（Unicode）
  ↓ 基于两种编码
  ├─ AL16UTF16（2字节/字符，可靠，费空间）
  └─ UTF8（1-3字节/字符，省空间，易截断）
  ↓ 对应两种数据类型
  ├─ NCHAR（定长）
  │  ├─ 语法：NCHAR[(字符数)]，默认1字符
  │  ├─ 限制：PL/SQL最多32767字节，数据库字段最多2000字节
  │  └─ 特性：不足长度补空格
  └─ NVARCHAR2（变长）
     ├─ 语法：NVARCHAR2(字符数)，必须指定长度
     ├─ 限制：PL/SQL最多32767字节，数据库字段最多4000字节
     └─ 特性：不足长度不补空格
  ↓ 与普通类型的转换
     ├─ 普通→国家：安全
     └─ 国家→普通：可能丢失数据（显示?）

国家字符集专门存储 Unicode 多语言数据，依赖两种编码方式： UTF8 和 AL16UTF16。

编码方式 字符占用空间 优势 劣势 适用场景
AL16UTF16 固定2字节/字符 长度计算简单（1字符=2字节），避免截断错误 存储ASCII字符（如英文）占用空间多（本可1字节却用2字节） 多语言混合场景（如中日英混存），优先保证可靠性
UTF8 变长（1-3字节/字符） 存储单字节字符（如英文）更省空间 长度计算复杂（同字符数可能占不同字节），易发生截断错误 以单字节语言（如英文）为主，需节省空间的场景

每个使用AL16UTF16编码的字符都占用2个字节。这将简化字符串的长度计算，避免采用混合语言编程时发生截断错误，但是这会比ASCII字符所组成的字符串需要更多空间。每个使用UTF8编码的字符占用1、2或3个字节。这就能让我们把更多的字符放到变量或数据表的字段中，但这只是在大多数字符用单字节形式表现的条件下才能做到。这种编码在传递数据到字节缓冲器时可能会引起截断错误。

Oracle公司推荐使用默认的AL16UTF16编码，这样可以获取最大的运行时可靠性。如果想知道一个Unicode字符串占用多少字节，就要使用LENGTHB函数，而不是LENGTH。

NCHAR（定长国家字符类型）

  • 作用：存储固定长度的多语言字符（不足长度自动补空格）。
  • 语法：NCHAR[(maximum_size)]，maximum_size是字符数（不是字节），必须用整数文字（不能用常量/变量）。
  • 长度限制：
    • 物理上限：32767字节（PL/SQL变量中）。
      → 因编码不同，最大字符数不同：
      • AL16UTF16（2字节/字符）：最多32767÷2≈16383字符；
      • UTF8（平均1-3字节/字符）：最多32767÷3≈10922字符。
    • 数据库字段上限：2000字节（插入超过会报错）。
  • 默认值：不指定maximum_size时，默认1个字符。

我们用NCHAR类型来储存定长国家特有字符数据。数据的内部表现取决于数据库创建时指定的国家特有字符集，字符集可能采用变长编码(UTF8)或定长编码(AL16UTF16)。因为这种类型总是与多字节字符兼容，所以我们可以使用它支持任何Unicode字符数据。NCHAR数据类型可接受一个可选参数来让我们指定字符的最大长度。语法如下：

1

NCHAR[(maximum_size)]

因为物理限制是32767个字节，所以在AL16UTF16编码格式下最大长度为32767/2，UTF8编码格式下是32767/3。

我们不能使用常量或变量来指定最大值，只能使用整数文字。

如果我们没有指定最大长度，它默认值就为1。这个值总是代表字符的个数，不像CHAR类型，既可以采用字符形式又可以采用字节形式。

1

my_string NCHAR(100);   -- maximum size is 100 characters

NCHAR在数据库字段中的最大宽度是2000字节。所以，我们不能向NCHAR字段中插入值超过2000字节的内容。

如果NCHAR的值比NCHAR字段定义的宽度要小，Oracle就会自动补上空格，填满定义的宽度。

我们可以在语句和表达式中交互使用CHAR和NCHAR值。从CHAR转到NCHAR总是安全的，但在NCHAR值转换到CHAR的过程中，如果CHAR类型不能完全表现NCHAR类型的值，就会引起数据丢失。这样的数据丢失会导致字符看起来像问号(?)。

NVARCHAR2（变长国家字符类型）

  • 作用：存储可变长度的多语言字符（不足长度不补空格）。
  • 语法：NVARCHAR2(maximum_size)，maximum_size是字符数（不是字节），必须用整数文字。
  • 长度限制：
    • 物理上限：32767字节（PL/SQL变量中）。
      → 最大字符数同NCHAR：AL16UTF16≈16383字符，UTF8≈10922字符。
    • 数据库字段上限：4000字节（比NCHAR字段宽松）。
  • 必须指定长度：没有默认值，声明时必须写maximum_size。

我们可以使用NVARCHAR2数据类型来存储变长的Unicode字符数据。数据的内部表现取决于数据库创建时所指定的国家特有字符集，它有可能采用变长编码(UTF8)或是定长编码(AL16UTF16)。因为这个类型总与多字节兼容，我们可以用它来支持Unicode字符数据。

NVARCHAR2数据类型需要接受一个指定最大大小的参数。语法如下：

1

NVARCHAR2(maximum_size)

因为物理限制是32767个字节，所以在AL16UTF16编码格式下最大长度为32767/2，UTF8编码格式下是32767/3。

我们不能使用常量或变量来指定最大值，只能使用整数文字。

最大值总是代表字符的个数，不像CHAR类型，既可以采用字符形式又可以采用字节形式。

1

my_string NVARCHAR2(200);   -- maximum size is 200 characters

NVARCHAR2在数据库字段中的最大宽度是4000字节。所以，我们不能向NVARCHAR2字段中插入长度超过4000字节的值。

NCHAR/NVARCHAR2（国家字符类型）和CHAR/VARCHAR2（普通字符类型）可以互相转换，但有风险：

  • 安全转换：从CHAR→NCHAR、VARCHAR2→NVARCHAR2（因为国家字符集支持更多语言，普通字符集的内容都能被包含）。
  • 风险转换：从NCHAR→CHAR、NVARCHAR2→VARCHAR2（若普通字符集不支持国家字符集中的字符，会丢失数据，显示为问号“?”）。

我们可以在语句和表达式中交互使用VARCHAR2和NVARCHAR2值。从VARCHAR2向NVARCHAR2转换总是安全的，但在 NVARCHAR2值转换到VARCHAR2的过程中，如果VARCHAR2类型不能完全表现NVARCHAR2类型的值，就会引起数据丢失。这样的数据丢失会导致字符看起来像问号(?)。

4. LOB类型

LOB(large object)数据类型BFILE、BLOB、CLOB和NCLOB可以最大存储4G的无结构数据(例如：文本、图形、视频剪辑和音频等)块。并且，它们允许高效地随机地分段访问数据。

LOB类型和LONG、LONG RAW类型相比有几个不同的地方。比如，LOB(除了NCOLB)可以作为对象类型的一个属性，但LONG类型不可以。LOB的最大值是4G，而LONG只有2G。LOB支持随机访问数据，但LONG只支持顺序访问。

LOB类型中可以存储了LOB定位器，它能够指向存放于外部文件中的"大对象"，in-linein-line（行内存储）既LOB 数据的 “全部内容” 或 “部分小内容”，直接存储在它所属的 “表行数据所在的数据块” 中，不需要额外的物理块存储 LOB 数据 或 out-of-line（行外存储）的形式既LOB 数据的 “全部内容” 存储在 “独立的数据块” 中（这些块专门用于存储 LOB 数据，称为 LOB 段），而 LOB 所属的表行数据中，只存储一个 “LOB 定位器（LOB Locator）”—— 这个定位器相当于 “地址”，指向独立 LOB 数据块的位置。BLOB、CLOB、NCLOB 或 BFILE 类型的数据库字段存储了定位器。其中 BLOB、CLOB和NCLOB 的数据存在数据库中，in-line (inside the row) 或 out-of-line (outside the row) 的形式，而 BFILE 数据存在数据库之外的操作系统文件中。

PL/SQL是通过定位器来操作LOB的。例如，当我们查询出一个BLOB值，只有定位器被返回。如果在事务中得到定位器，LOB定位器就会包含事务的ID号，这样我们就不能在另外一个事务中更新LOB内容了。同样，我们也不能在一个会话中操作另外一个会话中的定位器。

从9i开始，我们也可以把CLOB类型转成CHAR和VARCHAR2类型或是把BLOB转成RAW，反之亦然，这样，我们就能在大多数SQL和PL/SQL语句和函数中使用LOB类型了。要读、写和分段的操作LOB，我们可以使用Oracle系统包DBMS_LOB。

BFILE

BFILE数据类型用于存储二进制对象，它将存储的内容放到操作系统的文件中，而不是数据库内。每个BFILE变量都存储一个文件定位器，它指向服务器上的一个大的二进制文件。定位器包含目录别名，该别名给出了文件全路径。

BFILE类型是只读的，而且它的大小要依赖于系统，不能超过4G。我们的DBA要确保给定的BFILE存在且Oracle有读取它的权限。

BFILE并不参与事务，是不可恢复，不能被复制。能够被打开的BFILE最大数是由Oracle初始化参数SESSION_MAX_OPEN_FILES决定的。

BLOB、CLOB和NCLOB

BLOB数据类型可以在数据库中存放不超过4G的大型二进制对象；CLOB和NCLOB可以在数据库中分别存储大块CHAR类型和NCHAR类型的字符数据，都支持定宽和变宽字符集。同BFILE一样，这三个类型也都储存定位器，指向各自类型的一个大数据块。数据大小都不能超过4G。BLOB、 CLOB和NCLOB都可以在事务中使用，能够被恢复和复制。DBMS_LOB包可以对它们更改过的内容进行提交或回滚操作。BLOB、CLOB和 NCLOB的定位器都可以跨事务使用，但不能跨会话使用。

5. 布尔类型

布尔类型能存储逻辑值TRUE、FALSE和NULL(NULL代表缺失、未知或不可用的值)。只有逻辑操作符才允许应用在布尔变量上。

数据库SQL类型并不支持布尔类型，只有PL/SQL才支持。所以就不能往数据库中插入或从数据库中检索出布尔类型的值。

我们还可以使用各种函数来操作日期时间类型，如EXTRACT等。

6. 使用日期和时间子类型来避免"切断"问题

对于某些日期和时间类型的默认精度都要小于它们对应的最大精度。例如，DAY TO SECOND的默认精度是DAY(2) TO SECOND(6)，而最大精度是DAY(9) TO SECOND(9)。为了避免在使用这些类型进行赋值和传递参数时丢失精度，我们可以声明它们的子类型，这些类型都采用了最大的精度：

  • TIMESTAMP_UNCONSTRAINED
  • TIMESTAMP_TZ_UNCONSTRAINED
  • TIMESTAMP_LTZ_UNCONSTRAINED
  • YMINTERVAL_UNCONSTRAINED
  • DSINTERVAL_UNCONSTRAINED

二、用户自定义子类型

每个PL/SQL基类型都有对应的值集合和操作符集合。子类同样会指定同其基类型相同的值集合和操作符集合的子集作为它自己的值集合和操作符集合。所以说子类并不是一个新类型，它只是在基类的基础上添加了一个可选的约束。

子类可以增加可读性和兼容性。PL/SQL在STANDARD包里预定义了一些子类型。如下例：

1
2

SUBTYPE CHARACTER IS CHAR;
SUBTYPE INTEGER IS NUMBER(38, 0);   -- allows only whole numbers

子类型CHARACTER和基类型完全一样，所以CHARACTER是一个未作约束的子类型。但是，子类型INTEGER将基类NUMBER的值集合的子集作为自己的值集合，所以INTEGER是一个约束的子类型。

1. 定义子类型

我们可以在任何PL/SQL块、子程序或包中定义自己的子类型，语法如下：

1

SUBTYPE subtype_name IS base_type[(constraint)] [NOT NULL];

subtype_name就是声明的子类型的名称，base_type可以是任何标量类型或用户定义类型，约束只是用于限定基类型的精度和数值范围，或是最大长度。下面举几个例子：

1
2
3
4
5
6
7
8

DECLARE
 SUBTYPE birthdate IS DATE NOT NULL;   -- based on DATE type SUBTYPE counter IS NATURAL;   -- based on NATURAL subtype TYPE namelist IS TABLE OF VARCHAR2(10); SUBTYPE dutyroster IS namelist;   -- based on TABLE type TYPE timerec IS RECORD(
   minutes   INTEGER,
   hours     INTEGER
 ); 
 
 SUBTYPE finishtime IS timerec;   -- based on RECORD type 
 SUBTYPE id_num IS emp.empno%TYPE;   -- based on column type

我们可以使用%TYPE或%ROWTYPE来指定基类型。当%TYPE提供数据库字段中的数据类型时，子类型继承字段的大小约束(如果有的话)。但是，子类型并不能继承其他约束，如NOT NULL。

2. 使用子类型

一旦我们定义了子类型，我们就可以声明该类型的变量、常量等。下例中，我们声明了Counter类型变量，子类型的名称代表了变量的使用目的：

1
2
3

DECLARE
 SUBTYPE counter IS NATURAL; 
 ROWS   counter;

下面的例子演示了如何约束用户自定义子类型：

1
2
3

DECLARE
 SUBTYPE accumulator IS NUMBER; 
 total   accumulator(7, 2);

子类型还可以检查数值是否越界来提高可靠性。下例中我们把子类型Numeral的范围限制在-9到9之间。如果程序把这个范围之外的数值赋给Numeral类型变量，那么PL/SQL就会抛出一个异常。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14

DECLARE
  -- 定义子类型 numeral，限定为 1位数字（整数，无小数位）
  SUBTYPE numeral IS NUMBER(1, 0); 
  
  -- 声明变量，类型为自定义子类型 numeral
  x_axis  numeral;  -- 取值范围限定为 -9 .. 9（1位整数）
  y_axis  numeral;
BEGIN
  -- 赋值 10 给 x_axis，超出 numeral 类型的范围（1位整数）
  x_axis := 10;  -- 执行时会抛出 VALUE_ERROR 异常
  
  -- 后续逻辑（因异常此处不会执行）
  ...
END;

3. 类型兼容

一个未作约束的子类型是可以和它的基类型交互使用。例如下面的声明，amount值可以在不用转换的情况下直接赋给total：

1
2
3
4
5
6
7
8
9

DECLARE
 SUBTYPE accumulator IS NUMBER; 
 amount   NUMBER(7, 2);
 total    accumulator;
BEGIN
 ...
 total    := amount;
 ...
END;

如果基类型相同，那么不同的子类型也是可以交互使用。例如，下面的声明中，finished的值就可以赋给debugging：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

DECLARE
 SUBTYPE sentinel IS BOOLEAN; 
 SUBTYPE SWITCH IS BOOLEAN; 
 finished    sentinel;
 debugging   SWITCH;
BEGIN
 ...
 debugging    := finished;
 ...
END;

不同的子类型也是有可能交互使用，只要它们的基类型属于同一个数据类型种类。例如下面的声明中，verb值就能赋给sentence：

 1
 2
 3
 4
 5
 6
 7
 8
 9
10

DECLARE
 SUBTYPE word IS CHAR(15); 
 SUBTYPE text IS VARCHAR2(1500); 
 verb       word;
 sentence   text(150);
BEGIN
 ...
 sentence    := verb;
 ...
END;

三、 数据类型转换

有时我们需要把一个值从一个类型转换成另一个类型。例如，如果我们想检查一个ROWID，我们就必须把它转成一个字符串。PL/SQL支持显式和隐式(自动的)数据类型转换。

1. 显式转换

如果要进行类型间的转换，我们可以使用内置函数。例如将一个CHAR类型的值转换成一个DATE或NUMBER类型的值，我们就可以使用函数TO_DATE和TO_NUMBER。反过来，如果从DATE或NUMBER转成CHAR的话，可以使用TO_CHAR函数。

2. 隐式转换

PL/SQL有时会帮助我们进行隐式地数据类型转换。下面的例子中，CHAR型变量start_time和finish_time都有一个代表从午夜开始后所经过的秒数的值。这两个变量的差值要放到elapsed_time中去。所以，PL/SQL会自动地把CHAR类型转换成NUMBER类型。

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

DECLARE
  start_time    CHAR(5);       -- 存储开始时间（午夜后的秒数，字符型）
  finish_time   CHAR(5);       -- 存储结束时间（午夜后的秒数，字符型）
  elapsed_time  NUMBER(5);     -- 存储耗时（秒数，数值型）
BEGIN
  /* 获取系统时间，格式为午夜后的秒数（0-86399） */
  SELECT TO_CHAR(SYSDATE, 'SSSSS')
    INTO start_time
    FROM SYS.DUAL;

  -- 此处执行需要计时的操作（示例中省略具体逻辑）
  -- do something

  /* 再次获取系统时间 */
  SELECT TO_CHAR(SYSDATE, 'SSSSS')
    INTO finish_time
    FROM SYS.DUAL;

  /* 计算耗时（结束时间 - 开始时间，单位：秒） */
  elapsed_time := finish_time - start_time;

  -- 将耗时插入结果表（省略部分字段）
  INSERT INTO results
       VALUES (elapsed_time, ...);
END;

另外，在把查询的结果赋给变量之前，如果有必要的话PL/SQL也会把原值类型转成对应的变量类型，典型的例子就是把DATE类型放入VARCHAR2变量中。

同样，在把变量值赋给数据库字段时，PL/SQL在必要的情况下，也会进行数据类型转换。如果PL/SQL无法确定采用哪种转换形式，就会发生变异错误。这种情况下，我们就必须使用类型转换函数。表格中的 “X” 代表 “支持隐式转换”（即 PL/SQL 会自动完成转换，无需手动写函数）。

源类型\目标类型 BIN_INT BLOB CHAR CLOB DATE LONG NUMBER PLS_INT RAW UROWID VARCHAR2
BIN_INT X X X X
BLOB X
CHAR X X X X X X X X X X
CLOB X X
DATE X X
LONG X X X
NUMBER X X X X X
PLS_INT X X X X X
RAW X X X
UROWID X X
VARCHAR2 X X X X X X X X X

注意，列表中只列出表现不同的类型，而那些表现相同的类型，如CLOB和NCLOB，CHAR和NCHAR，还有VARCHAR2和NVARCHAR2都是可以互相替换。

要在CLOB和NCLOB之间转换，我们必须使用转换函数TO_CLOB和TO_NCLOB。

TIMESTAMP，TIMESTAMP WITH TIME ZONE，TIMESTAMP WITH LOCAL TIME ZONE，INTERVAL DAY TO SECOND和INTERVAL YEAR TO MONTH 都可以作为DATE类型用同样的转换规则进行转换。但是由于它们的内部表现形式不同，这些类型不能相互转换。

3. 显式转换 VS 隐式转换

通常，依赖隐式的数据转换不是一个好习惯，因为这样做会引起性能问题并可能随着软件版本变更而影响到后续的版本。隐式转换是与环境相关的，它的转换结果有时是无法预知的。所以，最好还是使用类型转换函数。这样的话，程序就有更好的可读性和可维护性。

4. DATE值

当把一个查询出来的DATE类型值放到CHAR或VARCHAR2变量中时，PL/SQL必须将内置的二进制值转成字符值，它就会调用 TO_CHAR将日期按照默认的日期格式转成字符值。同样，把一个CHAR或VARCHAR2插入到DATE类型的字段中，也是需要进行类型转换的， PL/SQL会调用TO_DATE将字符按照默认的日期格式转成日期类型的值。如果对转换有特殊要求，我们就得显式地指明转换格式。

5. RAW和LONG

RAW值在把查询出来的RAW或LONG RAW类型值赋给CHAR类型或VARCHAR2类型变量的时候，PL/SQL必须把内置的二进制值转成字符值。这种情况下，PL/SQL会把每个RAW 或LONG RAW类型的二进制字节转成等值的一对十六进制字符值。比如PL/SQL会把二进制11111111转换成字符"FF"。函数RAWTOHEX也具有这样的功能。把CHAR或VARCHAR2类型值插入RAW或LONG RAW字段时也是需要类型转换的。变量中的每对字符要转成等值二进制字节，如果无法进行正常的转换，PL/SQL就会抛出异常。简单说二进制数据 “穿了十六进制字符的外衣”，就能被 CHAR/VARCHAR2 存储；反之，“外衣”（十六进制字符）脱掉后，又能变回二进制数据存入 RAW。

小结

在数据库时代，特别是Oracle数据库时代，PL/SQL已经成为了数据库应用程序开发的标准语言。