oracle 数据类型 -- 字符和二进制串类型

最新推荐文章于 2023-12-18 15:41:07 发布

weixin_34110749

最新推荐文章于 2023-12-18 15:41:07 发布

阅读量2.5k

点赞数

文章标签：数据库运维操作系统

原文链接：https://my.oschina.net/u/1862478/blog/1931520

版权

2019独角兽企业重金招聘Python工程师标准>>>

1 Oracle数据类型概述

CHAR：这是一个定长字符串，会用空格填充来达到其最大长度。非null的CHAR(2) 总是包含2字节信息（使用了默认国家语言支持National Language Support，NLS设置）。CHAR字段最多可以存储2,000字节的信息。
NCHAR：这是一个包含UNICODE格式数据的定长字符串。Unicode是一种对字符进行编码的通用方法，而不论使用的是何种计算机系统平台。有了NCHAR类型，就允许数据库中包含采用两种不同字符集的数据：使用数据库字符集的CHAR类型和使用国家字符集的NCHAR类型。非null的NCHAR(2)总是包含2个字符的信息（注意，在这方面，它与CHAR类型有所不同）。NCHAR字段最多可以存储2,000字节的信息。
VARCHAR2：VARCHAR的同义词。这是一个变长字符串，与CHAR类型不同，它不会用空格填充至最大长度。VARCHAR2(12)可能包含0～12字节的信息（使用默认NLS设置）。VARCHAR2最多可以存储4,000字节的信息。
NVARCHAR2：这是一个包含UNICODE格式数据的变长字符串。NVARCHAR2(12)可以包含0～12字符的信息。NVARCHAR2最多可以存储4,000字节的信息。
RAW：这是一种变长二进制数据类型，这说明采用这种数据类型存储的数据不会发生字符集转换。可以把它看作由数据库存储的信息的二进制字节串。这种类型最多可以存储2,000字节的信息。
NUMBER：这种数据类型能存储精度最多达38位的数字。每个数存储在一个变长字段中，其长度在0（尾部的NULL列就是0字节）～22字节之间。Oracle的NUMBER类型精度很高，远远高于许多编程语言中常规的FLOAT和DOUBLE类型。
BINARY_FLOAT：这是Oracle 10g Release 1及以后版本中才有的一种新类型。它是一个32位单精度浮点数，可以支持至少6位精度，占用磁盘上5字节的存储空间。
LONG：这种类型能存储最多2G的字符数据（2GB是指2千兆字节，而不是2千兆字符，因为在一个多字节字符集中，每个字符可能有多个字节）。由于LONG类型有许多限制，而且提供LONG类型只是为了保证向后兼容性，所以强烈建议新应用中不要使用LONG类型，而且在现有的应用中也要尽可能将LONG类型转换为CLOB类型。
LONG RAW：LONG RAW类型能存储多达2GB的二进制信息。由于LONG同样的原因，建议在将来的所有开发中都使用CLOB类型，另外现有的应用中也应尽可能将LONG RAW转换为BLOB类型。
DATE：这是一个7字节的定宽日期/时间数据类型。总包含7个属性，包括：世纪、世纪中哪一年、月份、月中的哪一天、小时、分钟和秒。
TIMESTAMP：这是一个7字节或12字节的定宽日期/时间数据类型。它与DATE数据类型不同，因为TIMESTAMP可以包含小数秒（fractional second）；带小数秒的TIMESTAMP在小数点右边最多可以保留9位。
TIMESTAMP WITH TIME ZONE：与前一种类型类似，这是一个12字节的定宽TIMESTAMP，不过它还提供了时区（TIME ZONE）支持。数据中会随TIMESTAMP存储有关时区的额外信息，所以原先插入的TIME ZONE会与数据一同保留。
TIMESTAMP WITH LOCAL TIME ZONE：与TIMESTAMP类似，这是一种7字节或12.字节的定宽日期/时间数据类型；不过，这种类型对时区敏感（time zone sensitive）。如果在数据库中有修改，会参考数据中提供的TIME ZONE，根据数据库时区对数据中的日期/时间部分进行“规范化”。
INTERVAL YEAR TO MONTH：这是一个5字节的定宽数据类型，用于存储一个时间段，这个类型将时段存储为年数和月数。可以在日期运算中使用这种时间间隔使一个DATE或TIMESTAMP类型增加或减少一段时间。
INTERVAL DAY TO SECOND：这是一个12字节的定宽数据类型，用于存储一个时段，这个类型将时段存储为天/小时/分钟/秒数，还可以有最多9位的小数秒。
BFILE：这种数据类型允许在数据库列中存储一个Oracle目录对象（操作系统目录的一个指针）和一个文件名，并读取这个文件。这实际上允许你以一种只读的方式访问数据库服务器上可用的操作系统文件，就好像它们存储在数据库表本身中一样。
BLOB：在Oracle9i及以前的版本中，这种数据类型允许存储最多4GB的数据，在Oracle 10g及以后的版本中允许存储最多（4GB）×（数据库块大小）字节的数据。BLOB包含不需要进行字符集转换的“二进制“数据，适合存储电子表格、字处理文档、图像文件等。
CLOB：允许存储最多（4GB）×（数据库块大小）字节的数据。CLOB包含要进行字符集转换的信息。很适合存储纯文本信息。
NCLOB：允许存储最多（4GB）×（数据库块大小）字节的数据。NCLOB存储用数据库国家字符集编码的信息，这些信息要进行字符集转换。
ROWID：ROWID实际上是数据库中一行的12字节地址。ROWID中编码有足够的信息，足以在磁盘上定位这一行，以及标识ROWID指向的对象（表等）。
UROWID：UROWID是一个通用ROWID，用于表（如IOT和通过异构数据库网关访问的没有固定ROWID的表）。UROWID是行主键值的一种表示，因此，取决于所指向的对象，UROWID的大小会有所变化。
以上列表中还少了许多类型，如INT、INTEGER、SMALLINT、FLOAT、REAL等。这些类型实际上都是在上表所列的某种类型的基础上实现的，它们只是固有Oracle类型的同义词。

