oracle lob函数,LOB函数_数据仓库服务 GaussDB(DWS)_工具指南_DSC SQL语法迁移工具_SQL语法迁移参考_Oracle语法迁移（至GaussDB A）_华为云...

本节介绍如下LOB函数：

DBMS_LOB.APPEND

DBMS_LOB.APPEND函数将源LOB的内容追加到指定的LOB。

输入：DBMS_LOB.APPEND

[sys.]dbms_lob.append(o_menuxml, to_clob('DSJKSDAJKSFDA'));

输出

o_menuxml := CONCAT(o_menuxml, CAST('DSJKSDAJKSFDA' AS CLOB));

输入：DBMS_LOB.APPEND

CREATE

OR REPLACE PROCEDURE append_example IS clobSrc CLOB ;

clobDest CLOB ;

BEGIN

SELECT

clobData INTO clobSrc

FROM

myTable

WHERE

id = 2 ;

SELECT

clobData INTO clobDest

FROM

myTable

WHERE

id = 1 ;

readClob ( 1 ) ;

DBMS_LOB.APPEND ( clobDest ,clobSrc ) ;

readClob ( 1 ) ;

END append_example ;

/

输出

CREATE

OR REPLACE PROCEDURE append_example IS clobSrc CLOB ;

clobDest CLOB ;

BEGIN

SELECT

clobData INTO clobSrc

FROM

myTable

WHERE

id = 2 ;

SELECT

clobData INTO clobDest

FROM

myTable

WHERE

id = 1 ;

readClob ( 1 ) ;

clobDest := CONCAT( clobDest ,clobSrc ) ;

readClob ( 1 ) ;

end ;

/

DBMS_LOB.COMPARE

DBMS_LOB.COMPARE函数比较两个LOB的所有/部分内容。DBMS_LOB.COMPARE是Oracle系统函数，DWS并不隐式支持该函数。要支持此函数，DSC需在MIG_ORA_EXT模式中创建一个COMPARE函数。迁移后的语句将使用此新函数MIG_ORA_EXT.MIG_CLOB_COMPARE，SQL示例如下：

在SQL中使用COMPARE

输入：在SQL中使用DBMS_LOB.COMPARE

SELECT a.empno ,dbms_lob.compare ( col1 ,col2 ) FROM emp a ,emp b ;

输出

SELECT a.empno ,MIG_ORA_EXT.MIG_CLOB_COMPARE ( col1 ,col2 ) FROM emp a ,emp b ;

输入：在SQL中使用DBMS_LOB.COMPARE，其中CREATE TABLE使用5个参数

CREATE TABLE abc nologging AS SELECT dbms_lob.compare ( col1 ,col2 ,3 ,5 ,4 ) FROM emp a ,emp b ;

输出

CREATE UNLOGGED TABLE abc AS ( SELECT MIG_ORA_EXT.MIG_CLOB_COMPARE ( col1 ,col2 ,3 ,5 ,4 ) FROM emp a ,emp b ) ;

输入：在函数(NVL2)的SQL中使用DBMS_LOB.COMPARE

SELECT REPLACE( NVL2( DBMS_LOB.COMPARE ( ENAME ,Last_name ) ,'NO NULL' ,'ONE NULL' ) ,'NULL' ) FROM emp ;

输出

SELECT REPLACE( DECODE ( MIG_ORA_EXT.MIG_CLOB_COMPARE ( ENAME ,Last_name ) ,NULL ,'ONE NULL' ,'NO NULL' ) ,'NULL' ,'' ) FROM emp ;

在PL/SQL中使用COMPARE

输入：在PL/SQL中使用DBMS_LOB.COMPARE

declare v_clob clob;

v_text varchar(1000);

v_compare_res INT;

BEGIN

v_clob := TO_CLOB('abcddedf');

v_text := '123454';

v_compare_res := dbms_lob.compare(v_clob, TO_CLOB(v_text));

DBMS_OUTPUT.PUT_LINE(v_compare_res);

end;

/

输出

declare v_clob clob;

v_text varchar(1000);

v_compare_res INT;

BEGIN

