更新时间:2022-08-16 GMT+08:00

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

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系统函数,GaussDB(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);