oracle lob 資料類型應用執行個體

LOB有三種類型：

BLOB：Binary Large Object 二進位大型物件
CLOB：Character Large Object 單字元大型物件
Double-byte Character Large Object 雙位元組字元大型物件
oracle中大資料類型：
 LONG: 可變長的字串資料，最長2G，LONG具有VARCHAR2列的特性，可以儲存長文本，一個表中最多一個LONG列
 LONG RAW: 可變長位元據，最長2G

　　 CLOB:  字元大對象Clob 用來儲存單位元組的字元資料
　　 NCLOB: 用來儲存多位元組的字元資料
　　 BLOB: 用於儲存位元據

　　 BFILE: 儲存在檔案中的位元據，這個檔案中的資料只能被唯讀訪。但該檔案不包含在資料庫內。
       bfile欄位實際的檔案儲存體在檔案系統中,欄位中儲存的是檔案定位指標.bfile對oracle來說是唯讀,也不參與事務性控制和資料恢複.
　　
　　CLOB，NCLOB，BLOB都是內部的LOB(Large Object)類型，最長4G，沒有LONG只能有一列的限制
　　要儲存圖片、文字檔、Word檔案各自最好用BLOB，LONG　RAW也不錯，但Long是oracle將要廢棄的類型，而且有只能有一列的限制，因此建議用BLOB。

oracle預存程序操作LOB欄位 執行個體一：
做項目過程中遇到這麼一個問題，就是有多套系統（0，A,B,C），其中中心系統(0)要提取其他子系統(A,B,C)的資料。設計的原理是在中心系統中建立預存程序，然後通過DBlink串連到子系統，根據子系統表內的標示欄位，提取資料（插入或更新到中心系統中）。

預存程序文法（偽碼）：

代碼如下

複製代碼

CREATE OR REPLACE PROCEDURE "Pro_N_ROLES" ( V_Filter in varchar2 := ' ' ) is     V_ROLE varchar2(30);     V_TREEAUTH varchar2(4);     V_ORI number(22);     V_UPDATEFLAG varchar2(2); //子系統 標示欄位     v_Sql varchar2(4000);     type vv is     ref cursor;     CUR_DATA vv; begin     v_Sql := 'select  ROLE,TREEAUTH,UPDATEFLAG from ROLES@DB_N //建立好的 Dblink where UPLOADFLAG = ''Y''' || V_Filter ; //子系統 標示欄位，     open CUR_DATA     for v_Sql;     loop         fetch CUR_DATA into V_ROLE,V_TREEAUTH ,V_UPDATEFLAG;         exit     when CUR_DATA%NOTFOUND;         if V_UPDATEFLAG = 'N' then             begin                 INSERT                 INTO                     J_ROLES                     (                         ROLE,                         TREEAUTH,                         SORI, //子系統www.111cn.net主索引值                         S_FLAG //子系統標示                     )                     VALUES                     (                         V_ROLE,                         V_TREEAUTH,                         V_ORI, //A系統資料表主索引值                         'A系統'                     )                     ;                 commit;                 UPDATE                     ROLES@DB_N                     SET UPLOADFLAG = 'N',                     UPDATEFLAG     = 'Y'                 WHERE                     ORI = V_ORI;                 commit;             end;         end if;         if V_UPDATEFLAG = 'Y' then             begin                 UPDATE                     J_ROLES                     SET ROLE        = V_ROLE,                     TREEAUTH    = V_TREEAUTH,                     ORI     = V_ORI,                     SORI = V_ORI,                     S_FLAG   = 'A系統'                 WHERE                     SORI   = V_ORI                     and S_FLAG = 'A系統';                 commit;                 UPDATE                     ROLES@DB_N                     SET UPLOADFLAG = 'N',                     UPDATEFLAG     = 'Y'                 WHERE                     ORI = V_ORI;                 commit;             end;         end if; //001這裡加入更新CLOB欄位代碼     end loop;     close CUR_DATA; end Pro_N_ROLES;

oracle預存程序中沒有longvarchar類型的定義，定義的varchar類型變數最長長度為32767個字元，因此無法直接用預存程序變數儲存CLOB類型欄位。
網上有不少方法，有的是通過建立暫存資料表的方法，有的是通過截取然後合并的方法。根據我這邊的實際情況，這樣的欄位不多，採用直接更新的方法。

所以在上面的注釋“//001這裡加入更新CLOB欄位代碼”出增加如下代碼：   

 

代碼如下

複製代碼

UPDATE              J_ROLES             SET OTHERSITUATION =             (             SELECT                 OTHERSITUATION             FROM                  ROLES@DB_N             WHERE                 ORI = V_ORI             ) ，   SUGGEST =             (             SELECT                 SUGGEST             FROM                  ROLES@DB_N             WHERE                 ORI = V_ORI             )                     WHERE             SORI   = V_ORI             and S_FLAG = 'A系統';

Oracle9i資料庫儲存和讀取clob檔案(PL/SQL指令碼) 執行個體二（轉）

代碼如下

複製代碼

//先建立表 lob_example1 create table lob_example1( id number(6) primary key, name varchar2(10), resume clob ); //插入資料 insert into lob_example1 values(1,'豬',empty_clob()); insert into lob_example1 values(2,'狗',empty_clob()); commit; //建立目錄，儲存在c盤根目錄 CREATE OR REPLACE DIRECTORY DOCS AS 'C:'; //建立將檔案內容寫入資料庫CLOB的預存程序 CREATE OR REPLACE PROCEDURE update_doc(    t_id  number,    filename varchar2    ) as    lobloc clob;    fileloc bfile;    amount int;    src_offset int:=1;    dest_offset int:=1;    csid int:=0;    lc  int:=0;    warning int; begin    fileloc:=bfilename('DOCS',filename);    dbms_lob.fileopen(fileloc,0);    amount:=dbms_lob.getlength(fileloc);    select resume into lobloc from lob_example1      where id=t_id for update;    dbms_lob.loadclobfromfile(lobloc,fileloc,amount,dest_offset,src_offset,csid,lc,warning);    dbms_lob.fileclose(fileloc);    commit; end; //調用預存程序,把檔案讀入資料庫CLOB中，c盤下要有這兩個檔案 call update_doc(1,'aa.csv'); call update_doc(2,'bb.csv'); //察看id是2和1的行中檔案大小 select length(resume) from lob_example1 where id=2; select length(resume) from lob_example1 where id=1; /////////////////////////////////////////////////////檔案已經放入資料庫 //將檔案從資料庫clob中讀出來 CREATE OR REPLACE PROCEDURE get_doc(    t_id  number,    filename varchar2    ) as    lobloc clob;    amount int;    offset int:=1;    buffer varchar2(2000);    handle utl_file.file_type; begin    select resume into lobloc from lob_example1 where id=t_id;    amount:=dbms_lob.getlength(lobloc);    dbms_lob.read(lobloc,amount,offset,buffer);    handle:=utl_file.fopen('DOCS',filename,'w',2000);    utl_file.put_line(handle,buffer);    utl_file.fclose(handle); end; /   //調用這個過程,把檔案讀出來 call get_doc(1,'zz.csv');

//哈哈，檔案被儲存在c盤了啊，快去看看吧

//對了，檔案大了會導致緩衝區太小，緩衝區最大允許36k，大了我也不知道怎麼辦，正在解決中！

作者：大狼一號