关闭

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

标签: oracleinteger数据库buffernull存储
3125人阅读 评论(0) 收藏 举报
分类:
在oracle中,有4个大对象(lobs)类型可用,分别是blob,clob,bfile,nclob。
  下面是对lob数据类型的简单介绍。
   blob:二进制lob,为二进制数据,最长可达4GB,存贮在数据库中。
   clob:字符lob,字符数据,最长可以达到4GB,存贮在数据库中。
   bfile:二进制文件;存贮在数据库之外的只读型二进制数据,最大长度由操作系统限制。
   nclob:支持对字节字符集合(nultibyte characterset)的一个clob列。
  对于如何检索和操作这些lob数据一直是oracle数据库开发者经常碰到的问题。下面我将在oracle对lob数据处理的一些方法和技巧,介绍给读者,希望能够对读者以后的开发有所帮助。
  
  oracle中可以用多种方法来检索或操作lob数据。通常的处理方法是通过dbms_lob包。
  其他的方法包括使用api(application programminginterfaces)应用程序接口和oci(oracle call interface)oracle调用接口程序。
  一、在oracle开发环境中我们可以用dbms_lob包来处理!dbms_lob包功能强大,简单应用。既可以用来读取内部的lob对象,也可以用来处理bfile对象。但处理两者之间,还有一点差别。处理内部lob对象(blob,clob)时,可以进行读和写,但处理外部lob对象bfile时,只能进行读操作,写的操作可以用pl/sql处理。另外用sql也可以处理lob,但要注意sql仅可以处理整个lob,不能操作lob的数据片。
  
  在dbms_lob包中内建了read(),append,write(),erase(),copy(),getlength(),substr()等函数,可以很方便地操作lob对象。这里不做深入讨论,读者可以参看相关的书籍。
  
  对于pl/sql,下面介绍一种技巧,用动态的pl/sql语句处理clob对象来传替表名!
  example 1.
  动态PL/SQL,对CLOB字段操作可传递表名table_name,表的唯一标志字段名field_id,clob字段名field_name记录号v_id,开始处理字符的位置v_pos,传入的字符串变量v_clob
  
  修改CLOB的PL/SQL过程:updateclob
  create or replace procedure updateclob(
  table_name in varchar2,
  field_id in varchar2,
  field_name in varchar2,v_id in number,
  v_pos in number,
  v_clob in varchar2)
  is
  lobloc clob;
  c_clob varchar2(32767);
  amt binary_integer;
  pos binary_integer;
  query_str varchar2(1000);
  begin
  pos:=v_pos*32766+1;
  amt := length(v_clob);
  c_clob:=v_clob;
  query_str :='select '||field_name||'from '||table_name||'
  where '||field_id||'= :id for update ';
  --initialize buffer with data to be inserted or updated
  EXECUTE IMMEDIATE query_str INTO lobloc USING v_id;
  --from pos position, write 32766 varchar2 into lobloc
  dbms_lob.write(lobloc, amt, pos, c_clob);
  commit;
  exception
  when others then
  rollback;
  end;
  l /用法说明:
  在插入或修改以前,先把其它字段插入或修改,CLOB字段设置为空empty_clob(),
  然后调用以上的过程插入大于2048到32766个字符。
  如果需要插入大于32767个字符,编一个循环即可解决问题。
  查询CLOB的PL/SQL函数:getclob
  create or replace function getclob(
  table_name in varchar2,
  field_id in varchar2,
  field_name in varchar2,
  v_id in number,
  v_pos in number) return varchar2
  is
  lobloc clob;
  buffer varchar2(32767);
  amount number := 2000;
  offset number := 1;
  query_str varchar2(1000);
  begin
  query_str :='select '||field_name||' from '||table_name||'
  where '||field_id||'= :id ';
  --initialize buffer with data to be found
  EXECUTE IMMEDIATE query_str INTO lobloc USING v_id;
  offset:=offset+(v_pos-1)*2000;
  --read 2000 varchar2 from the buffer
  dbms_lob.read(lobloc,amount,offset,buffer);
  return buffer;
  exception
  when no_data_found then
  return buffer;
  end;
  l 用法说明:
  用select getclob(table_name,field_id,field_name,v_id,v_pos) as
  partstr from dual;
  可以从CLOB字段中取2000个字符到partstr中,
  编一个循环可以把partstr组合成dbms_lob.getlength(field_name)长度的目标字符串。

---------------------------------------------------------------------------------------------------------------------------------

