Oracle 21c Express中MERGE存储过程无法插入数据问题

核心错误：USING子句逻辑错误 #

你的存储过程无法插入数据的根本原因是MERGE语句的USING子句逻辑错误。原代码中USING子句从READINGS表查询数据，相当于把目标表和自身做关联，而非用传入的存储过程参数去匹配目标表。这种逻辑下，只有当目标表中已存在与传入参数完全匹配的行时才会触发操作，新数据根本没被纳入MERGE的数据源，自然无法插入。

参数与别名的使用规则 #

• 存储过程参数：可直接引用参数名（如STATION_ID），也可用存储过程名.参数名（如UPSERTREADINGS.STATION_ID）限定。但如果参数名与表列名完全相同，Oracle会优先解析为表列名，易引发逻辑错误，建议给参数加前缀（如p_）区分。
• USING子句的别名（e）：e是USING子句中数据集的别名，只有当USING的数据源是你要用来匹配/插入的数据集时，才需要用e.列名引用该数据集中的字段。在你的场景中，USING应是包含传入参数的单行数据集，此时e代表这行待处理的数据。

修正后的存储过程代码 #

create or replace PROCEDURE UPSERTREADINGS 
(
   p_STATION_ID NVARCHAR2
 , p_READING_DATE TIMESTAMP 
 , p_READING_TYPE NVARCHAR2 
 , p_RESULT_VALUE NUMBER 
 , p_RESULT_TEXT NVARCHAR2 
 , p_RESULT_UNIT NVARCHAR2 
 , p_READING_STATUS NVARCHAR2 
 , p_ACTIVITY_TYPE NVARCHAR2 
 , p_COMMENTS NVARCHAR2 
 , p_SOURCE_FILE_ID NUMBER
) AS
BEGIN
    MERGE INTO READINGS r
    -- 用DUAL构造包含传入参数的单行数据集作为匹配源
    USING (SELECT p_STATION_ID AS STATION_ID, 
                  p_READING_DATE AS READING_DATE, 
                  p_READING_TYPE AS READING_TYPE,
                  p_RESULT_VALUE AS RESULT_VALUE,
                  p_RESULT_TEXT AS RESULT_TEXT,
                  p_RESULT_UNIT AS RESULT_UNIT,
                  p_READING_STATUS AS READING_STATUS,
                  p_ACTIVITY_TYPE AS ACTIVITY_TYPE,
                  p_COMMENTS AS COMMENTS,
                  p_SOURCE_FILE_ID AS SOURCE_FILE_ID
           FROM DUAL) e 
    ON ( r.STATION_ID = e.STATION_ID 
         AND r.READING_DATE = e.READING_DATE 
         AND r.READING_TYPE = e.READING_TYPE )
    WHEN MATCHED THEN 
        UPDATE SET 
            r.RESULT_VALUE = e.RESULT_VALUE,
            r.RESULT_TEXT = e.RESULT_TEXT,
            r.RESULT_UNIT = e.RESULT_UNIT,
            r.READING_STATUS = e.READING_STATUS,
            r.ACTIVITY_TYPE = e.ACTIVITY_TYPE,
            r.COMMENTS = e.COMMENTS,
            r.SOURCE_FILE_ID = e.SOURCE_FILE_ID
    WHEN NOT MATCHED THEN 
        INSERT (STATION_ID, READING_DATE, READING_TYPE, RESULT_VALUE, RESULT_TEXT, RESULT_UNIT, READING_STATUS, ACTIVITY_TYPE, COMMENTS, SOURCE_FILE_ID) 
        VALUES (e.STATION_ID, e.READING_DATE, e.READING_TYPE, e.RESULT_VALUE, e.RESULT_TEXT, 
                e.RESULT_UNIT, e.READING_STATUS, e.ACTIVITY_TYPE, e.COMMENTS, e.SOURCE_FILE_ID);
    COMMIT;
END UPSERTREADINGS;

关键修改说明 #

1. 给所有参数添加p_前缀，避免与表列名冲突，提升代码可读性。
2. USING子句改为从DUAL表查询，构造包含传入参数的单行数据集，确保待处理的新数据被纳入MERGE逻辑。
3. UPDATE和INSERT语句统一使用e.列名引用USING数据集中的字段，逻辑更清晰，也避免了参数与列名的歧义问题。

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