oracle 中CLOB与BLOB、BFILE

一、oracle中Blob和Clob类型的区别
BLOB和CLOB都是大字段类型,BLOB是按二进制来存储的,而CLOB是可以直接存储文字的。其实两个是可以互换的的,或者可以直接用LOB字段代替这两个。但是为了更好的管理ORACLE数据库,通常像图片、文件、音乐等信息就用BLOB字段来存储,先将文件转为二进制再存储进去。而像文章或者是较长的文字,就用CLOB存储,这样对以后的查询更新存储等操作都提供很大的方便。
BLOB全称为二进制大型对象(Binary Large Object)。它用于存储数据库中的大型二进制对象。可存储的最大大小为4G字节 
CLOB全称为字符大型对象(Character Large Object)。它与LONG数据类型类似,只不过CLOB用于存储数据库中的大型单字节字符数据块,不支持宽度不等的字符集
可存储的最大大小为4G字节,Clob可以存储单字节字符数据,Blob可以存储无结构的二进制数据
二、empty_clob()和empty_blob()的使用:
在向带有Lob类型的字段加入数据时,使用EMPTY_CLOB()初始化CLOB字段,然后再使用输出流向字段中写数据(这些数据往往都是字节流量相对较大的). 如果是直接在oracle客户端向表中写数据,就不用这么麻烦了.

三、Blob 使用

Oracle中插入图片并显示(用BLOB类型) 要在oracle里面存入图片 用 blob类型

首先在数据库里建立:

--连接到管理员 conn sys/tbsoft as sysdba;

--为scott用户授权

grant create any directory to scott;

--回到scott用户

conn scott/tiger;

--创建存储图片的表 CREATE TABLE IMAGE_LOB (T_ID VARCHAR2 (5) NOT NULL,T_IMAGE BLOB NOT NULL);

--创建存储图片的目录 CREATE OR REPLACE DIRECTORY IMAGES AS 'C:\picture';

--在c:下自己建一个叫picture的文件夹

CREATE OR REPLACE PROCEDURE IMG_INSERT (TID VARCHAR2,FILENAME VARCHAR2) AS F_LOB BFILE;--文件类型 B_LOB BLOB; BEGIN iNSERT INTO IMAGE_LOB (T_ID, T_IMAGE) VALUES (TID,EMPTY_BLOB ()) RETURN T_IMAGE INTO B_LOB; --插入空的blob F_LOB:= BFILENAME ('IMAGES', FILENAME); --获取指定目录下的文件 DBMS_LOB.FILEOPEN(F_LOB, DBMS_LOB.FILE_READONLY); --以只读的方式打开文件 DBMS_LOB.LOADFROMFILE (B_LOB, F_LOB,DBMS_LOB.GETLENGTH (F_LOB)); --传递对象 DBMS_LOB.FILECLOSE (F_LOB); --关闭原始文件 COMMIT; END; /

--在C:\picture下放一张图片1.gif

--将该图片存入表 call IMG_INSERT('1','1.gif');

四、Oracle中varchar2 转换为CLOB

原来表中的列定义成VARCHAR2类型,众所周知,VARCHAR2类型最大支持长度为4000。如果由于业务需要,想把此列转换为CLOB类型,在Oracle中直接通过ALTER语句转换是行不通的。下面根据具体事例讲解在Oracle数据库中如何把表列由VARCHAR2类型转换为CLOB类型。

示例准备

1. 新建两张张表TB_WITHOUT_DATA(此VARCHAR2列不包含数据)和TB_WITH_DATA(此Varchar2列包含数据)

  1. create table TB_WITHOUT_DATA  
  2. (  
  3.   id NUMBER,  
  4.   name VARCHAR2(100),  
  5.   description VARCHAR2(2000)  
  6. );  

  1. create table TB_WITH_DATA  
  2. (  
  3.   id NUMBER,  
  4.   name VARCHAR2(100),  
  5.   description VARCHAR2(2000)  
  6. );  
  7.   
  8. insert into TB_WITH_DATA VALUES (1,'David Louis','He is capable of resolving such kind of issue');  
  9. insert into TB_WITH_DATA VALUES (2,'German Noemi','She is very beatiful and charming');  
  10. insert into TB_WITH_DATA VALUES (3,'Oliver Queen','He is main actor in the Green Arrow');  
  11. insert into TB_WITH_DATA VALUES (4,'Mark Williams','He plays snooker very well');  
  12. insert into TB_WITH_DATA VALUES (5,'Sita Rama Raju Kata','I do not know this guy');  
  13. insert into TB_WITH_DATA VALUES (6,'Promethus','This is a very nice movie');  
  14. commit;  

