04-intro.sql

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

REM 04-INTRO.SQL
REM This file contains the introductory examples used in
REM Chapter 4 of "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 4: First Cursor Example ***
DECLARE
  /* Output variables to hold the results of the query */
  v_StudentID    students.id%TYPE;
  v_FirstName    students.first_name%TYPE;
  v_LastName     students.last_name%TYPE;

  /* Bind variable used in the query */
  v_Major        students.major%TYPE := 'Computer Science';

  /* Cursor declaration */
  CURSOR c_Students IS
    SELECT id, first_name, last_name
      FROM students
      WHERE major = v_Major;
BEGIN
  /* Identify the rows in the active set, and prepare for further
     processing of the data */
  OPEN c_Students;
  LOOP
    /* Retrieve each row of the active set into PL/SQL variables */
    FETCH c_Students INTO v_StudentID, v_FirstName, v_LastName;

    /* If there are no more rows to fetch, exit the loop */
    EXIT WHEN c_Students%NOTFOUND;
  END LOOP;

  /* Free resources used by the query */
  CLOSE c_Students;
END;
/

REM *** Chapter 4: OPEN Example ***
DECLARE
  v_RoomID      classes.room_id%TYPE;
  v_Building    rooms.building%TYPE;
  v_Department  classes.department%TYPE;
  v_Course      classes.course%TYPE;
  CURSOR c_Buildings IS
    SELECT building
      from rooms, classes
      where rooms.room_id = classes.room_id
      and department = v_Department
      and course = v_Course;
BEGIN
  -- Assign to bind variables before the cursor OPEN.
  v_Department := 'HIS';
  v_Course := 101;

  -- Open the cursor.
  OPEN c_Buildings;

  -- Reassign the bind variables - this has no effect,
  -- since the cursor is already open.
  v_Department := 'XXX';
  v_Course := -1;
END;
/

REM *** Chapter 4: FETCH Example ***
DECLARE
  v_Department  classes.department%TYPE;
  v_Course      classes.course%TYPE;
  CURSOR c_AllClasses IS
    SELECT *
      FROM classes;
  v_ClassesRecord  c_AllClasses%ROWTYPE;
BEGIN
  OPEN c_AllClasses;

  -- This is a legal FETCH statement, returning the first
  -- row into a PL/SQL record which matches the select list
  -- of the query.
  FETCH c_AllClasses INTO v_ClassesRecord;

  -- This FETCH statement is illegal, since the select list
  -- of the query returns all 7 columns in the classes table
  -- but we are only fetching into 2 variables.
  FETCH c_AllClasses INTO v_Department, v_Course;
END;
/

REM *** Chapter 4: Implicit Cursor Attributes Example 1 ***
BEGIN
  UPDATE rooms
    SET number_seats = 100
    WHERE room_id = 99980;
  -- If the previous UPDATE statement didn't match any rows, 
  -- insert a new row into the rooms table.
  IF SQL%NOTFOUND THEN
    INSERT INTO rooms (room_id, number_seats)
      VALUES (99980, 100);
  END IF;
END;
/

REM *** Chapter 4: Implicit Cursor Attributes Example 2 ***
BEGIN
  UPDATE rooms
    SET number_seats = 100
    WHERE room_id = 99980;
  -- If the previous UPDATE statement didn't match any rows, 
  -- insert a new row into the rooms table.
  IF SQL%ROWCOUNT = 0 THEN
    INSERT INTO rooms (room_id, number_seats)
      VALUES (99980, 100);
  END IF;
END;
/

REM *** Chapter 4: NO_DATA_FOUND vs. %NOTFOUND
DECLARE
  -- Record to hold room information.
  v_RoomData   rooms%ROWTYPE;
BEGIN
  -- Retrieve information about room ID -1.
  SELECT *
    INTO v_RoomData
    FROM rooms
    WHERE room_id = -1;

  -- The following statement will never be executed, since
  -- control passes immediately to the exception handler.
  IF SQL%NOTFOUND THEN
    INSERT INTO temp_table (char_col)
      VALUES ('Not found!');
  END IF;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    INSERT INTO temp_table (char_col)
      VALUES ('Not found, exception handler');
END;
/