第三章 PL/SQL数据类型

第三章 PL/SQL数据类型

预定义数据类型

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

下图是在PL/SQL中可以使用的预定义类型，其中标量类型又分为四类：数字、字符、布尔和日期/时间。

1、数字型

 

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

 

BINARY_INTEGER

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

 

BINARY_INTEGER子类型

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

 

NATURAL

NATURALN

POSITIVE

POSITIVEN

SIGNTYPE

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

 

NUMBER

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

 

NUMBER[(precision,scale)] 

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

NUMBER 

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

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。

 

NUMBER子类型

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

DEC

DECIMAL

DOUBLE PRECISION

FLOAT

INTEGER

INT

NUMERIC

REAL

SMALLINT

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

 

PLS_INTEGER

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

 

2、字符型

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

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类型相兼容。

 

LONG和LONG RAW

我们可以使用LONG类型来存储变长的字符串。除了LONG类型的最大长度是32760字节之外，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字节；但是，不可以从LONG字段中检索超过32760字节的字符放到LONG类型变量中去。同样，对于LONG RAW类型来说，这条规则同样适用，在数据库中它的最大长度也是2**31字节，而变量的长度在32760字节以内。LONG类型字段可以存储文本、字符数组或短文等。我们可以对LONG字段进行UPDATE、INSERT和SELECT操作，但不能在表达式、SQL函数调用、或某个SQL子句(如：WHERE、GROUP BY和CONNECT BY)中使用它。

 

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

 

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，但是不能把超过32767字节的LONG RAW类型放入RAW变量中。

 

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转换成字符串)中的查询：

 

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就无效了。

 

VARCHAR2

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

 

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)形式进行定义，这样，即使字符中包含多字节字符也不会出问题。所以，对于双字节或多字节字符集，我们可以指定单字节字符集中字符个数的