oracle对大对象类型操作:blob,clob,nclob

转载 2014年01月14日 16:50:02

1.基本介绍

Oracle和plsql都支持lob(large object) 类型,用来存储大数量数据,如图像文件,声音文件等。Oracle 9i realse2支持存储最大为4g的数据,oracle 10g realse1支持最大8到128万亿字节的数据存储,依赖于你的db的block size。

在plsql中可以申明的lob类型的变量如下: 

  • BFILE        二进制文件,存储在数据库外的操作系统文件,只读的。把此文件当二进制处理。 
  • BLOB        二进制大对象。存储在数据库里的大对象,一般是图像声音等文件。 
  • CLOB        字符型大对象。一般存储大数量文本信息。存储单字节,固定宽度的数据。 
  • NCLOB        字节字符大对象。存储单字节大块,多字节固定宽度,多字节变宽度数据。

Oracle将lob分类为两种:

  1. 存储在数据库里的,参与数据库的事务。BLOB,CLOB,NCCLOB。
  2. 存储在数据库外的BFILE,不参与数据库的事务,也就是不能rollback或commit等,它依赖于文件系统的数据完整性。
LONG和LONG RAW这两种数据类型也是存储字符的,但是有系列的问题,不建议使用,这里也就不讨论了。 

2.LOB的使用 

本部分不讨论lob的所有细节,只讨论lob的基本原理和在plsql中的基本使用,为plsql开发使用lob提供一个基础性指导。 
本部分使用的表是: 
/** 
table script 
**/ 
CREATE TABLE waterfalls ( 
       falls_name VARCHAR2(80),--name 
       falls_photo BLOB,--照片 
       falls_directions CLOB,--文字 
       falls_description NCLOB,--文字 
       falls_web_page BFILE);--指向外部的html页面 
/
这个表我们并不需要clob和nclob两个,只取一就可以,这里全部定义只是为了演示使用。

1. 理解LOB的Locator

表中的Lob类型的列中存储的只是存储指向数据库中实际存储lob数据的一个指针。 
在plsql中申明了一个lob类型的变量,然后从数据库中查询一个lob类型的值分配给变量,也只是将指针复制给了它,那么这个变量也会指向数据库中实际存放lob数据的地方。如:
--understanding lob locators 
  DECLARE 
       photo BLOB; 
    BEGIN 
       SELECT falls_photo 
         INTO photo 
         FROM waterfalls 
        WHERE falls_name='Dryer Hose'; 
Lob工作原理图解 
       从上面的图可以看出,要处理lob数据,必须先获得lob locators。我们可以通过一个select语句获取,当赋值给lob变量的时候,它也获得同样的lob locators。我们在plsql中处理可以使用dbms_lob包,里面内置了很多过程和函数来读取和修改我们的lob数据。下面给出处理lob数据的一般方法。 
1.        通过select语句获取一个lob locator。 
2.        通过调用dbms_lob.open打开lob。 
3.        调用dbms_lob.getchunksize获得最佳读写lob值。 
4.        调用dbms_lob.getlength获取lob数据的字节值。 
5.        调用dbms_lob.read获取lob数据。 
6.        调用dbms_lob.close关闭lob。 

2.Empty lob and Null lob

Empty的意思是我们已经获取了一个lob locator,但是没有指向任何lob数据。Null是定义了一个变量,但是没有获得lob locator。对lob类型的处理和其他类型不一样。如下面的例子:
/* null lob example*/
DECLARE
  directions CLOB; --定义了,但是没有分配值,为null 
BEGIN
  IF directions IS NULL THEN
    dbms_output.put_line('directions is null');
  ELSE
    dbms_output.put_line('directions is not null');
  END IF;
END;
/
DECLARE
  directions CLOB; --定义一个,并且分配值 