2 字符和二进制串类型

Oracle中的字符数据类型包括CHAR、VARCHAR2以及带“N“的相应”变体“（NCHAR和NVARCHAR2），这些字符数据类型能存储2,000字节或4,000字节的文本。这些文本会由数据库根据需要在不同字符集之间转换。字符集（chrarcter set）是各个字符的一种二进制表示（用位和字节表示）。目前有多种不同的字符集，每种字符集能表示不同的字符，例如：
US7ASCII字符集是128字符的ASCII标准表示。它使用字节的低7位表示这128个字符。
WE8ISO8859P1字符集是一种西欧字符集，不仅能不是128个ASCII字符，还能表示128个扩展字符，这个字符集使用了字节的全部8位。

2.1 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）希望数据采用多字节表示，就必须执行转换，只有这样客户应用才能使用这些数据。

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后跟有两个字节来表示长度。

scott@ORCL>create table t
  2  ( char_column char(20),
  3     varchar2_column varchar2(20)
  4  )
  5  /

表已创建。

scott@ORCL>insert into t values ( 'Hello World', 'Hello World' );

已创建 1 行。

scott@ORCL>select * from t;

CHAR_COLUMN          VARCHAR2_COLUMN
-------------------- --------------------
Hello World          Hello World

scott@ORCL>select * from t where char_column = 'Hello World';

CHAR_COLUMN          VARCHAR2_COLUMN
-------------------- --------------------
Hello World          Hello World

scott@ORCL>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并不相同。可以确认这两个串是截然不同的：

scott@ORCL>select * from t where char_column = varchar2_column;

未选定行

它们彼此并不相等。我们要么必须用空格填充VARCHAR2_COLUMN列，使其长度到达20字节，要么必须从CHAR_COLUMN列截去尾部的空格，如下：

