is there a better way to write avg procedure?

Question

I wrote this procedure but it keeps showing "WARNING: Procedure creates with compilation errors" and I don't know why, here's the table I created

create table EnrolledInClasses (
    St_Id char(9) primary key, 
    C_Id char(6),
    GradeN Number(2),
    constraint FK_StId Foreign key (St_Id) references Student (St_Id),
    constraint FK_CoID Foreign key (C_Id) references course (C_Id)
    constraint CheckGrade check (Grade>-1)
);

and here's the procedure:

CREATE OR REPLACE PROCEDURE Avg_grades
IS
    avg_grades NUMBER := 0;
BEGIN
    SELECT AVG (Grade)
    INTO avg_grades
    FROM EnrolledInClasses

    dbms_output.put_line('The average of grades is :'||avg_grades);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.put_line ('No Data Is Found..');
END;
/

It is tagged with plsqldeveloper, so I guess Oracle...? But the compilation error should usually have more info - search for that, it will tell you what's wrong. — ootwch, Apr 05 '20 at 12:01
Your posted code is missing a semi-colon at the end of the SELECT statement. — APC, Apr 05 '20 at 13:45

William Robertson · Answer 1 · 2020-04-07T12:30:39.793

Since you are using PL/SQL Developer, make sure you are writing your code in a Procedure window. Then the compilation error will be clearly highlighted. In this case it's the missing semicolon at the end of the select into statement.

Or in SQL*Plus you can use show errors:

Warning: Procedure created with compilation errors.

SQL> show errors
Errors for PROCEDURE AVG_GRADES:

LINE/COL ERROR
-------- -----------------------------------------------------------------
6/1      PL/SQL: SQL Statement ignored
9/12     PL/SQL: ORA-00933: SQL command not properly ended

or the full syntax, show errors procedure avg_grades.

Or you can directly query dba|all|cdb|user_errors:

select * from user_errors e
where  e.name = 'AVG_GRADES'
and    e.attribute = 'ERROR';

Also, the table definition could be written more succinctly as:

create table enrolledinclasses
( st_id   primary key  constraint fk_stid references student(st_id)
, c_id    constraint   fk_coid references course (c_id)
, grade   number(2)    constraint checkgrade check (grade>-1)
);

student.st_id and course.c_id should probably be numbers or integers, or at least varchar2, and not char.

It is good practice to use code indentation to align code into blocks showing their dependency structure. That would give something like this:

create or replace procedure avg_grades as
    avg_grades number := 0;
begin
    select avg(grade) into avg_grades
    from   enrolledinclasses;

    dbms_output.put_line('The average grade is: ' || avg_grades);
exception
    when no_data_found then
        dbms_output.put_line('No Data Is Found.');
end;

You might want to round() that average before displaying it, as by default it could be displayed with a lot more decimal places than you might want.

Also, it's not possible to get a no_data_found exception from a single-group aggregate query like this. If there are no rows in the table you will get a null value as the result, not an error.

is there a better way to write avg procedure?

1 Answers1