Afaque Hussain Afaque Hussain - 1 month ago 6
SQL Question

Can't figure out the errors in this pl/ql procedure

I want to get the average score from the database and then grade each student in accordance to their average grade.

PROCEDURE avge (stu_id IN NUMBER, z OUT VARCHAR2, lv_avg OUT NUMBER)
IS
BEGIN
SELECT AVG(SCORE)
into lv_avg
FROM GRADE
WHERE STUDENT_ID = stu_id;

IF (lv_avg BETWEEN 90 AND 100) THEN
z := 'A';
ELSIF (lv_avg BETWEEN 80 AND 90) THEN
z := 'B';
ELSIF (lv_avg < 80) THEN
z := 'C';
END IF;
END;


These are the errors that pop up are:

ORA-06550: line 5, column 22:
PL/SQL: ORA-00904: "STU_ID": invalid identifier
ORA-06550: line 2, column 3:
PL/SQL: SQL Statement ignored
ORA-06550: line 6, column 7:
PLS-00201: identifier 'LV_AVG' must be declared

Answer

PL/SQL doesn't require brackets around if expressions, because they are already terminated with then, so the ones in your code are redundant.

case can avoid some repetition of z :=. I would also choose a more descriptive parameter name than z - and as lv_avg is neither a local variable nor anything to do with luncheon vouchers, I would not prefix it with lv_.

Also all of your keywords except into seem to be in uppercase. Why is that?

I would use something like this:

create or replace procedure avge
    ( p_student_id  in  grade.student_id%type
    , p_out_grade   out varchar2
    , p_out_avg     out grade.score%type )
is
begin
    select avg(score) into p_out_avg
    from   grade
    where  student_id = p_student_id;

    p_out_grade :=
        case
            when p_out_avg between 90 and 100 then 'A'
            when p_out_avg between 80 and  90 then 'B'
            when p_out_avg < 80 then 'C'
        end;
end avge;
Comments