Oracle SQL MERGE语句使用多COLUMN_VALUE报错的解决方法咨询

在Oracle SQL编写MERGE语句的存储过程中，同时引入两个自定义数组参数时出现Missing right parenthesis编译错误，单独使用一个数组时可正常执行编译。

现有代码 #

存储过程代码 #

procedure proc_1
(
    in_param_1 IN VARCHAR2,
    in_param_array_1 IN CUSTOM_ARRAY_TYPE,
    in_param_array_2 IN CUSTOM_ARRAY_TYPE
)
as
    PRAGMA AUTONOMOUS_TRANSCATION

    BEGIN

    MERGE INTO table T
    USING (SELECT in_param_1 param_1, COLUMN_VALUE array_col1 FROM TABLE(in_param_array_1), COLUMN_VALUE array_col2 FROM TABLE (in_param_array_2)) S
    ON (T.col1 = S.param_1)
    WHEN MATCHED THEN
    ...
    WHEN NOT MATCHED THEN
    ...

自定义类型定义 #

TYPE CUSTOM_ARRAY_TYPE
AS
TABLE OF VARCHAR2(4);

错误触发场景 #

当MERGE的USING子查询中同时引用两个数组的COLUMN_VALUE时，编译报错：Missing right parenthesis；仅使用单个数组时可正常编译：

USING (SELECT in_param_1 param_1, COLUMN_VALUE array_col1 FROM TABLE(in_param_array_1)) S

错误根源是USING子查询中对两个TABLE()集合的引用方式不符合Oracle语法，Oracle需要明确的集合关联逻辑，以下是两种常见场景的正确写法：

场景1：按数组索引位置配对元素 #

若需将两个数组相同索引位置的元素配对（如in_param_array_1(1)与in_param_array_2(1)组合），需通过ROW_NUMBER()标记元素位置，再基于位置关联：

procedure proc_1
(
    in_param_1 IN VARCHAR2,
    in_param_array_1 IN CUSTOM_ARRAY_TYPE,
    in_param_array_2 IN CUSTOM_ARRAY_TYPE
)
as
    PRAGMA AUTONOMOUS_TRANSACTION -- 修正拼写错误
BEGIN
    MERGE INTO table T
    USING (
        SELECT 
            in_param_1 param_1,
            a.array_col1,
            b.array_col2
        FROM (
            SELECT COLUMN_VALUE array_col1, ROW_NUMBER() OVER(ORDER BY 1) rn 
            FROM TABLE(in_param_array_1)
        ) a
        JOIN (
            SELECT COLUMN_VALUE array_col2, ROW_NUMBER() OVER(ORDER BY 1) rn 
            FROM TABLE(in_param_array_2)
        ) b ON a.rn = b.rn
    ) S
    ON (T.col1 = S.param_1)
    WHEN MATCHED THEN
        UPDATE SET T.col2 = S.array_col1, T.col3 = S.array_col2 -- 示例更新逻辑
    WHEN NOT MATCHED THEN
        INSERT (col1, col2, col3) VALUES (S.param_1, S.array_col1, S.array_col2); -- 示例插入逻辑
END;

场景2：两个数组元素做笛卡尔积 #

若需两个数组的所有元素两两组合，可使用CROSS JOIN明确声明交叉连接：

procedure proc_1
(
    in_param_1 IN VARCHAR2,
    in_param_array_1 IN CUSTOM_ARRAY_TYPE,
    in_param_array_2 IN CUSTOM_ARRAY_TYPE
)
as
    PRAGMA AUTONOMOUS_TRANSACTION -- 修正拼写错误
BEGIN
    MERGE INTO table T
    USING (
        SELECT 
            in_param_1 param_1,
            a.COLUMN_VALUE array_col1,
            b.COLUMN_VALUE array_col2
        FROM TABLE(in_param_array_1) a
        CROSS JOIN TABLE(in_param_array_2) b
    ) S
    ON (T.col1 = S.param_1)
    WHEN MATCHED THEN
        UPDATE SET T.col2 = S.array_col1, T.col3 = S.array_col2 -- 示例更新逻辑
    WHEN NOT MATCHED THEN
        INSERT (col1, col2, col3) VALUES (S.param_1, S.array_col1, S.array_col2); -- 示例插入逻辑
END;

额外注意：原代码中PRAGMA AUTONOMOUS_TRANSCATION存在拼写错误，正确应为PRAGMA AUTONOMOUS_TRANSACTION，该错误也会导致编译失败，需同步修正。

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