Oracle - DBMS_LOB函式和用法

襲冷發表於2018-01-17
Oracle函式
GETLENGTH
    返回指定 LOB 資料的長度的函式
DBMS_LOB.GETLENGTH(lob_loc  IN  BLOB/CLOB/BFILE/NCLOB) RETURN  INTEGER;

OPEN
    開啟LOB物件    
DBMS_LOB.OPEN(
    lob_loc    IN  OUT  NOCOPY  BLOB/CLOB/BFILE,
    open_mode  IN  BINARY_INTEGER    -- 只讀:DBMS_LOB.LOB_READONLY；讀寫：DBMS_LOB.LOB_READWRITE
);

READ
    從 LOB 資料中讀取指定長度資料到緩衝區(變數中)的過程
DBMS_LOB.READ(
    lob_loc  IN  BLOB/CLOB/BFILE,     -- LOB 資料
    amount   IN  OUT  NOCOPY  BINARY_INTEGER),    -- IN:要讀取的字元數；OUT：實際讀取的字元數
    offset   IN  INTEGER,      --  起始位置
    buffer   OUT  RAW/VARCHAR2    -— 儲存返回資料的變數
);
    例：
DECLARE

   var_clob    CLOB;
   var_buff    VARCHAR2(1000);
   var_len     NUMBER(4);
   var_start   NUMBER(4);

BEGIN

   SELECT RESUME INTO var_clob FROM TLOB WHERE NO = 1;
   var_len := DBMS_LOB.GETLENGTH(varc);
   var_start := 1;
   DBMS_LOB.READ(var_clob, var_len, var_start, var_buff);
   DBMS_OUTPUT.PUT_LINE('RETURN: '||var_buff);

END;

WRITE
    將指定數量的資料寫入LOB的過程。
DBMS_LOB.WRITE(
    lob_loc  IN  OUT  NOCOPY  BLOB/CLOB,   -- 被寫入 LOB
    amount   IN  BINARY_INTEGER,   -- 寫入長度(指寫入 LOB 資料)
    offset   IN  INTEGER,          -- 寫入起始位置(指被寫入 LOB)
    buffer   IN  RAW/VARCHAR2      -- 寫入 LOB 的資料
);
    例：
DECLARE

    v_clob    CLOB;
    v_wstr    VARCHAR2(1000);
    v_strat   NUMBER(4);
    v_len     NUMBER(4);

BEGIN

    v_wstr := 'CLOB';
    v_len := LENGTH(VWSTR);
    v_strat := 5;
    
    SELECT RESUME INTO v_clob FROM TLOB WHERE NO = 1 FOR UPDATE;  --更新 LOB 資料需要 FOR UPDATE 鎖定
    
    DBMS_LOB.WRITE(v_clob, v_len, v_strat, v_wstr);
    DBMS_OUTPUT.PUT_LINE('改寫結果為: ' || v_clob);
    
    COMMIT;

END;

APPEND
    將指定的LOB資料追加到指定的LOB資料後的過程。
DBMS_LOB.APPEND(
    dest_lob  IN  OUT  NOCOPY  BLOB,       --追加到的目標LOB
    src_lob   IN  BLOB                     -- 用來追加的LOB
);
    例：
DECLARE

    varcl    CLOB;
    vastr    VARCHAR2(1000);

BEGIN

    vastr := '，這是大物件列';
    SELECT RESUME INTO varcl FROM TLOB WHERE NO = 1 FOR UPDATE;
    DBMS_LOB.APPEND(varcl, vastr);
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('追加結果為: ' || varcl);

END;

WRITEAPPEND  
    將緩衝區資料寫到LOB尾部  
    DBMS_LOB.WRITEAPPEND(
        lob_loc  IN  OUT  NOCOPY  BLOB/CLOB/NCLOB,
        amount   IN  BINARY_INTEGER,
        buffer   IN  RAW/VARCHAR2
    );

TRIM
    截斷LOB資料中從第一位置開始指定長度的部分資料的過程
DBMS_LOB.TRIM(
    lob_loc  IN  OUT  NOCOPY  BLOB/CLOB/NCLOB,   --LOB資料
    newlen   IN  INTEGER  -- 階段長度
);
    例：
DECLARE

    varc  CLOB;
    len   NUMBER(4);
    
BEGIN

    len := 4;
    SELECT RESUME INTO varc FROM TLOB WHERE NO = 1 FOR UPDATE;
    DBMS_LOB.TRIM(varc, len);
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('截斷結果為: '||varc);
    
END;

