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