02-rt.sql

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

REM 02-RT.SQL
REM This file contains the record and table examples
REM used in Chapter 2 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 2: Using Records ***
DECLARE
  -- Define a record to match some fields in the students table.
  -- Note the use of %TYPE for the fields.
  TYPE t_StudentRecord IS RECORD (
    FirstName  students.first_name%TYPE,
    LastName   students.last_name%TYPE,
    Major      students.major%TYPE);

  -- Declare a variable to receive the data.
  v_Student  t_StudentRecord;
BEGIN
  -- Retrieve information about student with ID 10,000.
  -- Note how the query is returning columns which match the
  -- fields in v_Student.
  SELECT first_name, last_name, major
    INTO v_Student
    FROM students
    WHERE ID = 10000;
END;
/

REM *** Chapter 2: The COUNT Table Attribute ***
DECLARE
  TYPE t_NumberTable IS TABLE OF NUMBER
    INDEX BY BINARY_INTEGER;
  v_Numbers t_NumberTable;
  v_Total NUMBER;
BEGIN
  -- Insert 50 rows into the table.
  FOR v_Counter IN 1..50 LOOP
    v_Numbers(v_Counter) := v_Counter;
  END LOOP;

  v_Total := v_Numbers.COUNT;
END;
/

REM *** Chapter 2: The DELETE Table Attribute ***
DECLARE
  TYPE t_ValueTable IS TABLE OF VARCHAR2(10)
    INDEX BY BINARY_INTEGER;
  v_Values t_ValueTable;
BEGIN
  -- Insert rows into the table.
  v_Values(1) := 'One';
  v_Values(3) := 'Three';
  v_Values(-2) := 'Minus Two';
  v_Values(0) := 'Zero';
  v_Values(100) := 'Hundred';
  
  v_Values.DELETE(100);  -- Removes 'Hundred'
  v_Values.DELETE(1,3);  -- Removes 'One' and 'Three'
  v_Values.DELETE;       -- Removes all remaining values
END;
/

REM *** Chapter 2: The EXISTS Table Attribute ***
DECLARE
  t_FirstNameTable IS TABLE OF students.first_name%TYPE
    INDEX BY BINARY_INTEGER;
  FirstNames  t_FirstNameTable;
BEGIN
  -- Insert rows into the table.
  FirstNames(1) := 'Scott';
  FirstNames(3) := 'Joanne';

  -- Check to see if rows exist.
  IF FirstNames.EXISTS(1) THEN
    INSERT INTO temp_table (char_col) VALUES
      ('Row 1 exists!');
  ELSE
    INSERT INTO temp_table (char_col) VALUES
      ('Row 1 doesn't exist!');
  END IF;
  IF FirstNames.EXISTS(2) THEN
    INSERT INTO temp_table (char_col) VALUES
      ('Row 2 exists!');
  ELSE
    INSERT INTO temp_table (char_col) VALUES
      ('Row 2 doesn''t exist!');
  END IF;
END;
/

REM *** Chapter 2: FIRST & LAST Table Attributes ***
DECLARE
  t_LastNameTable IS TABLE OF students.last_name%TYPE
    INDEX BY BINARY_INTEGER;
  v_LastNames  t_LastNameTable;
  v_Index  BINARY_INTEGER;
BEGIN
  -- Insert rows in the table.
  v_LastNames(43) := 'Mason';
  v_LastNames(50) := 'Junebug';
  v_LastNames(47) := 'Taller';

  -- Assigns 43 to v_Index.
  v_Index := v_LastNames.FIRST;

  -- Assigns 50 to v_Index.
  v_Index := v_LastNames.LAST;
END;
/

REM *** Chapter 2: NEXT & PRIOR Table Attributes ***
DECLARE
  TYPE t_MajorTable IS TABLE OF students.major
    INDEX BY BINARY_INTEGER;
  v_Majors t_MajorTable;
  v_Index  BINARY_INTEGER;
BEGIN
  -- Insert values into the table.
  v_Majors(-7) := 'Computer Science';
  v_Majors(4) := 'History';
  v_Majors(5) := 'Economics';

  -- Loop over all the rows in the table, and insert them into
  -- temp_table.
  v_Index := v_Majors.FIRST;
  LOOP
    INSERT INTO temp_table (num_col, char_col)
      VALUES (v_Index, v_Majors(v_Index));
    EXIT WHEN v_Index = v_Majors.LAST;
    v_Index := v_Majors.NEXT(v_Index);
  END LOOP;
END;
/