CLOSE
    關閉已經開啟的LOB
DBMS_LOB.CLOSE(lob_loc  IN  OUT  NOCOPY  BLOB/CLOB/BFILE);
 
SUBSTR
    從LOB資料中提取子字串的函式。
DBMS_LOB.SUBSTR(
    lob_loc  IN  BLOB/CLOB/BFILE,   -- 提取的來源
    amount   IN  INTEGER:=32762,   -- 提取長度
    offset   IN  INTEGER:=1   -- 開始位置
)RETURN  RAW/VARCHAR2;   -- 提取到的內容
    例：
DECLARE

    vclob   CLOB;
    sustr   VARCHAR2(1000);
    len     NUMBER(4);
    start   NUMBER(4);

BEGIN

    SELECT RESUME INTO vclob FROM TLOB WHERE NO = 1;
    len := 4;
    start := 1;
    sustr := DBMS_LOB.SUBSTR(vclob, len, start);
    DBMS_OUTPUT.PUT_LINE('結果為: '||sustr);

END;
   
INSTR
    從LOB資料中查詢子字串位置的函式。
DBMS_LOB.INSTR(
    lob_loc  IN  BLOB/CLOB/NCLOB/BFILE,
    pattern  IN  RAW/VARCHAR2,
    offset   IN  INTERGER:=1,
    nth      IN  INTEGER:=1
)RETURN  INTEGER;
    例：
DECLARE
 
    vclob    CLOB;
    instr    VARCHAR2(1000);
    sustr    VARCHAR2(1000);
    len      NUMBER(4);

BEGIN
   
    SELECT RESUME INTO vclob FROM TLOB WHERE NO = 1;
    sustr := '大物件';
    len := DBMS_LOB.INSTR(vclob, instr);
    DBMS_OUTPUT.PUT_LINE('位置為: ' || len);

    sustr := DBMS_LOB.SUBSTR(vclob, LENGTH(instr), len);
    DBMS_OUTPUT.PUT_LINE('位置為' || len || '長度為' || LENGTH(instr) || '的子字串為：' || sustr);

END;

COMPARE

    比較二個大物件是否相等。返回數值0為相等，-1為不相等。

DBMS_LOB.COMPARE(
    lob_1       IN  BLOB/CLOB/BFILE,
    lob_2       IN  BLOB/CLOB/BFILE,
    amount      IN  INTEGER:=4294967295,      --要比較的字元數(CLOB),位元組數(BLOB)
    offset_1    IN  INTEGER:=1,               --lob_1 的起始位置
    offset_2    IN  INTEGER:=1                --lob_2 的起始位置
);
    例：
DECLARE 

    varc1    CLOB;
    varc2    CLOB;
    varc3    CLOB;
    len      NUMBER(4);

BEGIN

    SELECT RESUME INTO varc1 FROM TLOB WHERE NO = 1;
    SELECT RESUME INTO varc2 FROM TLOB WHERE NO = 2;
    SELECT RESUME INTO varc3 FROM TLOB WHERE NO = 3;

    len := DBMS_LOB.COMPARE(varc1,varc1);
    DBMS_OUTPUT.PUT_LINE('比較的結果為: ' || len);

    len := DBMS_LOB.COMPARE(varc2,varc3);
    DBMS_OUTPUT.PUT_LINE('比較的結果為: ' || len);

END;
 
ERASE
     刪除LOB資料中指定位置的部分資料的過程
DBMS_LOB.ERASE(
    lob_loc  IN  OUT  NOCOPY  BLOB/CLOB/NCLOB,
    amount   IN  OUT  NOCOPY  INTEGER,        --字元/位元組數
    offset   IN  INTEGER:=1                   —起始位置
);
     例：

DECLARE

    vclob       CLOB;
    len         NUMBER(4);
    start       NUMBER(4);

BEGIN  

    len := 1;
    start := 5;
    SELECT RESUME INTO vclob FROM TLOB WHERE NO = 1 FOR UPDATE;
    DBMS_LOB.ERASE(vclob, len, start);
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('擦除結果為: '|| clob);

END;

COPY

    從指定位置開始將源LOB複製到目標LOB

DBMS_LOB.COPY(
    dest_lob     IN  OUT  NOCOPY  BLOB/CLOB/NCLOB,  -- 目標的LOB
    src_lob      IN  BLOB/CLOB/NCOB,    -- 拷貝的來源的LOB
    amount       IN  INTEGER,           -- 拷貝的長度
    dest_offset  IN  INTEGER:=1,        -- 目標從哪裡開始接收
    src_offset   IN  INTEGER:=1         -- 來源從來歷開始拷貝
);
    例：
