文档首页 > > 工具指南> DSC SQL语法迁移工具> SQL语法迁移参考> Oracle语法迁移（至GaussDB A）>

其他函数

查看PDF

其他函数

分享
更新时间：2021/03/09 GMT+08:00

本节介绍如下函数：

类型转换

Oracle/PLSQL的CAST函数用于转换数据类型。

CAST ( AS TEXT)

一般情况下，静态值迁移为CAST AS TEXT。但是，在 PL/SQL存储过程和函数中 ，SELECT INTO子句中包的含静态值不迁移，静态值输入和输出完全相同。

输入： CAST（AS TEXT）

BEGIN
               IF
                    V_DETAILSTATUS = '002'
                    THEN
                         meta.p_mt_std_cover_log.r_cover_log ( '384969028' ,'566' ,'35' ) ;
                    SELECT
                    'abc'  INTO V_RECL_USER
               FROM
                    STAT_DETAIL
               WHERE
                    DAPCODE = V_DAPCODE
                    AND DETAIL_ID = V_DETAIL_ID ;
               ELSIF
...
END;

输出

BEGIN
               IF
                    V_DETAILSTATUS = '002'
                    THEN
                         meta.p_mt_std_cover_log.r_cover_log ( '384969028' ,'566' ,'35' ) ;
                    SELECT
                    'abc'  INTO V_RECL_USER
               FROM
                    STAT_DETAIL
               WHERE
                    DAPCODE = V_DAPCODE
                    AND DETAIL_ID = V_DETAIL_ID ;
               ELSIF
...
END;

TO_CLOB

在Oracle中，CLOB数据类型可存储单字节或多字节字符集（支持超过4 GB）中的变长字符数据（字符型大对象）。TO_CLOB函数用于将字符串（CHAR、VARCHAR2 、NCHAR、NVARHCAR2、CLOB或NCLOB）转换为CLOB数据类型。DSC支持SQL和PL/SQL中的TO_CLOB。

输入：在SQL中使用TO_CLOB

create table clob_t ( c1 int, c2 clob );
insert into clob_t values ( 1, TO_CLOB('abcddedf') );

输出

create table clob_t ( c1 int, c2 clob );
insert into clob_t values ( 1, CAST('abcddedf' AS CLOB) );

输入：在PL/SQL中使用TO_CLOB

declare v_clob clob;
        v_text varchar(1000);
begi  in Sn
    v_clob := TO_CLOB('abcddedf');
    insert into clob_t values ( 2, v_clob );

 v_text := '123454';
 insert into clob_t values ( 3, TO_CLOB(v_text || v_clob) );
end;
/

输出

declare v_clob clob;
        v_text varchar(1000);
begin
    v_clob := CAST('abcddedf' AS CLOB);
    insert into clob_t values ( 2, v_clob );

 v_text := '123454';
 insert into clob_t values ( 3, CAST(v_text || v_clob AS CLOB) );
end;
/

sys_guid( )

不支持类似于SYS_ID VARCHAR2 (32) DEFAULT sys_guid( )的函数名称，因此表列名称显示为默认值。

输入

  CREATE TABLE SAD.FND_DATA_CHANGE_LOGS_T
   (    LOGID NUMBER,
        TABLE_NAME VARCHAR2(40) NOT NULL ENABLE,
        TABLE_KEY_COLUMNS VARCHAR2(200),
        TABLE_KEY_VALUES VARCHAR2(200),
        COLUMN_NAME VARCHAR2(40) NOT NULL ENABLE,
        COLUMN_CHANGE_FROM_VALUE VARCHAR2(200),
        COLUMN_CHANGE_TO_VALUE VARCHAR2(200),
        DESCRIPTION VARCHAR2(500),
        SYS_ID VARCHAR2(32) DEFAULT sys_guid()
   );

输出

CREATE TABLE sad.fnd_data_change_logs_t 
  ( 
     logid                                  NUMBER
     ,table_name                        VARCHAR2 (40) NOT NULL /* ENABLE */ 
     ,table_key_columns              VARCHAR2 (200)
     ,table_key_values                VARCHAR2 (200) 
     ,column_name                     VARCHAR2 (40) NOT NULL /* ENABLE */ 
     ,column_change_from_value   VARCHAR2 (200)
     ,column_change_to_value      VARCHAR2 (200) 
     ,description                         VARCHAR2 (500)
     ,sys_id                               VARCHAR2 (32) DEFAULT MIG_ORA_EXT.Sys_guid () 
  );

