注册

[merge into]语句如何获取所执行的动作,类似oracle的output子句

DM_082903 2023/06/14 774 3

为提高效率,提问时请提供以下信息,问题描述清晰可优先响应。
【DM版本】: dm8
【操作系统】:linux
【CPU】: x84
【问题描述】*:
在执行merge into 语句, 希望能得到具体执行的update动作还是insert动作;目前查看文档所知,merge语句并不支持返回值

CREATE TABLE IF NOT EXISTS person ( id bigint PRIMARY KEY AUTO_INCREMENT, name character varying(50 char) default '', age bigint default 0, sex char(10) default 'm', active tinyint default 0, is_delete tinyint default 0, constraint idx_test_name unique(name, sex) ); MERGE INTO "person" USING (SELECT 'test_1',21,'female',true,false FROM DUAL UNION ALL SELECT 'test_2',22,'female',true,false FROM DUAL UNION ALL SELECT 'test_4',23,'female',true,false FROM DUAL UNION ALL SELECT 'test_3',23,'female',true,false FROM DUAL ) AS "excluded" ("name","age","sex","active","is_delete" ) ON "person"."name" = "excluded"."name" AND "person"."sex" = "excluded"."sex" WHEN MATCHED THEN UPDATE SET "age"="excluded"."age","active"="excluded"."active" WHEN NOT MATCHED THEN INSERT ("name","age","sex","active","is_delete") VALUES ("excluded"."name","excluded"."age","excluded"."sex","excluded"."active","excluded"."is_delete")

Oracle数据库中merge into 语句可支持 output子句,试了dm8发现不支持参考,如下

MERGE INTO "person" USING (SELECT 'test_1',21,'female',true,false FROM DUAL UNION ALL SELECT 'test_2',22,'female',true,false FROM DUAL UNION ALL SELECT 'test_4',23,'female',true,false FROM DUAL UNION ALL SELECT 'test_3',23,'female',true,false FROM DUAL ) AS "excluded" ("name","age","sex","active","is_delete" ) ON "person"."name" = "excluded"."name" AND "person"."sex" = "excluded"."sex" WHEN MATCHED THEN UPDATE SET "age"="excluded"."age","active"="excluded"."active" WHEN NOT MATCHED THEN INSERT ("name","age","sex","active","is_delete") VALUES ("excluded"."name","excluded"."age","excluded"."sex","excluded"."active","excluded"."is_delete") OUTPUT $ACTION AS action

或者说数据库行记录中是否有对应的伪列(例如postgres的隐藏列xmax),可基于它实现所需的诉求?

回答 0
暂无回答
扫一扫
联系客服