BEGIN
  --删除一行 
  DELETE FROM waterfalls WHERE falls_name = 'Munising Falls';
  --插入一行通过使用 EMPTY_CLOB(  ) to 建立一个lob locator 
  INSERT INTO waterfalls
    (falls_name, falls_directions)
  VALUES
    ('Munising Falls', empty_clob());
  --获得lob locator,上面插入的数据,因为我们插入的是一个empty_clob(),那么lob locator不指向任何数据,虽然给变量分配了只 
  SELECT falls_directions
    INTO directions
    FROM waterfalls
   WHERE falls_name = 'Munising Falls';
  IF directions IS NULL THEN
    dbms_output.put_line('directions is NULL');
  ELSE
    dbms_output.put_line('directions is not NULL'); --打印此句 
  END IF;
  dbms_output.put_line('Length = ' || dbms_lob.getlength(directions)); --结果为o 
END;
注意: 
  1. 上面例子中的empty_clob()是oracle的内置函数,创建了一个lob locator。但是我们没有让它指向任何数据,所以是empty。而且通过select语句给变量directions分配了lob locator,所以不是null,但是length为0,故为empty。 
  2. 在基本类型中,我们判断一个变量是不是有数据,只要is null就可以了。但是在lob类型中我们从以上的例子看出来是不正确的。Lob首先必须判断is null看是否分配lob locator,如果分配了还需要进一步检查length是否为0,看是否是empty,所以完整的是下面这样:
IF some_clob IS NULL THEN
    --如果is null为true表示未分配,肯定没有数据 
  ELSIF dbms_lob.getlength(some_clob) = 0 THEN
    --分配了length为0,也没有数据 
  ELSE
    --有数据 
  END IF;

3.建立LOB

在上面我们使用empty_clob()建立了一个空的clob,lob locator只是一个指针,真正的数据是存储在磁盘中或数据库文件中。我 们先建立一个空的clob,然后我们可以update来让变量真正指向有数据的lob。Empty_clob()可以用来处理clob和nclob。在oracle 8i中可以使用temporary lob达到同样的效果。

4. 向LOB里写入数据

当获得一个有效的lob locator之后,就可以使用dbms_lob包的下列procedure向lob中写入数据。 
       DBMS_LOB.WRITE:允许自动写入数据到lob中。 
       DBMS_LOB.WRITEAPPEND:向lob的末尾写入数据。
--write lob 
DECLARE
  directions      CLOB;
  amount          BINARY_INTEGER;
  offset          INTEGER;
  first_direction VARCHAR2(100);
  more_directions VARCHAR2(500);
BEGIN
  --Delete any existing rows for 'Munising Falls' so that this 
  --example can be executed multiple times 
  DELETE FROM waterfalls WHERE falls_name = 'Munising Falls';
  --Insert a new row using EMPTY_CLOB(  ) to create a LOB locator 
  INSERT INTO waterfalls
    (falls_name, falls_directions)
  VALUES
    ('Munising Falls', empty_clob());
  --Retrieve the LOB locator created by the previous INSERT statement 
  SELECT falls_directions
    INTO directions
    FROM waterfalls
   WHERE falls_name = 'Munising Falls';
  --Open the LOB; not strictly necessary, but best to open/close LOBs. 
  dbms_lob.open(directions, dbms_lob.lob_readwrite);
  --Use DBMS_LOB.WRITE to begin 
  first_direction := 'Follow I-75 across the Mackinac Bridge.';
  amount          := length(first_direction); --number of characters to write 
  offset          := 1; --begin writing to the first character of the CLOB 
  dbms_lob.write(directions, amount, offset, first_direction);
  --Add some more directions using DBMS_LOB.WRITEAPPEND 
  more_directions := ' Take US-2 west from St. Ignace to Blaney Park.' ||
                     ' Turn north on M-77 and drive to Seney.' ||
                     ' From Seney, take M-28 west to Munising.';
  dbms_lob.writeappend(directions,
                       length(more_directions),
                       more_directions);
  --Add yet more directions 
  more_directions := ' In front of the paper mill, turn right on H-58.' ||
                     ' Follow H-58 to Washington Street. Veer left onto' ||
                     ' Washington Street. You''ll find the Munising' ||
                     ' Falls visitor center across from the hospital at' ||
                     ' the point where Washington Street becomes' ||
                     ' Sand Point Road.';
  dbms_lob.writeappend(directions,
                       length(more_directions),
                       more_directions);
  --Close the LOB, and we are done. 
  dbms_lob.close(directions);