sys.dbms_job.submit

dbms_job函数不支持通过sys来调用，例如，sys.dbms_job.submit(job => v_jobid, what => 'begin eip_htm_integration_pkg.import_instruction_job; end;', next_date => SYSDATE)。

如果从删除sys，则调用生效，例如，dbms_job.submit(job => v_jobid, what => 'begin eip_htm_integration_pkg.import_instruction_job; end;', next_date => SYSDATE)。

脚本：EIP_HTM_INTEGRATION_PKG.SQL, SAD_CALC_CUSTOM_DRAWBACK_PKG.SQL

输入

CREATE OR REPLACE PACKAGE BODY "SAD"."EIP_HTM_INTEGRATION_PKG" IS
PROCEDURE greate_import_instruction_job IS
v_jobid NUMBER;
BEGIN
IF bas_lookup_misc_pkg.exits_run_job('eip_htm_integration_pkg.import_instruction_job') = 'N'
THEN
sys.dbms_job.submit(job       => v_jobid,
what      => 'begin
eip_htm_integration_pkg.import_instruction_job;
end;',
next_date => SYSDATE);
COMMIT;
END IF;
---
END greate_import_instruction_job;
END EIP_HTM_INTEGRATION_PKG;

输出

CREATE
OR REPLACE PROCEDURE SAD.EIP_HTM_INTEGRATION_PKG#greate_import_instruction_job IS v_jobid NUMBER ;
ex_data_error
EXCEPTION ;
ex_prog_error
EXCEPTION ;
BEGIN
IF
BAS_LOOKUP_MISC_PKG#exits_run_job ( 'eip_htm_integration_pkg.import_instruction_job' ) = 'N' THEN
sys.dbms_job.submit ( job => v_jobid ,what => 'begin eip_htm_integration_pkg.import_instruction_job; end;' ,next_date => SYSDATE ) ;
/*  COMMIT;  */
NULL ;
END IF ;
---
END ;

用户环境

在Oracle中，USERENV命名空间返回当前会话信息。DSC可迁移如下示例中的USERENV语句。

不支持以下情况，出现以下情况工具将报异常。

错误原因

示例输入

示例输出

参数不支持

SELECT userenv('LANG') LANG FROM DUAL;

SELECT userenv('LANG') LANG FROM DUAL;

参数是变量

SELECT userenv(ENTRYID) LANG FROM DUAL;

SELECT userenv(ENTRYID) LANG FROM DUAL;

未提供参数

SELECT userenv() LANG FROM DUAL;

SELECT userenv() LANG FROM DUAL;

空参数

SELECT userenv('SID','INSTANCE' ) LANG FROM DUAL;

SELECT userenv('SID','INSTANCE' ) LANG FROM DUAL;

提供了多个参数

SELECT userenv('' ) LANG FROM DUAL;

SELECT userenv('' ) LANG FROM DUAL;

一个语法有效另外一个无效 （不支持的参数）

SELECT USERENV( 'sid' ) ,USERENV( 'lang' ) FROM dual ;

SELECT USERENV( 'sid' ) ,USERENV( 'lang' ) FROM dual ;

输入内容含双引号

SELECT USERENV( "sid" ) FROM dual ;

SELECT USERENV( "sid" ) FROM dual ;

在使用此函数之前：

  1. 创建并使用MIG_ORA_EXT模式。
  2. 复制customs_scripts文件的内容，并在要执行迁移的所有目标数据库中执行此脚本。详情请参见迁移流程。

输入：USERENV

SELECT  userenv('SID') SID FROM DUAL;

输出

SELECT  mig_ora_ext.userenv('SID') SID FROM DUAL;

输入：USERENV

CREATE
     OR REPLACE PROCEDURE test_userEnvFun AS 
     v_sid NUMBER ( 8 ) := 0 ;
     v_instid NUMBER ( 8 ) := 0 ;
