|
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>
|