3.1 SQL数据类型
3.1.1 不同的最大大小
SQL数据类型在PL/SQL和SQL中有不同的最大值,如下:
类型 在PL/SQL中的最大值 在SQL中的最大值
CHAR 32767 bytes 2000 bytes
NCHAR 32767 bytes 2000 bytes
RAW 32767 bytes 2000 bytes
VARCHAR2 32767 bytes 4000 bytes
NVARCHAR2 32767 bytes 4000 bytes
LONG 32767 bytes 2GB - 1
LONG RAW 32767 bytes 2GB
BLOB 128TB (4GB - 1) * database_block_size
CLOB 128TB (4GB - 1) * database_block_size
NCLOB 128TB (4GB - 1) * database_block_size
3.1.2 针对BINARY_FLOAT和BINARY_DOUBLE的额外的PL/SQL常量
BINARY_FLOAT表示单精度IEEE-754格式的浮点数
BINARY_DOUBLE表示双精度IEEE-754格式的浮点数
它们的计算不会抛出异常,所以你必须通过与预定义的一些常量进行比较,来检查它们计算产生
的值是否溢出等,PL/SQL比SQL有更多的此类常量
下面列出了为BINARY_FLOAT和BINARY_DOUBEL预定义的一些常量,带星号的表示SQL也有
BINARY_FLOAT_NAN(*)
表示不是一个数值
BINARY_FLOAT_INFINITY(*)
BINARY_FLOAT_MAX_NORMAL
BINARY_FLOAT_MIN_NORMAL
BINARY_FLOAT_MAX_SUBNORMAL
BINARY_FLOAT_MIN_SUBNORMAL
BINARY_DOUBLE_NAN(*)
表示不是一个数值
BINARY_DOUBLE_INFINITY(*)
BINARY_DOUBLE_MAX_NORMAL
BINARY_DOUBLE_MIN_NORMAL
BINARY_DOUBLE_MAX_SUBNORMAL
BINARY_DOUBLE_MIN_SUBNORMAL
3.1.3 针对BINARY_FLOAT和BINARY_DOUBLE的额外的PL/SQL子类型
PL/SQL预定义了如下的子类型:
- SIMPLE_FLOAT,是SQL数据类型BINARY_FLOAT的子类型
- SIMPLE_DOUBLE,是SQL数据类型BINARY_DOUBLE的子类型
每个子类型都具有和其基类型相同的取值范围和非空约束
如果你知道一个变量永远不会有NULL值,那就可以将其声明为SIMPLE_FLOAT或SIMPLE_DOUBLE类型
由于不再需要空值判断,所以子类型比基类型更好的性能
PLSQL_CODE_TYPE='NATIVE'要比PLSQL_CODE_TYPE='INTERPRETED'的性能提升更好
3.1.4 CHAR和VARCHAR2类型的变量
赋值或插入太长的值
赋值给变量超出其声明的大小,会报错
给表字段插入超出其定义大小的值,也会报错
为多字节字符声明变量
CHAR或VARCHAR2类型变量的最大大小是32767字节,不管你声明变量时指定的单位是字符还是字节
变量中能存放的最大字符数取决于字符集类型和某些字符本身(有时)
字符集类型 变量可以存放的最大字符数
单字节字符集 32767
n字节固定宽度的多字节字符集(例如AL16UTF16) FLOOR(32767/n)
n字节可变宽度的多字节字符集(例如AL32UTF8) 取决于字符本身,范围从FLOOR(32767/n)到32767
当声明CHAR变量或VARCHAR2变量时,为确保它总是能存放n个字符,不管使用任何字符集,那就
需要以字符为单位来声明它们的长度: CHAR(n CHAR) 或 VARCHAR2(n CHAR) 其中n不能超过
FLOOR(32767/4) 即 8191这个数值
CHAR和VARCHAR2数据类型的不同
预定义的子类型
CHAR数据类型有一个在PL/SQL和SQL同时都有的预定义子类型: CHARACTER
VARCHAR2数据类型有一个在PL/SQL和SQL同时都有的预定义子类型: VARCHAR ,同时它在PL/SQL
中还有一个预定义的子类型: STRING
每种子类型都具有和它的基类型相同的值范围
空白补齐
在使用CHAR和VARCHAR2时空白补齐是不同的
考虑如下的情况:
-
你给变量赋的值小于变量定义的最大长度 -
你给字段插入的值小于字段定义的宽度 -
如果数据的接收者的类型是CHAR,那么PL/SQL会使用空白将它补齐至最大长度你从字段中获取的值要插入一个变量,而且小于变量定义的最大长度
如果数据的接收者的类型是VARCHAR2,那么PL/SQL既不会用空白补齐也不会删除尾部的空白 值比较
SQL中的字符值的比较规则也应用于PL/SQL字符变量,在比较中,如果至少有一个值的类型是
VARCHAR2或NVARCHAR2,非空白补齐的比较语义就其作用,否则空白补齐的比较语义会起作用
3.1.5 LONG和LONG RAW类型的变量
注意:
支持LONG和LONG RAW数据类型仅仅是为了向后兼容性,对于新的应用程序
不要使用LONG,而应该使用VARCHAR2(32760), BLOB, CLOB或NCLOB
不要使用LONG RAW,而应该使用BLOB
你可以向LONG类型的字段插入LONG类型的值,也可以向LONG RAW类型的字段插入LONG RAW类型的值
你不能从LONG或LONG RAW类型的字段中获取超过32760字节的值赋给LONG或LONG RAW类型的变量
你可以向LONG RAW类型的字段中插入任何RAW类型的值,你不能从LONG RAW类型的字段中获取
超过32760字节的值赋给RAW类型的变量
3.1.6 ROWID和UROWID类型的变量
当你获取一个rowid值赋给一个ROWID类型变量时,应该使用 ROWIDTOCHAR 函数将这个二进制值转换为字符值
要想转换一个ROWID类型变量的值为一个rowid,需要使用 CHARTOROWID 函数
如果值不能表示一个有效的rowid,PL/SQL会抛出一个预定义的异常SYS_INVALID_ROWID
要想获取一个rowid值赋给UROWID变量,或者转换一个UROWID变量的值成一个rowid,直接使用赋值语句即可,
转换是隐式的
注意:
UROWID类型要比ROWID更通用,因为它与逻辑rowid和物理rowid都兼容
当你更新表中的一行已使用HCC(Hybrid Columnar Compression)压缩的数据时,ROWID会发生变化
3.2 布尔数据类型
PL/SQL数据类型BOOLEAN存储了逻辑值,分别是TRUE,FALSE和NULL,其中NULL表示未知的值
声明BOOLEAN类型变量的语法如下:
变量名 BOOLEAN
你唯一可以赋给BOOLEAN变量的就是BOOLEAN表达式
由于SQL中没有与BOOLEAN对应的数据类型,所以你不可以:
- 将BOOLEAN值插入表字段中
- 从表字段中获取的值赋给BOOLEAN类型变量
- 在SQL函数中使用BOOLEAN变量(然而,一个SQL查询可以调用一个拥有BOOLEAN类型参数的PL/SQL函数)
- 在SQL查询中使用BOOLEAN表达式(除了在SQL查询或PL/SQL匿名块中作为参数被调用) 注意:
在静态SQL查询中调用PL/SQL函数,该函数的参数如果是BOOLEAN类型,不能是字面量,必须将BOOLEAN
字面量赋给一个BOOLEAN变量,再把这个变量传给函数
你不能把BOOLEAN值传递给DBMS_OUTPUT.PUT或DBMS_OUTPUT.PUT_LINE子程序,为了打印一个BOOLEAN值,
你可以使用IF或CASE语句将其转换成一个字符值来打印
DBMS_OUTPUT.PUT_LINE(
CASE
WHEN b IS NULL THEN 'Unknown'
WHEN b THEN 'Yes'
WHEN NOT b THEN 'No'
END
);
下面是一个在SQL语句中调用PL/SQL函数(使用BOOLEAN类型参数)的例子
FUNCTION f (x BOOLEAN, y PLS_INTEGER)
RETURN employees.employee_id%TYPE
AUTHID CURRENT_USER
AS
BEGIN
IF x THEN
RETURN y;
ELSE
RETURN 2 * y;
END IF;
END;
-- 调用
DECLARE
name employees.last_name%TYPE;
b BOOLEAN := TRUE;
BEGIN
SELECT last_name INTO name
FROM employees
WHERE employee_id = f(b, 100)
DBMS_OUTPUT.PUT_LINE(name)
b := FALSE;
SELECT last_name INTO name
FROM employees
WHERE employee_id = f(b, 100);
DBMS_OUTPUT.PUT_LINE(name);
3.3 PLS_INTEGER和BINARY_INTEGER数据类型
PL/SQL数据类型PLS_INTEGER和BINARY_INTEGER是完全相同的,为了简单起见,本文档使用PLS_INTEGER
来代表PLS_INTEGER和BINARY_INTEGER
PLS_INTEGER数据类型存储有符号整数,范围是 -2147483648 ~ 2147483647 (32位字节)
相比NUMBER数据类型和NUMBER的子类型,使用PLS_INTEGER有自己的好处:
- PLS_INTEGER的值需要更少的存储
- PLS_INTEGER的操作使用硬件计算,比较快 所以为了程序执行的效率,尽量使用PLS_INTEGER类型的值
3.3.1 阻止PLS_INTEGER溢出
如果两个PLS_INTEGER值的计算导致了溢出,就会抛出一个溢出异常
对于超出PLS_INTEGER取值范围的计算,可以使用INTEGER类型,这是一个NUMBER数据类型的预定义子类型
样例程序如下:
DELCARE
p1 PLS_INTEGER := 2137483647;
p2 PLS_INTEGER := 1;
n NUMBER;
BEGIN
n := p1 + p2;
END;
/
程序执行会报错:
ORA-01426 numeric overflow
只需要将p2的类型改为 p2 INTEGER := 1 即可顺利执行
3.3.2 预定义的PLS_INTEGER子类型
下面是PLS_INTEGER子类型
数据类型 数据描述
NATURAL 非负的PLS_INTEGER数值
NATURALN 非负的PLS_INTEGER数值(非空约束)
POSITIVE 正的PLS_INTEGER数值
POSITIVEN 正的PLS_INTEGER数值(非空约束)
SIGNTYPE PLS_INTEGER值: -1, 0和1(常用于程序中的三态逻辑)
SIMPLE_INTEGER PLS_INTEGER数值(非空约束)
PLS_INTEGER和它的子类型可以隐式转换为如下这些数据类型:
CHAR VARCHAR2 NUMBER LONG
上面这些数据类型(除了LONG)和PLS_INTEGER子类型都可以隐式转换为PLS_INTEGER类型
3.3.3 PLS_INTEGER的子类型: SIMPLE_INTEGER
SIMPLE_INTEGER是PLS_INTEGER的一个预定义子类型,它具有和PLS_INTEGER一样的取值范围,
但是要多一个非空约束,它和PLS_INTEGER的不同就在于溢出语义
如果你已知一个变量永远不会有空值,或者需要溢出检查,那最好使用SIMPLE_INTEGER类型而
不是PLS_INTEGER类型,无需检查是否为空和是否溢出,使得SIMPLE_INTEGER比PLS_INTEGER
的性能更好
SIMPLE_INTEGER溢出语义
如果一个表达式中的所有操作数都是SIMPLE_INTEGER类型,那么PL/SQL会使用
two's complement arithmetic,且忽略溢出
因为溢出被忽略,所以计算的结果值可以从负到正或者从正到付进行包裹,例如
2**30 + 2**30 = 0x40000000 + 0x40000000 = 0x80000000 = -2**31
-2**31 + -2**31 = 0x80000000 + 0x80000000 = 0x00000000 = 0
下面的程序不会报错
DECLARE
n SIMPLE_INTEGER := 2147483645;
BEGIN
FOR j IN 1..4
LOOP
n := n + 1;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(n, 'S9999999999'))
END LOOP;
FOR j IN 1..4
LOOP
n := n - 1;
DBMS_OUTPUT.PUT_LINE(TO_CHAR(n, 'S9999999999'));
END LOOP;
END;
/
输出结果如下:
+2147483646
+2147483647
-2147483648
-2147483647
-2147483648
+2147483647
+2147483646
+2147483645
含有SIMPLE_INTEGER和其他操作数的表达式
如果一个表达式既有SIMPLE_INTEGER类型的操作数,也有其他类型的操作数,那么PL/SQL
会隐式的将SIMPLE_INTEGER类型转换为PLS_INTEGER NOT NULL(非空约束)
对于这种SIMPLE_INTEGER类型与其他类型值混在一起的情况,PL/SQL编译器会提出一个警告,
会导致禁用某些优化,而对性能产生负面影响
在SIMPLE_INTEGER取值范围内的整数字面量
为了确保向后兼容性,当在计算表达式中所有操作数都是整数字面量时,PL/SQL会把整数字面量
都隐式的转换为PLS_INTEGER(如果可以的话)
3.4 用户自定义的PL/SQL子类型
PL/SQL允许你定义自己的子类型,基类型可以是任何标量或用户定义的PL/SQL数据类型
子类型可以:
- 提供与ANSI/ISO数据类型的兼容性
- 表示要使用的那种类型的数据项的意图(例如Balance表示金额,Counter表示计数)
- 查探范围外的值
3.4.1 无约束的子类型
无约束的子类型和它的基类型的取值是相同的,所以这种子类型算是基类型的一个别名,所以在
子类型之间,以及子类型和基类型之间,可以交换使用,不会导致数据转换发生
定义一个无约束的子类型的语法如下:
SUBTYPE 子类型名 IS 基类型名
例如,PL/SQL为了保持与ANSI标准的兼容性,预定义了:
SUBTYPE "DOUBLE PRECISION" IS FLOAT;
3.4.2 有约束的子类型
有约束的子类型的取值是其基类型的子集
如果基类型允许你可以指明size, precision和scale,或者值范围,那么你可以为子类型指明这些
定义一个有约束的子类型的语法如下:
SUBTYPE 子类型名 IS 基类型名 {precision [, scale] | RANGE 低值 .. 高值} [NOT NULL]
否则,你可以在子类型上放置的约束就只有 NOT NULL
注意
可以指定值范围的基类型只有PLS_INTEGER及其子类型
一个有约束的子类型的值可以隐式的转换为它的基类型,但是只有当基类型的值不违反有约束
子类型的约束时才可以隐式转换为子类型
一个有约束子类型可以隐式转换为另一个具有相同基类型的有约束子类型,只有当其不违反目标
子类型的约束的前提下
下面的示例程序中,有约束子类型可以查探出超出范围的值
DECLARE
SUBTYPE Balance IS NUMBER(8, 2);
checking_account Balance;
savings_account Balance;
BEGIN
checking_account := 2000.00;
savings_account := 1000000.00
END;
/
执行时会报错:
ORA-06502: PL/SQL: numeric or value error: number precision too large
3.4.3 在相同的数据类型族中的子类型和基类型
如果在同一数据类型族下的两个子类型有不同的基类型,只有当源值不违反目标子类型的约束时
才可以隐式的转换
样例程序如下:
DECLARE
SUBTYPE Word IS CHAR(6);
SUBTYPE Text IS VARCHAR2(15);
verb Word := 'run';
sentence1 Text;
sentence2 Text := 'Hurry!';
sentence3 Text := 'See Tom run.';
BEGIN
sentence1 := verb; -- OK,因为值是3个字符,约束是15个字符,不违反
verb := sentence2; -- OK,因为值是6个字符,约束是6个字符,不违反
verb := sentence3; -- 失败,因为值是12个字符,而约束是6个字符,违反
