05-pack.sql

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

REM 05-PACK.SQL
REM This file contains the package examples
REM used in Chapter 5 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].

REM *** Chapter 5: A Package Specification ***
CREATE OR REPLACE PACKAGE ClassPackage AS
  -- Add a new student into the specified class.
  PROCEDURE AddStudent(p_StudentID  IN students.id%TYPE,
                       p_Department IN classes.department%TYPE, 
                       p_Course     IN classes.course%TYPE);

  -- Removes the specified student from the specified class.
  PROCEDURE RemoveStudent(p_StudentID  IN students.id%TYPE,
                          p_Department IN classes.department%TYPE, 
                          p_Course     IN classes.course%TYPE);

  -- Exception raised by RemoveStudent.
  e_StudentNotRegistered EXCEPTION;

  -- Table type used to hold student info.
  TYPE t_StudentIDTable IS TABLE OF students.id%TYPE
    INDEX BY BINARY_INTEGER;

  -- Returns a PL/SQL table containing the students currently
  -- in the specified class.
  PROCEDURE ClassList(p_Department  IN  classes.department%TYPE,
                      p_Course      IN  classes.course%TYPE,
                      p_IDs         OUT t_StudentIDTable,
                      p_NumStudents IN OUT BINARY_INTEGER);
END ClassPackage;
/

REM *** Chapter 5: A Package Body
CREATE OR REPLACE PACKAGE BODY ClassPackage AS
  -- Add a new student for the specified class.
  PROCEDURE AddStudent(p_StudentID  IN students.id%TYPE,
                       p_Department IN classes.department%TYPE, 
                       p_Course     IN classes.course%TYPE) IS
  BEGIN
    INSERT INTO registered_students (student_id, department, course)
      VALUES (p_StudentID, p_Department, p_Course);
    COMMIT;
  END AddStudent;


  -- Removes the specified student from the specified class.
  PROCEDURE RemoveStudent(p_StudentID  IN students.id%TYPE,
                          p_Department IN classes.department%TYPE, 
                          p_Course     IN classes.course%TYPE) IS
  BEGIN
    DELETE FROM registered_students
      WHERE student_id = p_StudentID
      AND department = p_Department
      AND course = p_Course;

    -- Check to see if the DELETE operation was successful. If
    -- it didn't match any rows, raise an error.
    IF SQL%NOTFOUND THEN
      RAISE e_StudentNotRegistered;
    END IF;

    COMMIT;
  END RemoveStudent;


  -- Returns a PL/SQL table containing the students currently
  -- in the specified class.
  PROCEDURE ClassList(p_Department  IN  classes.department%TYPE,
                      p_Course      IN  classes.course%TYPE,
                      p_IDs         OUT t_StudentIDTable,
                      p_NumStudents IN OUT BINARY_INTEGER) IS

    v_StudentID  registered_students.student_id%TYPE;

    -- Local cursor to fetch the registered students.
    CURSOR c_RegisteredStudents IS
      SELECT student_id
        FROM registered_students
        WHERE department = p_Department
        AND course = p_Course;
  BEGIN
    /* p_NumStudents will be the table index. It will start at
       0, and be incremented each time through the fetch loop.
       At the end of the loop, it will have the number of rows 
       fetched, and therefore the number of rows returned in
       p_IDs. */
    p_NumStudents := 0;

    OPEN c_RegisteredStudents;
    LOOP
      FETCH c_RegisteredStudents INTO v_StudentID;
      EXIT WHEN c_RegisteredStudents%NOTFOUND;

      p_NumStudents := p_NumStudents + 1;
      p_IDs(p_NumStudents) := v_StudentID;
    END LOOP;
  END ClassList;
END ClassPackage;
/

REM *** Chapter 5: Calling a Packaged Procedure ***
DECLARE
  v_HistoryStudents ClassPackage.t_StudentIDTable;
  v_NumStudents     BINARY_INTEGER := 20;
BEGIN
  -- Fill the PL/SQL table with the first 20 History 101
  -- students.
  ClassPackage.ClassList('HIS', 101, v_HistoryStudents,
                         v_NumStudents);

  -- Insert these students into temp_table.
  FOR v_LoopCounter IN 1..v_NumStudents LOOP
    INSERT INTO temp_table (num_col, char_col)
      VALUES (v_HistoryStudents(v_LoopCounter), 
              'In History 101');
  END LOOP;
END;
/

REM *** Chapter 5: Overloading Packaged Subprograms ***
CREATE OR REPLACE PACKAGE ClassPackage AS
  -- Add a new student into the specified class.
  PROCEDURE AddStudent(p_StudentID  IN students.id%TYPE,
                       p_Department IN classes.department%TYPE, 
                       p_Course     IN classes.course%TYPE);

  -- Also adds a new student, by specifying the first and last
  -- names, rather than ID number.
  PROCEDURE AddStudent(p_FirstName IN students.first_name%TYPE,
                       p_LastName  IN students.last_name%TYPE,
                       p_Department IN classes.department%TYPE, 
                       p_Course     IN classes.course%TYPE);
  
END ClassPackage;
/

CREATE OR REPLACE PACKAGE BODY ClassPackage AS
  -- Add a new student for the specified class.
  PROCEDURE AddStudent(p_StudentID  IN students.id%TYPE,
                       p_Department IN classes.department%TYPE, 
                       p_Course     IN classes.course%TYPE) IS
  BEGIN
    INSERT INTO registered_students (student_id, department, course)
      VALUES (p_StudentID, p_Department, p_Course);
    COMMIT;
  END AddStudent;

  -- Add a new student by name, rather than ID.
  PROCEDURE AddStudent(p_FirstName IN students.first_name%TYPE,
                       p_LastName  IN students.last_name%TYPE,
                       p_Department IN classes.department%TYPE, 
                       p_Course     IN classes.course%TYPE) IS
    v_StudentID students.ID%TYPE;
  BEGIN
    /* First we need to get the ID from the students table. */
    SELECT ID
      INTO v_StudentID
      FROM students
      WHERE first_name = p_FirstName
      AND last_name = p_LastName;

    -- Now we can add the student by ID.
    INSERT INTO registered_students (student_id, department, course)
      VALUES (v_StudentID, p_Department, p_Course);
    COMMIT;
  END AddStudent;
  
END ClassPackage;
/