|
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 v_AverageGrade CHAR(1);
5 v_NumericGrade NUMBER;
6 v_NumberStudents NUMBER;
7
8 CURSOR c_Grades IS
9 SELECT grade
10 FROM myStudent
11 WHERE department = p_Department
12 AND course = p_Course;
13 BEGIN
14 SELECT COUNT(*)
15 INTO v_NumberStudents
16 FROM myStudent
17 WHERE department = p_Department
18 AND course = p_Course;
19
20 IF v_NumberStudents = 0 THEN
21 RAISE_APPLICATION_ERROR(-20001, 'No students registered for ' ||
22 p_Department || ' ' || p_Course);
23 END IF;
24
25 SELECT AVG(DECODE(grade, 'A', 5,
26 'B', 4,
27 'C', 3,
28 'D', 2,
29 'E', 1))
30 INTO v_NumericGrade
31 FROM myStudent
32 WHERE department = p_Department
33 AND course = p_Course;
34
35 SELECT DECODE(ROUND(v_NumericGrade), 5, 'A',
36 4, 'B',
37 3, 'C',
38 2, 'D',
39 1, 'E')
40 INTO v_AverageGrade
41 FROM dual;
42
43 RETURN v_AverageGrade;
44 END AverageGrade;
45 /
Function created.
SQL>
SQL>
SQL> VARIABLE v_AveGrade VARCHAR2(1)
SQL> EXEC :v_AveGrade := AverageGrade('HIS', 101)
PL/SQL procedure successfully completed.
SQL> PRINT v_AveGrade
V_AVEGRADE
--------------------------------
B
SQL> EXEC :v_AveGrade := AverageGrade('NUT', 307)
BEGIN :v_AveGrade := AverageGrade('NUT', 307); END;
*
ERROR at line 1:
ORA-20001: No students registered for NUT 307
ORA-06512: at "JAVA2S.AVERAGEGRADE", line 21
ORA-06512: at line 1
SQL> PRINT v_AveGrade
V_AVEGRADE
--------------------------------
B
SQL> EXEC :v_AveGrade := AverageGrade('MUS', 410)
BEGIN :v_AveGrade := AverageGrade('MUS', 410); END;
*
ERROR at line 1:
ORA-20001: No students registered for MUS 410
ORA-06512: at "JAVA2S.AVERAGEGRADE", line 21
ORA-06512: at line 1
SQL> PRINT v_AveGrade
V_AVEGRADE
--------------------------------
B
SQL> EXEC :v_AveGrade := AverageGrade('CS', 102)
PL/SQL procedure successfully completed.
SQL> PRINT v_AveGrade
V_AVEGRADE
--------------------------------
B
SQL>
SQL>
SQL>
SQL>
SQL> drop table myStudent;
Table dropped.
SQL>
|