06-examp.sql

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

REM 06-EXAMP.SQL
REM This file contains the examples
REM used in Chapter 6 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 6: Raising NO_DATA_FOUND ***
DECLARE
  TYPE t_NumberTableType IS TABLE OF NUMBER
    INDEX BY BINARY_INTEGER;
  v_NumberTable t_NumberTableType;
  v_TempVar NUMBER;
BEGIN
  v_TempVar := v_NumberTable(1);
END;
/

REM *** Chapter 6: Raising an exception ***
DECLARE
  e_TooManyStudents EXCEPTION;  -- Exception to indicate an error condition
  v_CurrentStudents NUMBER(3);  -- Current number of students registered
                                 -- for HIS-101
  v_MaxStudents NUMBER(3);      -- Maximum number of students allowed for
                                 -- HIS-101
BEGIN
  /* Find the current number of registered students, and the maximum number of
     students allowed. */
  SELECT current_students, max_students
    INTO v_CurrentStudents, v_MaxStudents
    FROM classes
    WHERE department = 'HIS' AND course = 101;
  /* Check the number of students in this class. */
  IF v_CurrentStudents > v_MaxStudents THEN
    /* Too many students registered -- raise exception. */
    RAISE e_TooManyStudents;
  END IF;
END;
/

REM *** Chapter 6: Handling an exception ***
DECLARE
  e_TooManyStudents EXCEPTION;  -- Exception to indicate an error condition
  v_CurrentStudents NUMBER(3);  -- Current number of students registered
                                 -- for HIS-101
  v_MaxStudents NUMBER(3);       -- Maximum number of students allowed for
                                 -- HIS-101
BEGIN
  /* Find the current number of registered students, and the maximum number of
     students allowed. */
  SELECT current_students, max_students
    INTO v_CurrentStudents, v_MaxStudents
    FROM classes
    WHERE department = 'HIS' AND course = 101;
  /* Check the number of students in this class. */
  IF v_CurrentStudents > v_MaxStudents THEN
    /* Too many students registered - raise exception. */
    RAISE e_TooManyStudents;
  END IF;
EXCEPTION
  WHEN e_TooManyStudents THEN
    /* Handler which executes when there are too many students registered
       for HIS-101. We will insert a log message explaining what has happened. */
    INSERT INTO log_table (info) VALUES ('History 101 has ' || v_CurrentStudents ||
      'students: max allowed is ' || v_MaxStudents);
END;
/

REM *** Chapter 6: The OTHERS Exception Handler ***
DECLARE
  e_TooManyStudents EXCEPTION;  -- Exception to indicate an error condition
  v_CurrentStudents NUMBER(3);  -- Current number of students registered
                                 -- for HIS-101
  v_MaxStudents NUMBER(3);      -- Maximum number of students allowed for
                                 -- HIS-101
BEGIN
  /* Find the current number of registered students, and the maximum number of
     students allowed. */
  SELECT current_students, max_students
    INTO v_CurrentStudents, v_MaxStudents
    FROM classes
    WHERE department = 'HIS' AND course = 101;
  /* Check the number of students in this class. */
  IF v_CurrentStudents > v_MaxStudents THEN
    /* Too many students registered - raise exception. */
    RAISE e_TooManyStudents;
  END IF;
EXCEPTION
  WHEN e_TooManyStudents THEN
    /* Handler which executes when there are too many students registered
       for HIS-101. We will insert a log message explaining what has happened. */
    INSERT INTO log_table (info) VALUES ('History 101 has ' || v_CurrentStudents ||
      'students: max allowed is ' || v_MaxStudents);
  WHEN OTHERS THEN
    /* Handler which executes for all other errors. */
    INSERT INTO log_table (info) VALUES ('Another error occurred');
END;
/

REM *** Chapter 6: Using SQLCODE and SQLERRM ***
DECLARE
  e_TooManyStudents EXCEPTION;  -- Exception to indicate an error condition
  v_CurrentStudents NUMBER(3);  -- Current number of students registered
                                 -- for HIS-101
  v_MaxStudents NUMBER(3);      -- Maximum number of students allowed for
                                 -- HIS-101

  v_ErrorCode NUMBER;           -- Variable to hold the error message code
  v_ErrorText VARCHAR2(200);    -- Variable to hold the error message text

BEGIN
  /* Find the current number of registered students, and the maximum number of
     students allowed. */
  SELECT current_students, max_students
    INTO v_CurrentStudents, v_MaxStudents
    FROM classes
    WHERE department = 'HIS' AND course = 101;
  /* Check the number of students in this class. */
  IF v_CurrentStudents > v_MaxStudents THEN
    /* Too many students registered - raise exception. */
    RAISE e_TooManyStudents;
  END IF;
