oracle mysql blob_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类型列

/* 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。可选

参与评论 您还未登录,请先 登录 后发表或查看评论

“相关推荐”对你有帮助么?

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
©️2022 CSDN 皮肤主题:深蓝海洋 设计师:CSDN官方博客 返回首页

打赏作者

与阳光共进早餐

你的鼓励将是我创作的最大动力

¥2 ¥4 ¥6 ¥10 ¥20
输入1-500的整数
余额支付 (余额:-- )
扫码支付
扫码支付:¥2
获取中
扫码支付

您的余额不足,请更换扫码支付或充值

打赏作者

实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值