Calculate average grade : Utility Function « Stored Procedure Function « Oracle PL / SQL

1. Oracle PL / SQL
2. Stored Procedure Function
3. Utility Function

Calculate average grade

  
SQL> CREATE TABLE myStudent (
  2    student_id NUMBER(5) NOT NULL,
  3    department CHAR(3)   NOT NULL,
  4    course     NUMBER(3) NOT NULL,
  5    grade      CHAR(1)
  6    );

Table created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10000, 'CS', 102, 'A');

1 row created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10002, 'CS', 102, 'B');

1 row created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10003, 'CS', 102, 'C');

1 row created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10000, 'HIS', 101, 'A');

1 row created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10001, 'HIS', 101, 'B');

1 row created.

SQL>
SQL> INSERT INTO myStudent (student_id, department, course, grade)
  2                           VALUES (10002, 'HIS', 101, 'B');

1 row created.

SQL>
SQL>
SQL> CREATE OR REPLACE FUNCTION AverageGrade (p_Department IN VARCHAR2,p_Course IN NUMBER) RETURN VARCHAR2 AS
  2
  3    v_AverageGrade VARCHAR2(1);
  4    v_NumericGrade NUMBER;
  5    v_NumberStudents NUMBER;
  6
  7    CURSOR c_Grades IS
  8      SELECT grade
  9        FROM myStudent
 10        WHERE department = p_Department
 11        AND course = p_Course;
 12  BEGIN
 13    SELECT COUNT(*)
 14      INTO v_NumberStudents
 15      FROM myStudent
 16      WHERE department = p_Department
 17        AND course = p_Course;
 18
 19    IF v_NumberStudents = 0 THEN
 20      RAISE_APPLICATION_ERROR(-20001, 'No students registered for ' ||
 21        p_Department || ' ' || p_Course);
 22    END IF;
 23
 24    SELECT AVG(DECODE(grade, 'A', 5,
 25                             'B', 4,
 26                             'C', 3,
 27                             'D', 2,
 28                             'E', 1))
 29      INTO v_NumericGrade
 30      FROM myStudent
 31      WHERE department = p_Department
 32      AND course = p_Course;
 33
 34    SELECT DECODE(ROUND(v_NumericGrade), 5, 'A',
 35                                         4, 'B',
 36                                         3, 'C',
 37                                         2, 'D',
 38                                         1, 'E')
 39      INTO v_AverageGrade
 40      FROM dual;
 41
 42    RETURN v_AverageGrade;
 43  END AverageGrade;
 44  /

Function created.

SQL>
SQL> select AverageGrade('HIS', 101) from dual;

AVERAGEGRADE('HIS',101)
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
B

1 row selected.

SQL>
SQL>
SQL> drop table myStudent;

Table dropped.

SQL>
SQL>

   
  

Related examples in the same category