SQL>
SQL>
SQL> CREATE TABLE empExam (
2 empID INT NOT NULL,
3 ExamID INT NOT NULL,
4 Mark INT,
5 Taken SMALLINT,
6 Comments VARCHAR(255),
7 CONSTRAINT PK_empExam PRIMARY KEY (empID, ExamID));
Table created.
SQL>
SQL>
SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (1,1,55,1,'Satisfactory');
1 row created.
SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (1,2,73,1,'Good result');
1 row created.
SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (2,3,44,1,'Hard');
1 row created.
SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken,Comments) VALUES (2,5,39,0,'Simple');
1 row created.
SQL> INSERT INTO empExam (empID,ExamID,Mark,Taken) VALUES (2,6,63,1);
1 row created.
SQL>
SQL> CREATE OR REPLACE PROCEDURE GetempComments(i_empID IN INT,o_Comments OUT VARCHAR)
2 AS
3 exams_sat INT;
4 avg_mark INT;
5 tmp_comments VARCHAR(100);
6 BEGIN
7 SELECT COUNT(ExamID) INTO exams_sat FROM empExam
8 WHERE empID = i_empID;
9 IF exams_sat = 0 THEN
10 tmp_comments := 'n/a - this emp sat no exams';
11 ELSE
12 SELECT AVG(Mark) INTO avg_mark FROM empExam
13 WHERE empID = i_empID;
14 CASE
15 WHEN avg_mark < 50 THEN tmp_comments := 'Poor';
16 WHEN avg_mark < 60 THEN tmp_comments := 'Adequate.';
17 WHEN avg_mark < 70 THEN tmp_comments := 'Satisfactory.';
18 ELSE tmp_comments := 'Excellent!';
19 END CASE;
20 END IF;
21 o_Comments := tmp_comments;
22 END;
23 /
SP2-0804: Procedure created with compilation warnings
SQL> SET SERVEROUT ON
SQL> DECLARE
2 comments VARCHAR(100);
3 BEGIN
4 GetempComments(2, comments);
5 dbms_output.put_line(comments);
6 END;
7 /
Poor
PL/SQL procedure successfully completed.
SQL>
SQL>
SQL> drop table empExam;
Table dropped.