08-cntcr.sql

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

REM 08-CNTR.SQL
REM This file contains the first version of the CountCredits
REM function, used as the second debugging example in Chapter 8
REM of "Oracle PL/SQL Programming".

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 CountCredits (
  /* Returns the number of credits for which the student
     identified by p_StudentID is currently registered */
  p_StudentID IN students.ID%TYPE)
  RETURN NUMBER AS

  v_TotalCredits NUMBER;  -- Total number of credits
  v_CourseCredits NUMBER; -- Credits for one course
  CURSOR c_RegisteredCourses IS
    SELECT department, course
      FROM registered_students
      WHERE student_id = p_StudentID;
BEGIN
  FOR v_CourseRec IN c_RegisteredCourses LOOP
    -- Determine the credits for this class.
    SELECT num_credits
      INTO v_CourseCredits
      FROM classes
      WHERE department = v_CourseRec.department
      AND course = v_CourseRec.course;

    -- Add it to the total so far.
    v_TotalCredits := v_TotalCredits + v_CourseCredits;
  END LOOP;

  RETURN v_TotalCredits;
END CountCredits;
/