再上一篇:11.7小结
上一篇:12.1Oracle数据类型概述
主页
下一篇:12.3二进制串:RAW类型
再下一篇:12.4数值类型
文章列表

12.2字符和二进制串类型

Oracle 9i 10g编程艺术:深入数据库体系结构

Oracle 中的字符数据类型包括CHAR、VARCHAR2以及带“N“的相应”变体“(NCHAR和NVARCHAR2), 这些字符数据类型能存储2,000字节或4,000字节的文本。这些文本会由数据库根据需要在不同字符集之 间转换。字符集(chrarcter set)是各个字符的一种二进制表示(用位和字节表示)。目前有多种不同的 字符集,每种字符集能表示不同的字符,例如:
US7ASCII字符集是128字符的ASCII标准表示。它使用字节的低7位表示这128个字符。 WE8ISO8859P1字符集是一种西欧字符集,不仅能不是128个ASCII字符,还能表示128个
扩展字符,这个字符集使用了字节的全部8 位。
在深入分析CHAR、VARCHAR2及带“N“的变体(NCHAR和NVARCHAR2)之前,我们先来简要地了解这 些不同的字符集对于我们意味着什么,搞清楚这一点会很有帮助。

12.2.1 NLS概述

如前所述,NLS代表国家语言支持(National Language Support)。NLS是数据库的一个非常强大的 特性,但是人们往往未能正确理解这个特性。NLS控制着数据的许多方面。例如,它控制着数据如何存储; 还有我们在数据中是会看到多个逗号和一个句号(如12.000,000.01),还是会看到多个点号和一个逗号(如
12.000.000,01)。但是最重要的,它控制着以下两个方面: 文本数据持久存储在磁盘上时如何编码 透明地将数据从一个字符集转换到另一个字符集
正是这个透明的部分最让人困惑,它实在太透明了,以至于我们甚至不知道发生了这种转换。下面来 看一个小例子:
假设你在数据库中用WE8ISO8859P1字符集存储8位的数据,但是你的某些客户使用的是一种7位字 符集,如US7ASCII。这些客户不想要 8位的数据,需要从数据库将数据转换为他们能用的形式。尽管听上 去不错,但是如果你不知道会发生这种转换,就会发现,过一段时间后,数据会“丢失“字符,WE8ISO8859P1 字符集中的某些字符在US7ASCII中并没有,这些字符会转换为US7ASCII中的某个字符。原因就在于这里 发生了字符集转换。简而言之,如果你从数据库获取了使用字符集1 的数据,将其转换为使用字符集2, 再把这些数据插入回数据库中(完成以上的逆过程),就极有可能大幅修改数据。字符集转换过程通常会修 改数据,而你往往会把一个较大的字符集(在此例中就是8位字符集)映射到一个较小的字符集(此例中 的7位字符集)。这是一种有损转换(lossy conversion),字符就会被修改,这只是因为:较小的字符集 不可能表示较大字符集中的每一个字符。但是这种转换必须发生。如果数据库以一种单字节字符集存储数 据,但是客户(如一个Java应用,因为Java语言使用Unicode)希望数据采用多字节表示,就必须执行
转换,只有这样客户应用才能使用这些数据。 可以很容易地看到字符集转换。例如,我有一个数据库,它的字符集设置为WE8ISO8859P1,这是一

个典型的西欧字符集:
ops$tkyte@ORA10G> select *
2 from nls_database_parameters
3 where parameter = 'NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ ---------------------------------------- NLS_CHARACTERSET WE8ISO8859P1
现在,如果可以确保NLS_LANG设置为我的数据库字符集,如下(Windows用户要在注册表中修改/验

证这个设置):
ops$tkyte@ORA10G> host echo $NLS_LANG
AMERICAN_AMERICA.WE8ISO8859P1
然后,创建一个表,并放入一些“8位”数据,对于只希望得到7位ASCII数据的客户来说(以下将

这些客户称为“7位客户”),它们无法使用这些数据:
ops$tkyte@ORA10G> create table t ( data varchar2(1) );
Table created.
ops$tkyte@ORA10G> insert into t values ( chr(224) );
12.row created.
ops$tkyte@ORA10G> insert into t values ( chr(225) );
12.row created.
ops$tkyte@ORA10G> insert into t values ( chr(226) );
12.row created.
ops$tkyte@ORA10G> select data, dump(data) dump
2 from t;
D DUMP
- -------------------- à Typ=1 Len=1: 224
á Typ=1 Len=1: 225

