Oracle LOB 大对象处理

LOB类型列主要是用来存储大量数据的数据库字段，最大可以存储4G字节的非结构化数据。

一、LOB数据类型分类

1、按存储数据的类型分：

①字符类型：
   CLOB:存储大量单字节字符数据。
   NLOB:存储定宽多字节字符数据。
②二进制类型：
   BLOB:存储较大无结构的二进制数据。
③二进制文件类型：
   BFILE:将二进制文件存储在数据库外部的操作系统文件中。存放文件路径。

2、按存储方式分：

①存储在内部表空间：
    CLOB，NLOB和BLOB
   ②指向外部操作系统文件：
    BFILE

一、Null LOBs与Empty LOBs

DECLARE

    some_clob CLOB;

BEGIN

    IF some_clob IS NULL THEN

        dbms_output.put_line('a'); --NULL 表示该 LOB 字段或变量中连 LOB 指针都没有

    ELSIF dbms_lob.getlength(some_clob) = 0 THEN

        dbms_output.put_line('b'); --empty LOB 是指该 LOB 字段或变量中保存了一个 LOB 指针，但这个指针并没有指向任何 LOB 数据

    ELSE

        dbms_output.put_line('c'); --指针有实际内容

    END IF;

END;

二、LOB写入

Blob数据不能象其它类型数据一样直接插入（INSERT）。插入前必须先插入一个空的Blob对象，BLOB类型的空对象为EMPTY_BLOB()，之后通过SELECT命令查询得到先前插入的记录并锁定，继而将空对象修改为所要插入的Blob对象。

当获取到一个可用的 LOB 指针（定位器）后，就可以通过该指针写入 LOB 数据了。有两种写入数据的系统函数：
* DBMS_LOB.WRITE ：将数据随机地写入 LOB 中。
* DBMS_LOB.WRITEAPPEND ：从 LOB 的最后开始写入数据。

运用dbms_lob包用dbms_lob.write()写入只能存储３２k以下的图片。

注意：这里并不需要使用 UPDATE 来更新列 falls_myclob，因为这个 LOB 指针并没有发生变化，我们只是将数据写入它所指向的位置。

declare

  myclob          clob;

  amount          binary_integer;

  offset          integer;

  first_direction varchar2(100);

  more_myclob     varchar2(500);

begin

  --删除所有“munining Falls”的现有行,然后

  delete from waterfalls   where falls_name =      'Munising Falls';

  insert into waterfalls   (falls_name, falls_myclob)  values   ('Munising Falls', EMPTY_CLOB()); --使用EMPTY_CLOB()插入新行来创建LOB定位器

  select falls_myclob   into myclob   from waterfalls  where falls_name = 'Munising Falls'; --检索由前面的INSERT语句创建的LOB定位器

  --或直接

  INSERT  into waterfalls(falls_name, falls_myclob) values('Munising Falls' EMPTY_CLOB());  returning falls_myclob   into myclob;

  DBMS_LOB.OPEN(myclob, DBMS_LOB.LOB_READWRITE); --打开LOB;不是严格必要的，但是最好打开/关闭lob。

  first_direction := 'Follow I-75 across the Mackinac Bridge.';

  amount          := LENGTH(first_direction); --要写的字符数

  offset          := 1; --开始写CLOB的第一个字符

  DBMS_LOB.WRITE(myclob, amount, offset, first_direction); --使用DBMS_LOB。开始写

  more_myclob := ' Take US-2 west from St. Ignace to Blaney Park.' ||    ' From Seney, take M-28 west to Munising.'; --使用DBMS_LOB.WRITEAPPEND添加更多的myclob

  DBMS_LOB.WRITEAPPEND(myclob, LENGTH(more_myclob), more_myclob);

  more_myclob := ' In front of the paper mill, turn right on H-58.' ||    ' Sand Point Road.'; --添加更多的myclob

  DBMS_LOB.WRITEAPPEND(myclob, LENGTH(more_myclob), more_myclob);

  DBMS_LOB.CLOSE(myclob); --关闭LOB，就完成了。

end;

三、LOB读取

