PL/SQL游标编译报错：UNION ALL前语句未终止，是否漏声明？

错误原因 #

编译错误的核心原因是UNION ALL前后的查询语句不能各自包含ORDER BY子句。原代码中第一个SELECT语句末尾的order by t.attribute_id违反了PL/SQL的语法规则，导致UNION ALL前的SQL命令无法正确终止，进而触发“第2行SELECT语句被忽略”的报错。

修复后的代码 #

移除第一个SELECT后的ORDER BY子句，若需要对合并后的完整结果集排序，仅在整个游标查询的末尾保留一个ORDER BY即可（需确保排序字段在两个子查询中兼容）：

CURSOR recipe_attributes_cur(p_recipe_id IN recipe_data.recipe_id%TYPE) IS
    SELECT t.attribute_name, t.attribute_value,
         CASE
             WHEN ra.enum_list IS NOT NULL 
             THEN (SELECT choice_str
                     FROM recipe_enums_v
                    WHERE enum_list = ra.enum_list)
                     -- AND choice_id = t.attribute_value)
             ELSE TO_CHAR(NULL)
           END AS enum_value,
           ra.datatype_id
    FROM table(acls_recipe.get_sic_attributes_value(p_recipe_id)) t, recipe_attributes ra
    where ra.attribute_name = t.attribute_name
   UNION ALL
    SELECT ra.attribute_name, rd.recipe_value_num AS attribute_value,
           CASE
             WHEN ra.enum_list IS NOT NULL
             THEN (SELECT choice_str
                     FROM recipe_enums_v
                    WHERE enum_list = ra.enum_list
                      AND choice_id = rd.recipe_value_num)
             ELSE TO_CHAR (NULL)
           END AS enum_value,
           ra.datatype_id
      FROM recipe_data rd, recipe r, recipe_attributes ra
     WHERE rd.recipe_id = r.recipe_id
       AND rd.attribute_id =ra.attribute_id
       AND r.recipe_id = p_recipe_id
       AND rd.period_end is null
       AND ( ra.attr_group IN ('REQUIRED','ABORT','SELECTION') OR
             (ra.attr_group IN ('LIMIT','INTERNAL') AND
              rd.is_default = c_false
             )
           )
       -- exclude disabled parameter...
       AND ra.flavor NOT IN ('DISABLED', 'HIST', 'LOCKED', 'VX_ONLY')
       --exclude default value 'not set'
       AND rd.recipe_value_num NOT IN (c_not_set, c_set_to_auto)
       --exclude purge recipe attributes for implant recipes
       AND NOT (ra.attribute_name = 'PURGE_TYPE' AND
                r.recipe_type_id = c_rt_implant)
    ORDER BY attribute_id; -- 统一对合并后的结果集排序

补充说明 #

如果确实需要对第一个查询的结果单独排序后再合并，可将第一个查询用子查询包裹并添加ORDER BY（通常需配合ROWNUM，若仅排序不限制行数也可直接使用），示例如下：

SELECT * FROM (
    SELECT t.attribute_name, t.attribute_value,
         CASE
             WHEN ra.enum_list IS NOT NULL 
             THEN (SELECT choice_str
                     FROM recipe_enums_v
                    WHERE enum_list = ra.enum_list)
             ELSE TO_CHAR(NULL)
           END AS enum_value,
           ra.datatype_id,
           t.attribute_id
    FROM table(acls_recipe.get_sic_attributes_value(p_recipe_id)) t, recipe_attributes ra
    where ra.attribute_name = t.attribute_name
    ORDER BY t.attribute_id
)
UNION ALL
-- 第二个查询内容
ORDER BY attribute_id;

但你的场景中无需单独排序，直接移除第一个ORDER BY即可解决编译错误。

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