10-examp.sql

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

REM 10-EXAMP.SQL
REM This files contains the examples from Chapter 10 of
REM "Oracle PL/SQL Programming".

REM This is version 1.0 of this file, update 2/18/96.
REM Comments and questions should go to Scott Urman, at
REM [e-mail].


REM *** Chapter 10: First DBMS_SQL Example ***
CREATE OR REPLACE PROCEDURE RecreateTempTable (
  /* Drops temp_table and recreates it. The table description
     is passed in with p_Description, and should be the contents
     of the CREATE TABLE statement, after the table name. For
     example, the following is a legal call:

     RecreateTempTable('(num_col NUMBER, char_col VARCHAR2(50))');
  */
  p_Description IN VARCHAR2) IS

  v_Cursor        NUMBER;
  v_CreateString  VARCHAR2(100);
  v_DropString    VARCHAR2(100);
  v_NumRows       INTEGER; 
BEGIN
  /* Open the cursor for processing. */
  v_Cursor := DBMS_SQL.OPEN_CURSOR;

  /* Drop the table first. */
  v_DropString := 'DROP TABLE temp_table';

  /* Parse and execute the 'DROP TABLE' command. Trap the 
     ORA-942 error in case the table doesn't yet exist. */
  BEGIN
    -- DBMS_SQL.V7 is a constant defined in the package header.
    DBMS_SQL.PARSE(v_Cursor, v_DropString, DBMS_SQL.V7);
    v_NumRows := DBMS_SQL.EXECUTE(v_Cursor);
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE != -942 THEN
        RAISE;
      END IF;
  END;
  /* Now recreate it. We need to create the CREATE TABLE
     string first, then parse and execute it. */
  v_CreateString := 'CREATE TABLE temp_table ' || p_Description;
  DBMS_SQL.PARSE(v_Cursor, v_CreateString, DBMS_SQL.V7);
  v_NumRows := DBMS_SQL.EXECUTE(v_Cursor);

  /* Close the cursor, now that we are finished. */
  DBMS_SQL.CLOSE_CURSOR(v_Cursor);
EXCEPTION
  WHEN OTHERS THEN
    /* Close the cursor first, then reraise the error so it is
       propagated out. */
    DBMS_SQL.CLOSE_CURSOR(v_Cursor);
    RAISE;
END RecreateTempTable;
/

REM *** Chapter 10: Non-Query DML and DDL Statements ***
CREATE OR REPLACE PROCEDURE DeleteMajor(
  /* Uses DBMS_SQL to delete all students with the specified
     major from students. The number of rows deleted is
     returned in p_RowsDeleted. */
  p_Major       IN students.major%TYPE,
  p_RowsDeleted OUT INTEGER) AS

  v_CursorID   INTEGER;
  v_DeleteStmt VARCHAR2(100);
BEGIN
  -- Open the cursor for processing.
  v_CursorID := DBMS_SQL.OPEN_CURSOR;

  -- Determine the SQL string.
  v_DeleteStmt := 'DELETE FROM students WHERE major = :m';

  -- Parse the statement.
  DBMS_SQL.PARSE(v_CursorID, v_DeleteStmt, DBMS_SQL.V7);

  -- Bind p_Major to the placeholder.
  DBMS_SQL.BIND_VARIABLE(v_CursorID, ':m', p_Major);

  -- Execute the statement.
  p_RowsDeleted := DBMS_SQL.EXECUTE(v_CursorID);

  -- Close the cursor.
  DBMS_SQL.CLOSE_CURSOR(v_CursorID);
EXCEPTION
  WHEN OTHERS THEN
    -- Close the cursor, then raise the error again.
    DBMS_SQL.CLOSE_CURSOR(v_CursorID);
    RAISE;
END DeleteMajor;
/

REM *** Chapter 10: Executing Queries ***
CREATE OR REPLACE PROCEDURE DynamicQuery (
  /* Uses DBMS_SQL to query the students table, and puts the
     results in temp_table. The first names, last names, and
     majors are inserted for up to two majors inputted. */
  p_Major1 IN students.major%TYPE DEFAULT NULL,
  p_Major2 IN students.major%TYPE DEFAULT NULL) AS

  v_CursorID   INTEGER;
  v_SelectStmt VARCHAR2(500);
  v_FirstName  students.first_name%TYPE;
  v_LastName   students.last_name%TYPE;
  v_Major      students.major%TYPE;
  v_Dummy      INTEGER;