v_clob := CAST('abcddedf' AS CLOB);

v_text := '123454';

v_compare_res := MIG_ORA_EXT.MIG_CLOB_COMPARE(v_clob,cast(v_text as CLOB));

DBMS_OUTPUT.PUT_LINE(v_compare_res);

end;

/

DBMS_LOB.CREATETEMPORARY

DBMS_LOB.CREATETEMPORARY函数在用户默认的临时表空间中创建一个临时LOB及其对应索引。DBMS_LOB.FREETEMPORARY用于删除临时LOB及其索引。

输入：DBMS_LOB.CREATETEMPORARY和DBMS_LOB.FREETEMPORARY

declare v_clob clob;

begin

DBMS_LOB.CREATETEMPORARY(v_clob, TRUE, DBMS_LOB.SESSION);

v_clob := TO_CLOB('abcddedf');

DBMS_OUTPUT.PUT_LINE(v_clob);

DBMS_LOB.FREETEMPORARY(v_clob);

end;

/

输出

declare v_clob clob;

begin

-- DBMS_LOB.CREATETEMPORARY(v_clob, TRUE, DBMS_LOB.SESSION);

v_clob := CAST('abcddedf' AS CLOB);

DBMS_OUTPUT.PUT_LINE(CAST(v_clob AS TEXT));

-- DBMS_LOB.FREETEMPORARY(v_clob);

NULL;

end;

/

DBMS_LOB.FREETEMPORARY

DBMS_LOB.FREETEMPORARY函数释放默认临时表空间中的临时BLOB或CLOB。在调用FREETEMPORARY之后，释放的LOB定位器标记为无效。

输入：DBMS_LOB.CREATETEMPORARY和DBMS_LOB.FREETEMPORARY

declare v_clob clob;

begin

DBMS_LOB.CREATETEMPORARY(v_clob, TRUE, DBMS_LOB.SESSION);

v_clob := TO_CLOB('abcddedf');

DBMS_OUTPUT.PUT_LINE(v_clob);

DBMS_LOB.FREETEMPORARY(v_clob);

end;

/

输出

declare v_clob clob ;

BEGIN

/*DBMS_LOB.CREATETEMPORARY(v_clob, TRUE, DBMS_LOB.SESSION);*/

v_clob := cast( 'abcddedf' as CLOB ) ;

DBMS_OUTPUT.PUT_LINE ( v_clob ) ;

/* DBMS_LOB.FREETEMPORARY(v_clob); */

null ;

end ;

/

DBMS_LOB.INSTR

DBMS_LOB.INSTR函数从指定的偏移量开始，返回在LOB中第n次匹配模式的位置。

输入：在SQL中使用DBMS_LOB.INSTR

SELECT expr1, …, DBMS_LOB.INSTR(str, septr, 1, 5)

FROM tab1

WHERE …;

输出

SELECT expr1, …, INSTR(str, septr, 1, 5)

FROM tab1

WHERE …

输入：在PL/SQL中使用DBMS_LOB.INSTR

BEGIN

…

pos := DBMS_LOB.INSTR(str,septr,1, i);

...

END;

/

输出

BEGIN

…

pos := INSTR(str,septr,1, i);

...

END;

/

DBMS_LOB.SUBSTR

DBMS_LOB.SUBSTR适用于V1R8C10。通过配置参数MigDbmsLob，用户可以指定迁移此函数还是直接保留。

输入：DBMS_LOB.SUBSTR，MigDbmsLob设为true

如果参数MigDbmsLob设为true，则迁移。相反，如果参数MigDbmsLob设为false，则不迁移。

输入

select dbms_lob.substr('!2d3d4dd!',1,5);

输出

If the config param is true, it should be migrated as below:

select substr('!2d3d4dd!',5,1);

If false, it should be retained as it is:

select dbms_lob.substr('!2d3d4dd!',1,5);

输入

select dbms_lob.substr('!2d3d4dd!',5);

输出

If the config param is true, it should be migrated as below:

select substr('!2d3d4dd!',1,5);

If false, it should be retained as it is:

select dbms_lob.substr('!2d3d4dd!',5);