oracledb-python中执行Upsert时如何复用绑定变量？

问题原因 #

你遇到的错误是因为位置绑定变量（:1、:2这类）是按出现次数计数的。你的SQL语句中，:1到:4各出现2次，:5出现3次，总共需要11个绑定值，但每个数据元组只提供了5个，因此触发参数数量不匹配的错误。

解决方案 #

方案1：使用命名绑定变量 #

把位置变量替换为命名变量，同一个变量无论使用多少次，只需要传递一次值即可。

修改后的SQL语句：

BEGIN
    UPDATE Competition 
    SET abbreviation = :abbreviation, 
        descriptions = :descriptions, 
        levels = :levels, 
        source = :source, 
        competitionId = :competitionId
    WHERE competitionId = :competitionId;
    
    IF sql%notfound THEN
        INSERT INTO Competition (abbreviation, descriptions, levels, source, competitionId)
        VALUES (:abbreviation, :descriptions, :levels, :source, :competitionId);
    END IF;
END;

对应的Python代码需要将parsed_data改为字典列表（每个字典的键对应SQL中的命名变量）：

# 示例：将原元组数据转换为字典格式
parsed_data = [
    {
        "abbreviation": "CBA",
        "descriptions": "中国男子篮球职业联赛",
        "levels": 1,
        "source": "官方",
        "competitionId": 1001
    },
    # 更多数据项...
]

cursor.executemany(upsert_string, parsed_data)

方案2：使用Oracle MERGE语句（推荐） #

MERGE是Oracle官方支持的标准Upsert语法，既能避免绑定变量重复计数问题，性能也更优。

命名绑定版本

MERGE INTO Competition t
USING (
    SELECT :abbreviation AS abbreviation,
           :descriptions AS descriptions,
           :levels AS levels,
           :source AS source,
           :competitionId AS competitionId
    FROM DUAL
) s
ON (t.competitionId = s.competitionId)
WHEN MATCHED THEN
    UPDATE SET t.abbreviation = s.abbreviation,
               t.descriptions = s.descriptions,
               t.levels = s.levels,
               t.source = s.source,
               t.competitionId = s.competitionId
WHEN NOT MATCHED THEN
    INSERT (abbreviation, descriptions, levels, source, competitionId)
    VALUES (s.abbreviation, s.descriptions, s.levels, s.source, s.competitionId)

位置绑定版本（兼容原元组列表）

如果想继续使用原位置绑定的方式，只需替换变量格式：

MERGE INTO Competition t
USING (
    SELECT :1 AS abbreviation, :2 AS descriptions, :3 AS levels, :4 AS source, :5 AS competitionId
    FROM DUAL
) s
ON (t.competitionId = s.competitionId)
WHEN MATCHED THEN
    UPDATE SET t.abbreviation = s.abbreviation,
               t.descriptions = s.descriptions,
               t.levels = s.levels,
               t.source = s.source,
               t.competitionId = s.competitionId
WHEN NOT MATCHED THEN
    INSERT (abbreviation, descriptions, levels, source, competitionId)
    VALUES (s.abbreviation, s.descriptions, s.levels, s.source, s.competitionId)

此时Python代码无需修改，直接用原元组列表parsed_data执行executemany即可。

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