Oracle中大对象(LOB)处理方法

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 ;

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 ;

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 ;

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

en

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 ;

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 ;

* 在调用此函数之前需要写插入一个字符串到 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();

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();