END;
在这个例子里,我们使用了write 和writeappend这两个过程来插入数据到lob中。因为开始的时候,我们插入了一个空的lob locator。要注意一点,我们最后使用了dbms_lob.close方法关闭lob。这是一个好的方法,特别是在处理oracle text的时候,任何oracle text domain和function-based indexes被update是在wirte和writeappend的时候调用的,而不是在close的时候被update的。        
我们向lob中写入数据的时候,没有必要更新表中的列。因为它保存的只是一个locator,我们的变量也获得同样的locator,当我们写入数据去lob的时候,locator并没有改变。改变的只是locator指向的物理数据。 

5. 从lob中读取数据

步骤:a.通过select查询获得lob locator初始化lob变量。2.调用dbms_lob.read过程读取lob数据。 
下面是dbms_lob.read过程的定义,注意参数.  
 
PROCEDURE READ(lob_loc IN BLOB, --初始化后的lob变量lob locator 
               amount  IN OUT NOCOPY INTEGER, --读取的数量(clob为字符数,blob,bfile是字节数) 
               offset  IN INTEGER, --开始读取位置 
               buffer  OUT RAW); --读到的数据,raw要显示用转换函数,见bfile 
PROCEDURE READ(lob_loc IN CLOB CHARACTER SET any_cs,
               amount  IN OUT NOCOPY INTEGER,
               offset  IN INTEGER,
               buffer  OUT VARCHAR2 CHARACTER SET lob_loc%charset);
PROCEDURE READ(file_loc IN BFILE,
               amount   IN OUT NOCOPY INTEGER,
               offset   IN INTEGER,
               buffer   OUT RAW);

--从lob中读取数据 
DECLARE
  directions   CLOB;
  directions_1 VARCHAR2(300);
  directions_2 VARCHAR2(300);
  chars_read_1 BINARY_INTEGER;
  chars_read_2 BINARY_INTEGER;
  offset       INTEGER;
BEGIN
  --首先获得一个lob locator 
  SELECT falls_directions
    INTO directions
    FROM waterfalls
   WHERE falls_name = 'Munising Falls';
  --记录开始读取位置 
  offset := 1;
  --尝试读取229个字符,chars_read_1将被实际读取的字符数更新 
  chars_read_1 := 229;
  dbms_lob.read(directions, chars_read_1, offset, directions_1);
  --当读取229个字符之后,更新offset,再读取225个字符 
  IF chars_read_1 = 229 THEN
    offset       := offset + chars_read_1; --offset变为offset+chars_read_1,也就是从300开始 
    chars_read_2 := 255;
    dbms_lob.read(directions, chars_read_2, offset, directions_2);
  ELSE
    chars_read_2 := 0; --否则后面不在读取 
    directions_2 := '';
  END IF;
  --显示读取的字符数 
  dbms_output.put_line('Characters read = ' ||
                       to_char(chars_read_1 + chars_read_2));
  --显示结果 
  dbms_output.put_line(directions_1);
  dbms_output.put_line(length(directions_1));
  dbms_output.put_line(directions_2);
  dbms_output.put_line(length(directions_2));
END;

Dbms_lob.read的第2个参数是传递要读取的数量。对于clob是字符数,blob和bfile都是字节数。它是随着读取的数目自动更新的,offset不会更新。所以分布读取需要手动更新offset,下个offset是上一个offset+读取的数量。我们可以通过dbms_lob.get_length(lob_locator)获得这个lob的长度,结果clob是字符数,blob和bfile是字节数,然后分布读取。




