dbms_sql.sql

介绍Oracle PL SQL编程

    fdbk                      INTEGER;
    statement                 VARCHAR2(2000);

    -- Define a local function to ensure sequence does not exist.
    FUNCTION verify_sequence
      ( sequence_name_in      IN     VARCHAR2)
    RETURN BOOLEAN IS

      -- Defines default return value.
      retval                  BOOLEAN := FALSE;

      -- Cursor returns a single row when finding a sequence.
      CURSOR find_sequence IS
        SELECT   null
        FROM     user_objects
        WHERE    object_name = sequence_name_in;
      
    BEGIN

      -- The for-loop sets the Boolean when a sequence is found.
      FOR i IN find_sequence LOOP
        retval := TRUE;
      END LOOP;

      -- Return Boolean state.
      RETURN retval;

    END verify_sequence;

  BEGIN

    -- If sequence exists delete it.
    IF verify_sequence(sequence_name) = TRUE THEN

      -- Build dynamic SQL statement.
      statement := 'DROP SEQUENCE '||sequence_name;

      -- Parse and execute the statement.
      dbms_sql.parse(c,statement,dbms_sql.native);
      fdbk := dbms_sql.execute(c);

      -- Close the open cursor.
      dbms_sql.close_cursor(c);

      -- Print module name message.
      dbms_output.put_line(
        '-> dbms_sql_tutorial.drop_sequence');

      -- Print line break.
      dbms_output.put_line(sline);

      -- Print output line.
      dbms_output.put_line(
        'Dropped Sequence <'||sequence_name||'>');

    ELSE

      -- Print module name message.
      dbms_output.put_line(
        '-> dbms_sql_tutorial.drop_sequence');

      -- Print line break.
      dbms_output.put_line(sline);

      -- Print output line.
      dbms_output.put_line(
        'Sequence <'||sequence_name||'> does not exists');

    END IF;

  END drop_sequence;

  /*
  || ------------------------------------------------------------------
  */

  -- Procedure drops a table using concatenation.
  PROCEDURE drop_table
    ( table_name              IN     VARCHAR2) IS

    -- Define local DBMS_SQL variables.
    c                         INTEGER := dbms_sql.open_cursor;
    fdbk                      INTEGER;
    statement                 VARCHAR2(2000);

    -- Define a local function to ensure table does exist.
    FUNCTION verify_table
      ( object_name_in        IN     VARCHAR2)
    RETURN BOOLEAN IS

      -- Defines default return value.
      retval                  BOOLEAN := FALSE;

      -- Cursor returns a single row when finding a table.
      CURSOR find_object IS
        SELECT   null
        FROM     user_objects
        WHERE    object_name = object_name_in;
      
    BEGIN

      -- The for-loop sets the Boolean when a table is found.
      FOR i IN find_object LOOP
        retval := TRUE;
      END LOOP;

      -- Return Boolean state.
      RETURN retval;

    END verify_table;

  BEGIN

    IF verify_table(table_name) = TRUE THEN

      -- Build dynamic SQL statement.
      statement := 'DROP TABLE '||table_name;

      -- Parse and execute the statement.
      dbms_sql.parse(c,statement,dbms_sql.native);
      fdbk := dbms_sql.execute(c);

      -- Close the open cursor.
      dbms_sql.close_cursor(c);

      -- Print module name message.
      dbms_output.put_line('
        -> dbms_sql_tutorial.drop_table');

      -- Print line break.
      dbms_output.put_line(sline);

      -- Print output message.
      dbms_output.put_line(
        'Dropped Table <'||table_name||'>');

    ELSE

      -- Print module name message.
      dbms_output.put_line(
        '-> dbms_sql_tutorial.drop_table');

      -- Print line break.
      dbms_output.put_line(sline);

      -- Print output message.
      dbms_output.put_line(
        'Object <'||table_name||'> does not exist');

    END IF;

  END drop_table;

  /*
  || ------------------------------------------------------------------
  */

  -- Procedure encapsulates a PL/SQL block SELECT-INTO.
  PROCEDURE increment_sequence
    ( sequence_name           IN     VARCHAR2
    , sequence_value          IN OUT NUMBER  ) IS

    -- Define local DBMS_SQL variables.
    c                         INTEGER := dbms_sql.open_cursor;
    fdbk                      INTEGER;
    statement                 VARCHAR2(2000);

    -- Define a local function to ensure sequence does not exist.
    FUNCTION verify_sequence
      ( sequence_name_in      IN     VARCHAR2)
    RETURN BOOLEAN IS

      -- Defines default return value.
      retval                  BOOLEAN := FALSE;

      -- Cursor returns a single row when finding a sequence.
      CURSOR find_sequence IS
        SELECT   null
        FROM     user_objects
        WHERE    object_name = sequence_name_in;
      
    BEGIN

      -- The for-loop sets the Boolean when a sequence is found.
      FOR i IN find_sequence LOOP
        retval := TRUE;
      END LOOP;

      -- Return Boolean state.
      RETURN retval;

    END verify_sequence;

  BEGIN

    IF verify_sequence(sequence_name) = TRUE THEN

      /*
      || Debugging Tip:
      || =============
      || When you are using a SELECT-INTO-FROM within DBMS_SQL, which is a
      || reserved PLSQL syntax not directly supported by DBMS_SQL.  You  
      || need to encapsulate it in a PLSQL wrapper.  When you use a PLSQL
      || wrapper, the semicolons must be used in the statement and the
      || PLSQL block because DBMS_SQL adds a single semicolon to execute
      || the PLSQL block.  If you forget to encapsulate the SQL in a
      || PLSQL wrapper, you will raise the following error message.
      || -------------------------------------------------------------------
      || ORA-01006: bind variable does not exist
      */

      -- Build dynamic SQL statement as anonymous block PL/SQL unit.
      statement := 'BEGIN'||CHR(10)
                || ' SELECT PLSQL.'||sequence_name||'.nextval'||CHR(10)
                || ' INTO   :retval'||CHR(10)
                || ' FROM   DUAL;'||CHR(10)
                || 'END;';

      -- Parse the statement.
      dbms_sql.parse(c,statement,dbms_sql.native);

      /*
      || Technical Note:
      || ==============
      || The BIND_VARIABLE procedure is returning a NUMBER
      || and does not require parameter four.
      */

      -- Bind variable retval to an output sequence value.
      dbms_sql.bind_variable(c,'retval',sequence_value);

      -- Execute the dynamic cursor.
      fdbk := dbms_sql.execute(c);

      -- Copy the variable value from the bind variable.
      dbms_sql.variable_value(c,'retval',sequence_value);

      -- Close the open cursor.
      dbms_sql.close_cursor(c);

      -- Print module name message.
      dbms_output.put(
        'Sequence <'||sequence_name||'> ');

      -- Print output message.
      dbms_output.put_line(
        'Value <'||sequence_value||'>');

    ELSE

      -- Print module name message.
      dbms_output.put_line(
        '-> dbms_sql_tutorial.increment_sequence');

      -- Print line break.
      dbms_output.put_line(sline);

      -- Print output message.
      dbms_output.put_line(
        'Sequence <'||sequence_name||'> does not exist');

    END IF;

  END increment_sequence;

  /*
  || ------------------------------------------------------------------
  */

  -- Procedure demonstrates a DML with ordered bind variables.
  PROCEDURE insert_into_table
    ( table_name              IN     VARCHAR2
    , table_column_value1     IN     NUMBER
    , table_column_value2     IN     VARCHAR2
    , table_column_value3     IN     VARCHAR2) IS

    -- Define local DBMS_SQL variables.
    c                         INTEGER := dbms_sql.open_cursor;
    fdbk                      INTEGER;
    statement                 VARCHAR2(2000);

    -- Define a local function to ensure table does exist.
    FUNCTION verify_table
      ( object_name_in        IN     VARCHAR2)
    RETURN BOOLEAN IS

      -- Defines default return value.
      retval                  BOOLEAN := FALSE;

      -- Cursor returns a single row when finding a table.
      CURSOR find_object IS
        SELECT   null
        FROM     user_objects
        WHERE    object_name = object_name_in;
      
    BEGIN

      -- The for-loop sets the Boolean when a table is found.
      FOR i IN find_object LOOP
        retval := TRUE;
      END LOOP;

      -- Return Boolean state.
      RETURN retval;

    END verify_table;

  BEGIN

    -- If table exists insert into it.
    IF verify_table(table_name) = TRUE THEN

      /*
      || Debugging Tip:
      || =============
      || Statement strings are terminated by a line return CHR(10) to
      || ensure that a space is not missing between concatenated segments.
      || Using a BIND variable provides efficiencies in SQL statements
      || because it avoids the reparsing of the statement.  Therefore,
      || they should be used as follows for performance gains:
      ||
      ||   SQL STATEMENTS    PREDICATES
      ||   --------------    ----------
      ||   SELECT            WHERE
      ||   UPDATE            SET
      ||                     WHERE
      ||   DELETE            WHERE
      ||
      || Error Explanations:
      || ------------------
      || 1. An explicit size is always required for a VARCHAR2 variable
      ||    and the overloaded procedure has an output size variable in the
      ||    fourth position that you may need to use.  The output length is
      ||    provided below to demonstrate it.
      || 2. A bad bind variable message typically means the identifier is
      ||    outside of the VARCHAR2 string and treated as a session level
      ||    undefined bind variable.
      || 3. A "missing SELECT keyword" can occur on an insert statement
      ||    if you put bind variables into the INTO clause for column
      ||    names.
      || 4. If you have quote marks around VARCHAR2 bind variables, you
      ||    may raise the "bind variable does not exist" error.  If you
      ||    need to use that syntax, you can encapsulate the DML in a
      ||    PLSQL wrapper.
      || -------------------------------------------------------------------
      || 1. ORA-06502: PL/SQL: numeric or value error
      || 2. PLS-00049: bad bind variable
      || 3. ORA-00928: missing SELECT keyword
      || 4. ORA-01006: bind variable does not exist
      */

      -- Build dynamic SQL statement.
      statement := 'INSERT '
                || 'INTO '||table_name||' '
                || 'VALUES '
                || '( :table_column_value1'
                || ', :table_column_value2'
                || ', :table_column_value3)';

      -- Parse the statement.
      dbms_sql.parse(c,statement,dbms_sql.native);

      -- Bind each bind variable.
      dbms_sql.bind_variable(c,'table_column_value1',table_column_value1);
      dbms_sql.bind_variable(c,'table_column_value2',table_column_value2);
      dbms_sql.bind_variable(c,'table_column_value3',table_column_value3);

      -- Execute the dynamic statement.
      fdbk := dbms_sql.execute(c);

      -- Close the open cursor.
      dbms_sql.close_cursor(c);

      -- Commit the records.
      commit;

      -- Print module name message.
      dbms_output.put_line(
        '-> dbms_sql_tutorial.insert_into_table');

      -- Print line break.
      dbms_output.put_line(sline);

      -- Print output messages.
      dbms_output.put_line(
        'Value inserted <'||table_column_value1||'>');
      dbms_output.put_line(
        'Value inserted <'||table_column_value2||'>');
      dbms_output.put_line(
        'Value inserted <'||table_column_value3||'>');

    ELSE

      -- Print module name message.
      dbms_output.put_line(
        '-> dbms_sql_tutorial.insert_into_table');

      -- Print line break.
      dbms_output.put_line(sline);

      -- Print output message.
      dbms_output.put_line(
        'Object <'||table_name||'> does not exist');

    END IF;

  END insert_into_table;

  /*
  || ------------------------------------------------------------------
  */

  -- Procedure demonstrates a DML with ordered bind variables.
  PROCEDURE inserts_into_table
    ( table_name              IN     VARCHAR2
    , table_column_values1    IN     DBMS_SQL.NUMBER_TABLE
    , table_column_values2    IN     DBMS_SQL.VARCHAR2_TABLE
    , table_column_values3    IN     DBMS_SQL.VARCHAR2_TABLE) IS

    -- Define local DBMS_SQL variables.
    c                         INTEGER := dbms_sql.open_cursor;
    fdbk                      INTEGER;
    statement                 VARCHAR2(2000);

    -- Define a local function to ensure table does exist.
    FUNCTION verify_table
      ( object_name_in        IN     VARCHAR2)
    RETURN BOOLEAN IS

      -- Defines default return value.
      retval                  BOOLEAN := FALSE;

      -- Cursor returns a single row when finding a table.
      CURSOR find_object IS
        SELECT   null
        FROM     user_objects
        WHERE    object_name = object_name_in;