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>
SQL> CREATE OR REPLACE FUNCTION AverageGrade (
2 p_Department IN VARCHAR2,
3 p_Course IN NUMBER) RETURN VARCHAR2 AS
4
5 v_AverageGrade VARCHAR2(1);
6 v_NumericGrade NUMBER;
7 v_NumberStudents 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 Debug.Reset;
16 Debug.Debug('p_Department', p_Department);
17 Debug.Debug('p_Course', p_Course);
18
19 SELECT COUNT(*)
20 INTO v_NumberStudents
21 FROM myStudent
22 WHERE department = p_Department
23 AND course = p_Course;
24
25 Debug.Debug('After select, v_NumberStudents', v_NumberStudents);
26
27 IF v_NumberStudents = 0 THEN
28 RAISE_APPLICATION_ERROR(-20001, 'No students registered for ' ||
29 p_Department || ' ' || p_Course);
30 END IF;
31
32 SELECT AVG(DECODE(grade, 'A', 5,
33 'B', 4,
34 'C', 3,
35 'D', 2,
36 'E', 1))
37 INTO v_NumericGrade
38 FROM myStudent
39 WHERE department = p_Department
40 AND course = p_Course;
41
42 SELECT DECODE(ROUND(v_NumericGrade), 5, 'A',
43 4, 'B',
44 3, 'C',
45 2, 'D',
46 1, 'E')
47 INTO v_AverageGrade
48 FROM dual;
49
50 RETURN v_AverageGrade;
51 END AverageGrade;
52 /
Function created.
SQL>
SQL>
SQL>
SQL>
SQL>
SQL> drop table myStudent;
Table dropped.
|