Oracle - DBMS_LOB函数和用法

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