BEGIN
  -- Open the cursor for processing.
  v_CursorID := DBMS_SQL.OPEN_CURSOR;

  -- Create the query string.
  v_SelectStmt := 'SELECT first_name, last_name, major
                     FROM students
                     WHERE major IN (:m1, :m2)
                     ORDER BY major, last_name';

  -- Parse the query.
  DBMS_SQL.PARSE(v_CursorID, v_SelectStmt, DBMS_SQL.V7);

  -- Bind the input variables.
  DBMS_SQL.BIND_VARIABLE(v_CursorID, ':m1', p_Major1);
  DBMS_SQL.BIND_VARIABLE(v_CursorID, ':m2', p_Major2);

  -- Define the output variables.
  DBMS_SQL.DEFINE_COLUMN(v_CursorID, 1, v_FirstName, 20);
  DBMS_SQL.DEFINE_COLUMN(v_CursorID, 2, v_LastName, 20);
  DBMS_SQL.DEFINE_COLUMN(v_CursorID, 3, v_Major, 30);

  -- Execute the statement. We don't care about the return
  -- value, but we do need to declare a variable for it.
  v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);

  -- This is the fetch loop.
  LOOP
    -- Fetch the rows into the buffer, and also check for the exit
    -- condition from the loop.
    IF DBMS_SQL.FETCH_ROWS(v_CursorID) = 0 THEN
      EXIT;
    END IF;

    -- Retrieve the rows from the buffer into PL/SQL variables.
    DBMS_SQL.COLUMN_VALUE(v_CursorID, 1, v_FirstName);
    DBMS_SQL.COLUMN_VALUE(v_CursorID, 2, v_LastName);
    DBMS_SQL.COLUMN_VALUE(v_CursorID, 3, v_Major);

    -- Insert the fetched data into temp_table.
    INSERT INTO temp_table (char_col)
      VALUES (v_FirstName || ' ' || v_LastName || ' is a ' ||
              v_Major || ' major.');
  END LOOP;

  -- Close the cursor.
  DBMS_SQL.CLOSE_CURSOR(v_CursorID);

  -- Commit our work.
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    -- Close the cursor, then raise the error again.
    DBMS_SQL.CLOSE_CURSOR(v_CursorID);
    RAISE;
END DynamicQuery;
/

REM *** Chapter 10: Executing PL/SQL Blocks ***
CREATE OR REPLACE PROCEDURE DynamicPLSQL (
  /* Executes a PL/SQL block dynamically. The block 
     selects from students, and uses p_StudentID as an
     input placeholder. */
  p_StudentID IN students.ID%TYPE) IS

  v_CursorID  INTEGER;
  v_BlockStr  VARCHAR2(500);
  v_FirstName students.first_name%TYPE;
  v_LastName  students.last_name%TYPE;
  v_Dummy     INTEGER;

BEGIN
  -- Open the cursor for processing.
  v_CursorID := DBMS_SQL.OPEN_CURSOR;

  -- Create the string containing the PL/SQL block.
  -- In this string, the :first_name and :last_name
  -- placeholders are output variables, and :ID is an
  -- input variable.
  v_BlockStr := 
    'BEGIN
       SELECT first_name, last_name
         INTO :first_name, :last_name
         FROM students
         WHERE ID = :ID;
     END;';

  -- Parse the statement.
  DBMS_SQL.PARSE(v_CursorID, v_BlockStr, DBMS_SQL.V7);

  -- Bind the placeholders to the variables. Note that we
  -- do this for both the input and output variables.
  -- We pass the maximum length for :first_name and
  -- :last_name.
  DBMS_SQL.BIND_VARIABLE(v_CursorID, ':first_name', v_FirstName, 30);
  DBMS_SQL.BIND_VARIABLE(v_CursorID, ':last_name', v_LastName, 30);
  DBMS_SQL.BIND_VARIABLE(v_CursorID, ':ID', p_StudentID);

  -- Execute the statement. We don't care about the return
  -- value, but we do need to declare a variable for it.
  v_Dummy := DBMS_SQL.EXECUTE(v_CursorID);

  -- Retrieve the values for the output variables.
  DBMS_SQL.VARIABLE_VALUE(v_CursorID, ':first_name', v_FirstName);
  DBMS_SQL.VARIABLE_VALUE(v_CursorID, ':last_name', v_LastName);

  -- Insert them into temp_table.
  INSERT INTO temp_table (num_col, char_col)
    VALUES (p_StudentID, v_FirstName || ' ' || v_LastName);

  -- Close the cursor.
  DBMS_SQL.CLOSE_CURSOR(v_CursorID);

  -- Commit our work.
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    -- Close the cursor, then raise the error again.
    DBMS_SQL.CLOSE_CURSOR(v_CursorID);
    RAISE;
END DynamicPLSQL;
/