Use case ... when to output comments for marks

   
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.

Related examples in the same category

1.	You could use the case statement in an SQL statement
2.	Combine case with NVL function
3.	Combine Case with group by
4.	Use the CASE expression and SUM function
5.	Use the CASE expression and SUM function to create an order price report
6.	case switch based on clob data
7.	Use case when and grouping function together
8.	Use case when clause to decode value
9.	Use case when statement to replace if statement
10.	Use case when statement with between ... and
11.	Use case when statement with exists and subquery
12.	Use case when statement with in()
13.	Use case when statement with to_char() like
14.	Use case when with comparasion operator
15.	A searched CASE statement
16.	use case when to control the order index
17.	case when count(*) NOT between ... and
18.	Rename case when column

Use case ... when to output comments for marks : Case Query « Select Query « Oracle PL / SQL

Related examples in the same category