Oracle 字符类型整理

1、Char
定长格式字符串，在数据库中存储时不足位数填补空格，它的声明方式如下CHAR(L)，L为字符串长度，缺省为1，最大为2000。不建议使用，会带来不必要的麻烦。
a、字符串比较的时候，如果不注意（char不足位补空格）会带来错误
b、字符串比较的时候，如果用trim函数，这样该字段上的索引就失效（有时候会带来严重性能问题）
c、浪费存储空间(无法精准计算未来存储大小，只能留有足够的空间；字符串的长度就是其所占用空间的大小)
d、char的效率比varchar2的效率稍高。

2、Varchar2/varchar
目前VARCHAR是VARCHAR2的同义词。工业标准的VARCHAR类型可以存储空字符串，但是oracle不这样做，尽管它保留以后这样做的权利。Oracle自己开发了一个数据类型VARCHAR2，这个类型不是一个标准的VARCHAR，它将在数据库中varchar列可以存储空字符串的特性改为存储NULL值。如果你想有向后兼容的能力，Oracle建议使用VARCHAR2而不是VARCHAR。
不定长格式字符串，它的声明方式如下VARCHAR2(L)，L为字符串长度，没有缺省值，最大为4000。在多字节语言环境中，实际存储的字符个数可能小于L值，例如：当语言
环境为中文(SIMPLIFIED CHINESE_CHINA.ZHS16GBK)时，一个VARCHAR2(200)的数据列可以保存200个英文字符或者100个汉字字符；对于4000字节以内的字符串，建议都用该类型

a。VARCHAR2比CHAR节省空间，在效率上比CHAR会稍微差一些，即要想获得效率，就必须牺牲一定的空间，这也就是我们在数据库设计上常说的‘以空间换效率’。
b。VARCHAR2虽然比CHAR节省空间，但是如果一个VARCHAR2列经常被修改，而且每次被修改的数据的长度不同，这会引起‘行迁移’(Row Migration)现象，而这造成多余的I/O，是数据库设计和调整中要尽力避免的，在这种情况下用CHAR代替VARCHAR2会更好一些。不过区别也不是太大，如果就是想用varchar2，那么在发生行迁移，可以通过pctfree来调整，然后对表的数据进行重组

nchar,nvarchar/nvarchar2国家字符集，与环境变量NLS指定的语言集密切相关。只有要用到unicode时才会用到这些国家字符集，nchar和nvarchar2依据所选的字符集来存储数据，可能一个字符占两个或多个字节，防止乱码

3. 行
包括RAW和LONG RAW两种类型,用来存储二进制数据，不会在字符集间转换
RAW，类似于CHAR，声明方式RAW(L)，L为长度，以字节为单位，作为数据库列最大2000，作为变量最大32767字节。
LONG RAW，类似于LONG，作为数据库列最大存储2G字节的数据，作为变量最大32760字节。
行标识，只有一种类型--ROWID，用来存储“行标识符”，可以利用ROWIDTOCHAR函数来将行标识转换成为字符。

4、Date
Date类型是一个7字节的定长数据类型(从世纪到秒)，绝对没有“千年虫”问题。
举个例子：性能a>b>c
a、SQL> ...Where date_colum>=to_date(’01-jan-2007’,’dd-mon-yyyy’)
     and date_colum< to_date(’02-jan-2007’,’dd-mon-yyyy’)
b、SQL> ...Where trunc(date_colum,’y’)=to_date(’01-jan-2007’,’dd-mon-yyyy’)
c、SQL> ...Where to_char(date_colum,’yyyy’)=’2007’

5、 Timestamp
ORACLE已经在DATE数据类型上扩展出来了TIMESTAMP数据类型，它包括了所有DATE数据类型的年月日时分秒的信息，而且包括了小数秒的信息。如果你想把DATE类型转换成TIMESTAMP类型，就使用CAST 函数
语法Timestamp(n)，n指定秒的小数位数，取值范围0～9。缺省是６。

SQL> select to_char(systimestamp,'yyyy-mm-dd hh24:mi:ss.ff8') from dual;

TO_CHAR(SYSTIMESTAMP,'YYYY-MM-
------------------------------
2012-08-11 10:34:48.89800000
这是因为显示格式是按照参数NLS_TIMESTAMP_FORMAT定的缺省格式显示。

说明：
1)当你把一个表中date类型字段的数据移到另一个表的timestamp类型字段中去的时候，可以直接写INSERT SELECT语句， oracle会自动为你做转换的。
2) to_char函数支持date和timestamp，但是trunc却不支持TIMESTAMP数据类型

a。把DATE类型转换成TIMESTAMP类型
SQL> select cast(sysdate as timestamp) from dual;

CAST(SYSDATEASTIMESTAMP)
--------------------------------------------------------------------------------
11-8月 -12 10.35.40.000000 上午
正如你看到的，在转换后的时间段尾部有了一段“.000000”。这是因为从date转换过来的时候，没有小数秒的信息，缺省为0