scott@ORCL>select * from t where trim(char_column) = varchar2_column;

CHAR_COLUMN          VARCHAR2_COLUMN
-------------------- --------------------
Hello World          Hello World

scott@ORCL>select * from t where char_column = rpad( varchar2_column, 20 );

CHAR_COLUMN          VARCHAR2_COLUMN
-------------------- --------------------
Hello World          Hello World

对于使用变长串的应用，绑定输入时会出现问题，而且肯定会得到“没有找到数据“之类的错误：

scott@ORCL>variable varchar2_bv varchar2(20)
scott@ORCL>exec :varchar2_bv := 'Hello World';

PL/SQL 过程已成功完成。

scott@ORCL>select * from t where char_column = :varchar2_bv;

未选定行

scott@ORCL>select * from t where varchar2_column = :varchar2_bv;

CHAR_COLUMN          VARCHAR2_COLUMN
-------------------- --------------------
Hello World          Hello World

在此，搜索VARCHAR2串成功了，但是搜索CHAR列未成功。VARCHAR2绑定变量不会像字符串直接量那样提升为CHAR(20)。要完成绑定，解决方案是使用CHAR类型：

scott@ORCL>variable char_bv char(20)
scott@ORCL>exec :char_bv := 'Hello World';

PL/SQL 过程已成功完成。

scott@ORCL>select * from t where char_column = :char_bv;

CHAR_COLUMN          VARCHAR2_COLUMN
-------------------- --------------------
Hello World          Hello World

scott@ORCL>select * from t where varchar2_column = :char_bv;

未选定行

不过，如果混合使用并匹配VARCHAR2和CHAR，你就会不断地遭遇这个问题。不仅如此，开发人员现在还必须在应用中考虑字段宽度。如果开发人员喜欢使用RPAD()技巧将绑定变量转换为某种能与CHAR字段比较的类型（当然，与截断（TRIM）数据库列相比，填充绑定变量的做法更好一些，因为对列应用函数TRIM很容易导致无法使用该列上现有的索引），可能必须考虑到经过一段时间后列长度的变化。如果字段的大小有变化，应用就会受到影响，因为它必须修改字段宽度。

正是由于以下这些原因：定宽的存储空间可能导致表和相关索引比平常大出许多，还伴随着绑定变量问题，所以无论什么场合我都会避免使用CHAR类型。即使是CHAR(1)字段（即单字符字段）也不建议使用CHAR类型。

1. 字符串语法

VARCHAR2<SIZE><BYTE|CHAR>                 <SIZE>是介于1～4,000之间的一个数，表示最多占用4,000字节的存储空间。
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字符数据的列。

下面这个小例子展示了BYTE和CHAR之间的区别，并显示出上界的作用。我们将创建一个包括3列的表，前两列的长度分别是1字节和1字符，最后一列是4,000字符。

scott@ORCL>select *
  2  from nls_database_parameters
  3  where parameter = 'NLS_CHARACTERSET';

PARAMETER            VALUE
----------------    ---------------------
NLS_CHARACTERSET    AL32UTF8


scott@ORCL>create table t
  2  ( a varchar2(1),
  3     b varchar2(1 char),
  4     c varchar2(4000 char)
  5  )
  6  /

表已创建。

现在，如果想在这个表中插入一个UTF字符，这个字符长度为2个字节，可以观察到以下结果：

scott@ORCL>insert into t (a) values (unistr('\00d6'));
insert into t (a) values (unistr('\00d6'))
                          *
第 1 行出现错误:
ORA-12899: 列 "SCOTT"."T"."A" 的值太大 (实际值: 2, 最大值: 1)

这个例子展示了两点：
1. VARCHAR2(1 byte)的单位是字节，而不是字符。这里确实只有一个Unicode字符，但是它在一个字节中放不下。
2. 将应用从单字节定宽字符集移植到一个多字节字符集时，可能会发现原来在字段中能放下的文本现在却无法放下。