错误方法

  1. ALTER TABLE TB_WITHOUT_DATA MODIFY description clob;  
错误信息:

SQL Error: ORA-22858: invalid alteration of datatype 22858. 00000 -  "invalid alteration of datatype" *Cause:    An attempt was made to modify the column type to object, REF,            nested table, VARRAY or LOB type. *Action:   Create a new column of the desired type and copy the current            column data to the new type using the appropriate type            constructor.

解决方法

 方法一:对于此列没有数据的可通过以下方法修改-首先把该列改成Long类型,然后再改成clob类型

  1. alter table TB_WITHOUT_DATA modify description long;--首先改成Long类型  
  2. alter table TB_WITHOUT_DATA modify description clob;--在Long类型的基础上改成clob类型  

注:对于此列已经存在数据的,不能通过此方法,否则会报如下错误:

  1. alter table TB_WITH_DATA modify description long;--更改包含数据的列 
  1. SQL Error: ORA-01439: column to be modified must be empty to change datatype  
  2. 01439. 00000 -  "column to be modified must be empty to change datatype"  

方法二:此方法适合此列包含数据和此列不包含数据两种情况

步骤一:把原来表中该列重命名

  1. alter table TB_WITHOUT_DATA rename column description to description_bak;  
  2. alter table TB_WITH_DATA rename column description to description_bak;  

步骤二:在表中增加该列,并指定改列类型为clob

  1. alter table TB_WITHOUT_DATA add description clob;  
  2. alter table TB_WITH_DATA add description clob;  
步骤三:对此列包含数据的需要包数据从步骤一重命名列中拷出(对于此列没有数据的此步骤省略)
  1. update TB_WITH_DATA set description=description_bak;  
  2. commit;  
步骤四:删除步骤一中的备份列
  1. alter table TB_WITHOUT_DATA drop column description_bak;  
  2. alter table TB_WITH_DATA drop column description_bak;  
步骤五:验证

1) 表结构验证

  1. DESC TB_WITHOUT_DATA  
  2. Name        Null Type            
  3. ----------- ---- -------------   
  4. ID               NUMBER          
  5. NAME             VARCHAR2(100)   
  6. DESCRIPTION      CLOB   
  1. DESC TB_WITH_DATA  
  2. Name        Null Type            
  3. ----------- ---- -------------   
  4. ID               NUMBER          
  5. NAME             VARCHAR2(100)   
  6. DESCRIPTION      CLOB    
2) 数据验证
  1. select * from TB_WITH_DATA;  
  2.   
  3.         ID NAME                       DESCRIPTION                                       
  4. ---------- -------------------------- ------------------------------------------------  
  5.          1 David Louis                He is capable of resolving such kind of issue     
  6.          2 German Noemi               She is very beatiful and charming                 
  7.          3 Oliver Queen               He is main actor in the Green Arrow               
  8.          4 Mark Williams              He plays snooker very well                        
  9.          5 Sita Rama Raju Kata        I do not know this guy                            
  10.          6 Promethus                  This is a very nice movie                         
  11.   
  12.  6 rows selected   

方法三:此方法适合此列包含数据和此列不包含数据两种情况