DECLARE 

    vdest_lob    CLOB;
    vsrc_lob     CLOB;
    amount       NUMBER;
    dest_offset  NUMBER;
    src_offset   NUMBER;

BEGIN

    SELECT RESUME INTO vdest_lob FROM TLOB WHERE NO = 1 FOR UPDATE; 
    SELECT RESUME INTO vsrc_lob FROM TLOB WHERE NO = 2 ;
      
    amount := DBMS_LOB.GETLENGTH(vsrc_lob);
    dest_offset := DBMS_LOB.GETLENGTH(vdest_lob) + 1;
    src_offset := 1;
      
    DBMS_LOB.COPY(vdest_lob, vsrc_lob, amount, dest_offset, src_offset);
    DBMS_OUTPUT.PUT_LINE('拷貝結果為: ' || vdest_lob);

END;

CREATETEMPORARY
    在使用者的臨時表空間中，建立臨時LOB
DBMS_LOB.CREATETEMPORARY(
    lob_loc  IN  OUT  NOCOPY  BLOB/DLOB/NCLOB,
    cache    IN  BOOLEAN,     -- 是否將LOB讀取到緩衝區
    dur      IN  PLS_INTEGER:=10    -- 指定何時清除臨時LOB（10：會話結束時；12：呼叫結束時）
);

ISTEMPORARY
    確定定位符是否為臨時LOB
DBMS_LOB.ISTEMPORARY(lob_loc  IN  BLOB/CLOB/NCLOB)  RETURN  INTEGER;

FILEEXISTS
    確定FILE_LOC對應的OS檔案是否存在。1：存在；0：不存在
DBMS_LOB.FILEEXISTS(file_loc  IN  BFILE) RETURN  INTEGER;

FILEGETNAME
    獲取BFILE定位符所對應的目錄別名和檔名
DBMS_LOB.FILEGETNAME(
    file_loc    IN  BFILE,
    dir_alias   OUT  VARCHAR2,
    filename    OUT  VARCHAR2
);

FILEISOPEN
    確定BFILE對應的OS檔案是否開啟     
DBMS_LOB.FILEISOPEN(file_loc  IN  BFILE)  RETURN  INTEGER;

FREETEMPORARY
    釋放在預設臨時表空間中的臨時LOB
DBMS_LOB.FREETEMPORARY(lob_loc  IN  OUT  NOCOPY  BLOB/CLOB/NCLOB);

FILEOPEN
    開啟檔案
DBMS_LOB.FILEOPEN(
    file_loc   IN  OUT  NOCOPY  BFILE,
    open_mode  IN  BINARY_INTEGER:FILE_READONLY
);

LOADBLOBFROMFILE
    將BFILE資料裝載到BLOB中，並且在裝載後取得最新的偏移位置
DBMS_LOB.LOADBLOBFROMFILE(
    dest_loc     IN  OUT  NOCOPY  BLOB,
    src_bfile    IN  BFILE,
    amount       IN  INTEGER,
    dest_offset  IN  OUT  INTEGER,
    src_offset   IN  OUT  INTEGER
);
    例：
DECLARE

    piece_bfile BFILE := BFILENAME('BFILE_DATA','test.jpg'); --建立一個BFILE指標
    photo_blob BLOB;

BEGIN

    SELECT bifle_content INTO photo_blob FROM bfile_demo WHERE bfile_id = 101;

    DBMS_LOB.OPEN(photo_blob, DBMS_LOB.LOB_READWRITE);
    DBMS_LOB.OPEN(piece_bfile);

    DBMS_LOB.LOADBLOBFROMFILE(photo_blob, piece_bfile, DBMS_LOB.LOBMAXSIZE, 1, 1);

    DBMS_LOB.CLOSE(photo_blob);
    DBMS_LOB.CLOSE(piece_bfile);
 
END;

FILECLOSE
    關閉開啟的BFILE定位符所指向的OS檔案
DBMS_LOB.FILECLOSE(FILE_LOC  IN  OUT  NOCOPY  BFILE);

FILECLOSEALL
    關閉當前會話已經開啟的所有BFILE檔案
DBMS_LOB.FILECLOSEALL();

參考

    http://blog.csdn.net/cunxiyuan108/article/details/16873733

    http://www.linuxidc.com/Linux/2011-12/49911.htm

    http://www.th7.cn/db/Oracle/201406/58454.shtml



相關文章