dbms_sql.sql

介绍Oracle PL SQL编程

      
    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||' '
                || '( card_number '
                || ', card_name '
                || ', card_suit)'
                || 'VALUES '
                || '( :card_number'
                || ', :card_name'
                || ', :card_suit)';

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

      -- Bind each bind variable.
      dbms_sql.bind_array(c,'card_number',table_column_values1);
      dbms_sql.bind_array(c,'card_name',table_column_values2);
      dbms_sql.bind_array(c,'card_suit',table_column_values3);

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

      -- Print the number of rows inserted.
      dbms_output.put_line('Inserted ['||fdbk||'].');

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

      -- Commit the records.
      commit;

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

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

      -- Use a for-loop to print values.
      FOR i IN 1..table_column_values1.COUNT LOOP

        -- Print output message.
        dbms_output.put_line(
          'Value inserted <'||table_column_values1(i)||'>');
        dbms_output.put_line(
          'Value inserted <'||table_column_values2(i)||'>');
        dbms_output.put_line(
          'Value inserted <'||table_column_values3(i)||'>');

      END LOOP;

    ELSE

      -- Print module name message.
      dbms_output.put_line(
        '-> dbms_sql_tutorial.inserts_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 inserts_into_table;

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

  -- Procedure demonstrates multiple row DQL.
  PROCEDURE multiple_row_return IS

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

  BEGIN

    -- Build dynamic SQL statement.
    statement := 'SELECT ''A'' FROM DUAL';

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

    /*
    || Debugging Tip:
    || =============
    || Define the column values and DO NOT forget to assign a size
    || parameter for a string datatype, like VARCHAR2; however, if you
    || forget, the error message is:
    || -------------------------------------------------------------------
    || PLS-00307: too many declarations of 'DEFINE_COLUMN' match this call
    */

    -- Define the column mapping to the value_out variable.
    dbms_sql.define_column(c,1,value_out,1);

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

    -- Use a loop to read all rows.
    LOOP

      -- Exit when no more rows to fetch.
      EXIT WHEN dbms_sql.fetch_rows(c) = 0;

      -- Copy the contents of column #1 to the value_out variable.
      dbms_sql.column_value(c,1,value_out);

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

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

      -- Print output message.
      dbms_output.put_line('Value from COLUMN_VALUE <'||value_out||'>');

    END LOOP;

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

  END multiple_row_return;

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

  -- Procedure demonstrates multiple row with columns DQL.
  PROCEDURE multiple_row_return
    ( table_name    VARCHAR2
    , column_name1  VARCHAR2
    , column_name2  VARCHAR2
    , column_name3  VARCHAR2 )IS

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

  BEGIN

    -- Build dynamic SQL statement.
    statement := 'SELECT '
              ||  column_name1 ||','
              ||  column_name2 ||','
              ||  column_name3 ||' '
              || 'FROM '|| table_name;

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

    /*
    || Debugging Tip:
    || =============
    || Define the column values and DO NOT forget to assign a size
    || parameter for a string datatype, like VARCHAR2; however, if you
    || forget, the error message is:
    || -------------------------------------------------------------------
    || PLS-00307: too many declarations of 'DEFINE_COLUMN' match this call
    */

    -- Define the column mapping to the value_out variable.
    dbms_sql.define_column(c,1,nvalue_out);
    dbms_sql.define_column(c,2,cvalue_out1,2000);
    dbms_sql.define_column(c,3,cvalue_out2,2000);

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

    -- Use a loop to read all rows.
    LOOP

      -- Exit when no more rows to fetch.
      EXIT WHEN dbms_sql.fetch_rows(c) = 0;

      -- Copy the contents of column #1 to the value_out variable.
      dbms_sql.column_value(c,1,nvalue_out);
      dbms_sql.column_value(c,2,cvalue_out1);
      dbms_sql.column_value(c,3,cvalue_out2);

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

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

      -- Print output message.
      dbms_output.put_line(
        'Value from ['||column_name1||'] '||
        'is: ['||nvalue_out||']');
      dbms_output.put_line(
        'Value from ['||column_name1||'] '||
        'is: ['||SUBSTR(cvalue_out1,1,5)||']');
      dbms_output.put_line(
        'Value from ['||column_name1||'] '||
        'is: ['||SUBSTR(cvalue_out2,1,8)||']');

    END LOOP;

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

  END multiple_row_return;

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

  /*
  || Demonstrate a single row return using the DEFINE_COLUMN and COLUMN_VALUE
  || program unit, as you would in an explicit cursor.
  */
  -- Procedure single row DQL.
  PROCEDURE single_row_return IS

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

  BEGIN

    -- Build dynamic SQL statement.
    statement := 'SELECT ''A'' FROM DUAL';

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

    /*
    || Debugging Tip:
    || =============
    || Define the column values and DO NOT forget to assign a size
    || parameter for a string datatype, like VARCHAR2; however, if you
    || forget, the error message is:
    || -------------------------------------------------------------------
    || PLS-00307: too many declarations of 'DEFINE_COLUMN' match this call
    ||
    || This is the message returned because the DEFINE_COLUMN procedure 
    || is overloaded and it doesn't know how to implicitly cast without
    || the OUT_VALUE_SIZE argument. Only CHAR, RAW and VARCHAR2 support
    || a fourth argument.
    */

    -- Define the column mapping to the value_out variable.
    dbms_sql.define_column(c,1,value_out,1);

    -- Execute dynamic SQL statement.
    fdbk := dbms_sql.execute_and_fetch(c);

    -- Copy the contents of column #1 to the value_out variable.
    dbms_sql.column_value(c,1,value_out);

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

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

    -- Print output message.
    dbms_output.put_line(
      'Value from COLUMN_VALUE <'||value_out||'>');

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

  END single_row_return;

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

  -- Procedure demonstrates single row DQL.
  PROCEDURE single_row_return
    ( table_name    VARCHAR2
    , column_name1  VARCHAR2
    , column_name2  VARCHAR2
    , column_name3  VARCHAR2 ) IS

    -- Define local DBMS_SQL variables.
    c                         INTEGER := dbms_sql.open_cursor;
    fdbk                      INTEGER;
    statement                 VARCHAR2(2000);
    cvalue_out1               VARCHAR2(20);
    cvalue_out2               VARCHAR2(30);
    nvalue_out                NUMBER;

  BEGIN

    -- Build dynamic SQL statement.
    statement := 'SELECT '
              ||  column_name1 ||','
              ||  column_name2 ||','
              ||  column_name3 ||' '
              || 'FROM '|| table_name;

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

    /*
    || Debugging Tip:
    || =============
    || Define the column values and DO NOT forget to assign a size
    || parameter for a string datatype, like VARCHAR2; however, if you
    || forget, the error message is:
    || -------------------------------------------------------------------
    || PLS-00307: too many declarations of 'DEFINE_COLUMN' match this call
    ||
    || This is the message returned because the DEFINE_COLUMN procedure 
    || is overloaded and it doesn't know how to implicitly cast without
    || the OUT_VALUE_SIZE argument. Only CHAR, RAW and VARCHAR2 support
    || a fourth argument.
    */

    -- Define the column mapping to the value_out variable.
    dbms_sql.define_column(c,1,nvalue_out);
    dbms_sql.define_column(c,2,cvalue_out1,20);
    dbms_sql.define_column(c,3,cvalue_out2,30);

    -- Execute dynamic SQL statement.
    fdbk := dbms_sql.execute_and_fetch(c);

    -- Copy the contents of column #1 to the value_out variable.
    dbms_sql.column_value(c,1,nvalue_out);
    dbms_sql.column_value(c,2,cvalue_out1);
    dbms_sql.column_value(c,3,cvalue_out2);

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

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

    -- Print output message.
    dbms_output.put_line(
      'Value from COLUMN_VALUE <'||nvalue_out||'>');
    dbms_output.put_line(
      'Value from COLUMN_VALUE <'||cvalue_out1||'>');
    dbms_output.put_line(
      'Value from COLUMN_VALUE <'||cvalue_out2||'>');

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

  END single_row_return;

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

END dbms_sql_tutorial;
/

-- ==========================================================================
--  This is a debugging and log management technique for capturing the code
--  attempted to be compiled as a specification and then any error messages.
--  You would remark these out when your code is production ready and then
--  remove the remarking comments when debugging changes to your code.
-- ==========================================================================

SPOOL dbms_sql_body.log

list

show errors

SPOOL OFF