oracle 中的大对象数据类型blob、clob、nclob的意义以及区别

           这些天一直在试图把一个用Oracle数据库做的项目转换为用msSql2000数据库的,本以为只要把数据库的连接池改改就可以了,没想到事实并没那么简单~ 要改的地方有好多,改了几天...
  • yoyo_yaya
  • yoyo_yaya
  • 2007年01月26日 15:41
  • 23519

oracle中blob,clob,nclob主要区别是什么?

BLOB全称为二进制大型对象(Binary Large Object)。它用于存储数据库中的大型二进制对象。可存储的最大大小为4G字节 CLOB CLOB全称为字符大型对象(Character Lar...
  • claram
  • claram
  • 2008年08月05日 19:17
  • 15879

oracle中的blob类型,clob类型,nclob类型主要区别是什么?

BLOB :  全称为二进制大型对象(Binary Large Object)。它用于存储数据库中的大型二进制对象。可存储的最大大小为4G字节 CLOB :  CLOB全称为字符大型对象(Cha...
  • fiwrc
  • fiwrc
  • 2017年03月15日 16:47
  • 1425

JDBC CLOB\BLOB操作

这几天遇到个数据迁移大字段的问题一直没有时间解决,今天终于有时间解决这个问题。 场景 SQL Server数据库某张表的数据迁移到Oracle数据库,早期未考虑特殊字段类型的问题,但真正业务应用测...
  • ybygjy
  • ybygjy
  • 2012年08月30日 20:13
  • 6995

【摘抄】oracle对大对象类型操作:blob,clob,nclob,bfile

        Oracle和plsql都支持lob(large object) 类型,用来存储大数量数据,如图像文件,声音文件等。Oracle 9i realse2支持存储最大为4g的数据,orac...
  • wga168
  • wga168
  • 2010年03月09日 20:58
  • 893

Oracle中的大对象(long,long raw,clob,blob,nclob,bfile)

(1)Oralce7与Oralce8中大对象的对比 Oralce7:有两种基本的long类型大对象:long 字符 和 long raw 二进制.使用内部存储方式,最多可以存储2g的数据,只能按照顺...
  • e_wsq
  • e_wsq
  • 2011年12月13日 20:00
  • 595

【转】oracle对大对象类型操作:blob,clob,nclob,bfile

本文转自 javaeye  13-4 Lob类型         13.4.1 基本介绍         Oracle和plsql都支持lob(large object)类型,用来存储大数量数据,如图...
  • zhaofmyan
  • zhaofmyan
  • 2009年08月12日 15:18
  • 794

oracle对大对象类型操作:blob,clob,nclob,bfile

3-4 Lob类型        13.4.1 基本介绍        Oracle和plsql都支持lob(large object) 类型,用来存储大数量数据,如图像文件,声音文件等。Oracle...
  • chunguangtang
  • chunguangtang
  • 2010年11月02日 21:28
  • 1006

【转】Oracle + PHP Cookbook(php oracle clob 长度超过4000如何写入)

在甲骨文LOB和PHP工作 由哈里Fuecks 达到4,000字节的限制?输入LOB ...   在这个“Oracle + PHP Cookbook”HowTo中,您将学习可用的LOB类型和与之相关的...
  • yzyssg1
  • yzyssg1
  • 2017年06月12日 17:51
  • 442

Oracle中的大字段 Blob/Clob

数据库中提供了两种字段类型 Blob  和 Clob 用于存储大型字符串或二进制数据(如图片)。Blob 采用单字节存储,适合保存二进制数据,如图片文件。Clob 采用多字节存储,适合保存大型文本数据...
  • nobody_java
  • nobody_java
  • 2007年06月22日 10:22
  • 3619
收藏助手
不良信息举报
您举报文章:oracle对大对象类型操作:blob,clob,nclob
举报原因:
原因补充:

(最多只允许输入30个字)