13-4 Lob类型          13.4.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这两种数据类型也是存储字符的,但是有系列的问题,不建议使用,这里也就不讨论了。  13.4.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表示未分配,肯定没有数据     ELSEIF 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指向的物理数据。  在sqlplus中显示上面的例子:          SQL> SET LONG 2000                 SQL> COLUMN falls_directions WORD_WRAPPED FORMAT A70      SQL> SELECT falls_directions        2  FROM waterfalls        3  WHERE falls_name='Munising Falls';  其中set long 2000是显示2000个字符。Word_wrappend是自动换行。  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);  下面是一个读取clob的例子:  --从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是字节数,然后分布读取。  13.4.3 使用Bfile          Bfile和clob,nclob,blob是不同的。Bfile是外部的lob类型,其他三个是oracle内部的lob类型,它们至少有三点主要不同的地方:  1.        bfile的值是存在操作系统的文件中,而不是数据库中。  2.        bfile不参与数据库事务操作。也就是改变bifle不能commit或rollback。但是改变bfile的locator可以commit或rollback。  3.        bfile在plsql和oracle中是只读的,不允许写。你必须生成一个外部的操作系统文件让bfile locator能够完全指向它。  在plsql中使用bifle,仍然需要lob locator,只不过是一个目录和文件的别名,你可以使用biflename函数获得一个bfile locator。使用create or replace directory [alias] as ‘file locator directory’,你必须具有CREATE ANY DIRECTORY权限才能使用。如:  CREATE DIRECTORY bfile_data AS 'c:PLSQL BookCh12_Misc_Datatypes';      GRANT READ ON DIRECTORY bfile_data TO gennick;  --读的权限给这个用户。  通过all_directory查找目录信息。

0
0
查看评论
发表评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场

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

1.基本介绍 Oracle和plsql都支持lob(large object) 类型,用来存储大数量数据,如图像文件,声音文件等。Oracle 9i realse2支持存储最大为4g的数据,orac...
  • caixingyun
  • caixingyun
  • 2014-01-15 17:43
  • 2023

Hibernate对与Oracle中Clob类型的使用

Hibernate对与Oracle中Clob类型的使用 一.理解和分析: 1.为何实用Clob:   oracle数据库当需要存入大数据量(大于4000)时,varchar2不够用,可以使...
  • u013120247
  • u013120247
  • 2016-05-31 09:10
  • 3715

Oracle处理CLOB超过4000入库问题

最近在公司里面遇到抓取某新闻网站上的信息,但是当把抓取的新闻内容content(包含标签格式),插入到oracle,content字段中,字段是CLOB类型,报出字符串长度过长的错误。经过在网上查资料...
  • jiangsanfeng1111
  • jiangsanfeng1111
  • 2016-06-28 16:31
  • 3485

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

13-4 Lob类型          13.4.1 基本介绍          Oracle和plsql都支持lob(large ob...
  • longwenze
  • longwenze
  • 2013-03-04 09:33
  • 354

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

13-4 Lob类型         13.4.1 基本介绍         Oracle和plsql都支持lob(large object) 类型,用来存...
  • e_wsq
  • e_wsq
  • 2011-12-13 19:58
  • 679

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

1.基本介绍 Oracle和plsql都支持lob(large object) 类型,用来存储大数量数据,如图像文件,声音文件等。Oracle 9i realse2支持存储最大为4g的数据,orac...
  • qingtongqizhw
  • qingtongqizhw
  • 2014-01-14 16:50
  • 601

.NET中处理ORACLE大对象(CLOB,BLOB,BFILE)的开发

.NET中处理ORACLE大对象(CLOB,BLOB,BFILE)的开发了解如何使用 .NET 读写大对象 (LOB) 以及如何通过 BFILE 读取数据。本文相关下载:·示例代码·Oracle 数据...
  • sscsgss
  • sscsgss
  • 2011-01-24 14:52
  • 3162

oracle字段类型blob,clob,nclob

高手znp的面试必问问题其中之一就是这个,另一个是JAVA的8种基本数据类型。 今天咱也好好学学这个东西。 说明:三种大型对象(LOB),用来保存较大的图形文件或带格式的文本文件,如Miceoso...
  • e_wsq
  • e_wsq
  • 2011-12-13 20:02
  • 594

如何将DB2数据库中的大对象(BLOB,CLOB)数据类型复制到Oracle数据库中

  • 2009-10-27 16:06
  • 91KB
  • 下载

Spring 让 LOB 数据操作变得简单易行,LOB 代表大对象数据,包括 BLOB 和 CLOB 两种类型

http://www.ibm.com/developerworks/cn/java/j-lo-spring-lob/index.html 本文讲解了在 Spring 中处理 LOB 数据...
  • liangxw1
  • liangxw1
  • 2016-04-13 10:31
  • 1222
    个人资料
    • 访问:2348796次
    • 积分:21600
    • 等级:
    • 排名:第396名
    • 原创:158篇
    • 转载:639篇
    • 译文:1篇
    • 评论:50条
    最新评论