oracle常用数据类型

时间：2017-07-11 来源：

　　1、Char

　　定长格式字符串，在数据库中存储时不足位数填补空格，它的声明方式如下CHAR(L)，L为字符串长度，缺省为1，作为变量最大32767个字符，作为数据存储在ORACLE8中最大为2000。不建议使用，会带来不必要的麻烦

　　a、字符串比较的时候，如果不注意(char不足位补空格)会带来错误

　　b、字符串比较的时候，如果用trim函数，这样该字段上的索引就失效(有时候会带来严重性能问题)

　　c、浪费存储空间(无法精准计算未来存储大小，只能留有足够的空间;字符串的长度就是其所占用空间的大小)

　　2、Varchar2/varchar

　　目前VARCHAR是VARCHAR2的同义词。工业标准的VARCHAR类型可以存储空字符串，但是oracle不这样做，尽管它保留以后这样做的权利。Oracle自己开发了一个数据类型VARCHAR2，这个类型不是一个标准的VARCHAR，它将在数据库中varchar列可以存储空字符串的特性改为存储NULL值。如果你想有向后兼容的能力，Oracle建议使用VARCHAR2而不是VARCHAR。

　　不定长格式字符串，它的声明方式如下VARCHAR2(L)，L为字符串长度，没有缺省值，作为变量最大32767个字节，作为数据存储在ORACLE8中最大为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、Long/long raw

　　Oracle已经废弃，只是为了向下兼容保留着，应该全部升级到lob

　　LONG，在数据库存储中可以用来保存高达2G的数据，作为变量，可以表示一个最大长度为32760字节的可变字符串

　　LONG RAW，类似于LONG，作为数据库列最大存储2G字节的数据，作为变量最大32760字节。

　　Long类型有很多限制

　　a、表中只能有一列long类型

　　b、Long类型不支持分布式事务

　　c、太多的查询不能在long上使用了

　　4、行，包括RAW和LONG RAW两种类型。用来存储二进制数据，不会在字符集间转换

　　RAW，类似于CHAR，声明方式RAW(L)，L为长度，以字节为单位，作为数据库列最大2000，作为变量最大32767字节。

　　LONG RAW，类似于LONG，作为数据库列最大存储2G字节的数据，作为变量最大32760字节。

　　行标识，只有一种类型--ROWID，用来存储“行标识符”，可以利用ROWIDTOCHAR函数来将行标识转换成为字符。

　　5、Date

　　Date类型是一个7字节的定长数据类型(从世纪到秒)，绝对没有“千年虫”问题。

　　举个例子：性能a>b>c

　　a、Where date_colum>=to_date(’01-jan-2007’，’dd-mon-yyyy’)

　　and date_colum< to_date(’02-jan-2007’，’dd-mon-yyyy’)

　　b、Where trunc(date_colum,’y’)=to_date(’01-jan-2007’，’dd-mon-yyyy’)

　　c、Where to_char(date_colum,’yyyy’)=’2007’

　　6、 Timestamp

　　ORACLE已经在DATE数据类型上扩展出来了TIMESTAMP数据类型，它包括了所有DATE数据类型的年月日时分秒的信息，

　　而且包括了小数秒的信息。如果你想把DATE类型转换成TIMESTAMP类型，就使用CAST 函数

　　语法Timestamp(n)，n指定秒的小数位数，取值范围0～9。缺省是6。

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

　　TO_CHAR(SYSTIMESTAMP,'YYYY-MM-

　　2010-03-03 20:18:21.31974900

　　这是因为显示格式是按照参数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)

　　--------------------------------------------------------------------------------

　　03-3月 -10 08.25.31.000000 下午

　　正如你看到的，在转换后的时间段尾部有了一段“。000000”。这是因为从date转换过来的时候，没有小数秒的信息，缺省为0

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

　　SQL> select sysdate from dual;

　　SYSDATE

　　-----------

　　2010-3-3 20

　　c. 为了得到系统时间，返回成timestamp数据类型。你可以使用systimpstamp函数。

　　SQL> select systimestamp from dual;

　　SYSTIMESTAMP

　　--------------------------------------------------------------------------------

　　03-3月 -10 08.27.41.114314 下午 +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 sysdate from dual;

　　SYSDATE

　　-----------

　　2006-1-1 10

　　SQL> select systimestamp from dual;

　　SYSTIMESTAMP

　　--------------------------------------------------------------------------------

　　03-3月 -10 08.31.12.665055 下午 +08:00

　　e. 参数fixed_date恢复默认值

　　SQL> alter system set fixed_date=none;

　　System altered

　　SQL> select sysdate from dual;

　　SYSDATE

　　-----------

　　2010-3-3 20

　　8、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是变长类型，在计算表存储空间的时候要切记

　　9、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尽量不用：

　　oracle8以后：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。

　　(本文来自网络,如有侵权请来信告知，本站将在第一时间删除。)