在讲解方法三之前,需要包表恢复到准备阶段,由于时间关系,直接通过drop然后re-create方法,脚本如下:

  1. drop table TB_WITHOUT_DATA;  
  2. drop table TB_WITH_DATA;  
  3.   
  4. create table TB_WITHOUT_DATA  
  5. (  
  6.   id NUMBER,  
  7.   name VARCHAR2(100),  
  8.   description VARCHAR2(2000)  
  9. );  
  10.   
  11. create table TB_WITH_DATA  
  12. (  
  13.   id NUMBER,  
  14.   name VARCHAR2(100),  
  15.   description VARCHAR2(2000)  
  16. );  
  17.   
  18. insert into TB_WITH_DATA VALUES (1,'David Louis','He is capable of resolving such kind of issue');  
  19. insert into TB_WITH_DATA VALUES (2,'German Noemi','She is very beatiful and charming');  
  20. insert into TB_WITH_DATA VALUES (3,'Oliver Queen','He is main actor in the Green Arrow');  
  21. insert into TB_WITH_DATA VALUES (4,'Mark Williams','He plays snooker very well');  
  22. insert into TB_WITH_DATA VALUES (5,'Sita Rama Raju Kata','I do not know this guy');  
  23. insert into TB_WITH_DATA VALUES (6,'Promethus','This is a very nice movie');  
  24. commit;  
步骤一:重命名两张表
  1. rename TB_WITHOUT_DATA to TB_WITHOUT_DATA_BAK;  
  2. rename TB_WITH_DATA to TB_WITH_DATA_BAK;  
步骤二:创建两张新表(通过以下语句创建两张表)
  1. create table TB_WITHOUT_DATA  
  2. as  
  3. select id, name, to_clob(description) description  
  4. from TB_WITHOUT_DATA_BAK;  
  5.   
  6. create table TB_WITH_DATA  
  7. as  
  8. select id, name, to_clob(description) description  
  9. from TB_WITH_DATA_BAK;  
表结构与数据验证:
  1. desc TB_WITHOUT_DATA  
  2. Name        Null Type            
  3. ----------- ---- -------------   
  4. ID               NUMBER          
  5. NAME             VARCHAR2(100)   
  6. DESCRIPTION      CLOB  
  7.   
  8. desc TB_WITH_DATA  
  9. Name        Null Type            
  10. ----------- ---- -------------   
  11. ID               NUMBER          
  12. NAME             VARCHAR2(100)   
  13. DESCRIPTION      CLOB   
  14.   
  15. select * from TB_WITH_DATA;  
  16.   
  17. select * from TB_WITH_DATA;  
  18.   
  19.         ID NAME                       DESCRIPTION                                       
  20. ---------- -------------------------- ------------------------------------------------  
  21.          1 David Louis                He is capable of resolving such kind of issue     
  22.          2 German Noemi               She is very beatiful and charming                 
  23.          3 Oliver Queen               He is main actor in the Green Arrow               
  24.          4 Mark Williams              He plays snooker very well                        
  25.          5 Sita Rama Raju Kata        I do not know this guy                            
  26.          6 Promethus                  This is a very nice movie                         
  27.   
  28.  6 rows selected   
步骤三:删除备份表:
  1. DROP TABLE TB_WITHOUT_DATA_BAK;  
  2. DROP TABLE TB_WITH_DATA_BAK; 
五、 bfile是把文件放在数据库外,数据库只是记录了文件位置(可以指向表)

blob是把文件内容放在数据库里面,但也得借助bfile才能把文件内容存放到数据库。

BFIle的使用实例:

在客户端用SQL*PLUS输入以下红色代码: 以sysdba身份登录 1.先在oracle数据库中建立一个目录别名,用于将文件定位指针映射到文件系统:   create DIRECTORY 'tmpdir' AS 'D:/tmp';   tmpdir表示逻辑目录名,' D:/tmp'是实际目录。 注意该目录oracle应该有读权限  2.根据需要授权   GRANT READ ON DIRECTORY tmpdir TO scott;  3.以scott用户登录 建立一个含有bfile字段的表  create table bfiletest(id number(3), fname bfile); 4.插入数据  这里需要使用bfilename来进行bfile字段的insert 或者 update操作  INSERT INTO bfiletest  VALUES (1, BFILENAME ('TMPDIR', 'a.JPG'));  bfilename的参数1是DIRECTORY名,参数2是文件名。注意:这一行中插入的是 一个指向D:/tmp的文件定位指针映射,不是文件本身。

 
阅读更多
版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/Eason_oracle/article/details/53377280
个人分类: oracle
想对作者说点什么? 我来说一句

没有更多推荐了,返回首页

关闭
关闭
关闭