2.7.2.1. GaussDB-Oracle 目标库

本节描述 伪多发 GaussDB-Oracle 目标库的 SQL 兼容情况说明。

目前兼容 505.2 版本。

2.7.2.1.1. MERGE 语句

语法结构

MERGE INTO [ schema. ] { table | view } [ t_alias ]
USING { [ schema. ] { table | view }
      | subquery
} [ t_alias ]
ON ( condition )
[ merge_update_clause ]
[ merge_insert_clause ];

merge_update_clause:
    WHEN MATCHED THEN
        UPDATE SET column = { expr | DEFAULT }
                   [, column = { expr | DEFAULT } ]...
        [ where_clause ]
        [ DELETE where_clause ]

merge_insert_clause:
    WHEN NOT MATCHED THEN
        INSERT [ ( column [, column ]... ) ]
        VALUES ( { expr [, expr ]... | DEFAULT } )
        [ where_clause ]

subquery:
    SELECT [ DISTINCT | ALL ] select_expr_list
    [ FROM from_list ]
    [ WHERE condition ]
    [ GROUP BY group_expression_list [ HAVING condition ] ]
    [ ORDER BY order_expression_list ]
    [ FOR UPDATE [ OF column ]
      [ { NOWAIT | WAIT integer | SKIP LOCKED } ] ]
    [ row_limiting_clause ]

select_expr_list:
    table_name.*
    | table_alias_name.*
    | expr [ [ AS ] column_alias_name ]

from_list:
    DUAL
    | table_reference [, table_reference ... ]

table_reference:
    simple_table
    | joined_table
    | pivot_clause
    | unpivot_clause

simple_table:
    table_name [ table_alias_name ]
    | ( select_stmt ) [ table_alias_name ]
    | ( table_reference_list )

joined_table:
    table_reference [ INNER ] JOIN simple_table [ join_condition ]
    | table_reference outer_join_type JOIN simple_table join_condition

outer_join_type:
    { LEFT | RIGHT | FULL } [ OUTER ]

join_condition:
    ON expression

group_expression_list:
    group_expression [, group_expression ... ]

group_expression:
    expression

order_expression_list:
    order_expression [, order_expression ... ]

order_expression:
    expression [ ASC | DESC ]

row_limiting_clause:
    [ OFFSET offset { ROW | ROWS } ]
    [ FETCH { FIRST | NEXT } [ { rowcount } ] { ROW | ROWS } { ONLY } ]

pivot_clause:
    PIVOT (
        aggregate_function ( expr ) [ [ AS ] alias ]
        [, aggregate_function ( expr ) [ [ AS ] alias ] ... ]
        pivot_for_clause
        pivot_in_clause
    )

pivot_for_clause:
    FOR { column }

pivot_in_clause:
    IN (
        { { expr | ( expr [, expr ... ] ) } [ [ AS ] alias ] ... }
        [, { { expr | ( expr [, expr ... ] ) } [ [ AS ] alias ] ... } ]
    )

unpivot_clause:
    UNPIVOT (
        { column }
        pivot_for_clause
        unpivot_in_clause
    )

unpivot_in_clause:
    IN (
        { column | ( column [, column ... ] ) }
    )

操作模式

MERGE 语句支持以下操作组合：

• 只更新（仅 WHEN MATCHED）

• 只插入（仅 WHEN NOT MATCHED）

• 更新 + 插入（同时包含 WHEN MATCHED 和 WHEN NOT MATCHED）

不支持的语法特性

1. 带序列（sequence）的 INSERT 不准确

   Oracle 中使用 sequence.NEXTVAL 的 MERGE 语句转换不准确：

   MERGE INTO t1 t
   USING (
       SELECT seq_test.NEXTVAL AS new_id, dept_id
       FROM t2
   ) s
   ON (t.id = s.dept_id)
   WHEN NOT MATCHED THEN
       INSERT (id, department_id)
       VALUES (s.new_id, s.dept_id);
   

   限制说明：USING 子查询中的 sequence.NEXTVAL 无法被转换，所以查询结果可能不准确。

