08-cc2.sql

来自「《Oracle8i PL/SQL程序设计》附源码」· SQL 代码 · 共 42 行

REM 08-CC2.SQL
REM This file contains the second version of the CountCredits function,
REM from Chapter 8 of "Oracle PL/SQL Programming".  This version has
REM 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 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
  Debug.Reset;
  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;

    Debug.Debug('Inside loop, v_CourseCredits', v_CourseCredits);
    -- Add it to the total so far.
    v_TotalCredits := v_TotalCredits + v_CourseCredits;
  END LOOP;

  Debug.Debug('After loop, returning', v_TotalCredits);
  RETURN v_TotalCredits;
END CountCredits;
/