H2数据库中的Oracle MERGE语句 [英] Oracle MERGE statement in H2 database

问题描述

我们开始使用内存数据库中的H2进行自动化测试.我们使用Oracle进行生产和销售.开发环境.因此，我们的想法是在H2测试数据库中复制表结构，就像在我们的Oracle开发数据库中一样.

We started to use the H2 in memory database for automated testing. We use Oracle for our production & dev environments. So the idea is to duplicate the table structure in H2 test-database as it is in our Oracle dev-database.

Oracle SQL语句包括MERGE语句，并对表名和查询中的USING使用别名.

The Oracle SQL statements include MERGE statements and use alias for table names and USING in the query.

我如何动态修改此查询以使其与H2兼容，而又不会更改开发环境中的现有查询?

How can I modify this query dynamically to be compatible with H2 in a way that it does not alter the existing query in the dev environment?

要与H2兼容的Oracle SQL示例

Example of the Oracle SQL, to be made compatible with H2,

MERGE INTO TABLE T1
USING ( SELECT ....
        ...........
        FROM DUAL) T2

(T1和T2是表的别名)

(T1 & T2 are the alias for the table)

推荐答案

H2中的 MERGE语句的语法稍有不同，更简单:

The MERGE statement in H2 has a slightly different, simpler syntax:

MERGE INTO TEST(ID, NAME) KEY(ID)
SELECT 1, 'Hello' FROM DUAL

我猜您将不得不编写两个语句，一个用于H2，一个用于Oracle. SELECT部分将是相同的. Oracle MERGE语句会更长，我相信将是:

I guess you would have to write two statements, one for H2, and one for Oracle. The SELECT part would be the same however. The Oracle MERGE statement would be longer, I believe it would be:

MERGE INTO TEST T
USING (SELECT 1 ID, 'Hello' NAME FROM DUAL) D
ON (T.ID = D.ID)
WHEN MATCHED THEN 
UPDATE SET T.NAME = D.NAME
WHEN NOT MATCHED THEN 
INSERT (B.ID, B.NAME) VALUES (D.ID, D.NAME);

这篇关于H2数据库中的Oracle MERGE语句的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！