03-examp.sql

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

REM 03-EXAMP.SQL
REM This file contains the examples used in Chapter 3 of
REM "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 3: SELECT Example ***
DECLARE
  v_StudentRecord  students%ROWTYPE;
  v_Department     classes.department%TYPE;
  v_Course         classes.course%TYPE;
BEGIN
  -- Retrieve one record from the students table, and store it
  -- in v_StudentRecord. Note that the WHERE clause will only
  -- match one row in the table.
  -- Note also that the query is returning all of the fields in
  -- the students table (since we are selecting *). Thus the
  -- record into which we fetch is defined as students%ROWTYPE.
  SELECT *
    INTO v_StudentRecord
    FROM students
    WHERE id = 10000;

  -- Retrieve two fields from the classes table, and store them
  -- in v_Department and v_Course. Again, the WHERE clause will
  -- only match one row in the table.
  SELECT department, course
    INTO v_Department, v_Course
    FROM classes
    WHERE room_id = 99997;
END;
/

REM *** Chapter 3: INSERT Example ***
DECLARE
  v_StudentID  students.id%TYPE;
BEGIN
  -- Retrieve a new student ID number
  SELECT student_sequence.NEXTVAL
    INTO v_StudentID
    FROM dual;

  -- Add a row to the students table
  INSERT INTO students (id, first_name, last_name)
    VALUES (v_StudentID, 'Timothy', 'Taller');

  -- Add a second row, but use the sequence number directly
  -- in the INSERT statement.
  INSERT INTO students (id, first_name, last_name)
    VALUES (student_sequence.NEXTVAL, 'Patrick', 'Poll');
END;
/

REM *** Chapter 3: UPDATE Example ***
DECLARE
  v_Major           students.major%TYPE;
  v_CreditIncrease  NUMBER := 3;
BEGIN
  -- This UPDATE statement will add 3 to the current_credits
  -- field of all students who are majoring in History.
  v_Major := 'History';
  UPDATE students
    SET current_credits = current_credits + v_CreditIncrease
    WHERE major = V_Major;
END;
/

REM *** Chapter 3: DELETE Example ***
DECLARE
  v_StudentCutoff  NUMBER;
BEGIN
  v_StudentCutoff := 10;
  -- Delete any classes which don't have enough students registered.
  DELETE FROM classes
    WHERE current_students < v_StudentCutoff;

  -- Delete any Economics students who don't have any credits yet.
  DELETE FROM students
    WHERE current_credits = 0
    AND   major = 'Economics';
END;
/