oracle常用数据类型

author：skate
time：2010/03/03

---

 

oracle常用数据类型

 

今天同事问些数据类型的问题，有的还真有点记不清了，于是就简单总结下常用的数据类型以备日后查用

 

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。缺省是６。

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:将二进制文件存储在数据库外部的操作系统文件中。存放文件路径。

大对象数据的录入

 1，声明LOB类型列
  /*
   conn scott/tiger;
   Create TableSpace ts5_21
      DataFile 'E:/Oracle/ts5_21.dbf'
   Size 5m;
  */
  Create Table tLob (
     no Number(4),
     name VarChar2(10),
     resume CLob,
     photo BLob,
     record BFile
    )
  Lob (resume,photo)Store As (
   Tablespace ts5_21  --指定存储的表空间
   Chunk 6k  --指定数据块大小
   Disable Storage In Row
  );

内部LOB的存储参数 
具体语法可以参见ORACLE文档， 
LOB ( lob项，...） STORE AS lob_segment_name  
CHUNK integer 
PCTVERSION integer 
CACHE 
NOCACHE LOGGING/NOLOGGING 
TABLESPACE tablespace_name 
STORAGE storage子句 
INDEX INDEX字句 

lob_segment_name:缺省式LOB$n 
CHUNK：连续分配在一起的BLOCK数目，存放连续的LOB数据。这些CHUNK的数据存放在LOB INDEX里面，使用内部LOB标示和LOB值作为键。 
PCTVERSION：LOB一致读需要的系统空间。一旦LOB申请超过PCTVERSION的值，ORACLE就会收旧的空间并REUSE之。 
CACHE：使用SGA区的DB BUFFER CACHE处理LOB的READ/WRITE。 
NOCACHE LOGGING：不使用SGA区的BUFFER，数据的改变纪录到REDO LOG。 
  存取LOB比较频繁时，使用CACHE 
  存取LOB不频繁时，使用NOCACHE 
NOCACHE NOLOGGING：不使用SGA区的BUFFER和REDO LOG 

2，插入大对象列
  ①先插入普通列数据
 
  ②遇到大对象列时，插入空白构造函数。
   字符型：empty_clob()，empty_nclob()
   二进制型：empty_blob()
   二进制文件类型：BFileName函数指向外部文件。
     BFileName函数：
      BFileName(‘逻辑目录名’，‘文件名’);
      逻辑目录名只能大写，因为数据词典是以大写方式存储。Oracle是区分大小写的。
      在创建时，无需将BFileName函数逻辑目录指向物理路径，使用时才做检查二者是否关联。
   例子：
   Insert Into tLob Values(1,'Gene',empty_clob(),empty_blob(),bfilename('MYDIR','IMG_0210.JPG'));
  
  ③将逻辑目录和物理目录关联。(如果是二进制文件类型)
   授予 CREATE ANY DIRECTORY 权限
    Grant  CREATE ANY DIRECTORY  TO 用户名 WITH ADMIN OPTION；
   关联逻辑目录和物理目录
    本地
    Create Directory  逻辑目录名  As  ‘文件的物理目录’；
    网络：
    Create Directory  逻辑目录名  As  ‘//主机名(IP)/共享目录’；
   例子：
    Create Directory  MYDIR As 'E:/Oracle';
 
  插入例子：
  insert into tlob values(1,'Gene','CLOB大对象列',empty_blob(),bfilename('MYDIR','IMG_0210.JPG'));
 

 

大对象数据的读取和操作：DBMS_LOB包

 

在oracle中有多种方法可以对lob数据进行操作,但是最常用的为dbms_lob包,它主要提供了以下几个过程供用户对内部lob字段进行维护:

APPEND（） 将源LOB中的内容加到目的LOB中
COPY（） 从源LOB中复制数据到目的LOB
ERASE（） 删除LOB中全部或部分内容
TRIM（） 将LOB值减少到指定的长度
WRITE（） 向LOB 中写入数据
COMPARE（） 比较两个同种数据类型的LOB的部分或全部值是否相同
GETLENGTH（） 获取LOB的长度
READ（） 从LOB中读出数据

  DBMS_LOB包：包含处理大对象的过程和函数
  /*
  insert into tlob values(1,'Gene','CLOB大对象列',empty_blob(),bfilename('MYDIR','IMG_0210.JPG'));
  insert into tlob values(2,'Jack','CLOB大对象列',empty_blob(),bfilename('MYDIR','IMG_0210.JPG'));
  insert into tlob values(3,'Mary','大对象列CLOB',empty_blob(),bfilename('MYDIR','IMG_0210.JPG'));
  */
  1，读取大对象数据的过程和函数
   ①：DBMS_LOB.Read()：从LOB数据中读取指定长度数据到缓冲区的过程。
     DBMS_LOB.Read(LOB数据，指定长度，起始位置，存储返回LOB类型值变量)；
    例子：
    Declare
       varC clob;
      vRStr  varchar2(1000);
       ln number(4);
       Strt number(4);
    Begin
       select resume into varC from tlob where no = 1;
       ln := DBMS_LOB.GetLength(varC);
       Strt := 1;
       DBMS_LOB.Read(varC, ln, Strt, vRStr);
       DBMS_output.put_line('Return:  '||vRStr);
    End;
 
   ②：DBMS_LOB.SubStr():从LOB数据中提取子字符串的函数。
     DBMS_LOB.SubStr(LOB数据，指定提取长度，提取起始位置):
     例子：
    Declare
      varC clob;
       vRStr  varchar2(1000);
       ln number(4);
       Strt number(4);
    Begin
       select resume into varC from tlob where no = 1;
       ln := 4;
       Strt := 1;
       vRStr := DBMS_LOB.SubStr(varC, ln, Strt);
       DBMS_output.put_line('结果为:  '||vRStr);
    End;
  
   ③：DBMS_LOB.InStr()：从LOB数据中查找子字符串位置的函数。
    DBMS_LOB.InStr(LOB数据, 子字符串);
    例子：
    Declare
       varC clob;
       vSubStr  varchar2(1000);
       vRStr  varchar2(1000);
       ln number(4);
    Begin
       select resume into varC from tlob where no = 1;
       vSubStr := '大对象';
       ln := DBMS_LOB.InStr(varC,vSubStr);
       DBMS_output.put_line('位置为:  '||ln);
    
     vRStr := DBMS_LOB.SubStr(varC, Length(vSubStr), ln);
     DBMS_output.put_line('位置为'||ln||'长度为'||Length(vSubStr)||'的子字符串为：'||vRStr);
    End;
  
   ④：DBMS_LOB.GetLength()：返回指定LOB数据的长度的函数。
    DBMS_LOB.GetLength(LOB数据)；
  
   ⑤：DBMS_LOB.Compare()：比较二个大对象是否相等。返回数值0为相等，-1为不相等。
    DBMS_LOB.Compare(LOB数据，LOB数据)；
    例子：
    Declare
       varC1 clob;
       varC2 clob;
       varC3 clob;
       ln number(4);
    Begin
       select resume into varC1 from tlob where no = 1;
       select resume into varC2 from tlob where no = 2;
       select resume into varC3 from tlob where no = 3;
       ln := DBMS_LOB.Compare(varC1,varC1);
       DBMS_output.put_line('比较的结果为:  '||ln);
       ln := DBMS_LOB.Compare(varC2,varC3);
       DBMS_output.put_line('比较的结果为:  '||ln);
    End;

  2，操作大对象数据的过程
   操作会改变数据库中原有数据，需要加上Updata锁锁上指定数据列，修改完后提交事务。
  
   ①：DBMS_LOB.Write()：将指定数量的数据写入LOB的过程。
    DBMS_LOB.Write(被写入LOB, 写入长度(指写入LOB数据)，写入起始位置(指被写入LOB)，写入LOB数据);
    例子：
    Declare
       varC clob;
       vWStr  varchar2(1000);
       vStrt number(4);
     ln number(4);
    Begin
       vWStr := 'CLOB';
       ln := Length(vWStr);
       vStrt := 5;
       select resume into varC from tlob where no = 1 FOR UPDATE;
       DBMS_LOB.Write(varC, ln, vStrt, vWStr);
       DBMS_output.put_line('改写结果为:  '||varC);
       Commit;
    End;
  
   ②：DBMS_LOB.Append()：将指定的LOB数据追加到指定的LOB数据后的过程。
    DBMS_LOB.Append(LOB数据，LOB数据)；
    例子：
    Declare
       varC clob;
       vAStr  varchar2(1000);
    Begin
     vAStr := '，这是大对象列';
     select resume into varC from tlob where no = 1 FOR UPDATE;
     DBMS_LOB.Append(varC, vAStr);
     commit;
     DBMS_output.put_line('追加结果为:  '||varC);
    End;
  
   ③：DBMS_LOB.Erase()：删除LOB数据中指定位置的部分数据的过程；
    DBMS_LOB.Erase(LOB数据，指定删除长度, 开始删除位置)；
    例子：
    Declare
       varC clob;
       ln number(4);
       strt number(4);
    Begin
     ln := 1;
     strt := 5;
       select resume into varC from tlob where no = 1 FOR UPDATE;
       DBMS_LOB.Erase(varC, ln, strt);
       commit;
       DBMS_output.put_line('擦除结果为:  '||varC);
    End; 
  
   ④：DBMS_LOB.Trim()：截断LOB数据中从第一位置开始指定长度的部分数据的过程；
    DBMS_LOB.Trim(LOB数据，截断长度)；
    例子：
    Declare
      varC clob;
      ln number(4);
    Begin
     ln := 4;
       select resume into varC from tlob where no = 1 FOR UPDATE;
       DBMS_LOB.Trim(varC, ln);
       COMMIT;
       DBMS_output.put_line('截断结果为:  '||varC);
    End;
  
   ⑤：DBMS_LOB.Copy()：从指定位置开始将源LOB复制到目标LOB；
    DBMS_LOB.Copy(源LOB，目标LOB，复制源LOB长度，复制到目标LOB开始位置，复制源LOB开始位置)
    例子：
    Declare
       vDEST_LOB clob;
       vSRC_LOB clob;
       AMOUNT number;
       DEST_OFFSET number;
       SRC_OFFSET number;
    Begin
       select resume into vDEST_LOB from tlob where no = 1 FOR UPDATE;
       select resume into vSRC_LOB from tlob where no = 2 ;
      
       AMOUNT := DBMS_LOB.GetLength(vSRC_LOB);
       DEST_OFFSET := DBMS_LOB.GetLength(vDEST_LOB)+1;
       SRC_OFFSET := 1;
      
       DBMS_LOB.Copy(vDEST_LOB, vSRC_LOB, AMOUNT, DEST_OFFSET, SRC_OFFSET);
       DBMS_output.put_line('拷贝结果为:  '||vDEST_LOB);
    End;

 

关于使用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。

 

 

补充文档:

oracle LOB介绍:
http://www.itpub.net/thread-22626-1-1.html

 

---end----