Oracle中MERGE语句遇多行匹配报错ORA-30926的问题排查

将搜索模式存储在数据库的patterns表中，比如name_pattern列存储'Basic%'这类字符串，用来实现基于模式的动态搜索。最初误以为Oracle对LIKE子句中的%做了转义处理，实际问题出在MERGE操作要求的稳定行集上：

• 当patterns表中的模式为'Basic%'和'%thing'时，products表中id为2的产品名称'Basic thing'会同时匹配这两个模式，导致MERGE执行时抛出错误ORA-30926: unable to get a stable set of rows in the source tables。
• 若patterns表中的模式为'Basic%'和'Super%'，每个产品只会匹配一个模式，MERGE就能正常执行。

示例代码 #

CREATE TABLE patterns(code CHAR(1),name_pattern VARCHAR2(20));
INSERT INTO patterns(code,name_pattern) VALUES('B','Basic%');
INSERT INTO patterns(code,name_pattern) VALUES('T','%thing');

CREATE TABLE products (id NUMBER,name VARCHAR2(20),code CHAR(1));
INSERT INTO products(id,name,code) VALUES(1,'Basic instinct',NULL);
INSERT INTO products(id,name,code) VALUES(2,'Basic thing',NULL);
INSERT INTO products(id,name,code) VALUES(3,'Super thing',NULL);
INSERT INTO products(id,name,code) VALUES(4,'Hyper instinct',NULL);

MERGE INTO products p USING
(
   SELECT code,name_pattern FROM patterns
) s
ON (p.name LIKE s.name_pattern)
WHEN MATCHED THEN UPDATE SET p.code=s.code;

SELECT * FROM products;

内容的提问来源于stack exchange，提问作者sbrbot