2. 子查询后不能跟 PIVOT 或 UNPIVOT

   使用 PIVOT 或 UNPIVOT 的子查询作为 MERGE 的源数据时不支持：

   SELECT *
   FROM (
       SELECT emp_id, income_type, amount
       FROM t
   ) PIVOT (
       SUM(amount) FOR income_type IN ('BASE', 'BONUS')
   );
   

   限制说明：MERGE 的 USING 子句可以包含 PIVOT 或 UNPIVOT 操作，但 FROM 不能是子查询。

3. UNPIVOT 中不能使用 AS 常量

   UNPIVOT 子句中不支持使用 AS 指定常量：

   UNPIVOT (
       amount FOR income_type IN (
           base_salary AS 'BASE',
           bonus AS 'BONUS'
       )
   )
   

   限制说明：UNPIVOT 的 IN 子句中不支持 AS '常量' 的写法。

4. 不支持 FALSE 表达式 + DUAL + UPDATE 的 MERGE

   使用 DUAL 表和虚假条件（如 1 = 0）的 MERGE 语句不支持：

   MERGE INTO table t
   USING DUAL ON (1 = 0)
   WHEN NOT MATCHED THEN INSERT ...
   

   限制说明：当使用 DUAL 作为源表且条件永远为 FALSE 时，多发比对将无法匹配到有效结果。

2.7.2.2. KINGBASE-ORACLE 目标库

2.7.2.2.1. MERGE 语句

语法结构

单表插入：

[ with_query_clause ] insert_into_clause
{ values_clause }
[ on_conflict_clause ]
[ returning_clause]

with_query_clause:
        WITH [ RECURSIVE ] with_query [, ...]。

with_query详细格式为：
        with_query_name [ ( column_name [, ...] ) ] AS
        ( {select | values | insert | update | delete} )

insert_into_clause:
        INSERT INTO table_name
        [ [ AS ] alias ] | sub_query [alias]
        [ ( column_name [, ...] ) ]

values_clause:
        VALUES ( { expression } [, ...] ) [, ...] |query)