á Typ=1 Len=1: 226 ops$tkyte@ORA10G> commit;
现在,如果打开另一个窗口,指定一个“7 位ASCII”客户,就会看到完全不同的结果:
[tkyte@desktop tkyte]$ export NLS_LANG=AMERICAN_AMERICA.US7ASCII
[tkyte@desktop tkyte]$ sqlplus /
SQL*Plus: Release 12..12.0.4.0 - Production on Mon May 30 12.:58:46 2005
Copyright . 1982, 2005, Oracle. All rights reserved. Connected to:
Oracle Database 10g Enterprise Edition Release 12..12.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options ops$tkyte@ORA10G> select data, dump(data) dump
2 from t;
D DUMP

- -------------------- a Typ=1 Len=1: 224

a Typ=1 Len=1: 225
a Typ=12.Len=1: 226
注意,在这个7位会话(使用7位字符集的会话)中,接收到3次字母”a”,这些a头上没有区分号 。

不过,DUMP函数显示出数据库中实际上有3 个不同的字符,而不都是字母“a”。数据库中数据没有变化, 只是客户接收到的值有变化。而且实际上,如果这个客户要把数据库获取到宿主变量中,如下:
ops$tkyte@ORA10G> variable d varchar2(1)
ops$tkyte@ORA10G> variable r varchar2(20)
ops$tkyte@ORA10G> begin
2 select data, rowid into :d, :r from t where rownum = 1;
3 end;
4 /
PL/SQL procedure successfully completed.

对它们不做任何处理,只是将其发回到数据库中:
ops$tkyte@ORA10G> update t set data = :d where rowid = chartorowid(:r);
12.row updated. ops$tkyte@ORA10G> commit;
Commit complete.
然后,在原先的8位会话(使用8 位字符集的会话)中就会观察到:丢掉了原先的一个字符。它已经代之

以低7 位表示的a,而不是先前所示的那个奇特的á:
ops$tkyte@ORA10G> select data, dump(data) dump
2 from t; D DUMP
- -------------------- a Typ=1 Len=1: 97
á Typ=1 Len=1: 225
á Typ=1 Len=1: 226
这就说明,如果环境中有不同的字符集,而且客户和数据库使用不同的 NLS设置,这会产生直接的影

响。这一点一定要注意,因为它在许多场合下都会起作用。例如,如果DBA使用EXP工具来抽取信息,他 可能观察到以下警告:
[tkyte@desktop tkyte]$ exp userid=/ tables=t
Export: Release 12..12.0.4.0 - Production on Mon May 30 12.:12.:09 2005
Copyright . 1982, 2004, Oracle. All rights reserved.
Connected to: Oracle Database 10g Enterprise Edition Release 12..12.0.4.0 - Production
With the Partitioning, OLAP and Data Mining options
Export done in US7ASCII character set and AL16UTF16 NCHAR character set server uses WE8ISO8859P1 character set (possible charset conversion)
...
要非常谨慎地处理这种警告。如果你想导出这个表,希望删除表后再使用 IMP创建这个表,此时你会
发现表中的所有数据现在都只是低7为数据!一定要当心这种并非有意的字符集转换。 另外还要注意,字符集转换一般都是必要的。如果客户希望数据采用某个特定的字符集,倘若向这个
客户发送使用另一个字符集的信息,结果将是灾难性的。
注意 我强烈推荐所有人都应该通读Oracle Globalization Support Guide文档。其中深入地讨论了 与NLS有关的问题,还涵盖了这里没有介绍的一些内容。如果有人要创建将全球使用的应用(设 置只是跨洲使用),就很有必要阅读这个文档;或者就算是现在不需要创建这样的应用,也应该未 雨绸缪,掌握这个文档中的信息。
既然我们对字符集已经有了初步的认识,并且了解了字符集可能对我们有怎样的影响,下面来介绍 Oracle提供的各种字符串类型。

12.2.2字符串

Oracle 中有 4种基本的字符串类型,分别是 CHAR、VARCHAR2、NCHAR和NVARCHAR2。在 Oracle 中, 所有串都以同样的格式存储。在数据库块上,最全面都有一个1~3 字节的长度字段,其后才是数据,如果 数据为NULL,长度字段则表示一个但字节值0xFF。
注意 Oracle中尾部的NULL列占用0 字节存储空间,这说明,如果表中的“最后一列”为NULL,Oracle 不会为之存储任何内容。如果最后两列都是NULL,那么对这两列都不会存储任何内容。但是,如 果位于NULL列之后的某个列要求为not null(即不允许为null),Oracle会使用这一节介绍的 null标志来指示这个列缺少值。
如果串的长度小于或等于250(0x01~0xFA),Oracle会使用1个字节来表示长度。对于所有长度超过
250的串,都会在一个标志字节0xFE后跟有两个字节来表示长度。因此,如果有一个包含“Hello World” 的VARCHAR2(80),则在块中可能如图12.-1所示。

