02-intro.sql

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

REM 02-INTRO.SQL
REM This file contains the examples used in Chapter 2 of
REM "Oracle PL/SQL Programming".

REM This is version 1.0 of this file, updated 2/17/96.
REM Comments and questions should go to Scott Urman at
REM [e-mail].


REM *** Chapter 2: An Anonymous Block ***
DECLARE
  /* Declare variables to be used in the block. */
  v_Department       CHAR(3)  := 'ECN';
  v_Course           NUMBER(3) := 203;
  v_Description      VARCHAR2(20) := 'Economics 203';
  v_MaxStudents      NUMBER := 15;
  v_CurrentStudents  NUMBER := 0;
  v_NumCredits       NUMBER := 3;
  v_RoomID           NUMBER := 99997;
BEGIN
  /* Add a row to the classes table, using the values of the
     variables. */
  INSERT INTO classes (department, course, description, max_students,
                       current_students, num_credits, room_id)
    VALUES (v_Department, v_Course, v_Description, v_MaxStudents,
            v_CurrentStudents, v_NumCredits, v_RoomID);
END;
/

REM *** Chapter 2: Labeled Block ***
<<l_AddNewRow>>
DECLARE
  /* Declare variables to be used in the block. */
  v_Department       CHAR(3)  := 'ECN';
  v_Course           NUMBER(3) := 203;
  v_Description      VARCHAR2(20) := 'Economics 203';
  v_MaxStudents      NUMBER := 15;
  v_CurrentStudents  NUMBER := 0;
  v_NumCredits       NUMBER := 3;
  v_RoomID           NUMBER := 99997;
BEGIN
  /* Add a row to the classes table, using the values of the
     variables. */
  INSERT INTO classes (department, course, description, max_students,
                       current_students, num_credits, room_id)
    VALUES (v_Department, v_Course, v_Description, v_MaxStudents,
            v_CurrentStudents, v_NumCredits, v_RoomID);
END l_AddNewRow;
/

REM *** Chapter 2: A Procedure ***
CREATE PROCEDURE AddNewRow AS
  /* Declare variables to be used in the block. */
  v_Department       CHAR(3)  := 'ECN';
  v_Course           NUMBER(3) := 203;
  v_Description      VARCHAR2(20) := 'Economics 203';
  v_MaxStudents      NUMBER := 15;
  v_CurrentStudents  NUMBER := 0;
  v_NumCredits       NUMBER := 3;
  v_RoomID           NUMBER := 99997;
BEGIN
  /* Add a row to the classes table, using the values of the
     variables. */
  INSERT INTO classes (department, course, description, max_students,
                       current_students, num_credits, room_id)
    VALUES (v_Department, v_Course, v_Description, v_MaxStudents,
            v_CurrentStudents, v_NumCredits, v_RoomID);
END AddNewRow;
/

REM *** Chapter 2: A Trigger ***
CREATE OR REPLACE TRIGGER CheckRoomID
  BEFORE INSERT OR UPDATE OF room_id
  ON classes
  FOR EACH ROW
DECLARE
  /* Temporary variable to hold the room ID */
  v_RoomID  NUMBER(5);
BEGIN
  /* Check to see if the room ID is valid by querying the
     rooms table. */
  SELECT room_id
    into v_RoomID
    FROM rooms
    where room_id = :new.room_id;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    /* We will get here when the room ID is not found. In this case
       raise an error indicating that the room ID is not valid. This
       will also cause the INSERT statement to fail. */
    RAISE_APPLICATION_ERROR(-20000, :new.room_id || ' is not a ' ||
        ' valid room');
END CheckRoomID;
/

REM *** Chapter 2: IF-THEN-ELSE Example 1 ***
DECLARE
  v_NumberSeats rooms.number_seats%TYPE;
  v_Comment VARCHAR2(35);
BEGIN
  /* Retrieve the number of seats in the room identified by ID 99999.
     Store the result in v_NumberSeats. */
  SELECT number_seats
    INTO v_NumberSeats
    FROM rooms
    WHERE room_id = 99999;
  IF v_NumberSeats < 50 THEN
    v_Comment := 'Fairly small';
  ELSIF v_NumberSeats < 100 THEN
    v_Comment := 'A little bigger';
  ELSE
    v_Comment := 'Lots of room';
  END IF;
END;
/

REM *** Chapter 2: IF-THEN-ELSE Example 2 ***
DECLARE
  v_NumberSeats rooms.number_seats%TYPE;
  v_Comment VARCHAR2(35);
BEGIN
  /* Retrieve the number of seats in the room identified by ID 99999.
     Store the result in v_NumberSeats. */
  SELECT number_seats
    INTO v_NumberSeats
    FROM rooms
    WHERE room_id = 99999;
  IF v_NumberSeats < 50 THEN
    v_Comment := 'Fairly small';
    INSERT INTO temp_table (char_col)
      VALUES ('Nice and cozy');
  ELSIF v_NumberSeats < 100 THEN
    v_Comment := 'A little bigger';
    INSERT INTO temp_table (char_col)
      VALUES ('Some breathing room');
  ELSE
    v_Comment := 'Lots of room';
  END IF;
END;
/