ORACLE常用数据库类型(转)

  1. 云栖社区>
  2. 博客>
  3. 正文

ORACLE常用数据库类型(转)

developerguy 2016-05-10 11:04:00 浏览898
展开阅读全文

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


大对象数据的录入

 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介绍

发信人: oracle_mo(洗洗睡了... ..)
整理人: zrsoft(2001-11-05 21:43:17), 站内信件  
0、LARGE OBJECT   
ORACLE8中有4种LOB  
- BLOB:Binary Large Object  
- CLOB:Character Large Object  
- NCLOB:固定长度的多字节Character Large Object  
- BFILE:DB外部的二进制文件  
它们分为两类:  
内部LOB:存放在DB内部,包括BLOB,CLOB,BCLOB  
外部文件:存放在DB外面,就是BFILE  

要注意的是ORACLE8不自动转换这些类型的数据。  

1、LONG和LOB的比较  
LONG/LONG RAW LOB  
--------------------------------------------------  
表中只能由一个列 可以有多列  
最大2G 最大4G  
SELECT返回值 SELECT返回指针  
存放在DB内 可以在DB的内或者外  
不支持OBJECT类型 支持  
顺序存取 随机存取  
--------------------------------------------------  

NCLOB不支持OBJECT类型  
LOB小于4000字节时是内部存放  

2、LOB解析  
LOB有两个不同的部分  
- LOB值:LOB代表的数据  
- LOB指针:LOB存放数据的位置  
LOB列内部不存放数据,而是LOB值的位置。当创建内部LOB时,值存放在LOB SEGMENT中,指向OUT-OF-LIN数据的指针放在列中。对外部LOB,只在列中存放位置。  

3、内部LOB  
就是存放在DB内部的LOB,包括BLOB,CLOB,NCLOB。它们可以是  
用户自定义的类型中的属性  
表中某列  
SQL 变量  
程序host变量  
PL/SQL中的变量、参数、返回值  

内部LOB可以使用ORACLE的并发机制、REDO LOG、RECOVERY机制。  
BLOB被ORACLE8解释为二进制位流,类似LONG RAW。  
CLOB解释为单字节字符流  
NCLOB是固定的多字节字符流,基于DB NATIONAL字符集的字节长度。  

例子:  
CREATE TYPE picture_typ AS OBJECT (image BLOB);  

CREATE TABLE person_tab  
( pname VARCHAR2(20),  
  RESUME CLOB,  
  picture picture_typ  
);  

上面的语句完成后,数据将存放在5个物理SEGMENT中。  
- TABLE person_tab在缺省TABLESPACE,  
- RESUME存放的LOB SEGMENT   
- PICTURE存放的LOB SEGMENT  
- 标示RESUME存放位置的LOB INDEX SEGMENT  
- 标示PICTURE存放位置的LOB INDEX SEGMENT  
LOB INDEX是隐式创建的。  

当INSERT或者OBJECT CACHE中的OBJECT刷新到SERVER时,LOB生成。可以使用DBMS_LOB包和OCI来处理LOB。当ROW删除时,相应的内部LOB也会删除。UPDATE必须处理整个LOB值,不能UPDATE其中的一部分。  

4、内部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  
    
INDEX子句  
INDEX  lob_index_segtment_name   
INITTRANS integer  
MAXTRANS integer  
TABLESPACE tablespace_name  
STORAGE storage子句  

如果没有设置LOB存储参数和INDEX STROAGE,则采用如下缺省值  
CHUNK=1 DB_BLOCK  
PCTVERSION=10  
NOCACHE  
NOLOGGING  

例子:  
CREATE TABLE APARTMENTS (   
floor_plan BLOB,  
contract  CLOB,  
name VARCHAR2(10))  
LOB (floor_plan, contract)STORAGE AS(  
STORAGE (INITIAL 100K NEXT 100K PCTINCREASE 0)  
CHUNK 10  
PCTVERSION 20  
NOCACHE  
NOLOGGING  
INDEX (INITIAL 100K NEXT 100K)  
);  

5、内部LOB的并发  
LOB的读一致和其他ORACLE类型一样,但是它是在CHUNK级别上作VERSION的。  

6、外部LOB  
ORACLE8允许定义BFILE类型,可以把外部文件和BFILE对象连接起来,同时能提供BFILE的安全机制。  
BFILE对处理不需要transaction控制的OS文件很有用处。  
对BFILE的处理需要DBMS_LOB或者OCI。  
BFILE必须是READ-ONLY的,文件应该放在ORACLE能存取的地方。如果删除BFILE对象,外部文件并不删除。  
例子:  
CREATE TABLE home_page(  
EMPLOYEE REF EMPLOYEE_TYP,  
LAST_UPDATE DATE,  
HOMEPAGE BFILE);  

CREATE TYPE personal_info_typ AS OBJECT   
( EMPLOYEE REF employee_typ,  
PICTURE BFILE,  
THUMBPRINT BFILE,  
REINAPRINT BFILE  
);  

其实在BFILE对应的列或者属性中存放的是BFILE的位置,即是物理文件的位置。  
读BFILE不通过SGA.  

7、BFILE的安全  
ORACLE8提供安全机制。文件必须和DB在一台机器上,读取不存在文件的timeout由os决定。可以和内部LOB的读取方法一致,但是必须注意:文件的权限、文件系统空间限制、其他对文件的维护、OS允许的最大文件大小。  
ORACLE8不对BFILE采取TRANSACTION的支持,ORACLE的BACKUO和RECOVERY也不支持BFILE。  


