05-pf.sql

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

REM 05-PF.SQL
REM This file contains the procedure and function 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: Creating a procedure
CREATE OR REPLACE PROCEDURE AddNewStudent (
  p_FirstName  students.first_name%TYPE,
  p_LastName   students.last_name%TYPE,
  p_Major      students.major%TYPE) AS
BEGIN
  -- Insert a new row in the students table. Use
  -- student_sequence to generate the new student ID, and
  -- 0 for current_credits.
  INSERT INTO students (ID, first_name, last_name,
                        current_credits,  major)
    VALUES (student_sequence.nextval, p_FirstName, p_LastName,
            0, p_Major);

  COMMIT;
END AddNewStudent;
/

REM *** Chapter 5: Calling a procedure ***
DECLARE
  -- Variables describing the new student
  v_NewFirstName  students.first_name%TYPE := 'Margaret';
  v_NewLastName   students.last_name%TYPE := 'Mason';
  v_NewMajor      students.major%TYPE := 'History';
BEGIN
  -- Add Margaret Mason to the database.
  AddNewStudent(v_NewFirstName, v_NewLastName, v_NewMajor);
END;
/

REM *** Chapter 5: Parameter Modes ***
CREATE OR REPLACE PROCEDURE ModeTest (
  p_InParameter    IN NUMBER,
  p_OutParameter   OUT NUMBER,
  p_InOutParameter IN OUT NUMBER) IS

  v_LocalVariable  NUMBER;
BEGIN
  /* Assign p_InParameter to v_LocalVariable. This is legal,
     since we are reading from an IN parameter and not writing
     to it. */
  v_LocalVariable := p_InParameter;  -- Legal

  /* Assign 7 to p_InParameter. This is ILLEGAL, since we
     are writing to a IN parameter. */
  p_InParameter := 7;  -- Illegal

  /* Assign 7 to p_OutParameter. This is legal, since we 
     are writing to an OUT parameter and not reading from
     it. */
  p_OutParameter := 7;  -- Legal

  /* Assign p_OutParameter to v_LocalVariable. This is
     ILLEGAL, since we are reading from an OUT parameter. */
  v_LocalVariable := p_outParameter;  -- Illegal

  /* Assign p_InOutParameter to v_LocalVariable. This is legal,
     since we are reading from an IN OUT parameter. */
  v_LocalVariable := p_InOutParameter;  -- Legal

  /* Assign 7 to p_InOutParameter. This is legal, since we
     are writing to an IN OUT parameter. */
  p_InOutParameter := 7;  -- Legal
END ModeTest;
/

REM *** Chapter 5: Parameter Default Values ***
CREATE OR REPLACE PROCEDURE AddNewStudent (
  p_FirstName  students.first_name%TYPE,
  p_LastName   students.last_name%TYPE,
  p_Major      students.major%TYPE DEFAULT 'Economics') AS
BEGIN
  -- Insert a new row in the students table. Use
  -- student_sequence to generate the new student ID, and
  -- 0 for current_credits.
  INSERT INTO students VALUES (student_sequence.nextval, 
    p_FirstName, p_LastName, p_Major, 0);

  COMMIT;
END AddNewStudent;
/

REM *** Chapter 5: Creating a Function ***
CREATE OR REPLACE FUNCTION AlmostFull (
  p_Department classes.department%TYPE,
  p_Course     classes.course%TYPE)
  RETURN BOOLEAN IS

  v_CurrentStudents NUMBER;
  v_MaxStudents     NUMBER;
  v_ReturnValue     BOOLEAN;
  v_FullPercent     CONSTANT NUMBER := 90;
BEGIN
  -- Get the current and maximum students for the requested
  -- course.
  SELECT current_students, max_students
    INTO v_CurrentStudents, v_MaxStudents
    FROM classes
    WHERE department = p_Department
    AND course = p_Course;

  -- If the class is more full than the percentage given by 
  -- v_FullPercent, return TRUE. Otherwise, return FALSE.
  IF (v_CurrentStudents / v_MaxStudents * 100) > v_FullPercent THEN
    v_ReturnValue := TRUE;
  ELSE
    v_ReturnValue := FALSE;
  END IF;

  RETURN v_ReturnValue;
END AlmostFull;
/

REM *** Chapter 5: Calling a Function ***
DECLARE
  CURSOR c_Classes IS
    SELECT department, course
      FROM classes;
BEGIN
  FOR v_ClassRecord IN c_Classes LOOP
    -- Record all classes which don't have very much room left
    -- in temp_table.
    IF AlmostFull(v_ClassRecord.department, v_ClassRecord.course) THEN
      INSERT INTO temp_table (char_col) VALUES
        (v_ClassRecord.department || ' ' || v_ClassRecord.course ||
         ' is almost full!');
    END IF;
  END LOOP;
END;
/