使用系统函数 DBMS_LOB.READ( ) 来读取 LOB 中的数据，当然，首先要得到这个 LOB 指针。比如读取 CLOB 数据，应该指定字符串的偏移量（offset），从指定的偏移量的位置开始读取数据。
CLOB 的第一个字符的偏移量是1；也需要指定读取的字符串长度。如果这个 CLOB 数据太大，应该多次读取数据。对于 BLOB 数据，也是这样处理，唯一的区别就是它是按字节存储的。

DBMS_LOB.READ 中的第二个参数 chars_read_1，是 IN OUT 参数。

调用时按照该参数指定的长度来读取数据，读取完毕后，将其更新为实际读取的字符（字节）长度。

当读取后，该参数的值比你原来的值小，则说明已经读取到 LOB 的末尾了。

declare

  myclob   clob;

  myclob_1 varchar2(300);

  myclob_2 varchar2(300);

  chars_read_1 binary_integer;

  chars_read_2 binary_integer;

  offset       integer;

begin

  select falls_myclob into myclob from waterfalls where falls_name = 'Munising Falls'; --检索之前插入的LOB定位器

  offset := 1;  --从第一个字符开始阅读

  chars_read_1 := 229; --尝试读取myclob的229个字符时，chars_read_1将使用实际读取的字符数进行更新

  DBMS_LOB.READ(myclob, chars_read_1, offset, myclob_1);

  if chars_read_1 = 229 then  --如果读取229个字符，则更新偏移量并尝试读取255个字符。

    offset       := offset + chars_read_1;

    chars_read_2 := 255;

    DBMS_LOB.READ(myclob, chars_read_2, offset, myclob_2);

  else

    chars_read_2 := 0;

    myclob_2 := '';

  end if;

  DBMS_OUTPUT.PUT_LINE('Characters read = ' ||  TO_CHAR(chars_read_1 + chars_read_2));  --显示读取的字符总数

  DBMS_OUTPUT.PUT_LINE(myclob_1);  --显示myclob

  DBMS_OUTPUT.PUT_LINE(myclob_2);

end;

四、BFile文件大对象（存储在操作系统文件中的数据）

PL/SQL中的Bfile只能读取Bfile数据，而不能写入。

BLOB，CLOB，NCLOB 存储在数据库内，而 BFILE 存储在数据库外。BFILE 和其他三种大字段类型相比，BFILE 有以下三点不同：

BFILE 的数据是存储在操作系统文件中的，而不是在数据库中；
BFILE 数据不参与事务处理，也就是说，BFILE 数据的改变不能被提交和回滚（但 BFILE 指针的改变是可以提交或回滚的）；
从 PL/SQL 中，只能读取 BFILE 数据，而不能写入。必须得在数据库外先创建 BFILE 文件，再创建 BFILE 指针。

在 PL/SQL 中操作 BFILE，其实也是操作 LOB 指针。只是对于 BFILE 的指针来说，它指向的 BFILE 数据在数据库外。
所以，一个 BFILE 列的两行，可以存储指向同一个文件的 BFILE 指针。

1. 创建

BFILE 指针由目录(Oracle服务器上)和文件名组成（而实际的目录和文件可以不存在），将这两部分信息作为参数传入 BFILENAME 函数，该函数会返回一个 BFILE 指针。

create directory BFILE_DATA as 'D:/temp';

declare

 waterfall_picture bfile;

begin

 waterfall_picture := BFILENAME('BFILE_DATA','waterfall.gif'); --调用BFILENAME来创建BFILE定位器

 insert into waterfalls (falls_name, falls_web_page) values ('my waterfall',waterfall_picture); --保存我们的新定位在waterfalls

end

2. 读取

declare

 waterfall bfile;

 piece raw(60);

 amount binary_integer := 60;

 offset integer := 1;

begin

 select falls_web_page into waterfall from waterfalls where falls_name='my waterfall'; --检索LOB定位器

 DBMS_LOB.OPEN(waterfall); --打开定位器，读取60个字节，然后关闭定位器

 DBMS_LOB.READ(waterfall, amount, 1, piece);

 DBMS_LOB.CLOSE(waterfall);

 DBMS_OUTPUT.PUT_LINE(RAWTOHEX(piece));--十六进制显示结果

 --将原始结果转换为我们可以读取的字符串

 --DBMS_OUTPUT.PUT_LINE(UTL_RAW.CAST_TO_VARCHAR2(piece));

 end;

五、将文件系统数据库通过BFile导入到LOB字段中

