Oracle PL/SQL报错：procedure identifier must be declared 新手求助

Hey there! Let's work through your PL/SQL procedure issue step by step. That "procedure identifier must be declared" error usually pops up when your procedure fails to compile properly—meaning the database doesn't recognize it as a valid object yet. Since it works when you remove the invalid musmID exception handling, that's almost certainly where the problem lies.

Common Causes & Fixes #

First, let's break down why adding that exception might break compilation:

1. Missing custom exception declaration: If you tried to raise a custom exception for invalid museum IDs but forgot to declare it in the procedure's declaration section, the compiler will throw an error, preventing the procedure from being created.
2. Incorrect handling of NO_DATA_FOUND: When your query for the museum returns no results, Oracle triggers the built-in NO_DATA_FOUND exception. If you didn't handle this or map it to your custom exception properly, it can cause compilation issues.
3. Syntax errors in the exception block: Typos, misreferenced variables, or invalid logic in the exception handling code can also derail compilation.

Example of a Working Procedure #

Here's a corrected version of your procedure that includes both exception checks, with explanations:

CREATE OR REPLACE PROCEDURE calculate_art_avg_price(
    p_musmID IN NUMBER,
    p_increase_pct IN NUMBER
) IS
    v_museum_name VARCHAR2(100);
    v_old_avg_price NUMBER(10,2);
    v_new_avg_price NUMBER(10,2);
    -- Declare custom exceptions first
    INVALID_MUSEUM_ID EXCEPTION;
    EXCESSIVE_PERCENTAGE EXCEPTION;
BEGIN
    -- First check if percentage is too high (adjust threshold as needed)
    IF p_increase_pct > 100 THEN
        RAISE EXCESSIVE_PERCENTAGE;
    END IF;

    -- Fetch museum name and original average price
    SELECT m.museum_name, AVG(a.price)
    INTO v_museum_name, v_old_avg_price
    FROM museums m
    JOIN artworks a ON m.musmID = a.musmID
    WHERE m.musmID = p_musmID
    GROUP BY m.museum_name;

    -- Calculate new average price
    v_new_avg_price := v_old_avg_price * (1 + p_increase_pct / 100);

    -- Output results
    DBMS_OUTPUT.PUT_LINE('博物馆名称: ' || v_museum_name);
    DBMS_OUTPUT.PUT_LINE('原平均价格: ' || v_old_avg_price);
    DBMS_OUTPUT.PUT_LINE('新平均价格: ' || v_new_avg_price);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        -- Map Oracle's built-in no-data exception to our custom invalid ID exception
        RAISE INVALID_MUSEUM_ID;
    WHEN INVALID_MUSEUM_ID THEN
        DBMS_OUTPUT.PUT_LINE('错误: 无效的博物馆ID - ' || p_musmID);
    WHEN EXCESSIVE_PERCENTAGE THEN
        DBMS_OUTPUT.PUT_LINE('错误: 涨幅百分比过高，当前输入为 ' || p_increase_pct || '%');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('发生未知错误: ' || SQLERRM);
END;
/

Debugging Tips #

If you still run into issues:

• Check compilation errors: In SQL*Plus, run SHOW ERRORS PROCEDURE calculate_art_avg_price; right after trying to create the procedure. This will show you exactly where the compiler hit a snag.
• Verify object names: Double-check that your table names (museums, artworks) and column names (musmID, museum_name, price) match what's in your database—typos here can also cause silent failures.
• Test incrementally: Build the procedure piece by piece: first get the average price working, then add the percentage calculation, then add one exception at a time. This makes it easier to pinpoint where things go wrong.

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