PL/SQL : How to display subject title and lecturer names in a procedure?

Question

My procedure will take in an subject code as a parameter and the proceed to display the subject title and lecturers’ names who have taught the subject before 2016. My procedure should also prompt input a subject code and display the results. I tried my code as following without putting it into a procedure and it returns the correct results but when I include my code as part of a PL/SQL procedure, I get an error that says

Warning: Procedure created with compilation errors.

I am quite new to PL/SQL so any advices would be helpful! The procedure code that I had been trying for hours as follow:

    CREATE OR REPLACE PROCEDURE WHO(CODE CHAR)
IS
FNAME VARCHAR(256)
LNAME VARCHAR(256)
TITLE VARCHAR(256)

CURSOR C1
IS
    SELECT FIRST_NAME, LAST_NAME, SUBJECT.NAME
    FROM ACADEMIC INNER JOIN TEACHES
    ON STAFF# = LECTURER
    INNER JOIN SUBJECT
    ON TEACHES.CODE=SUBJECT.CODE
    WHERE YEAR<2016 AND TEACHES.CODE=CODE
    GROUP BY FIRST_NAME, LAST_NAME, SUBJECT.NAME;
BEGIN
    OPEN C1;
    LOOP
    IF C1%NOTFOUND THEN EXIT;
    END IF;
    FNAME=FIRST_NAME;
    LNAME=LAST_NAME;
    DBMS.OUTPUT.PUT_LINE(FNAME||' '||LNAME);
    TITLE=SUBJECT.NAME;
    DBMS.OUTPUT.PUT_LINE(TITLE);
    END LOOP;
    CLOSE C1;
END WHO;
/

Answer 1

Tools like SQL Developer, PL/SQL Developer etc will highlight compilation errors, or on the SQL*Plus command line you can enter

show errors

immediately after compiling, or else

show errors procedure who

Or you can query the user_errors table. Whatever tool you are using, you need to be able to work with compilation errors.

Start by tidying up the code so it is easier to read and follow and generally looks more professional. Also although they are not actually causing any errors, you should really change those char and varchar types to the standard varchar2 (ideally everything should be anchored as academic.first_name%type etc, but one thing at a time).

Here is the fixed version:

create or replace procedure who
    ( code subject.code%type )  -- CHAR is a disastrous datatype you should never need
is
    fname varchar2(50);  -- VARCHAR2 is the standard string type, VARCHAR is reserved
    lname varchar2(50);  -- Also PL/SQL requires terminating semicolons here
    title varchar2(256);

    cursor c1 is
        select first_name
             , last_name
             , subject.name
        from   academic
               join teaches
                    on  staff# = lecturer
               join subject
                    on  teaches.code = subject.code
        where  year < 2016
        and    teaches.code = who.code  -- "code" on its own is ambiguous
        group by first_name, last_name, subject.name;

begin
    open c1;
    loop
        fetch c1 into fname, lname, title;  -- Added 'fetch into'

        if c1%notfound then
            exit;
        end if;

        dbms_output.put_line(fname || ' ' || lname);  -- It's dbms_output, with a '_'
        dbms_output.put_line(title);
    end loop;

    close c1;
end who;

This compiles. But you can still simplify this by using a Cursor FOR Loop instead of the elaborate, verbose way:

create or replace procedure who
    ( code subject.code%type )
is
begin
    for r in (
        select first_name
             , last_name
             , subject.name as title
        from   academic
               join teaches
                    on  staff# = lecturer
               join subject
                    on  teaches.code = subject.code
        where  year < 2016
        and    teaches.code = who.code
        group by first_name, last_name, subject.name
    )
    loop
        dbms_output.put_line(r.first_name || ' ' || r.last_name);
        dbms_output.put_line(r.title);
    end loop;
end who;

This implicitly declares a cursor and a record named r with the three fields based on the cursor, and it handles opening, fetching and closing for you.

I don't know your tables, but I am guessing they are something like this:

create table subject
( code        varchar2(20) primary key
, name        varchar2(30) not null );

create table academic
( staff#      integer primary key
, first_name  varchar2(20) not null
, last_name   varchar2(20) not null );

create table teaches
( lecturer    references academic(staff#) not null
, code        references subject not null
, year        number(4,0) not null
, constraint teaches_pk primary key (lecturer, code, year) );

If so, I would use table aliases in the query as below to avoid ambiguity:

select a.first_name
     , a.last_name
     , s.name as title
from   academic a
       join teaches t
            on  t.lecturer = a.staff#
       join subject s
            on  s.code = t.code
where  t.year < 2016
and    t.code = who.code
group by a.first_name, a.last_name, s.name