4. 数据类型
每个 PL/SQL 常量、变量、参数和函数返回值都有一个数据类型,它决定了它的存储格式以及它的有效值和操作。
本章解释了标量数据类型,它存储没有内部组件的值。
标量数据类型可以有子类型。子类型是一种数据类型,它是另一种数据类型的子集,另一种数据类型是它的基本类型。子类型具有与其基类型相同的有效操作。数据类型及其子类型构成数据类型族。
PL/SQL 在包 STANDARD 中预定义了许多类型和子类型,并允许您定义自己的子类型。
PL/SQL 标量数据类型有:
-
SQL 数据类型
-
BOOLEAN
-
PLS_INTEGER
-
BINARY_INTEGER
-
REF CURSOR
-
用户定义的子类型
4.1. SQL 数据类型
PL/SQL 数据类型包括 SQL 数据类型。
有关 SQL 数据类型的信息,请参阅《SQL 语言参考》 ——其中有关数据类型和子类型、数据类型比较规则、数据转换、文字和格式模型的所有信息都适用于 SQL 和 PL/SQL,除非此处另有说明:
-
不同的最大尺寸
-
BINARY_FLOAT 和 BINARY_DOUBLE 的其他 PL/SQL 子类型
与 SQL 不同,PL/SQL 允许您声明变量,以下主题适用: - CHAR 和 VARCHAR2 变量
4.1.1. 不同的最大尺寸
表中列出的 SQL 数据类型在 PL/SQL 和 SQL 中具有不同的最大大小。
数据类型 | PL/SQL 中的最大大小 | SQL 中的最大大小 |
---|---|---|
| 10*10*1024 字节 | 10*10*1024 字节 |
| 10*10*1024 字节 | 10*10*1024 字节 |
| (1G-1) 字节 | (1G-1) 字节 |
| (1G-1) 字节 | (1G-1) 字节 |
| (1G-1) 字节 | (1G-1) 字节 |
| (1G-1) 字节 | (1G-1) 字节 |
| (1G-1) 字节 | (1G-1) 字节 |
| (1G-1) 字节 | (1G-1) 字节 |
4.1.2. BINARY_FLOAT 和 BINARY_DOUBLE 的其他 PL/SQL 子类型
PL/SQL 预定义了这些子类型:
-
SIMPLE_FLOAT,SQL 数据类型 BINARY_FLOAT 的子类型
-
SIMPLE_DOUBLE,SQL 数据类型 BINARY_DOUBLE 的子类型
每个子类型都具有与其基本类型相同的范围,并且具有 NOT NULL 约束。
如果您知道变量永远不会有值 NULL,请将其声明为 SIMPLE_FLOAT 或 SIMPLE_DOUBLE,而不是 BINARY_FLOAT 或 BINARY_DOUBLE。 在没有检查空值的开销的情况下,子类型提供了比它们的基本类型更好的性能。
4.1.3. CHAR 和 VARCHAR2 变量
4.1.3.1. 分配或插入过长的值
如果分配给字符变量的值长于变量的最大大小,则会发生错误。例如:
\set SQLTERM / DECLARE c VARCHAR2(4 CHAR); BEGIN c := 'hello'; END; /
结果:
ERROR: value too long for type character varying(4) CONTEXT: PL/SQL function inline_code_block line 4 at assignment
同样,如果将字符变量插入到列中,并且变量的值长于定义的列宽度,则会发生错误。例如:
CREATE TABLE t1 (c CHAR(4 CHAR)); \set SQLTERM / DECLARE str VARCHAR2(5 CHAR) := 'hello'; BEGIN INSERT INTO t1(c) VALUES(str); END; /
结果:
ERROR: value too large for column "public"."t1"."c" (actual:5, maximum:4) CONTEXT: SQL statement "INSERT INTO t1(c) VALUES(str)" PL/SQL function inline_code_block line 4 at SQL statement
要在将字符值分配给变量或将其插入列之前从字符值中去除尾随空格,请使用SQL 语言参考TRIM中解释的函数。例如:
\set SQLTERM / DECLARE c VARCHAR2(3 CHAR); BEGIN c := TRIM(' qwe '); INSERT INTO t1(c) VALUES(TRIM(' qwe ')); END; /
结果:
ANONYMOUS BLOCK
4.1.3.2. 为多字节字符声明变量
CHAR 或 VARCHAR2 变量的最大大小为 32,767 字节,无论您以字符还是字节为单位指定最大大小。 变量中的最大字符数取决于字符集类型,有时还取决于字符本身:
字符集类型 | 最大字符数 |
---|---|
单字节字符集 | 32,767 |
n字节固定宽度多字节字符集 | FLOOR(32,767/n) |
n -byte 可变宽度多字节字符集,字符宽度介于 1 和n字节之间 | 取决于字符本身——可以是从32,767(对于仅包含1字节字符的字符串)到(对于仅包含n字节字符的字符串)的任何值。 |
声明 CHAR 或 VARCHAR2 变量时,为确保它始终可以容纳任何多字节字符集中的 n 个字符,请以字符形式声明其长度 — 即 CHAR(n CHAR) 或 VARCHAR2(n CHAR),其中 n 不超过 FLOOR (32767/4) = 8191。
4.1.3.3. CHAR 和 VARCHAR2 数据类型的区别
CHAR和VARCHAR2数据类型的不同之处在于: - 预定义的子类型 - 空白填充的工作原理 - 值比较
4.1.3.3.1. 预定义子类型
CHAR 数据类型在 PL/SQL 和 SQL 中都有一个预定义的子类型——CHARACTER。
VARCHAR2 数据类型在 PL/SQL 和 SQL 中都有一个预定义的子类型 VARCHAR,在 PL/SQL 中还有一个预定义的子类型 STRING。
每个子类型具有与其基本类型相同的值范围。
注意
在未来的 PL/SQL 版本中,为了适应新兴的 SQL 标准,VARCHAR 可能成为一种单独的数据类型,不再是 VARCHAR2 的同义词。
4.1.3.3.2. 空白填充的工作原理
这解释了在 CHAR 和 VARCHAR2 中使用空白填充的区别和注意事项。
考虑以下情况: - 您分配给变量的值小于变量的最大大小。 - 您插入到列中的值比定义的列宽度短。 - 您从列检索到变量中的值小于变量的最大大小。
如果接收者的数据类型是CHAR,PL/SQL 将值填充到最大大小。有关原始值中尾随空格的信息会丢失。
如果接收者的数据类型是VARCHAR2,PL/SQL 既不填充值也不去除尾随空格。字符值被原封不动地分配,不会丢失任何信息。
示例 4-1 CHAR 和 VARCHAR2 空白填充差异
在此示例中,CHAR 变量和 VARCHAR2 变量的最大大小为 10 个字符。 每个变量都接收一个由五个字符组成的值,后面有一个空格。
分配给 CHAR 变量的值被空白填充到 10 个字符,并且您无法判断结果值中的六个尾随空白之一在原始值中。
分配给 VARCHAR2 变量的值没有改变,您可以看到它有一个尾随空格。
\set SQLTERM / DECLARE chr CHAR(10 CHAR); varchr VARCHAR2(10 CHAR); BEGIN chr := 'test '; varchr := 'test '; raise notice 'chr = (%)',chr; raise notice 'varchr = (%)',varchr; END; /
结果:
NOTICE: chr = (test ) NOTICE: varchr = (test )
4.1.3.3.3. 值比较
比较字符值的 SQL 规则适用于 PL/SQL 字符变量。
每当比较中的一个或两个值具有数据类型 VARCHAR2 或 NVARCHAR2 时,应用非填充比较语义;否则,将应用空白填充语义。有关详细信息,请参阅SQL 语言参考。
4.2. 布尔数据类型
PL/SQL 数据类型BOOLEAN存储逻辑值,即布尔值 TRUE 和 FALSE 以及值 NULL。 NULL 表示未知值。
BOOLEAN声明变量 的语法是:
variable_name BOOLEAN
您可以分配给BOOLEAN变量的唯一值是BOOLEAN表达式。有关详细信息,请参阅“布尔表达式”。
您不能将 BOOLEAN 值传递给 DBMS_OUTPUT.PUT 或 DBMS_OUTPUT.PUTLINE 子程序。要打印 BOOLEAN 值,请使用 IF 或 CASE 语句将其转换为字符值(有关这些语句的信息,请参阅“条件选择语句”)。
示例 4-2 打印 BOOLEAN 值
在此示例中,过程接受 BOOLEAN 参数并使用 CASE 语句打印 Unknown 如果参数的值为 NULL,如果为 TRUE,则打印 Yes,如果为 FALSE,则打印 No。
\set SQLTERM / CREATE OR REPLACE PROCEDURE show_boolean (a BOOLEAN) AS BEGIN RAISE NOTICE '%',CASE WHEN a IS NULL THEN 'Unknown' WHEN a THEN 'Yes' WHEN NOT a THEN 'No' END; END; / BEGIN show_boolean(TRUE); show_boolean(FALSE); show_boolean(NULL); END; /
结果:
NOTICE: Yes NOTICE: No NOTICE: Unknown
示例 4-3 SQL 语句使用 BOOLEAN 参数调用 PL/SQL 函数
在这个例子中,一条 SQL 语句调用一个带有BOOLEAN参数的 PL/SQL 函数。
drop table if exists student; create table student (id int PRIMARY KEY, name text, score number); insert into student values (1,'xx',99); insert into student values (2,'xm',80); \set SQLTERM / CREATE OR REPLACE FUNCTION f1 (a BOOLEAN, b PLS_INTEGER) RETURN student.id%TYPE AUTHID CURRENT_USER AS BEGIN IF a THEN RETURN b; ELSE RETURN 2*b; END IF; END; / DECLARE name student.name%TYPE; b BOOLEAN := TRUE; BEGIN SELECT name INTO name FROM student WHERE id = f1(b, 1); raise notice 'name = %',name; b := FALSE; SELECT name INTO name FROM student WHERE id = f1(b, 1); raise notice 'name = %',name; END; /
结果:
NOTICE: name = xx NOTICE: name = xm
4.3. PLS_INTEGER 和 BINARY_INTEGER 数据类型
PL/SQL 数据类型 PLS_INTEGER 和 BINARY_INTEGER 是相同的。
为简单起见,本文档使用 PLS_INTEGER 来表示 PLS_INTEGER 和 BINARY_INTEGER。
PLS_INTEGER 数据类型存储 -2,147,483,648 到 2,147,483,647 范围内的带符号整数,以 32 位表示。
4.3.1. 防止 PLS_INTEGER 溢出
具有溢出 PLS_INTEGER 范围的两个 PLS_INTEGER 值的计算会引发溢出异常。
对于 PLS_INTEGER 范围之外的计算,请使用 INTEGER,这是 NUMBER 数据类型的预定义子类型。
示例 4-4 PLS_INTEGER 计算引发溢出异常
此示例显示具有溢出 PLS_INTEGER 范围的两个 PLS_INTEGER 值的计算会引发溢出异常,即使您将结果分配给 NUMBER 数据类型也是如此。
\set SQLTERM / DECLARE a1 PLS_INTEGER := 2147483647; a2 PLS_INTEGER := 1; num NUMBER; BEGIN num := a1 + a2; END; /
结果:
ERROR: integer out of range CONTEXT: PL/SQL function inline_code_block line 6 at assignment
4.3.2. 预定义的 PLS_INTEGER 子类型
此摘要列出了 PLS_INTEGER 数据类型的预定义子类型并描述了它们存储的数据。
数据类型 | 资料说明 |
---|---|
NATURAL | PLS_INTEGER非负值 |
NATURALN | PLS_INTEGER带NOT NULL约束的非负值 |
POSITIVE | 正值PLS_INTEGER |
POSITIVEN | PLS_INTEGER带NOT NULL约束的正值 |
SIGNTYPE | PLS_INTEGER值 -1、0 或 1(用于编程三态逻辑) |
SIMPLE_INTEGER | PLS_INTEGER有NOT NULL约束的值。 |
PLS_INTEGER它的子类型可以隐式转换为这些数据类型: - CHAR - VARCHAR2 - NUMBER
除所有 PLS_INTEGER 子类型外,上述所有数据类型都可以隐式转换为 PLS_INTEGER。
仅当 PLS_INTEGER 值不违反子类型的约束时,才能将其隐式转换为 PLS_INTEGER 子类型。
示例 4-5 违反 SIMPLE_INTEGER 子类型的约束
此示例显示将 PLS_INTEGER 值 NULL 转换为 SIMPLE_INTEGER 子类型会引发异常。
\set SQLTERM / DECLARE x SIMPLE_INTEGER := 1; y PLS_INTEGER := NULL; BEGIN x := y; END; /
结果:
ERROR: domain simple_integer does not allow null values CONTEXT: PL/SQL function inline_code_block line 5 at assignment
4.3.3. PLS_INTEGER 的 SIMPLE_INTEGER 子类型
SIMPLE_INTEGER 是 PLS_INTEGER 数据类型的预定义子类型。
SIMPLE_INTEGER 与 PLS_INTEGER 具有相同的范围并具有 NOT NULL 约束。 它在溢出语义上与 PLS_INTEGER 有很大不同。
如果您知道变量永远不会有值 NULL 或需要溢出检查,请将其声明为 SIMPLE_INTEGER 而不是 PLS_INTEGER。 在没有检查空值和溢出的开销的情况下,SIMPLE_INTEGER 的性能明显优于 PLS_INTEGER。
4.4. 用户定义的 PL/SQL 子类型
PL/SQL 允许您定义自己的子类型。
基本类型可以是任何标量或用户定义的 PL/SQL 数据类型说明符,例如 CHAR、DATE 或 RECORD(包括先前定义的用户定义子类型)。
注意
本主题中的信息适用于用户定义的子类型和PL/SQL 预定义数据类型中列出的预定义子类型。
子类型可以: - 显示该类型数据项的预期用途 - 检测超出范围的值
4.4.1. 无约束子类型
不受约束的子类型与其基类型具有相同的值集,因此它只是基类型的另一个名称。
因此,相同基本类型的无约束子类型可以相互互换,也可以与基本类型互换。 不发生数据类型转换。
要定义不受约束的子类型,请使用以下语法:
SUBTYPE subtype_name IS base_type
有关subtype_nameand的信息base_type,请参阅子类型。
PL/SQL 预定义以与 ANSI 兼容的无约束子类型的示例是:
SUBTYPE "DOUBLE PRECISION" IS FLOAT
示例 4-6 用户定义的无约束子类型显示预期用途
在此示例中,不受约束的子类型Balance和Counter显示其类型的数据项的预期用途。
\set SQLTERM / DECLARE SUBTYPE num_subtype IS NUMBER; num1 num_subtype(6,2); SUBTYPE natural_subtype IS NATURAL; nat1 natural_subtype := 1; PROCEDURE add_num ( num1 IN OUT num_subtype, num2 IN num_subtype ) IS BEGIN num1 := num1 + num2; nat1 := nat1 + 1; END; BEGIN NULL; END; /
4.4.2. 约束子类型
受约束的子类型仅具有其基本类型的值的子集。
如果基本类型允许您指定大小、精度和比例或值的范围,那么您可以为其子类型指定它们。子类型定义语法是:
SUBTYPE subtype_name IS base_type { precision [, scale ] | RANGE low_value .. high_value } [ NOT NULL ]
否则,您可以对其子类型施加的唯一约束是NOT NULL:
SUBTYPE subtype_name IS base_type [ NOT NULL ]
注意
您可以为其指定值范围的唯一基本类型是 PLS_INTEGER 及其子类型(预定义的和用户定义的)。
受约束的子类型可以隐式转换为其基类型,但只有当值不违反子类型的约束时,才能将基类型隐式转换为受约束的子类型。
仅当源值不违反目标子类型的约束时,才能将受约束的子类型隐式转换为具有相同基类型的另一个受约束的子类型。
示例 4-7 用户定义的约束子类型检测超出范围的值
在此示例中,受约束的子类型Balance检测超出范围的值。
\set SQLTERM / DECLARE SUBTYPE num_subtype IS NUMBER(8,2); num1 num_subtype; num2 num_subtype; BEGIN num1 := 1000.00; num2 := 2000000.00; END; /
结果:
ERROR: numeric field overflow DETAIL: A field with precision 8, scale 2 must round to an absolute value less than 10^6. CONTEXT: PL/SQL function inline_code_block line 7 at assignment
示例 4-8 具有相同基本类型的约束子类型之间的隐式转换
在此示例中,三个受约束的子类型具有相同的基本类型。 前两个子类型可以隐式转换为第三个子类型,但不能相互转换。
\set SQLTERM / DECLARE SUBTYPE typ1 IS PLS_INTEGER RANGE 0..9; SUBTYPE typ2 IS PLS_INTEGER RANGE 10..99; SUBTYPE typ3 IS PLS_INTEGER RANGE 0..99; val1 typ1 := 4; val2 typ2 := 35; val3 typ3; BEGIN val3 := val1; --true val1 := val2; --error val2 := val1; --error END; /
结果:
ERROR: numeric or value error CONTEXT: PL/SQL function inline_code_block line 10 at assignment
4.4.3. 具有相同数据类型族的基类型的子类型
如果两个子类型在同一数据类型族中具有不同的基类型,则只有在源值不违反目标子类型的约束时,才能将一个子类型隐式转换为另一种。
对于按数据类型族分组的预定义 PL/SQL 数据类型和子类型,请参阅 PL/SQL 预定义数据类型。
示例 4-9 具有相同系列的基本类型的子类型之间的隐式转换
在此示例中,子类型 chr6 和 varchr15 在同一数据类型系列中具有不同的基本类型。 第一个赋值语句将 chr6 值隐式转换为 varchr15。 第二个赋值语句将 varchr15 值隐式转换为 chr6 第三个赋值语句无法将 varchr15 值隐式转换为 chr6,因为该值太长。
\set SQLTERM / DECLARE SUBTYPE chr6 IS CHAR(6); SUBTYPE varchr15 IS VARCHAR2(15); word chr6 := 'hei'; val1 varchr15; val2 varchr15 := 'test'; val3 varchr15 := 'test_test_test'; BEGIN val1 := word; --true word := val2; --true word := val3; --error END; /
结果:
ERROR: value too long for type character(6) CONTEXT: PL/SQL function inline_code_block line 13 at assignment