Oracle执行存储过程dw_dart.sample_count报PLS-00201标识符未声明错误

根因 #

报错的核心原因是你认为已经创建完成的存储过程实际上编译失败，数据库中不存在有效的DW_DART.SAMPLE_COUNT对象。多数Oracle客户端不会在执行CREATE语句时主动弹出编译错误提示，很容易让人误以为过程创建成功。

原代码存在3个直接导致编译失败/逻辑隐患的问题：

• PL/SQL语法不允许在存储过程内直接写裸SELECT查询，必须将查询结果存入变量、绑定输出游标，否则会触发PLS-00428: SELECT语句缺少INTO子句错误，直接导致过程创建失败。
• 末尾SELECT * FROM final_query2语句后缺少分号，不符合PL/SQL语法规范。
• 周一判断逻辑硬匹配带多个空格的'MONDAY '字符串，yesterday子句冗余查询了未使用的category字段，日期判断用to_char做字符串比较存在隐式转换的性能和准确性问题。

修复方案 #

如果需要保留存储过程、调用时直接返回查询结果，使用带SYS_REFCURSOR输出参数的版本即可，修正后可正常编译执行：

-- 先删除无效的旧对象（如果存在）
DROP PROCEDURE dw_dart.sample_count;
/

-- 创建可正常编译的存储过程
CREATE OR REPLACE PROCEDURE dw_dart.sample_count(
    p_result OUT SYS_REFCURSOR
)
AS  
BEGIN  
    OPEN p_result FOR
    WITH today AS
    ( 
        SELECT DISTINCT
            source_desc, 
            src_dim_id, 
            src_update_datetime, 
            Dw_insert_datetime, 
            src_insert_datetime,
            current_date - 1 AS P_datetime, 
            current_date AS T_datetime ,
            rec_count 
        FROM dw_dart.tab_total_last_updated
        WHERE TRUNC(Dw_insert_datetime) = TRUNC(current_date)
     ),
     yesterday AS
     (
         SELECT
             src_dim_id, 
             source_desc, 
             src_update_datetime, 
             Dw_insert_datetime,
             src_insert_datetime,
             current_date AS T_datetime ,
             rec_count 
         FROM dw_dart.tab_total_last_updated
         WHERE 
             TRUNC(Dw_insert_datetime) =
                 CASE
                     WHEN TRIM(TO_CHAR(CURRENT_DATE, 'DAY', 'NLS_DATE_LANGUAGE=ENGLISH')) = 'MONDAY'
                         THEN TRUNC(CURRENT_DATE - 3)
                         ELSE TRUNC(CURRENT_DATE - 1)
                 END 
    ),
    diff AS
    (
        SELECT
            t.src_dim_id, 
            t.source_desc, 
            t.src_update_datetime, 
            t.Dw_insert_datetime,
            t.src_insert_datetime,
            t.P_datetime, 
            t.T_datetime ,
            t.rec_count AS t_rec_count, 
            y.Dw_insert_datetime AS Y_DW_insert_datetime, 
            y.rec_count AS p_rec_count 
        FROM today t 
        LEFT JOIN yesterday y ON y.source_desc = t.source_desc 
    ),       
    final_query1 AS
    (
        SELECT
            src_dim_id, 
            source_desc, 
            src_update_datetime, 
            Dw_insert_datetime, 
            src_insert_datetime,
            P_datetime, 
            T_datetime,
            p_rec_count,
            t_rec_count,
            t_rec_count - p_rec_count AS difference 
        FROM diff
    ),
    final_query2 AS
    (
        SELECT
            src_dim_id, 
            'Daily' as frequency, 
            source_desc, 
            src_update_datetime, 
            Dw_insert_datetime, 
            src_insert_datetime,
            P_datetime, 
            T_datetime,
            p_rec_count,
            t_rec_count,
            difference, 
            CASE 
                WHEN difference <> 0 THEN 'pass' ELSE 'fail' 
            END AS status 
        FROM final_query1
    )
    SELECT * FROM final_query2;
END;
/

调用方法 #

修复后不能直接用原EXECUTE dw_dart.sample_count;语句执行，需要绑定游标变量接收输出结果，在SQL Developer、PL/SQL Developer等客户端中执行以下语句即可拿到查询结果：

VAR res REFCURSOR;
EXEC dw_dart.sample_count(:res);
PRINT res;

验证步骤 #

创建完成后执行以下语句确认存储过程状态有效，再执行调用即可避免标识符未声明的错误：

-- 确认对象状态为VALID即为编译成功
SELECT owner, object_name, status 
FROM all_objects 
WHERE owner = 'DW_DART' 
  AND object_name = 'SAMPLE_COUNT'
  AND object_type = 'PROCEDURE';

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