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

DML(Oracle)

本节主要介绍Oracle DML的迁移语法。迁移语法决定了关键字/功能的迁移方式。

具体见以下节点内容:

SELECT

INSERT

MERGE

SELECT

概述

Oracle的SELECT语句可以启动查询,使用一个可选的ORDER BY子句,该子句用于从数据库的一个或多个表中提取记录。

输入:SELECT

SELECT col1, col2
  FROM tab1;

输出

SELECT col1, col2
  FROM tab1;
  1. 子句顺序

    HAVING子句必须出现在GROUP BY子句后面,而Oracle允许HAVING在GROUP BY子句之前或之后。在目标数据库中,HAVING子句被移至GROUP BY子句之后。

    图1 输入:子句顺序
    图2 输出:子句顺序
  2. 扩展Group By子句

    指定GROUP BY子句可让数据库将所选行基于expr(s)的值分组。如果该子句包含CUBE,ROLLUP,或GROUPING SETS扩展项,则数据库除正则分组外还会进行超聚合分组。这些功能在GaussDB(DWS)中不可用,可通过UNION ALL操作符实现。

    图3 输入:扩展Group By子句
    图4 输出:扩展Group By子句

    GROUPING_ID和ROLLUP

    GROUPING_ID会返回一个数字,该数字与关联到某行的GROUPING位向量相对应。GROUPING_ID仅适用于包含GROUP BY扩展项的SELECT语句,例如ROLLUP操作符和GROUPING函数。在包含多个GROUP BY表达式的查询中,要确定特定行的GROUP BY级别,需要使用多个GROUPING函数,这可能导致SQL语句变得复杂。在这种情况下,可使用GROUPING_ID避免语句复杂化。

  3. 括号中的表名

    表名不需要在括号内指定,而Oracle允许使用括号。

    图5 输入:括号中的表名
    图6 输出:括号中的表名
  4. UNIQUE关键字

    UNIQUE关键字迁移为DISTINCT关键字

    输入: SELECT UNIQUE

    SELECT UNIQUE a.item_id id,
                         a.menu_id parent_id,a.serialno menu_order
                    FROM ctp_menu_item_rel a WHERE  
        a.item_id IN(SELECT UNIQUE id FROM ctp_temp_item_table);

    输出

    SELECT DISTINCT a.item_id id,
       a.menu_id parent_id,a.serialno menu_order
         FROM ctp_menu_item_rel a WHERE  
           a.item_id IN(SELECT UNIQUE id FROM ctp_temp_item_table);
  5. USERENY

    输入CLIENT_INFO

    返回用户会话信息。

    SELECT 1
    FROM   sp_ht ht
    WHERE  ht.hth = pi_contract_number
           /*  AND ht.contract_status = 2  --delete by leinian 2014-03-03(ECO)  */
           AND ht.contract_status IN ( 1, 2 ) /*  add by leinian 2014-03-20(ECO)  */
           AND Nvl(ht.s3_pilot_flag, 'N') = 'N'
           AND NOT EXISTS (SELECT 1
                           FROM   asms.asms_lookup_values alv
                           WHERE  alv.type_code = 'HTLX_LOAN'
                                  AND ht.htlx = alv.code)
           AND ht.duty_erp_ou_id = To_number(Nvl(Rtrim(Ltrim(Substr(Userenv(
                                                             'client_info'),
                                                             1,
                                                             8))), 218))
           AND ht.source_code = 'ECONTRACT'
           AND ht.needing_engineering_service IS NOT NULL
           AND ht.khm != '28060'
           AND ht.htlx != '111' ;

    输出

    SELECT
              1
         FROM
              sp_ht ht
         WHERE
              ht.hth = pi_contract_number /*   AND ht.contract_status = 2  --delete by leinian 2014-03-03(ECO)   */
              AND ht.contract_status IN (
                   1
                   ,2
              ) /*   add by leinian 2014-03-20(ECO)   */
              AND Nvl( ht.s3_pilot_flag ,'N' ) = 'N'
              AND NOT EXISTS (
                   SELECT
                             1
                        FROM
                             asms.asms_lookup_values alv
                        WHERE
                             alv.type_code = 'HTLX_LOAN'
                             AND ht.htlx = alv.code
              )
              AND ht.duty_erp_ou_id = To_number( Nvl( Rtrim( Ltrim( SUBSTR( MIG_ORA_EXT.USERENV ( 'client_info' ) ,1 ,8 ) ) ) ,218 ) )
              AND ht.source_code = 'ECONTRACT'
              AND ht.needing_engineering_service IS NOT NULL
              AND ht.khm != '28060'
              AND ht.htlx != '111' ;

    USERENV('CLIENT_INFO)

    包中的函数转换后,不删除结束后的函数标记。4. sad_lookup_contract_pkg.bdy中的svproduct_is_for_pa函数被使用。

    USERENV('CLIENT_INFO')

    过程中使用的USERENV。迁移过程因工具而失败。

    SELECT 1
    FROM   sp_ht ht
    WHERE  ht.hth = pi_contract_number
    /*  AND ht.contract_status = 2  --delete by leinian 2014-03-03(ECO)  */
    AND ht.contract_status IN ( 1, 2 ) /*  add by leinian 2014-03-20(ECO)  */
    AND Nvl(ht.s3_pilot_flag, 'N') = 'N'
    /*  add by yangyirui 2012-09-10: S3切换合同不提供数据  */
    AND NOT EXISTS (SELECT 1
    FROM   asms.asms_lookup_values alv
    WHERE  alv.type_code = 'HTLX_LOAN'
    AND ht.htlx = alv.code)
    AND ht.duty_erp_ou_id = To_number(Nvl(Rtrim(Ltrim(Substr(Userenv(
    'client_info'),
    1,
    8))), 218))
    AND ht.source_code = 'ECONTRACT'
    AND ht.needing_engineering_service IS NOT NULL
    AND ht.khm != '28060'
    AND ht.htlx != '111'

    输入

    Error message :client_info argument for USERENV function is not supported by the DSC.
    
    4_sad_lookup_contract_pkg
    
    ================================
    CREATE OR REPLACE PACKAGE BODY SAD.sad_lookup_contract_pkg IS
     FUNCTION svproduct_is_for_pa(pi_contract_number IN VARCHAR2) RETURN VARCHAR2 IS
        l_flag VARCHAR2(1) := 'N';
      BEGIN
        FOR rec_lookup IN (SELECT 1
           FROM asms.asms_lookup_values alv
          WHERE alv.type_code = 'HTLX_LOAN'
            AND alv.duty_erp_ou_id = to_number(nvl(rtrim(ltrim(substr(userenv('client_info'), 1, 8))), 218))
                          )
        LOOP
          l_flag := 'Y';
        END LOOP;
    
        RETURN l_flag;
      END svproduct_is_for_pa;
    END sad_lookup_contract_pkg;
    /  
    

    输出

    CREATE OR replace FUNCTION sad_lookup_contract_pkg.Svproduct_is_for_pa ( 
    pi_contract_number IN VARCHAR2) 
    RETURN VARCHAR2 
    IS 
      l_flag VARCHAR2 ( 1 ) := 'N'; 
    BEGIN 
        FOR rec_lookup IN (SELECT 1 
                           FROM   asms.asms_lookup_values alv 
                           WHERE  alv.type_code = 'HTLX_LOAN' 
                                  AND alv.duty_erp_ou_id = To_number(Nvl( 
                                                           Rtrim(Ltrim(Substr( 
                                      mig_ora_ext.Userenv ( 
                                      'client_info'), 1, 8)) 
                                                           ), 
                                                                                218) 
                                                           )) 
        LOOP 
            l_flag := 'Y'; 
        END LOOP; 
    
        RETURN l_flag; 
    END; 
    
    / 

INSERT

概述

Oracle INSERT语句用于将单个记录或多个记录插入到表中。

NOLOGGING

在插入的脚本中对NOLOGGING进行注释。

Oracle 语法

迁移后语法

INSERT INTO TBL_ORACLE NOLOGGING
  SELECT emp_id, emp_name
   FROM emp;
INSERT INTO TBL_ORACLE /*NOLOGGING*/
  SELECT emp_id, emp_name
   FROM emp;
  1. INSERT ALL

    Oracle的INSERT ALL语句可通过单个INSERT语句向单个或多个表中插入多行。目标查询将转化为公用表表达式(CTE)。

    图7 输入:INSERT ALL
    图8 输出:INSERT ALL
  2. INSERT FIRST

    Oracle的INSERT FIRST语句用于在first条件为真时执行INSERT语句,而其他语句会被忽略。目标查询将转化为公用表表达式。

    图9 输入:INSERT FIRST
    图10 输出:INSERT FIRST
  3. INSERT(使用表别名)
    Oracle表别名通过为查询中的表分配名称或代码,用于声明和提高可读性。INSERT with Alias可与INSERT INTO语句一起使用。DSC可迁移含有表别名的INSERT INTO语句。
    1. Blogic操作

      输入:INSERT,使用表别名

      CREATE
           OR REPLACE FUNCTION myfct RETURN VARCHAR2 IS res VARCHAR2 ( 200 ) ;
           BEGIN
                res := 100 ;
                INSERT INTO emp18 RW ( RW.empno ,RW.ename ) SELECT
                     res ,RWN.ename
                FROM
                     emp16 RWN ;
                     COMMIT ;
                RETURN res ;
      END ;
      /

      输出

      CREATE
           OR REPLACE FUNCTION myfct RETURN VARCHAR2 IS res VARCHAR2 ( 200 ) ;
           BEGIN
                res := 100 ;
                INSERT INTO emp18 ( empno ,ename ) SELECT
                     res ,RWN.ename
                FROM
                     emp16 RWN ;
                     /* COMMIT ; */
                null ;
                RETURN res ;
      END ;
      /
    2. Bulk操作

      输入:INSERT,使用表别名

      INSERT
           INTO
                Public.emp14 ats (
                     ats.empno
                     ,ats.ename
                )
           VALUES (
                3
                ,'Categories'
           )
      ;

      输出

      INSERT
           INTO
                Public.emp14 (
                     empno
                     ,ename
                ) SELECT
                          3
                          ,'Categories'
      ;

      输入:INSERT,使用表别名

      INSERT
           INTO
                "abc" . "emp18" wmc (
                     wmc.empno
                     ,wmc.ename
                ) SELECT
                          wmc.empno
                          ,wm_concat (wmc.ename) AS eName
                     FROM
                          emp16 wmc
                     GROUP BY
                          empno
      ;

      输出

      INSERT
           INTO
                "abc" . "emp18" (
                     empno
                     ,ename
                ) SELECT
                          wmc.empno
                          ,STRING_AGG (
                               wmc.ename
                               ,','
                          ) AS eName
                     FROM
                          emp16 wmc
                     GROUP BY
                          empno
      ;

      输入:INSERT,使用表别名

      INSERT
           INTO
                emp14 "TABLE" (
                     "TABLE" .empno
                     ,ename
                ) SELECT
                          empno
                          ,ename
                     FROM
                          emp12
                     WHERE
                          emp12.salary > (
                               SELECT
                                         MAX( salary )
                                    FROM
                                         emp13 "TABLE"
                                    WHERE
                                         "TABLE" .empno > 5
                          )
      ;

      输出

      INSERT
           INTO
                emp14 (
                     empno
                     ,ename
                ) SELECT
                          empno
                          ,ename
                     FROM
                          emp12
                     WHERE
                          emp12.salary > (
                               SELECT
                                         MAX( salary )
                                    FROM
                                         emp13 "TABLE"
                                    WHERE
                                         "TABLE" .empno > 5
                          )
      ;

MERGE

MERGE是一种ANSI标准的SQL语法运算符,用于从一个或多个源中选择行来更新或插入表或视图。用户可指定更新或插入目标表或视图的条件。

目前,6.5.0及之后版本的GaussDB(DWS)支持此功能。DSC使用多种方法将MERGE迁移到GaussDB(DWS)兼容的SQL中。

配置参数mergeImplementation:

  • 默认设置为WITH。设为此值时,目标查询将转换成公用表表达式。
图11 输入:MERGE(1)
图12 输出:MERGE(2)
  • 也可设置为SPLIT。设为此值时,MERGE语句将被分解为多个INSERT和UPDATE语句。
图13 输入:MERGE(3)
图14 输出:MERGE(4)