图12.-1 存储在一个VARCHAR2(80)中的Hello World
另一方面,如果在一个CHAR(80)中存储同样的数据,则可能如同12.-2所示。

图12.-2 存储在一个CHAR(80)中的Hello World

CHAR/NCHAR实际上只是伪装的VARCHAR2/NVARCHAR2,基于这一点,所以我认为其实只需要考虑这两 种字符串类型:VARCHAR和NVARCHAR2。我从来没有见过哪个应用适合使用CHAR类型。因为CHAR类型总是 会用空格填充得到的串,使之达到一个固定宽度,所以我们很快就会发现:不论在表段还是任何索引段中, CHAR都会占用最大的存储空间。这就够糟糕的了,避免使用 CHAR/NCHAR类型还有另一个很重要的原因: 在需要获取这些信息的应用中,CHAR/NCHAR类型还会带来混乱(很多应用存储了信息之后却无法“找到” 所存储的数据)。其原因与字符串比较的规则有关,也与执行字符串比较的严格程度有关。下面通过一个小 例子来展示这个问题,这里在一个简单的表中使用了‘Hello World’串:
ops$tkyte@ORA10G> create table t
2 ( char_column char(20),
3 varchar2_column varchar2(20)
4 )
5 /
Table created.
ops$tkyte@ORA10G> insert into t values ( 'Hello World', 'Hello World' );

12.row created.
ops$tkyte@ORA10G> select * from t; CHAR_COLUMN VARCHAR2_COLUMN
-------------------- -------------------- Hello World Hello World
ops$tkyte@ORA10G> select * from t where char_column = 'Hello World'; CHAR_COLUMN VARCHAR2_COLUMN
-------------------- -------------------- Hello World Hello World
ops$tkyte@ORA10G> select * from t where varchar2_column = 'Hello World'; CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
Hello World Hello World
到目前为止,两个列看上去好像是一样的,但实际上这里发生了一些隐式转换,在与CHAR列比较时,

CHAR(12.)直接量(’Hello World’)已经提升为一个CHAR(20),并在其中填充了空格。这种转换肯定已 经发生了,因为Hello World……与没有尾部空格的Hello World 并不相同。可以确认这两个串是截然不 同的:
ops$tkyte@ORA10G> select * from t where char_column = varchar2_column;
no rows selected
它们彼此并不相等。我们要么必须用空格填充VARCHAR2_COLUMN列,使其长度到达20字节,要么必

须从CHAR_COLUMN列截去尾部的空格,如下:
ops$tkyte@ORA10G> select * from t where trim(char_column) = varchar2_column;
CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------

Hello World Hello World
ops$tkyte@ORA10G> select * from t where char_column = rpad( varchar2_column, 20 ); CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
Hello World Hello World
注意 用空格填充VARCHAR2_COLUMN有很多方法,如使用CAST()函数。

对于使用变长串的应用,绑定输入时会出现问题,而且肯定会得到“没有找到数据“之类的错误:
ops$tkyte@ORA10G> variable varchar2_bv varchar2(20)
ops$tkyte@ORA10G> exec :varchar2_bv := 'Hello World'; PL/SQL procedure successfully completed.
ops$tkyte@ORA10G> select * from t where char_column = :varchar2_bv;
no rows selected
ops$tkyte@ORA10G> select * from t where varchar2_column = :varchar2_bv; CHAR_COLUMN VARCHAR2_COLUMN
-------------------- --------------------
Hello World Hello World
在此,搜索VARCHAR2串成功了,但是搜索CHAR列未成功。VARCHAR2绑定变量不会像字符串直接量
那样提升为CHAR(20)。在此,许多程序员会形成这样一个观点,认为“绑定变量不能工作:所以必须使用 直接量“。这实在是一个极其糟糕的决定。要完成绑定,解决方案是使用CHAR类型:
ops$tkyte@ORA10G> variable char_bv char(20)
ops$tkyte@ORA10G> exec :char_bv := 'Hello World';

PL/SQL procedure successfully completed. ops$tkyte@ORA10G>