BEGIN
                 SELECT
                USERENV( 'sid' ) ,USERENV( 'instance' ) INTO v_sid ,v_instid
                FROM
               dual ;
end test_userEnvFun ;
/

输出

CREATE
     OR REPLACE PROCEDURE test_userEnvFun  AS 
     v_sid NUMBER ( 8 ) := 0 ;
     v_instid NUMBER ( 8 ) := 0 ;
BEGIN
                 SELECT
                MIG_ORA_EXT.USERENV ( 'sid' ) ,MIG_ORA_EXT.USERENV ( 'instance' ) INTO v_sid ,v_instid
                FROM
               dual ;
  end ;
   /

DBLINK

DBLINK应替换为可配置的数据源。

Oracle 语法

迁移后语法

 
SELECT col1, col2+col3 AS expcol2, col3 expcol3 
FROM stage.INF_RIM_112_INFO_M@HABIDB1 t
WHERE t.month_id = last_months
AND  DELETE_STATE = '0';

SELECT t.*
FROM (select * from stage.INF_RIM_112_INFO_M@HABIDB1 t 
WHERE t.month_id = last_months
AND  DELETE_STATE = '0') t,
dim.dim_ref_area_no t1
WHERE t1.AREA_ID_PROV = t.area_no
AND t1.area_no = to_char('abc');

SELECT t.*
FROM (select *
from stage.INF_RIM_112_INFO_M@HABIDB1 t) t,
dim.dim_ref_area_no t1
WHERE t1.AREA_ID_PROV = t.area_no
AND t1.area_no = to_char('abc');

SELECT COUNT(1) INTO V_CNT1
FROM ODS.ODS_EXECUTE_LOG@HABIDB1
WHERE MONTH_ID = LAST_MONTHS
AND PROCNAME IN ('P_INF_RIM_112_INFO_M')
AND RESULT = 'SUCCESS';
SELECT COUNT(1) AS cnt INTO V_CNT1
FROM ODS.ODS_EXECUTE_LOG@HABIDB1
WHERE MONTH_ID = LAST_MONTHS
AND PROCNAME IN ('P_INF_RIM_112_INFO_M')
AND RESULT = 'SUCCESS';
 
SELECT *
FROM exec_on_extension('ds_ora_habidb1', $q$SELECT col1, col2+col3 AS expcol2, col3 expcol3 
FROM stage.INF_RIM_112_INFO_M t
WHERE t.month_id = last_months
AND  DELETE_STATE = '0'$q$) 
AS (col1 TEXT, expcol2 TEXT, expcol3 TEXT);

SELECT t.*
FROM (SELECT * FROM exec_on_extension('ds_ora_habidb1', $q$select * 
from stage.INF_RIM_112_INFO_M t
WHERE t.month_id = last_months
AND DELETE_STATE = '0' 
$q$) AS (month_id TEXT, delete_state TEXT) ) t,
dim.dim_ref_area_no t1
WHERE t1.AREA_ID_PROV = t.area_no
AND t1.area_no = to_char('abc');

SELECT t.*
FROM (SELECT * FROM exec_on_extension('ds_ora_habidb1', $q$select * 
from stage.INF_RIM_112_INFO_M t
$q$) AS (a TEXT) ) t,
dim.dim_ref_area_no t1
WHERE t1.AREA_ID_PROV = t.area_no
AND t1.area_no = to_char('abc');

SELECT aggfn 
INTO V_CNT1 FROM exec_on_extension('ds_ora_habidb1', $q$SELECT COUNT(1)
FROM ODS.ODS_EXECUTE_LOG
WHERE MONTH_ID = LAST_MONTHS
AND PROCNAME IN ('P_INF_RIM_112_INFO_M')
AND RESULT = 'SUCCESS'$q$) AS (aggfn TEXT);

SELECT cnt 
INTO V_CNT1 FROM exec_on_extension('ds_ora_habidb1', $q$SELECT COUNT(1) AS cnt
FROM ODS.ODS_EXECUTE_LOG
WHERE MONTH_ID = LAST_MONTHS
AND PROCNAME IN ('P_INF_RIM_112_INFO_M')
AND RESULT = 'SUCCESS'$q$) AS (cnt TEXT);
分享：

    相关文档

    相关产品