query语法:
        [ WITH [ RECURSIVE ] with_query [, ...] ]
        SELECT [ ALL | DISTINCT [ ON ( expression [, ...] ) ] | UNIQUE [ ON ( expression [, ...] ) ] ]
                [ * | expression [ [ AS ] output_name ] [, ...] ]
                [ FROM from_item [, ...] ]
                [ WHERE condition ]
                [ GROUP BY grouping_element [, ...] ]
                [ HAVING condition [, ...] ]
                [ { UNION | INTERSECT | EXCEPT | MINUS } [ ALL | DISTINCT ] SELECT ]
                [ ORDER BY expression [ ASC | DESC ] [ NULLS { FIRST | LAST } ] [, ...] ]
                [ LIMIT { count | ALL } ]
                [ OFFSET start [ ROW | ROWS ] ]
                [ FETCH { FIRST | NEXT } [ count ] { ROW | ROWS } ONLY ]
                [ FOR { UPDATE } [ OF table_name[, ...] ]
        [ NOWAIT | SKIP LOCKED | WAIT seconds] [...] ]

        from_item:
        {table_name  [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
        |( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
        |with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
        |from_item [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]}

        condition ::=
        simple_comparison
        | group_comparison
                | logical_condition
                | pattern_match_condition
                | null_condition
                | between_condition
                | exists_condition
                | in_condition
                | composite_condition

        simple_comparison ::=
                expression {= | != | ^= | <> | > | < | >= | <=} expression

        group_comparison ::=
                expression {= | != | ^= | <> | > | < | >= | <=} {ANY | SOME | ALL}
                ({expression_list | subquery})

        logical_condition ::=
                NOT condition
                | condition AND condition
                | condition OR condition

        pattern_match_condition ::=
                expression [NOT] LIKE expression [ESCAPE expression]
                | expression [NOT] ILIKE expression [ESCAPE expression]
                | expression [NOT] SIMILAR TO expression [ESCAPE expression]
                | REGEXP_LIKE(source_char, pattern [, match_param])

        null_condition ::=
                expression IS [NOT] NULL
        between_condition ::=
                expression [NOT] BETWEEN expression AND expression

        exists_condition ::=
                [NOT] EXISTS (subquery)

        in_condition ::=
                expression [NOT] IN ({expression_list | subquery})
                | (expression [, ...]) [NOT] IN (subquery)

        composite_condition ::=
                (condition)
                | NOT condition
                | condition {AND | OR} condition
        expression_list ::=
                expression [, expression ...]

on_conflict_clause:
        ON CONFLICT [ conflict_target ] conflict_action

conflict_target：
        ( { index_column_name | ( index_expression ) }
        [, ...] ) [ WHERE index_predicate ]
        ON CONSTRAINT constraint_name

conflict_action:
        DO NOTHING
        DO UPDATE SET { column_name = { expression | DEFAULT } |
                                        ( column_name [, ...] ) = [ ROW ] ( { expression | DEFAULT }
                                        [, ...] ) |
                                        ( column_name [, ...] ) = ( sub-SELECT )
                                  } [, ...]
                          [ WHERE condition ]

returning_clause:
        RETURNING * | output_expression [ [ AS ] output_name ] [, ...]

2.7.2.2.2. DELETE 语句

语法结构

[ WITH [ RECURSIVE ] with_query [, ...] ]
DELETE FROM [ ONLY ] table_name [ [ AS ] alias ]
        [ USING using_list ]
        [ WHERE condition ]
[ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

with_query:
        with_query_name [ ( column_name [, ...] ) ] AS
        ( {select | values | insert | update | delete} )

2.7.2.2.3. UPDATE 语句

语法结构

[ WITH [ RECURSIVE ] with_query [, ...] ]
UPDATE table_name [ [ AS ] alias ]
        SET {{ column_name = { expression | DEFAULT } |
                  ( column_name [, ...] ) = ( sub-SELECT )
                } [, ...] }
        [ FROM from_list ]
        [ WHERE condition]
        [ RETURNING * | output_expression [ [ AS ] output_name ] [, ...] ]

with_query：
        with_query_name [ ( column_name [, ...] ) ] AS
        ( {select | values | insert | update | delete} )

from_list：
        {table_name  [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
        |( select ) [ AS ] alias [ ( column_alias [, ...] ) ]
        |with_query_name [ [ AS ] alias [ ( column_alias [, ...] ) ] ]
        | from_list [ NATURAL ] join_type from_item [ ON join_condition | USING ( join_column [, ...] ) ]}

2.7.2.2.4. MERGE 语句

语法结构

MERGE INTO [ schema. ] { target_table }  [ [ AS ] target_table_alias ]
        USING { [ schema. ] { source_table }  [ [ AS ] source_table_alias ]
        ON ( condition_expression )
[ merge_update_clause ]
[ merge_insert_clause ]

merge_update_clause:
WHEN MATCHED THEN
UPDATE SET column = { expr }[, column = { expr } ]...
[ where_clause ]
[ delete_clause ]

delete_clause:
[DELETE where_clause]

merge_insert_clause:
WHEN NOT MATCHED THEN
INSERT [ ( column [, column ]...) ]
VALUES ({ expr }[, { expr]...)
[ where_clause ]

2.7.2.3. GOLDENDB_MYSQL 目标库

2.7.2.3.1. INSERT 语句

语法结构

INSERT [IGNORE]
        INTO tbl_name
        [(col_name [, col_name] ...)]
        { {VALUES } (value_list) [, (value_list)] ... }
        [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [IGNORE]
        INTO tbl_name
        [(col_name [, col_name] ...)]
        { SELECT ...
          | TABLE table_name
        }
[ON DUPLICATE KEY UPDATE assignment_list]

value:
        {expr }

value_list:
        value [, value] ...

assignment:
        col_name =
                  value
assignment_list:
assignment [, assignment] ...

2.7.2.3.2. INSERT ALL 语句

语法结构

INSERT ALL
        INTO table_name VALUES (固定值1, 固定值2, ...)
        INTO table_name VALUES (固定值3, 固定值4, ...)
…
SELECT … FROM DUAL;

2.7.2.3.3. REPLACE 语句

语法结构

语法1：
REPLACE
 INTO tbl_name
[(col_name [, col_name] ...)]
 { {VALUES } (value_list) [, (value_list)] ...

}


语法2：
REPLACE
 INTO tbl_name
[(col_name [, col_name] ...)]
 {SELECT ... | TABLE table_name}

value:
 {expr }
value_list:
 value [, value] …
assignment:
 col_name = value
assignment_list:
 assignment [, assignment] ...

2.7.2.3.4. DELETE 语句

语法结构

单表：

DELETE [IGNORE] FROM table_name [[AS] table_alias]
        [WHERE where_condition]
        [ORDER BY ...]
[LIMIT row_count]

注：只有ORDER BY,没有LIMIT的情况，不考虑

多表(两表)：

DELETE  [IGNORE]
        tbl_name [, tbl_name] ...
        FROM table_references
        [WHERE where_condition]

table_references: 涉及的表及其连接条件

2.7.2.3.5. UPDATE 语句

语法结构

UPDATE单表：

UPDATE [IGNORE] table_reference
        SET assignment_list
        [WHERE where_condition]
        [ORDER BY ...]
[LIMIT row_count]

UPDATE多表（支持两表）：

UPDATE  [IGNORE] table_references
        SET assignment_list
[WHERE where_condition]

table_references：指示要从中检索行的一个或多个表。
assignment_list:
        assignment [, assignment] ...

2.7.2.3.6. MERGE 语句

语法结构

MERGE INTO [ schema.] { table } [ t_alias ]
USING { [ schema. ] { table}
| select…
} [ t_alias ]
ON ( condition )
[ merge_update_clause ]
[ merge_insert_clause ];

merge_update_clause：
WHEN MATCHED THEN
UPDATE SET column = { expr }
[, column = { expr } ]...
[ where_clause ]

merge_insert_clause：
WHEN NOT MATCHED THEN
INSERT [ (column [, column ]...) ]
VALUES ({ expr [, expr ]... })
[ where_clause ]

注：支持更新+插入/只更新/只插入

2.7.2.4. OCEAN_BASE_ORACLE 目标库

2.7.2.4.1. INSERT 语句

语法结构

INSERT  { single_table_insert | multi_table_insert }
single_table_insert:
        { INTO insert_table_clause  '(' column_list ')' values_clause [{ RETURNING | RETURN } returning_exprs]
        }

column_list:
          column_definition_ref [, column_definition_ref...]

returning_exprs:
        projection_col_name [,projection_col_name ...]

values_clause:
        VALUES ({ expr } [, { expr } ]...  )

returning_exprs:
          projection [, projection...]

multi_table_insert:
        { ALL { insert_into_clause [ values_clause ] }
        | conditional_insert_clause
        } subquery

conditional_insert_clause:
        [ ALL | FIRST ]
        WHEN condition
        THEN insert_into_clause
        [ values_clause ]
        [ insert_into_clause [ values_clause ] ]...
        [ WHEN condition
                THEN insert_into_clause
                [ values_clause ]
                [ insert_into_clause [ values_clause ] ]...
        ]...
        [ ELSE insert_into_clause
                [ values_clause ]
                [ insert_into_clause [ values_clause ] ]...
        ]

subquery::
        SELECT  [ { { DISTINCT | UNIQUE } | ALL } ] select_list
        FROM { table_reference | join_clause | ( join_clause ) }
          [ , { table_reference | join_clause | (join_clause) } ]
          [ where_clause ]
          [ hierarchical_query_clause ]
          [ group_by_clause ]
        | subquery { UNION [ALL] | INTERSECT | MINUS } subquery [ { UNION [ALL] | INTERSECT | MINUS } subquery ]
        | ( subquery ) [ order_by_clause ] [ row_limiting_clause ]

hierarchical_query_clause：
        SELECT [level], column, expr... FROM table [WHERE condition] [ START WITH start_expression ]
        CONNECT BY [NOCYCLE] { PRIOR child_expr = parent_expr | parent_expr = PRIOR child_expr }
        [ GROUP BY ... ] [ HAVING ... ] [ ORDER BY ... ]

2.7.2.4.2. DELETE 语句

语法结构

DELETE  FROM table_factor
        [WHERE where_expression]
        [{ RETURNING | RETURE } returning_exprs]

table_factor:
        {table_name | '(' table_reference ')' }

returning_exprs:
        projection_col_name [,projection_col_name ...]

2.7.2.4.3. UPDATE 语句

语法结构

UPDATE  dml_table_clause
        SET update_asgn_list
        [ WHERE where_condition]
        [{ RETURNING | RETURN } returning_exprs]

dml_table_clause:
        dml_table_name opt_table_alias

update_asgn_list:
        column_name = expr [, expr...]

where_condition:
        expression

returning_exprs:
        projection_col_name [,projection_col_name ...]

2.7.2.4.4. MERGE 语句

语法结构

MERGE  INTO target_table_name [opt_alias]
        USING source_table_name [opt_alias]
        ON (expr)
        [merge_update_clause] [merge_insert_clause]

merge_update_clause:
        WHEN MATCHED THEN UPDATE SET update_asgn_list [WHERE expr] [DELETE WHERE expr]

merge_insert_clause:
        WHEN NOT MATCHED THEN INSERT opt_insert_columns VALUES '(' insert_vals ')' [WHERE expr]

2.7.2.5. TDSQL_MYSQL 目标库

2.7.2.5.1. INSERT 语句

语法结构

INSERT [IGNORE]
        INTO tbl_name
        [(col_name [, col_name] ...)]
        { {VALUES } (value_list) [, (value_list)] ... }
        [ON DUPLICATE KEY UPDATE assignment_list]

INSERT [IGNORE]
        INTO tbl_name
        [(col_name [, col_name] ...)]
        { SELECT ...
          | TABLE table_name
        }
[ON DUPLICATE KEY UPDATE assignment_list]

value:
        {expr }

value_list:
        value [, value] ...

assignment:
        col_name =
                  value
assignment_list:
assignment [, assignment] ...

注：ON DUPLICATE KEY UPDATE assignment_list中仅支持values。

2.7.2.5.2. REPLACE 语句

语法结构

REPLACE
 INTO tbl_name
[(col_name [, col_name] ...)]
 { {VALUES } (value_list) [, (value_list)] ...
}

REPLACE
 INTO tbl_name
[(col_name [, col_name] ...)]
 {SELECT ... | TABLE table_name}

value:
 {expr }
value_list:
 value [, value] …
assignment:
 col_name = value
assignment_list:
 assignment [, assignment] ...

2.7.2.5.3. DELETE 语句

语法结构

DELETE单表：

DELETE [IGNORE] FROM table_name [[AS] table_alias]
        [WHERE where_condition]
        [ORDER BY ...]
[LIMIT row_count]

注：只有ORDER BY,没有LIMIT的情况，不考虑

DELETE多表：

DELETE  [IGNORE]
        tbl_name [, tbl_name] ...
        FROM table_references
        [WHERE where_condition]

2.7.2.5.4. UPDATE 语句

语法结构

UPDATE单表：

UPDATE [IGNORE] table_reference
        SET assignment_list
        [WHERE where_condition]
        [ORDER BY ...]
[LIMIT row_count]

UPDATE多表：

UPDATE  [IGNORE] table_references
        SET assignment_list
[WHERE where_condition]