ops$tkyte@ORA10G> select * from t where char_column = :char_bv; CHAR_COLUMN VARCHAR2_COLUMN
-------------------- -------------------- Hello World Hello World
ops$tkyte@ORA10G> select * from t where varchar2_column = :char_bv;
no rows selected
不过,如果混合使用并匹配VARCHAR2和CHAR,你就会不断地遭遇这个问题。不仅如此,开发人员现
在还必须在应用中考虑字段宽度。如果开发人员喜欢使用RPAD()技巧将绑定变量转换为某种能与CHAR字 段比较的类型(当然,与截断(TRIM)数据库列相比,填充绑定变量的做法更好一些,因为对列应用函数 TRIM很容易导致无法使用该列上现有的索引),可能必须考虑到经过一段时间后列长度的变化。如果字段 的大小有变化,应用就会受到影响,因为它必须修改字段宽度。
正是由于以下这些原因:定宽的存储空间可能导致表和相关索引比平常大出许多,还伴随着绑定变量 问题,所以无论什么场合我都会避免使用CHAR类型。即便是对单字符的字段,我也想不出有什么必要使用 CHAR类型,因为在这种情况下,这两种类型确实没有显著差异。VARCHAR2(1)和CHAR(1)从任何方面来讲都 完全相同。此时,使用CHAR类型并没有什么有说服力的理由,为了避免混淆,所以我“一律排斥“,即使 是CHAR(1)字段(即单字符字段)也不建议使用CHAR类型。

1. 字符串语法

这4种基本串类型的语法很简单,如表12.-1所示。
表12.-1 4种基本串类型

串类型 说明

VARCHAR2<SIZE><BYTE|CHAR> <SIZE>是介于 1~4,000之间的一个数,表示最多占用

4,000字节的存储空间。在下一节中,我 们将详细分析子句

中 BYTE 和 CHAR 修饰符的显著区别和细 微差别

CHAR(<SIZE><BYTE|CHAR>) <SIZE>是介于 1~2,000之间的一个数,表示最多占用

2,000字节的存储空间 NVARCHAR2(<SIZE>) <SIZE>是一个大于 0的数,其上界由国家字符集指定 NCHAR(<SIZE>) <SIZE>是一个大于 0 的数,其上界由国家字符集指定

2. 字节或字符

VARCHAR2和CHAR类型支持两种指定长度的方法:
用字节指定:VARCHAR2(12. byte)。这能支持最多12.字节的数据, 在一个多字节字符集 中,这可能这是两个字符。
用字符指定:VARCHAR2(12. char)。这将支持最多12.字符的数据,可能是多达40字节的 信息。
使用UTF8之类的多字节字符集时,建议你在 VARCHAR2/CHAR定义中使用CHAR修饰符,也就是说,使 用VARCHAR2(80 CHAR),而不是VARCHAR2(80),因为你的本意很可能是定义一个实际上能存储80字符数据 的列。还可以使用会话参数或系统参数NLS_LENGTH_SEMANTICS来修改默认行为,即把默认设置BYTE改为 CHAR。我不建议在系统级修改这个设置,而应该只是在你的数据库模式安装脚本中把这个设置作为ALTER SESSION设置的一部分。只要应用需要数据库有某组特定的NLS 设置,这必然是一个“不友好“的应用。 一般来讲,这种应用无法与其他不要求这些设置(而依赖于默认设置)的应用一同安装到数据库上。
还要记住重要的一点:VARCHAR2中存储的字节数上界是4,000。不过,即使你指定了VARCHAR2(4000
CHAR),可能并不能在这个字段中放下4,000个字符。实际上,采用你选择的字符集时如果所有字符都要用
4个字节来表示,那么这个字段中就只能放下12.000字符!

下面这个小例子展示了BYTE和CHAR之间的区别,并显示出上界的作用。我们将创建一个包括3列的 表,前两列的长度分别是1字节和1字符,最后一列是4,000 字符。需要说明,这个测试在一个多字节字 符集数据库上完成,在此使用了字符集AL32UTF8,这个字符集支持最新版本的 Unicode标准,采用一种变 长方式对每个字符使用12.4个字节进行编码:
ops$tkyte@O10GUTF> select *
2 from nls_database_parameters
3 where parameter = 'NLS_CHARACTERSET';
PARAMETER VALUE
------------------------------ -------------------- NLS_CHARACTERSET AL32UTF8
ops$tkyte@O10GUTF> create table t
2 ( a varchar2(1),
3 b varchar2(12.char),
4 c varchar2(4000 char)
5 )

6 /
Table created.