EXCEPTION
  WHEN e_TooManyStudents THEN
    /* Handler which executes when there are too many students registered
       for HIS-101. We will insert a log message explaining what has happened. */
    INSERT INTO log_table (info) VALUES ('History 101 has ' || v_CurrentStudents ||
      'students: max allowed is ' || v_MaxStudents);
  WHEN OTHERS THEN
    /* Handler which executes for all other errors. */
    v_ErrorCode := SQLCODE;
    v_ErrorText := SUBSTR(SQLERRM, 1, 200);  -- Note the use of SUBSTR here.
    INSERT INTO log_table (code, message, info) VALUES
      (v_ErrorCode, v_ErrorText, 'Oracle error occurred');
END;
/

REM *** Chapter 6: Different Values of SQLCODE and SQLERRM ***
DECLARE
  v_ErrorText   log_table.message%TYPE;  -- Variable to hold error message text
BEGIN
  /* SQLERRM(0) */
  v_ErrorText := SUBSTR(SQLERRM(0), 1, 200);
  INSERT INTO log_table (code, message, info)
    VALUES (0, v_ErrorText, 'SQLERRM(0)');

  /* SQLERRM(100) */
  v_ErrorText := SUBSTR(SQLERRM(100), 1, 200);
  INSERT INTO log_table (code, message, info)
    VALUES (100, v_ErrorText, 'SQLERRM(100)');

  /* SQLERRM(10) */
  v_ErrorText := SUBSTR(SQLERRM(10), 1, 200);
  INSERT INTO log_table (code, message, info)
    VALUES (10, v_ErrorText, 'SQLERRM(10)');

  /* SQLERRM with no argument */
  v_ErrorText := SUBSTR(SQLERRM, 1, 200);
  INSERT INTO log_table (code, message, info)
    VALUES (NULL, v_ErrorText, 'SQLERRM with no argument');

  /* SQLERRM(-1) */
  v_ErrorText := SUBSTR(SQLERRM(-1), 1, 200);
  INSERT INTO log_table (code, message, info)
    VALUES (-1, v_ErrorText, 'SQLERRM(-1)');

  /* SQLERRM(-54) */
  v_ErrorText := SUBSTR(SQLERRM(-54), 1, 200);
  INSERT INTO log_table (code, message, info)
    VALUES (-54, v_ErrorText, 'SQLERRM(-54)');

END;
/

REM *** Chapter 6: Using PRAGMA EXCEPTION_INIT ***
DECLARE
  e_MissingNull EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_MissingNull, -1400);
BEGIN
  INSERT INTO students (id) VALUES (NULL);
EXCEPTION
  WHEN e_MissingNull then
    INSERT INTO log_table (info) VALUES ('ORA-1400 occurred');
END;
/

REM *** Chapter 6: Using RAISE_APPLICATION_ERROR ***
CREATE OR REPLACE PROCEDURE Register (
  /* Registers the student identified by the p_StudentID parameter in the class
     identified by the p_Department and p_Course parameters. Before calling
     ClassPackage.AddStudent, which actually adds the student to the class, this
     procedure verifies that there is room in the class, and that the class
     exists. */
  p_StudentID IN students.id%TYPE,
  p_Department IN classes.department%TYPE,
  p_Course IN classes.course%TYPE) AS

  v_CurrentStudents NUMBER;  -- Current number of students in the class
  v_MaxStudents NUMBER;      -- Maximum number of students in the class

BEGIN
  /* Determine the current number of students registered, and the maximum
     number of students allowed to register. */
  SELECT current_students, max_students
    INTO v_CurrentStudents, v_MaxStudents
    FROM classes
    WHERE course = p_Course
    AND department = p_Department;

  /* Make sure there is enough room for this additional student. */
  IF v_CurrentStudents + 1 > v_MaxStudents THEN
    RAISE_APPLICATION_ERROR(-20000, 'Can''t add more students to ' ||
      p_Department || ' ' || p_Course);
  END IF;

  /* Add the student to the class. */
  ClassPackage.AddStudent(p_StudentID, p_Department, p_Course);

EXCEPTION
  WHEN NO_DATA_FOUND THEN
    /* Class information passed to this procedure doesn't exist. Raise an error
       to let the calling program know of this. */
    RAISE_APPLICATION_ERROR(-20001, p_Department || ' ' || p_Course ||
      ' doesn''t exist!');
END Register;
/