BFILE 提供了一种从数据库中访问文件系统中数据的方法。可能你想将这些数据保存到 BLOB 或 CLOB 字段中。
可以使用系统函数实现：

dbms_lob.loadfrombfile
dbms_lob.loadclobfrombfile
dbms_lob.loadblobfrombfile

下面我们将图片 watarfall.gif 保存到 BLOB 列中：

declare

  My_Falls_bfile bfile := BFILENAME('BFILE_DATA', 'waterfall.gif');

  photo               blob;

  destination_offset  integer := 1;

  source_offset       integer := 1;

begin

  delete from waterfalls where falls_name = 'my waterfall'; --删除Tannery Falls的行，所以这个例子可以运行多次。

  insert into waterfalls (falls_name, FALLS_PHOTO) values ('my waterfall', EMPTY_BLOB());--使用EMPTY_BLOB()插入新行来创建LOB定位器

  select FALLS_PHOTO into photo from waterfalls where falls_name = 'my waterfall'; --检索由前面的INSERT语句创建的LOB定位器

  DBMS_LOB.OPEN(photo, DBMS_LOB.LOB_READWRITE);--打开目标BLOB和源BFILE

  DBMS_LOB.OPEN(My_Falls_bfile);

  DBMS_LOB.LOADBLOBFROMFILE(photo,  My_Falls_bfile, DBMS_LOB.LOBMAXSIZE, destination_offset, source_offset);  --Load the contents of the BFILE into the BLOB column

  DBMS_LOB.CLOSE(photo);  --关闭两个lob

  DBMS_LOB.CLOSE(My_Falls_bfile);

end;

六、c#读写Oracle BOLB数据。

（1）写入数据到Orable Blob字段中。

首先要在BLob字段中插入一个Empty_blob(),才能写入下面的数据。

* 在调用此函数之前需要写插入一个字符串到 BLOB 中比如：

*        "CREATE TABLE tablewithlobs (a int, b BLOB, c CLOB, d NCLOB)";

*        "INSERT INTO tablewithlobs values (1, 'AA', 'AAA', N'AAAA')";

* 否则程序会在 OracleLob tempLob    = reader.GetOracleLob(0) 处出错。

写入：

conn.Open();

OracleCommand cmd = conn.CreateCommand();

OracleTransaction transaction = cmd.Connection.BeginTransaction();      // 利用事务处理（必须）

cmd.Transaction = transaction;

// 获得 OracleLob 指针

cmd.CommandText = "select fulls_myblob from waterfalls where fulls_name = 'myabc' FOR UPDATE";

using (OracleDataReader reader = cmd.ExecuteReader())

{

    reader.Read(); //Obtain the first row of data.

    OracleBlob tempLob = reader.GetOracleBlobForUpdate(0);   //Obtain a LOB.

    FileStream fs = new FileStream("c:\\1.txt", FileMode.Open); // 将文件写入 BLOB 中

    tempLob.BeginChunkWrite();

    int length = 10485760;

    byte[] Buffer = new byte[length];

    int i;

    while ((i = fs.Read(Buffer, 0, length)) > 0)

    {

        tempLob.Write(Buffer, 0, i);

    }

    fs.Close();

    tempLob.EndChunkWrite();

    cmd.Parameters.Clear();

}

transaction.Commit(); // 提交事务

conn.Close();

（2）读取Oracle Blob到文件中。

conn.Open();

OracleCommand cmd = conn.CreateCommand();

OracleTransaction trans = cmd.Connection.BeginTransaction();// 利用事务处理（必须）

cmd.Transaction = trans;

// 获得 OracleLob 指针

string sql = "select fulls_myblob from waterfalls where fulls_name = 'myabc'";

cmd.CommandText = sql;

OracleDataReader dr = cmd.ExecuteReader();

dr.Read();

OracleBlob tempLob = dr.GetOracleBlob(0);

dr.Close();

// 读取 BLOB 中数据，写入到文件中

FileStream fs = new FileStream("c:\\1.txt", FileMode.Create);

int length = 1048576;

byte[] Buffer = new byte[length];

int i;

while ((i = tempLob.Read(Buffer, 0, length)) > 0)

{

    fs.Write(Buffer, 0, i);

}

fs.Close();

tempLob.Clone();

cmd.Parameters.Clear();

trans.Commit();     // 提交事务

conn.Close();

巴特西