RAISE_APPLICATION_ERROR : Raise « PL SQL « Oracle PL / SQL

1. Oracle PL / SQL
2. PL SQL
3. Raise

RAISE_APPLICATION_ERROR

   
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
  5    v_AverageGrade CHAR(1);
  6    v_NumericGrade NUMBER;
  7    v_Numberlecturer 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    SELECT COUNT(*)
 16      INTO v_Numberlecturer
 17      FROM myStudent
 18      WHERE department = p_Department
 19        AND course = p_Course;
 20
 21    IF v_Numberlecturer = 0 THEN
 22      RAISE_APPLICATION_ERROR(-20001, 'No lecturer registered for ' ||
 23        p_Department || ' ' || p_Course);
 24    END IF;
 25
 26    SELECT AVG(DECODE(grade, 'A', 5,
 27                             'B', 4,
 28                             'C', 3,
 29                             'D', 2,
 30                             'E', 1))
 31      INTO v_NumericGrade
 32      FROM myStudent
 33      WHERE department = p_Department
 34      AND course = p_Course;
 35
 36    SELECT DECODE(ROUND(v_NumericGrade), 5, 'A',
 37                                         4, 'B',
 38                                         3, 'C',
 39                                         2, 'D',
 40                                         1, 'E')
 41      INTO v_AverageGrade
 42      FROM dual;
 43
 44    RETURN v_AverageGrade;
 45  END AverageGrade;
 46  /

Function created.

SQL>
SQL> drop table myStudent;

Table dropped.

SQL>

   
    
  

Related examples in the same category