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