oracle数据类型--字符和⼆进制串类型
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字节的信息。
unicode文件格式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。

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。