BLOB及CLOB欄位處理

 

BLOB及CLOB欄位處理

 

資料型別，不管blob還是clob，在SQL中，都可以直接當作varchar2來使用的。

比如 下面的表 create table testlob(id number(2), b_l blob);
可以直接 insert into testlob values (1,'123');

當然更專業的是用下面的方式:

這個案例把一個檔案寫入到blob，最典型的應用。

首先建立一個目錄

create or replace directory mydir as 'd:';

declare
dest_loc blob;

--指定要讀入的檔案，在mydir目錄下的
src_loc bfile := bfilename('MYDIR', 'ace_introdue.jpg'); --目錄名字一定要大寫
amount integer := 4000;
begin

--插入記錄，同時返回blob的locator
insert into testlob  values(2,empty_blob()) returning b_l into dest_loc;

--開啟 bfile
dbms_lob.open(src_loc, dbms_lob.lob_readonly);

--獲得檔案長度
select dbms_lob.getlength(src_loc) into amount from dual;

--開啟要寫入記錄的blob locator
dbms_lob.open(dest_loc, dbms_lob.lob_readwrite);

--直接把檔案load到blob欄位
dbms_lob.loadfromfile(dest_loc, src_loc,amount);

--關閉相應的locator
dbms_lob.close(dest_loc);
dbms_lob.close(src_loc);
commit;
end;
/

 

 

 

 

 

dbms_lob包的用法

<=====插入圖片========>
create directory img as 'c:\img';

保證在伺服器端即192.168.0.250的C盤下有名為img的資料夾並在此目錄下有"1.jpg"這張圖片
並保證當前使用者有對此目錄的讀取許可權（如果讀取檔案使用者不是建立目錄的使用者那麼要用建立
目錄的使用者執行grant read on directory img to XXX）

create or replace procedure insert_image(img_dir varchar2,img_name varchar2)
 img_blob blob;
 img_bfile bfile;
begin
/*將透過empty_blob()函式將型別為blob的列初始化為空以便以後填充*/
  insert into fw.my_image values(empty_blob())
  return fw.my_image.img into img_blob;
 
img_bfile := bfilename(img_dir,img_name); --獲得定位器指向的目錄和檔案
 if (dbms_lob.fileexists(img_bfile)!=0) then --如果檔案定位器指向的檔案存在
   dbms_lob.fileopen(img_bfile,dbms_lob.file_readonly); --開啟目標檔案
 
   /*將檔案位元組流資料載入到指定的LOB型別變數中*/
    dbms_lob.loadfromfile(img_blob,img_bfile,dbms_lob.getlength(img_bfile));
    dbms_lob.fileclose(img_bfile);--關閉檔案
    commit;
    dbms_output.put_line('已經從'||img_dir||'目錄中讀取了圖片'||img_name||'向表中插入');
     else  --如果檔案定位器指向的檔案不存在
       dbms_output.put_line('檔案沒找到');
  end if;
 
   exception when others then
   dbms_output.put_line(sqlerrm);
end;

呼叫過程
declare
begin
 fw.insert_image('IMG','1.jpg');

<====將查詢結果轉化為XML格式並列印======>

create or replace procedure query_to_xml
is
clob_var clob;
xml_content varchar2(32767);
 line_content varchar2(4000);
 line_id int; --行號
 begin
   line_id := 0;
   clob_var := dbms_xmlquery.getXML('select * from fw.math');  --將查詢SQL返回的結果集合轉化為XML格式並交給一

個CLOB變數儲存
   xml_content := dbms_lob.substr(clob_var,32767); --擷取此CLOB變數儲存的全部資料並交給一個PL/SQL裡的varchar2

型別變數儲存（最大長度為32767）
   dbms_output.put_line('以下是math表的記錄轉化而成的XML文件內容'); 
    while xml_content is not null   --迴圈
     loop
       line_id := line_id+1; --行號++
       line_content := substr(xml_content,1,instr(xml_content,'>')); --提取第1行
     dbms_output.put_line(line_id||':'||line_content);  --列印行號和行內容
     xml_content := substr(xml_content,instr(xml_content,chr(10))+1,length(xml_content)); --把已經讀取的行從

整個XML資料裡剔除掉，那麼剩下的XML資料的第1行就是整個XML資料的第2行，依此類推，進行遍歷（chr(10)返回一個換行

符）
      end loop;

    /*異常處理*/
     exception when others then
     dbms_output.put_line(sqlerrm);
end;

length(xml_content)這個引數也可以不寫 原因如下：

 

 

create or replace directory utllobdir as 'd:\images';
 declare
    a_blob  BLOB;
  a_bfile BFILE := BFILENAME('UTLLOBDIR','1.bmp');
 begin
    insert into bfile_tab values (a_bfile)
     returning bfile_column into a_bfile;
    insert into utl_lob_test values (empty_blob())
      returning blob_column into a_blob;
   dbms_lob.fileopen(a_bfile);
   dbms_lob.loadfromfile(a_blob, a_bfile, dbms_lob.getlength(a_bfile));
   dbms_lob.fileclose(a_bfile);
   commit;
end;

 

 

 

 

在sqlplus中操做blob和clob方法,下面的實現程式碼。

create directory "utllobdir" as 'd:'

create table blobtest(col1 BLOB);
create table clobtest(col1 CLOB);

--insert BLOB
declare
   a_blob BLOB;
   bfile_name BFILE := BFILENAME(\'ULTLOBDIR\',\'RMAN_Piner.pdf\');
begin
   insert into blobtest values (empty_blob())
   returning col1 into a_blob;
   dbms_lob.fileopen(bfile_name);
   dbms_lob.loadfromfile(a_blob, bfile_name, dbms_lob.getlength(bfile_name));
   dbms_lob.fileclose(bfile_name);
   commit;
end;

 

--update BLOB
declare
a_blob BLOB;
bfile_name BFILE := BFILENAME(\'ULTLOBDIR\',\'log.txt\');
begin
update blobtest set col1=empty_blob() where rownum=1
returning col1 into a_blob;
dbms_lob.fileopen(bfile_name);
dbms_lob.loadfromfile(a_blob, bfile_name, dbms_lob.getlength(bfile_name));
dbms_lob.fileclose(bfile_name);
commit;
end;

 

--insert CLOB
create

table clobtest(col1 CLOB);

declare
a_clob CLOB;
bfile_name BFILE := BFILENAME(\'ULTLOBDIR\',\'teslob.doc\');
begin
insert into clobtest values (empty_clob())
returning col1 into a_clob;
dbms_lob.fileopen(bfile_name);
dbms_lob.loadfromfile(a_clob, bfile_name, dbms_lob.getlength(bfile_name));
dbms_lob.fileclose(bfile_name);
commit;
end;

 

--update CLOB
declare
a_clob CLOB;
bfile_name BFILE := BFILENAME(\'ULTLOBDIR\'

,\'log.txt\');
begin
update clobtest set col1=empty_clob() where rownum=1
returning col1 into a_clob;
dbms_lob.fileopen(bfile_name);
dbms_lob.loadfromfile(a_clob, bfile_name, dbms_lob.getlength(bfile_name));
dbms_lob.fileclose(bfile_name);
commit;
end;

　　透過檢視lob欄位的長度判斷能否插入成功
select dbms_lob.getlength(col1) from blobtest;
select dbms_lob.getlength(col1) from clobtest;