Oracle数据库中的blob类型解析

2023-01-24 14:50:01

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类型列

 /* 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 );

（Tablespace ts5_21 --指定存储的表空间 Chunk 6k --指定数据块大小）

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

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、Where date_colum>=to_date(’01-jan-2007’,’dd-mon-yyyy’) and date_colum< DIV>

　　 b、Where trunc(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 time zone 和date类似，只不过它另外支持小数秒和时区。语法Timestamp(n)，n指定秒的小数位数，取值范围0～9。可选

码农公寓

相关文章