【转】oracle对BLOB类型数据的操作与性能问题

Oracle的Blob字段比较特殊，他比long字段的性能要好很多，可以用来保存例如图片之类的二进制数据。

　　写入Blob字段和写入其它类型字段的方式非常不同，因为Blob自身有一个cursor,你必须使用cursor对blob进行操作，因而你在写入Blob之前，必须获得cursor才能进行写入，那么如何获得Blob的cursor呢？

　　这需要你先插入一个empty的blob,这将创建一个blob的cursor,然后你再把这个empty的blob的cursor用select查询出来，这样通过两步操作，你就获得了blob的cursor,可以真正的写入blob数据了。

　　举例如下：

　　Oracle系列：LOB大对象处理

　　主要是用来存储大量数据的数据库字段，最大可以存储4G字节的非结构化数据。

　　主要介绍字符类型和二进制文件类型LOB数据的存储，单独介绍二进制类型LOB数据的存储。

　　一，Oracle中的LOB数据类型分类

　　1,按存储数据的类型分：

　　①字符类型：

　　CLOB:存储大量 单字节 字符数据。

　　NLOB:存储定宽 多字节 字符数据。

　　②二进制类型：

　　BLOB:存储较大无结构的二进制数据。

　　③二进制文件类型：

　　BFILE:将二进制文件存储在数据库外部的操作系统文件中。存放文件路径。

　　2,按存储方式分：

　　①存储在内部表空间：

　　CLOB,NLOB和BLOB

　　②指向外部操作系统文件：

　　BFILE

　　二，大对象数据的录入

　　1,声明LOB类型列

　　Create Table tLob （

　　no Number（4），

　　name VarChar2（10），

　　resume CLob,

　　photo BLob,

　　record BFile

　　）

　　Lob （resume,photo）Store As （

　　Tablespacets5_21  --指定存储的表空间

　　Chunk 6k --指定数据块大小

　　Disable Storage In Row

　　）；

　　2,插入大对象列

　　①先插入普通列数据

　　②遇到大对象列时，插入空白构造函数。

　　字符型：empty_clob（），empty_nclob（）

　　二进制型：empty_blob（）

　　二进制文件类型：BFileName函数指向外部文件。

　　BFileName函数：

　　BFileName（'逻辑目录名','文件名'）；

　　逻辑目录名只能大写，因为数据词典是以大写方式存储。Oracle是区分大小写的。

　　在创建时，无需将BFileName函数逻辑目录指向物理路径，使用时才做检查二者是否关联。

　　例子：

　　Insert Into tLobValues（1,'Gene',empty_clob（），empty_blob（），bfilename（'MYDIR','IMG_0210.JPG'））；

插入字符串到BLOB字段

  (2006-11-03 21:30:11)

转载▼

分类： oracle9i

插入字符串到BLOB字段

--创建测试表
CREATE Table T_BLOB
(ID NUMBER,
 BLB Blob);
--编写PLSQL
declare
    v_blob BLOB;
    amount BINARY_INTEGER;
    offset INTEGER;
    v_char VARCHAR2(1000);
Begin
  --往BLOB类型的字段插入记录，不能直接填入值，必须先往BLOB字段插入一个EMPTY_BLOB()
    INSERT INTO T_BLOB VALUES(1,EMPTY_BLOB());
    COMMIT;
  --更新和新增一样要将BLOB字段设置为EMPTY_BLOB()
    update T_BLOB set blb = empty_blob() where id = 1;
    select blb into v_blob from T_BLOB where id = 1 for update;
    DBMS_LOB.OPEN(v_blob,DBMS_LOB.LOB_READWRITE);
    v_char := 'This is an sample!这是个例子！';
    amount := LENGTHB(v_char);
    offset := 1;

  --UTL_RAW.cast_to_raw函数将字符串转换成二进制数 
    DBMS_LOB.WRITE(v_blob,amount,offset,UTL_RAW.cast_to_raw(v_char));
    DBMS_LOB.CLOSE(v_blob);
    commit;
end;

　　③将逻辑目录和物理目录关联。（如果是二进制文件类型）

　　授予 CREATE ANY DIRECTORY权限

　　Grant  CREATE ANY DIRECTORY  TO用户名 WITH ADMIN OPTION;

　　关联逻辑目录和物理目录

　　本地

　　CreateDirectory  逻辑目录名 As  '文件的物理目录';

　　网络：

　　CreateDirectory  逻辑目录名 As  '\\主机名（IP）\共享目录';

　　例子：

　　CreateDirectory  MYDIR As 'E:\Oracle';

　　插入例子：

　　insert into tlobvalues（1,'Gene','CLOB大对象列',empty_blob（），bfilename（'MYDIR','IMG_0210.JPG'））；

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

　　DBMS_LOB包：包含处理大对象的过程和函数

　　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 FORUPDATE;

　　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数据类型的性能问题：

　　Clob/blob实现是比较复杂的，这里只提提几个和性能相关的点，当然能不用lob尽量不用：

　　a、  一个lob字段包括lobindex和lobsegment

　　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

　　使用其他数据类型需要考虑的性能问题：

　　1、 Char

　　定长格式字符串，在数据库中存储时不足位数填补空格，不建议使用，会带来不必要的麻烦

　　a、

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

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

　　c、  浪费存储空间

　　2、 Varchar2/Varchar

　　不定长格式字符串，对于4000字节以内的字符串，建议都用该类型

　　a、 网上有说char比varchar2性能好，但是如果你有兴趣做测试的话，会发现没有区别（如果发生行迁移，可以通过pctfree来调整）

　　b、  充分利用存储空间

　　3、 Long/long raw

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

　　Long类型有很多限制

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

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

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

　　4、 Number

　　定义Number的方法：Number（p,s）

　　其中p,s都是可选的：

　　a、  p代表精度，默认为38

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

　　5、 Date

　　Date类型是一个7字节的定长数据类型，没啥好说的，一个例子：性能a>b>c

　　a、Wheredate_colum>=to_date（'01-jan-2007','dd-mon-yyyy'）

　　and date_colum<DIV>

　　b、Wheretrunc（date_colum,'y'）=to_date（'01-jan-2007','dd-mon-yyyy'）

　　c、Where to_char（date_colum,'yyyy'）='2007'

　　6、 Timestamp/timestamp with time zone/timestamp with local timezone

　　和date类似，只不过它另外支持小数秒和时区。语法Timestamp（n），n指定秒的小数位数，取值范围0~9.可选。