第二点的原因是，在一个单字节字符集中，包含20个字符的字符串长度就是20字节，完全可以在一个VARCHAR2(20)中放下。不过，在一个多字节字符集中，20个字符的字符串长度可以到达80字节（如果每个字符用4个字节表示），这样一来，20个Unicode字符很可能无法在20个字节中放下。你可能会考虑将DDL修改为VARCHAR2(20 CHAR)，或者在运行DDL创建表时使用前面提到的NLS_LENGTH_SEMANTICS会话参数。

scott@ORCL>insert into t (b) values (unistr('\00d6'));

已创建 1 行。

scott@ORCL>select length(b), lengthb(b), dump(b) dump from t;

 LENGTH(B)  LENGTHB(B)  DUMP
----------  ----------  ----
         1          2   Typ=1 Len=2: 195,150

这个INSERT成功了，而且可以看到，所插入数据的长度（LENGTH）就是一个字符，所有字符串函数都以字符为单位工作。这个字段的长度是一个字符，但是LENGTHB函数（字节长度）显示这个字段占用了2字节的存储空间，另外DUMP函数显示了这些字节到底是什么。这个例子展示了 VARCHAR2(N)并不一定存储N个字符，而只是存储N个字节。

人们经常遇到的另一个问题是：VARCHAR2的最大字节长度为4,000，而CHAR的最大字节长度为2,000。

scott@ORCL>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
*
第 1 行出现错误:
ORA-01461: 仅能绑定要插入 LONG 列的 LONG 值
ORA-06512: 在 line 6

在此显示出，一个4,000字符的字符串实际上长度为8,000字节，这样一个字符串无法永久地存储在一个VARCHAR2(4000 CHAR)字段中。这个字符串能放在PL/SQL变量中，因为在PL/SQL中VARCHAR2最大可以到达32KB。不过，存储在表中时，VARCHAR2则被硬性限制为最多只能存放4,000字节。我们可以成功地存储其中2,000个字符：

scott@ORCL>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 过程已成功完成。

scott@ORCL>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

它们与相应的VARCHAR2和CHAR是一样的，只是有以下不同：
文本采用数据库的国家字符集来存储和管理，而不是默认字符集。
长度总是字符数，而CHAR/VARCHAR2可能会指定是字节还是字符。
数据库的国家字符集有两个可取值：UTF8或AL16UTF16。这使得NCHAR和NVARCHAR类型很适于只存储多字节数据。

3 二进制串：RAW类型

Oracle除了支持文本，还支持二进制数据的存储。CHAR和VARCHAR2类型需要进行字符集转换，而二进制数据不会做这种字符集转换。因此，二进制数据类型适于存储加密信息，加密数据不是“文本“，而是原文本的一个二进制表示、包含二进制标记信息的字处理文档，等等。如果数据库不认为这些数据是”文本“，这些数据就应该采用一种二进制数据类型来存储，另外不应该应用字符集转换的数据也要使用二进制数据类型存储。

Oracle支持3种数据类型来存储二进制数据：
RAW类型，它很适合存储多达2,000字节的RAW数据。
BLOB类型，它支持更大的二进制数据。
LONG RAW类型，这是为支持向后兼容性提供的，新应用不应考虑使用这个类型。

二进制RAW类型的语法很简单：

RAW（<size>）

例如，以下代码创建了一个每行能存储12字节二进制信息的表：

scott@ORCL>create table t ( raw_data raw(16) );

表已创建。

从磁盘上的存储来看，RAW类型与VARCHAR2类型很相似。RAW类型是一个变长的二进制串，这说明前面创建的表T可以存储1～16字节的二进制数据。它不会像CHAR类型那样用空格填充。

处理RAW数据时，它被隐式地转换为一个VARCHAR2类型，也就是说，诸如SQL*Plus之类的许多工具不会直接显示RAW数据，而是会将其转换为一种十六进制格式来显示。在以下例子中，我们使用SYS_GUID()在表中创建了一些二进制数据，SYS_GUID()是一个内置函数，将返回一个全局惟一的16字节RAW串（GUID就代表全局惟一标识符，globally unique identifier）：

