oracle对大对象类型操作：blob，clob，nclob，bfile

在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查找目录信息。