b. 为了得到系统时间，返回成date数据类型。你可以使用sysdate函数。
SQL> select sysdate from dual;

SYSDATE
-----------
2012/8/11 1

c. 为了得到系统时间，返回成timestamp数据类型。你可以使用systimpstamp函数。
SQL> select systimestamp from dual;

SYSTIMESTAMP
--------------------------------------------------------------------------------
11-8月 -12 10.38.16.809000 上午 +08:00

d. 你可以设置初始化参数FIXED_DATE指定sysdate函数返回一个固定值。这用在测试日期和时间敏感的代码。注意，这个参数对于systimestamp函数无效。
SQL> alter system set fixed_date ='2006-01-01-10:00:00';
System altered


SQL> select sysdate from dual;
SYSDATE
-----------
2006-1-1 10

SQL> select systimestamp from dual;
SYSTIMESTAMP
--------------------------------------------------------------------------------
11-8月 -12 10.38.16.809000 上午 +08:00

e. 参数fixed_date恢复默认值
SQL> alter system set fixed_date=none;
System altered

SQL> select sysdate from dual;
SYSDATE
-----------
2012/8/11 1

6. Number
定义Number的方法：Number(p,s) ;其中p，s都是可选的：
p代表精度，默认为38
s代表小数位数，取值范围-84~127，默认取值要看是否指定了p，如果制定了p，默认s为0，如果没有指定p，默认取最大值。

几个例子：
a、 Number(5,0)=Number(5) 取值范围99999～-99999
b、 Number(5,2) 取值范围999.99～-999.99
注意：其中的整数位数只有3位，小数位数有2位，按照如下方法计算：
整数位数<=p-s
小数位数<=s
如果插入123.555存储在数据库中变成123.56 (在小数的第三位上四舍五入)，如果插入999.999，数据库就要抛错。
c、 Number(5,-2) 取值范围9999900～-9999900 （整数位数<=p-s，没有小数位数）
如果插入9999949存储在数据库中变成9999900（在整数的第二位上四舍五入），如果插入9999950，数据库就要抛错。
其他的数值类型都是number的衍生，底层都是number，比如integer/int完全映射到number(38)
性能相关：number是一种软实现的类型，如果需要对number做复杂的运算，建议先用cast内置函数转换number为浮点数类型
另外需要注意的一点是：number是变长类型，在计算表存储空间的时候要切记

7. Lob
Clob/blob实现是比较复杂的，主要是用来存储大量数据的数据库字段，最大可以存储4G字节的非结构化数据。
Oracle的Blob字段比较特殊，他比long 字段的性能要好很多，可以用来保存例如图片之类的二进制数据。
写入Blob字段和写入其它类型字段的方式非常不同，因为Blob自身有一个cursor，你必须使用cursor对blob进行操作，因而你在写入Blob之前，必须获得cursor才能进行写入，那么如何获得Blob的cursor呢？这需要你先插入一个empty的blob，这将创建一个blob的cursor，然后你再把这个empty的blob的cursor用select查询出来，这样通过两步操作，你就获得了blob的cursor，可以真正的写入blob数据了。

这里只提几个和性能相关的点，当然能不用lob尽量不用：
oralce中有四种类型的lob:clob,blob,nclob,bfile.
clob:内部字符大对象;
blob:内部二进制大对象;
nclob:内部定长多字节大对象;
bfile:外部二进制大文件,这个文件中的数据只能被只读访问,并且不包含在数据库内;bfile是早期的RDBMS BLOB的直接继承,作为数据库指针存储在数据库内部,指向外部的操作系统文件.

 a，按存储方式分： 
     内部LOB：存放在DB内部，包括BLOB，CLOB，BCLOB  
     外部文件：存放在DB外面，就是BFILE 
 b.按存储数据的类型分：
  ①字符类型：
   CLOB:存储大量 单字节 字符数据。
   NLOB:存储定宽 多字节 字符数据。
  ②二进制类型：
   BLOB:存储较大无结构的二进制数据。
  ③二进制文件类型：
   BFILE:将二进制文件存储在数据库外部的操作系统文件中。存放文件路径。

关于使用LOB数据类型的性能问题
a、 一个lob字段包括lobindex和lobsegment,LOB INDEX是隐式创建的
b、 Lob缺省可以存放在表中（表字段），条件是：
1.它的大小小于4kb
2.并且在定义的时候没有使用(disable storage inrow)字句（缺省是enable）
当lob大于4kb的时候它会被存放到lobsegment中
c、当lob存放在表中的时候，它可以被缓存，对于它的操作效率远远高于存储在lobsegment中的lob（不用lobindex）
d、 存储在lobsegment中的lob缺省不在缓冲区缓存，对于lob的读写都是物理IO，代价非常高，所以对于大于4kb的lob字段千万不要频繁更新，效率非常低
e、 存储在lobsegment中的lob可以在定义的时候指定使用cache（默认是nocache），这对于中等大小的lob（比如几k～几十k）很有用处，同时，它还可以减少物理IO。