文档首页/ 数据仓库服务 GaussDB(DWS)/ 工具指南/ DSC/ Oracle语法迁移/ 系统函数/ LOB函数
更新时间：2024-06-29 GMT+08:00
查看PDF
分享

LOB函数

本节介绍如下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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
 
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 ;
/

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
 
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系统函数，GaussDB(DWS)并不隐式支持该函数。要支持此函数，DSC需在MIG_ORA_EXT模式中创建一个COMPARE函数。迁移后的语句将使用此新函数MIG_ORA_EXT.MIG_CLOB_COMPARE，SQL示例如下：

在SQL中使用COMPARE

输入：在SQL中使用DBMS_LOB.COMPARE

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

输出

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

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

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

输出

1
 
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

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

输出

1
 
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

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
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;
/

输出

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
 
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

1
2
3
4
5
6
7
8
 
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;
/

输出

1
2
3
4
5
6
7
8
9
 
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

1
2
3
4
5
6
7
8
 
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;
/

输出

1
2
3
4
5
6
7
8
9
 
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

1
2
3
 
SELECT expr1, …, DBMS_LOB.INSTR(str, septr, 1, 5)
  FROM tab1
 WHERE …;

输出

1
2
3
 
SELECT expr1, …, INSTR(str, septr, 1, 5)
  FROM tab1
 WHERE …

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

1
2
3
4
5
6
 
BEGIN
  …
       pos := DBMS_LOB.INSTR(str,septr,1, i);
  ...
END;
/

输出

1
2
3
4
5
6
 
BEGIN
  …
       pos := INSTR(str,septr,1, i);
  ...
END;
/

DBMS_LOB.SUBSTR

DBMS_LOB.SUBSTR通过配置参数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);
父主题： 系统函数