8、目录DIRECTORY  
这是ORACLE为了管理BFILE引入的新元素,它指定SERVER文件系统的目录,可以把文件系统的目录抽象为数据库对象,更具有灵活性。  
DIRECTORY由DBA建立,系统用户拥有,通过GRANT/REVOKE来确定哪些用户有权限。  

CREATE OR REPLACE DIRECTORY 目录名 AS 路径名  
DROP DIRECTORY 目录名  

存取BFILE在如下情况时候产生异常:  
-用户没有操作DIRECTORY的权限  
-DIRECTORY所对应的物理目录不存在,或者没有存取该目录的权限。  
-文件不存在  

文件和权限的检查是在文件存取时候检查的,创建时候并不报错。  
不支持逻辑路径  

系统新增加了CREATE ANY DIRECTORY和DROP  ANY DIRECTORY。对DIRECTORY授权只有READ。  

对CREATE DIRECTORY和GRANT READ ON DIRECTORY是可以AUDIT的。  

8、DIRECTORY建立的原则  
DIRECTORY不要和DB DATAFILE在相同的目录下。  
要有选择的赋予CREATE ANY DIRECTORY和DROP ANY DIRECTORY ROLE。  
在建立之前要在OS级别上设置权限。  
如果移植DB到其他机器,需要注意DIRECTORY的路径是否改变。  

9、管理LOB  
ORACLE 通类似文件操作一样的接口:DBMS_LOB, OCI8。同时还有SQL语句的一些支持。  
管理LOB的一般方法:  
1)CREATE/POPULATE 包含LOB的TABLE,  
2)在程序中DECLARE AND INITIALIZE LOB Locator  
3)用SELECT FOR UPDATE 锁定包含LOB的ROW,  
4)用DBMS_LOB或者OCI维护相关LOB值  
5)COMMIT  

管理BFILE的一般方法  
1)建立OS目录,授予ORACLE 用户READ的权限  
2)把文件放到该目录  
3)建立包含BFILE的TABLE  
4)建立DIRECTORY,用GRANT授权  
5)在TABLE中INSERT与文件相关的值  
6)在程序中声明LOB Locator  
7)取得包含LOB Locator的行  
8)用DBMS_LOB和OCI读取BFILE  

10、LOB的操作  
可以用SQL、PL/SQL、3GL中嵌入式SQL或者OCI中的变量值直接INSERT 到LOB中。  

可以用其他的LOB、NULL或者EMPTY_CLOB()/EMPTY_BLOB()的值UPDATE LOB。当把一个LOB赋给另一个时,其实是新建了一个LOB。这些操作不需要SELECT FOR UPDATE。只有在更新LOB的其中一部分的时候才需要先LOCK。  
ORACLE8不会自动转换CLOB和BLOB。  
最好的办法是OCILobWrite,以为它使用ORACLE的流机制,最快、使用更好的空间和REDO。  

删除LOB的方法  
DELETE ....  
TRUNCATE TABLE....  
DROP TABLE ...  
删除后注意在OS级别上删除相关文件。  
如果只是要去掉LOB的Locator,可以用NULL或者空字符串''来UPDATE。  

11、DBMS_LOB包  
DBMS_LOB包用SYS用户提交DBMSLOB.SQL和PRVTLOB.PLB脚本。它们包含在CATPROC.SQL中。用户要授权才能使用它。匿名BLOCK中的DBMS_LOB例程使用当前用户的权限。STORED PROCEDURE中的DBMS_LOB调用使用其所有者的权限。  
它不支持BFILE的并发控制机制。  
你必须控制LOB的LOCK,DBMS_LOB不会隐式的LOCK LOB所在的ROW。  
DBMS_LOB.LOBMAXSIZE = 4G  

包含两类操作  
1)APPEND、COPY, EARSE,TRIM,WRITE,FILECLOSE,FILECLOSEALL,FILEOPEN,LOADFROMFILE  
2)COMPARE,FILEGETNAME.INSTR,GETLENGTH,READ,SUBSTR,FILEEXISTS,FILEISOPEN  

DBMS_LOB的任何参数为NULL,则返回值为NULL.  
如果目标LOB/BFILE=NULL,将触发异常。  
BLOB/BFILE的OFFSET以BYTE为单位,CLOB/NCLOB以字符为单位。且不能为负值,否则会触发异常。缺省OFFSET为1。  
参数不能为负值。  

PROCEDURE APPEND(DEST_LOB, SRC_LOB);  
把一个LOB加到另一个LOB中。  
FUNCTION COMPARE(L1, L2, AMOUNT, OFFSET1,OFFSET2)  
  =0  ,相同  
=-1,第一个小  
=1,第一个大  

12、系统管理方面的问题  
EXPORT/IMPORT支持LIBRARY和DIRECTORY,支持LOB。  
IMPORT转换EXPORT文件中的CLOB到当前缺省的字符集。NCLOB转换到当前Nationanl字符集。BLOB不转换。  
BFILE不能EXP/IMP。只有BFILE的名字和DIRECTORY被EXPORT出来。 

http://www.cnblogs.com/m-cnblogs/archive/2010/06/30/2708743.html

 

网友评论

登录后评论
0/500
评论