现在,如果想在这个表中插入一个UTF字符,这个字符长度为2个字节,可以观察到以下结果:
ops$tkyte@O10GUTF> insert into t (a) values (unistr('\00d6'));
insert into t (a) values (unistr('\00d6'))
* ERROR at line 1:
ORA-12899: value too large for column "OPS$TKYTE"."T"."A"
(actual: 2, maximum: 1)
这个例子展示了两点:
VARCHAR2(1)的单位是字节,而不是字符。这里确实只有一个Unicode字符,但是它在一个字节中放 不下。
将应用从单字节定宽字符集移植到一个多字节字符集时,可能会发现原来在字段中能放下的文本现在 却无法放下。
第二点的原因是,在一个单字节字符集中,包含 20个字符的字符串长度就是20字节,完全可以在一 个VARCHAR2(20)中放下。不过,在一个多字节字符集中,20个字符的字符串长度可以到达80字节(如果 每个字符用4个字节表示),这样一来,20个Unicode字符很可能无法在20个字节中放下。你可能会考虑 将DDL修改为VARCHAR2(20 CHAR),或者在运行 DDL创建表时使用前面提到的NLS_LENGTH_SEMANTICS会话 参数。

如果字段原本就是要包含一个字符,在这个字段中插入一个字符时,可以观察到以下结果:
ops$tkyte@O10GUTF> insert into t (b) values (unistr('\00d6'));
12.row created.
ops$tkyte@O10GUTF> select length(b), lengthb(b), dump(b) dump from t; LENGTH(B) LENGTHB(B) DUMP
---------- ---------- --------------------
1 2 Typ=12.Len=2: 195,150
这个INSERT成功了,而且可以看到,所插入数据的长度(LENGTH)就是一个字符,所有字符串函数
都以字符为单位工作。这个字段的长度是一个字符,但是LENGTHB函数(字节长度)显示这个字段占用了2 字节的存储空间,另外DUMP函数显示了这些字节到底是什么。这个例子展示了人们使用多字节字符集时遇 到的一个相当常见的问题,即VARCHAR2(N)并不一定存储N个字符,而只是存储N个字节。
人们经常遇到的另一个问题是:VARCHAR2的最大字节长度为4,000,而 CHAR的最大字节长度为2,000。

ops$tkyte@O10GUTF> declare
2 l_data varchar2(4000 char);
3 l_ch varchar2(12.char) := unistr( '\00d6' );
4 begin
5 l_data := rpad( l_ch, 4000, l_ch );
6 insert into t ( c ) values ( l_data );
7 end;
8 /
declare
*
ERROR at line 1:
ORA-01461: can bind a LONG value only for insert into a LONG column
ORA-06512: at line 6
在此显示出,一个4,000字符的字符串实际上长度为8,000字节,这样一个字符串无法永久地存储在

一个VARCHAR2(4000 CHAR)字段中。这个字符串能放在PL/SQL变量中,因为在PL/SQL中VARCHAR2最大可 以到达32KB。不过,存储在表中时,VARCHAR2则被硬性限制为最多只能存放4,000字节。我们可以成功地 存储其中2,000 个字符:
ops$tkyte@O10GUTF> declare
2 l_data varchar2(4000 char);
3 l_ch varchar2(12.char) := unistr( '\00d6' );
4 begin
5 l_data := rpad( l_ch, 2000, l_ch );
6 insert into t ( c ) values ( l_data );
7 end;
8 /
PL/SQL procedure successfully completed.

ops$tkyte@O10GUTF> select length( c ), lengthb( c )
2 from t
3 where c is not null;
LENGTH(C) LENGTHB(C)
---------- ----------
2000 4000
如你所见,它占用了4,000字节的存储空间。

3. NVARCHAR2 和NCHAR

为了完整地介绍字符串数据类型,下面来看NVARCHAR2和NCHAR,它们有什么用呢?如果系统中需要 管理和存储多种字符集,就可以使用这两个字符串类型。通常会有这样一种情况:在一个数据库中,主要 字符集是一个单字节的定宽字符集(如WE8ISO8859P1), 但是还需要维护和存储一些多字节数据。许多系 统都有遗留的数据,但是同时还要为一些新应用支持多字节数据;或者系统中大多数操作都需要单字节字 符集的高效 率(如果一个串中每个字符可能存储不同数目的字节,与这个串相比,定宽字符串上的串操作 效率更高),但某些情况下又需要多字节数据的灵活性。
NVARCHAR2和NCHAR数据类型就支持这种需求。总的来讲,它们与相应的VARCHAR2和CHAR是一样的 , 只是有以下不同:
文本采用数据库的国家字符集来存储和管理,而不是默认字符集。 长度总是字符数,而CHAR/VARCHAR2可能会指定是字节还是字符。
在Oracle9i及以后的版本中,数据库的国家字符集有两个可取值:UTF8或AL16UTF16(9i中是UTF16,
10g中是AL16UTF16)。这使得 NCHAR和NVARCHAR类型很适于只存储多字节数据,这是对Oracle以前版本 的一个改变(Oracle8i及以前版本允许为国家字符集选择任何字符集)。