REM *** Chapter 5: Multiple RETURN Statements ***
CREATE OR REPLACE FUNCTION ClassInfo (
  /* Returns 'Full' if the class is completely full, 
     'Some Room' if the class is over 80% full,
     'More Room' if the class is over 60% full,
     'Lots of Room' if the class is less than 60% full, and
     'Empty' if there are no students registered. */
  p_Department classes.department%TYPE,
  p_Course     classes.course%TYPE)
  RETURN VARCHAR2 IS

  v_CurrentStudents NUMBER;
  v_MaxStudents     NUMBER;
  v_PercentFull     NUMBER;
BEGIN
  -- Get the current and maximum students for the requested
  -- course.
  SELECT current_students, max_students
    INTO v_CurrentStudents, v_MaxStudents
    FROM classes
    WHERE department = p_Department
    AND course = p_Course;

  -- Calculate the current percentage.
  v_PercentFull := v_CurrentStudents / v_MaxStudents * 100;

  IF v_PercentFull = 100 THEN
    RETURN 'Full';
  ELSIF v_PercentFull > 80 THEN
    RETURN 'Some Room';
  ELSIF v_PercentFull > 60 THEN
    RETURN 'More Room';
  ELSIF v_PercentFull > 0 THEN
    RETURN 'Lots of Room';
  ELSE
    RETURN 'Empty';
  END IF;
END ClassInfo;
/

REM *** Chapter 5: Exceptions in Subprograms ***
CREATE OR REPLACE PROCEDURE RaiseError (
  /* Illustrates the behavior of unhandled exceptions and
     OUT variables. If p_Raise is TRUE, then an unhandled
     error is raised. If p_Raise is FALSE, the procedure
     completes successfully. */
  p_Raise IN BOOLEAN := TRUE,
  p_ParameterA OUT NUMBER) AS
BEGIN
  p_ParameterA := 7;

  IF p_Raise THEN
    /* Even though we have assigned 7 to p_ParameterA, this
       unhandled exception causes control to return immediately
       without returning 7 to the actual parameter associated 
       with p_ParameterA. */
    RAISE DUP_VAL_ON_INDEX;
  ELSE
    /* Simply return with no error. This will return 7 to the
       actual parameter. */
    RETURN;
  END IF;
END RaiseError;
If we call RaiseError with the following block,
DECLARE
  v_TempVar NUMBER := 1;
BEGIN
  INSERT INTO temp_table (num_col, char_col)
    VALUES (v_TempVar, 'Initial value');
  RaiseError(FALSE, v_TempVar);

  INSERT INTO temp_table (num_col, char_col)
    VALUES (v_TempVar, 'Value after successful call');

  v_TempVar := 2;
  INSERT INTO temp_table (num_col, char_col)
    VALUES (v_TempVar, 'Value before 2nd call');
  RaiseError(TRUE, v_TempVar);
EXCEPTION
  WHEN OTHERS THEN
    INSERT INTO temp_table (num_col, char_col)
      VALUES (v_TempVar, 'Value after unsuccessful call');
END;
/

REM *** Chapter 5: Local Subprograms ***
DECLARE
  CURSOR c_AllStudents IS
    SELECT first_name, last_name
      FROM students;

  v_FormattedName VARCHAR2(50);

  /* Function which will return the first and last name
     concatenated together, separated by a space. */
  FUNCTION FormatName(p_FirstName IN VARCHAR2,
                      p_LastName IN VARCHAR2)
    RETURN VARCHAR2 IS
  BEGIN
    RETURN p_FirstName || ' ' || p_LastName;
  END FormatName;

-- Begin main block.
BEGIN
  FOR v_StudentRecord IN c_AllStudents LOOP
    v_FormattedName := 
      FormatName(v_StudentRecord.first_name, 
                 v_StudentRecord.last_name);  
    INSERT INTO temp_table (char_col)
      VALUES (v_FormattedName);
  END LOOP;

  COMMIT;
END;
/

REM *** Chapter 5: Forward Declarations ***
DECLARE
  v_TempVal BINARY_INTEGER := 5;

  -- Forward declaration of procedure B.
  PROCEDURE B(p_Counter IN OUT BINARY_INTEGER);

  PROCEDURE A(p_Counter IN OUT BINARY_INTEGER) IS
  BEGIN
    IF p_Counter > 0 THEN
      B(p_Counter);
      p_Counter := p_Counter - 1;
    END IF;
  END A;

  PROCEDURE B(p_Counter IN OUT BINARY_INTEGER) IS
  BEGIN
    p_Counter := p_Counter - 1;
    A(p_Counter);
  END B;
BEGIN
  B(v_TempVal);
END;
/

REM *** Chapter 5: Dependency Example ***
CREATE OR REPLACE PROCEDURE RecordFullClasses AS
  CURSOR c_Classes IS
    SELECT department, course
      FROM classes;
BEGIN
  FOR v_ClassRecord IN c_Classes LOOP
    -- Record all classes which don't have very much room left
    -- in temp_table.
    IF AlmostFull(v_ClassRecord.department, v_ClassRecord.course) THEN
      INSERT INTO temp_table (char_col) VALUES
        (v_ClassRecord.department || ' ' || v_ClassRecord.course ||
         ' is almost full!');
    END IF;
  END LOOP;
END RecordFullClasses;
/