04-loop.sql

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

REM 04-LOOP.SQL
REM This file contains the cursor fetch loop examples used
REM in Chapter 4 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 4: LOOP..END LOOP Cursor Loop ***
DECLARE
  -- Declare variables to hold information about the students
  -- majoring in History.
  v_StudentID   students.id%TYPE;
  v_FirstName   students.first_name%TYPE;
  v_LastName    students.last_name%TYPE;

  -- Cursor to retrieve the information about History students
  CURSOR c_HistoryStudents IS
    SELECT id, first_name, last_name
      FROM students
      WHERE major = 'History';
BEGIN
  -- Open the cursor and initialize the active set
  OPEN c_HistoryStudents;
  LOOP
    -- Retrieve information for the next student
    FETCH c_HistoryStudents INTO v_StudentID, v_FirstName, v_LastName;

    -- Exit loop when there are no more rows to fetch
    EXIT WHEN c_HistoryStudents%NOTFOUND;

    -- Process the fetched rows, in this case sign up each
    -- student for History 301 by inserting them into the 
    -- registered_students table. Record the first and last
    -- names in temp_table as well.
    INSERT INTO registered_students (student_id, department, course)
      VALUES (v_StudentID, 'HIS', 301);

    INSERT INTO temp_table (num_col, char_col)
      VALUES (v_StudentID, v_FirstName || ' ' || v_LastName);

  END LOOP;

  -- Free resources used by the cursor
  CLOSE c_HistoryStudents;

  -- Commit our work
  COMMIT;
END;
/

REM *** Chapter 4: WHILE..LOOP Cursor Loop ***
DECLARE
  -- Cursor to retrieve the information about History students
  CURSOR c_HistoryStudents IS
    SELECT id, first_name, last_name
      FROM students
      WHERE major = 'History';

  -- Declare a record to hold the fetched information.
  v_StudentData  c_HistoryStudents%ROWTYPE;
BEGIN
  -- Open the cursor and initialize the active set
  OPEN c_HistoryStudents;

  -- Retrieve the first row, to set up for the WHILE loop
  FETCH c_HistoryStudents INTO v_StudentData;

  -- Continue looping while there are more rows to fetch
  WHILE c_HistoryStudents%FOUND LOOP
    -- Process the fetched rows, in this case sign up each
    -- student for History 101 by inserting them into the 
    -- registered_students table. Record the first and last
    -- names in temp_table as well.
    INSERT INTO registered_students (student_id, department, course)
      VALUES (v_StudentData.ID, 'HIS', 101);

    INSERT INTO temp_table (num_col, char_col)
      VALUES (v_StudentData.ID,
              v_StudentData.first_name || ' ' || v_StudentData.last_name);

    -- Retrieve the next row. The %FOUND condition will be checked
    -- before the loop continues again.
    FETCH c_HistoryStudents INTO v_StudentData;
  END LOOP;

  -- Free resources used by the cursor
  CLOSE c_HistoryStudents;

  -- Commit our work
  COMMIT;
END;
/

REM *** Chapter 4: Cursor FOR Loop ***
DECLARE
  -- Cursor to retrieve the information about History students
  CURSOR c_HistoryStudents IS
    SELECT id, first_name, last_name
      FROM students
      WHERE major = 'History';
BEGIN
  -- Begin the loop. An implicit OPEN of c_HistoryStudents
  -- is done here.
  FOR v_StudentData IN c_HistoryStudents LOOP
    -- An implicit FETCH is done here.

    -- Process the fetched rows, in this case sign up each
    -- student for History 101 by inserting them into the 
    -- registered_students table. Record the first and last
    -- names in temp_table as well.
    INSERT INTO registered_students (student_id, department, course)
      VALUES (v_StudentData.ID, 'HIS', 101);

    INSERT INTO temp_table (num_col, char_col)
      VALUES (v_StudentData.ID,
              v_StudentData.first_name || ' ' || v_StudentData.last_name);

    -- Before the loop will continue, an implicit check of
    -- c_HistoryStudents is done here.
  END LOOP;
  -- Now that the loop is finished, an implicit CLOSE of 
  -- c_HistoryStudents is done.

  -- Commit our work.
  COMMIT;
END;
/

REM *** Chapter 4: Using WHERE CURRENT OF ***
DECLARE
  -- Number of credits to add to each student's total
  v_NumCredits  classes.num_credits%TYPE;

  -- This cursor will select only those students who are currently
  -- registered for HIS 101.
  CURSOR c_RegisteredStudents IS
    SELECT *
      FROM students
      WHERE id IN (SELECT student_id
                     FROM registered_students
                     WHERE department= 'HIS'
                     AND course = 101)
      FOR UPDATE OF current_credits;

BEGIN
  -- Set up the cursor fetch loop.
  FOR v_StudentInfo IN c_RegisteredStudents LOOP
  -- Determine the number of credits for HIS 101.
  SELECT num_credits
    INTO v_NumCredits
    FROM classes
    WHERE department = 'HIS'
    AND course = 101;

  -- Update the row we just retrieved from the cursor.
  UPDATE students
    SET current_credits = current_credits + v_NumCredits
    WHERE CURRENT OF c_RegisteredStudents;
  END LOOP;

  -- Commit our work.
  COMMIT;
END;
/

REM *** Chapter 4: Fetching Across Commits ***
DECLARE
  -- Cursor to retrieve all students, and lock the rows as well.
  CURSOR c_AllStudents IS
    SELECT *
      FROM students
      FOR UPDATE;

  -- Variable for retrieved data.
  v_StudentInfo  c_AllStudents%ROWTYPE;
BEGIN
  -- Open the cursor. This will acquire the locks.
  OPEN c_AllStudents;

  -- Retrieve the first record.
  FETCH c_AllStudents INTO v_StudentInfo;

  -- Issue a COMMIT. This will release the locks, invalidating the
  -- cursor.
  COMMIT WORK;

  -- This FETCH will raise the ORA-1002 error.
  FETCH c_AllStudents INTO v_StudentInfo;
END;
/

REM *** Chapter 4: Fetching Across Commits, Example 2 ***
DECLARE
  -- Number of credits to add to each student's total
  v_NumCredits  classes.num_credits%TYPE;

  -- This cursor will select only those students who are currently
  -- registered for HIS 101.
  CURSOR c_RegisteredStudents IS
    SELECT *
      FROM students
      WHERE id IN (SELECT student_id
                     FROM registered_students
                     WHERE department= 'HIS'
                     AND course = 101);

BEGIN
  -- Set up the cursor fetch loop.
  FOR v_StudentInfo IN c_RegisteredStudents LOOP
  -- Determine the number of credits for HIS 101.
  SELECT num_credits
    INTO v_NumCredits
    FROM classes
    WHERE department = 'HIS'
    AND course = 101;

  -- Update the row we just retrieved from the cursor.
  UPDATE students
    SET current_credits = current_credits + v_NumCredits
    WHERE id = v_Studentinfo.id;

  -- We can commit inside the loop, since the cursor is
  -- not declared FOR UPDATE.
  COMMIT;
  END LOOP;
END;
/