Dynamic INSERT, UPDATE, and DELETE

In addition to DDL statements and DML single-row SELECT statements, you can construct INSERT, UPDATE, and DELETE statements dynamically and execute them at runtime.

You use the statements


EXECUTE IMMEDIATE ;
EXECUTE IMMEDIATE  INTO 
USING ;

to execute dynamic INSERT, UPDATE, and DELETE statements. The dynamic SQL string can also have a RETURNING clause if needed. As an example, the update_dyn_table procedure created earlier used an UPDATE statement using native dynamic SQL. Here's the code for your reference:


Create or replace Procedure update_dyn_table
                              (i_region_name VARCHAR2, i_order_id NUMBER,
                               retcd OUT NUMBER, errmsg OUT VARCHAR2)
authid current_user
Is
  Dyn_upd_string1 VARCHAR2(1000);
  Dyn_query_string VARCHAR2(1000);
  Dyn_Table_name1 VARCHAR2(30);
  Dyn_Table_name2 VARCHAR2(30);
  V_total_price NUMBER;
  V_total_quantity NUMBER;
Begin
  Dyn_table_name1 := 'ORDER_ITEMS_FOR_'||replace(trim(i_region_name),' ','_');
  Dyn_table_name2 := 'ORDERS_FOR_'||i_region_name;
  Dyn_query_string := ' SELECT SUM(quantity), SUM(unit_price*quantity) FROM '||
                                       dyn_table_name1||
                                      ' WHERE order_id = :input_order_id ';
  Dyn_upd_string1 := ' UPDATE '||dyn_table_name2||
               ' SET total_qty = :total_qty, total_price = :total_price WHERE '||
                         ' order_id = :input_order_id ';

   EXECUTE IMMEDIATE dyn_query_string INTO v_total_price, v_total_quantity
   USING i_order_id;
   EXECUTE IMMEDIATE dyn_upd_string1
     USING v_total_quantity, v_total_price, i_order_id;
   retcd := 0;
EXCEPTION WHEN OTHERS THEN
  retcd := SQLCODE;
  errmsg := 'ERR: '||SQLERRM';
End;
/

Also, you can insert data into the ORDERS_FOR_ and ORDER_ITEMS_FOR_ tables using dynamic INSERT statements.

In the case of dynamic INSERT, UPDATE, and DELETE statements executed using EXECUTE IMMEDIATE, the implicit cursor attribute SQL%ROWCOUNT is available and returns the number of rows processed by the INSERT, UPDATE, or DELETE.

ITPub博客

Native Dynamic SQL and Dynamic PL/SQL(5)

最新文章