文档首页 > > 工具指南> Migration Tool SQL语法迁移工具> Oracle语法迁移> 系统函数> LOB函数

LOB函数

分享
更新时间: 2019/08/09 GMT+08:00

本节介绍如下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并不隐式支持该函数。要支持此功能,Migration Tool需在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和iots索引。

输入: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 with 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));
    -- DB

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,用户可以指定迁移此功能还是保留该功能。

输入:当MigDbmsLob参数设为true时的DBMS_LOB.SUBSTR

如果参数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);
分享:

    相关文档

    相关产品

文档是否有解决您的问题?

提交成功!

非常感谢您的反馈,我们会继续努力做到更好!

反馈提交失败,请稍后再试!

*必选

请至少选择或填写一项反馈信息

字符长度不能超过200

提交反馈 取消

如您有其它疑问,您也可以通过华为云社区问答频道来与我们联系探讨

跳转到云社区