01-examp.sql

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

REM 01-EXAMP.SQL
REM This file contains the examples used in Chapter 1 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 1: PL/SQL Block Example 1 ***
DECLARE
  /* Declare variables which will be used in SQL statements */
  v_NewMajor VARCHAR2(10) := 'History';
  v_FirstName VARCHAR2(10) := 'Scott';
  v_LastName VARCHAR2(10) := 'Urman';
BEGIN
  /* Update the students table. */
  UPDATE students
    SET major = v_NewMajor
    WHERE first_name = v_FirstName
    AND last_name = v_LastName;
  /* Check to see if the record was found.  If not, then we need
     to insert this record. */
  IF SQL%NOTFOUND THEN
    INSERT INTO students (ID, first_name, last_name, major)
      VALUES (10020, v_FirstName, v_LastName, v_NewMajor);
  END IF;
END;
/

REM *** Chapter 1: PL/SQL Block Example 2 ***
DECLARE
  v_LoopCounter BINARY_INTEGER := 1;
BEGIN
  LOOP
    INSERT INTO temp_table (num_col)
      VALUES (v_LoopCounter);
    v_LoopCounter := v_LoopCounter + 1;
    EXIT WHEN v_LoopCounter > 50;
  END LOOP;
END;
/

REM *** Chapter 1: PL/SQL Block Example 3 ***
BEGIN
  FOR v_LoopCounter IN 1..50 LOOP
    INSERT INTO temp_table (num_col)
      VALUES (v_LoopCounter);
  END LOOP;
END;
/

REM *** Chapter 1: PL/SQL Block Example 4 ***
DECLARE
  v_FirstName VARCHAR2(20);
  v_LastName  VARCHAR2(20);
  -- Cursor declaration.  This defines the SQL statement to
  -- return the rows.
  CURSOR c_Students IS
    SELECT first_name, last_name
      FROM students;
BEGIN
  -- Begin cursor processing.
  OPEN c_Students;
  LOOP
    -- Retreive one row.
    FETCH c_Students INTO v_FirstName, v_LastName;
    -- Exit the loop after all rows have been retreived.
    EXIT WHEN c_Students%NOTFOUND;
    /* Process data here */
  END LOOP;
  -- End processing.
  CLOSE c_Students;
END; 
/