Oracle12c - DBMS_LOB函数和用法解决字符串拼接过长，超过缓冲区的问题

朱雀 2022-05-14 14:39 1140阅读 0赞

解决字符串拼接过长，超过缓冲区的问题

第一种： 截取后聚合， 缺点： 会损失聚合的内容

listagg(内容， ',')  within  group (order by  字段)

DBMS\_LOB.substr(字段， 截取多长，  从什么位置截取);

第二种： xmlagg   ,缺点速度慢， 但是不会损失内容

xmlagg(xmlparse(content  内容  || ',' wellformed)  order by 字段).getclobval();

如果对字符长度有限制则可以截取

substr(xmlagg(xmlparse(content  内容  || ',' wellformed)  order by 字段).getclobval(), 0, 1024);

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;

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