PL/SQL存储过程编译报错求助：按课程代码查询讲师与课程信息

Hey there! Let's walk through fixing your PL/SQL procedure step by step. I spotted several syntax and logical issues that are causing those compilation errors—let's break them down and fix them one by one.

First, let's list the problems in your original code: #

• Missing commas/semicolons in variable declarations: You declared FNAME VARCHAR(256) followed immediately by LNAME VARCHAR(256) without a comma or semicolon. PL/SQL requires clear separation between variables.
• Wrong assignment operator: PL/SQL uses := for assigning values, not =. Lines like FNAME=FIRST_NAME are invalid here.
• No FETCH to get cursor data: Your loop checks if the cursor has no data, but never actually retrieves rows from the cursor. Without a FETCH, you'll never access the query results, and the loop will either run infinitely or exit right away.
• Misspelled DBMS_OUTPUT: You wrote DBMS.OUTPUT (with a dot) instead of the correct DBMS_OUTPUT.
• Parameter name conflict: Your CODE parameter shares the same name as the TEACHES.CODE column, which confuses the database about which one to reference.
• Unnecessary GROUP BY: You’re using GROUP BY without any aggregate functions (like COUNT() or SUM()), which is redundant here and could distort your results.

Here's the corrected procedure: #

CREATE OR REPLACE PROCEDURE WHO(p_code CHAR) IS
    fname VARCHAR(256);
    lname VARCHAR(256);
    title VARCHAR(256);
    CURSOR c1 IS
        SELECT a.first_name, a.last_name, s.name
        FROM academic a
        INNER JOIN teaches t ON a.staff# = t.lecturer
        INNER JOIN subject s ON t.code = s.code
        WHERE t.year < 2016
          AND t.code = p_code; -- Use prefixed parameter to avoid column name conflict
BEGIN
    OPEN c1;
    LOOP
        FETCH c1 INTO fname, lname, title; -- Retrieve cursor row into variables
        EXIT WHEN c1%NOTFOUND; -- Exit loop once no more data exists
        
        DBMS_OUTPUT.PUT_LINE(fname || ' ' || lname);
        DBMS_OUTPUT.PUT_LINE(title);
        DBMS_OUTPUT.PUT_LINE('-----------------------'); -- Optional: Add a separator for readability
    END LOOP;
    CLOSE c1;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No lecturers found for course code: ' || p_code);
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('An error occurred: ' || SQLERRM);
END WHO;
/

Key fixes explained: #

1. Clean variable declarations: Added semicolons after each variable and commas between them to follow PL/SQL syntax rules.
2. Parameter renaming: Changed CODE to p_code to avoid conflicting with the TEACHES.CODE column—this makes your query unambiguous.
3. Cursor FETCH: Added FETCH c1 INTO fname, lname, title; to pull each row from the cursor into your variables. The EXIT WHEN is placed right after the fetch to ensure we exit as soon as there's no more data to process.
4. Fixed DBMS_OUTPUT: Corrected the spelling to the proper DBMS_OUTPUT package name (no dot).
5. Removed GROUP BY: Since you aren't using aggregate functions, this clause was unnecessary and could have grouped rows incorrectly.
6. Added exception handling: Added basic error handling to catch cases where no data is found or unexpected errors occur—this makes your procedure more robust and user-friendly.

How to run the procedure: #

First, enable output in your SQL tool (like SQL Developer or SQL*Plus), then execute the procedure with your course code:

SET SERVEROUTPUT ON;
EXECUTE WHO('CS101'); -- Replace 'CS101' with your actual course code

Or using an anonymous block:

SET SERVEROUTPUT ON;
BEGIN
    WHO('CS101');
END;
/

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