scott@ORCL>insert into t values ( sys_guid() );

已创建 1 行。

scott@ORCL>select * from t;

RAW_DATA
--------------------------------
A6A418FD9A6040A4B1DBB352CD5CBA9A

首先，RAW数据看上去就像是一个字符串。SQL*Plus就是以字符串形式获取和打印RAW数据，但是RAW数据在磁盘上并不存储为字符串。SQL*Plus不能在屏幕上打印任意的二进制数据。要记住，二进制数据可能包含诸如回车或换行等控制字符，还可能是一个Ctrl+G字符，这会导致终端发出“嘟嘟“的叫声。
其次，RAW数据看上去远远大于16字节，实际上，在这个例子中，你会看到32个字符。这是因为，每个二进制字节都显示为两个十六进制字符。所存储的RAW数据其实长度就是16字节，可以使用Oracle DUMP函数确认这一点。在此，我“转储“了这个二进制串的值，并使用了一个可选参数来指定显示各个字节值时应使用哪一种进制。这里使用了基数16，从而能将转储的结果与前面的串进行比较：

scott@ORCL>select dump(raw_data,16) from t;

DUMP(RAW_DATA,16)
----------------------------------------
Typ=23 Len=16: a6,a4,18,fd,9a,60,40,a4,b1,db,b3,52,cd,5c,ba,9a

DUMP显示出，这个二进制串实际上长度为16字节（LEN=16），另外还逐字节地显示了这个二进制数据。可以看到，这个转储显示与SQL*Plus将RAW数据获取为一个串时所执行的隐式转换是匹配的。另一个反向上（插入）也会执行隐式转换：

scott@ORCL>insert into t values ( 'abcdef' );

已创建 1 行。

这不会插入串abcdef，而会插入一个3字节的RAW数据，其字节分别是AB、CD、EF，如果用十进制表示则为字节171、205、239。如果试图使用一个包含非法16进制字符的串，就会收到一个错误消息：

scott@ORCL>select dump(raw_data,16) from t;

DUMP(RAW_DATA,16)
--------------------------------------------------------------------------------
--------------------------------------------------------------------------------
----------------------------------------
Typ=23 Len=16: a6,a4,18,fd,9a,60,40,a4,b1,db,b3,52,cd,5c,ba,9a
Typ=23 Len=3: ab,cd,ef

scott@ORCL>insert into t values ( 'abcdefgh' );
insert into t values ( 'abcdefgh' )
                             *
第 1 行出现错误:
ORA-01465: 无效的十六进制数字

RAW类型可以加索引，还能在谓词中使用，它与其他任何数据类型有同样的功能。不过，必须当心避免不希望的隐式转换，而且必须知道确实会发生隐式转换。
可以使用以下内置函数来执行这种操作：
HEXTORAW：将十六进制字符串转换为RAW类型
RAWTOHEX：将RAW串转换为十六进制串
SQL*Plus将RAW类型获取为一个串时，会隐式地调用RAWTOHEX函数，而插入串时会隐式地调用HEXTORAW函数。应该避免隐式转换，而在编写代码时总是使用显示转换，这是一个很好的实践做法。所以前面的例子应该写作：

scott@ORCL>select rawtohex(raw_data) from t;

RAWTOHEX(RAW_DATA)
----------------------------------------------------------------
A6A418FD9A6040A4B1DBB352CD5CBA9A
ABCDEF

scott@ORCL>insert into t values ( hextoraw('abcdef') );

已创建 1 行。

scott@ORCL>select rawtohex(raw_data) from t;

RAWTOHEX(RAW_DATA)
----------------------------------------------------------------
A6A418FD9A6040A4B1DBB352CD5CBA9A
ABCDEF
ABCDEF

转载于:https://my.oschina.net/u/1862478/blog/1931520