08-ag2.sql

《Oracle8i PL/SQL程序设计》附源码

REM 08-AG2.SQL
REM This file contains the second version of the AverageGrade 
REM function, used in Chapter 8 of "Oracle PL/SQL Programming".
REM This version has some debugging statements added.

REM This is version 1.0 of this file, updated 2/18/96.
REM Comments and questions should go to Scott Urman at
REM [e-mail].

CREATE OR REPLACE FUNCTION AverageGrade (
  p_Department IN VARCHAR2,
  p_Course IN NUMBER) RETURN VARCHAR2 AS

  v_AverageGrade VARCHAR2(1);
  v_NumericGrade NUMBER;
  v_NumberStudents NUMBER;

  CURSOR c_Grades IS
    SELECT grade
      FROM registered_students
      WHERE department = p_Department
      AND course = p_Course;
BEGIN
  Debug.Reset;
  Debug.Debug('p_Department', p_Department);
  Debug.Debug('p_Course', p_Course);

  /* First we need to see how many students there are for
     this class. If there aren't any, we need to raise an
     error. */
  SELECT COUNT(*)
    INTO v_NumberStudents
    FROM registered_students
    WHERE department = p_Department
    AND course = p_Course;

  Debug.Debug('After select, v_NumberStudents', v_NumberStudents);
  IF v_NumberStudents = 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'No students registered for ' ||
      p_Department || ' ' || p_Course);
  END IF;

  SELECT AVG(DECODE(grade, 'A', 5,
                           'B', 4,
                           'C', 3,
                           'D', 2,
                           'E', 1))
    INTO v_NumericGrade
    FROM registered_students
    WHERE department = p_Department
      AND course = p_Course;

  SELECT DECODE(ROUND(v_NumericGrade), 5, 'A',
                                       4, 'B',
                                       3, 'C',
                                       2, 'D',
                                       1, 'E')
    INTO v_AverageGrade
    FROM dual;

  RETURN v_AverageGrade;
END AverageGrade;
/