Native Dynamic SQL and Dynamic PL/SQL(3)

Dynamic DDL involves using any of the DDL statements with values for database objects provided at runtime. This eliminates the need for compile time semantics. A typical usage of dynamic DDL might be to create a table where the table and/or column names are available at runtime.

The statements

EXECUTE IMMEDIATE ;
EXECUTE IMMEDIATE  USING ;

are used to execute dynamic DLL statements.

The requirement is to create one orders_for_ table for each region that stores the order information for that region. This is done in three steps:

1. The procedure create_dyn_table creates the individual orders_for_ table using native dynamic SQL.

2. The procedure create_dyn_table2 creates the individual order_items_for_ table using native dynamic SQL.

3. The procedure create_dyn_for_all calls these two procedures for each region in region_tab.

Here's the code for these procedures:

Create or replace Procedure create_dyn_table
                              (i_region_name VARCHAR2,
                               retcd OUT NUMBER,
                              errmsg OUT VARCHAR2)
authid current_user
Is
  Dyn_string VARCHAR2(1000);
  Dyn_Table_name VARCHAR2(30);
Begin
  Dyn_table_name := 'ORDERS_FOR_'||replace(trim(i_region_name),' ','_');
  Dyn_string := ' CREATE TABLE ' ||dyn_table_name||
' (order_id NUMBER(10) PRIMARY KEY,
  order_date DATE    NOT NULL,
  total_qty NUMBER,
  total_price NUMBER(15,2) ,
  supp_id NUMBER(6) REFERENCES supplier_tab(supp_id)) ';
   EXECUTE IMMEDIATE dyn_string ;

   retcd := 0;

EXCEPTION WHEN OTHERS THEN
  retcd := SQLCODE;
  errmsg := 'ERR: Creating table '||dyn_table_name ||' - '||SQLERRM;
End;
/

This procedure does the following:

• Creates a dynamic table name concatenating the input region name with the constant ORDERS_FOR_ and places it in a PL/SQL variable named dyn_table_name. This is for the orders table for each region.

• Creates a dynamically generated CREATE TABLE statement using the dyn_table_name variable and places it in a PL/SQL variable named dyn_string. The table structure is the same as that of the ORDER_TAB table.

• Executes this dynamic string using the EXECUTE IMMEDIATE statement.

• Outputs a return code of 0 on success in the form. of an OUT parameter named retcd. On error it outputs the corresponding SQLCODE and SQLERRM in the form. of two OUT parameters, retcd and errmsg.

Next, here's the code for the create_dyn_table2 procedure:

Create or replace Procedure create_dyn_table2
                               (i_region_name VARCHAR2,
                                retcd OUT NUMBER,
                               errmsg OUT VARCHAR2)
authid current_user
Is
  Dyn_string VARCHAR2(1000);
  Dyn_Table_name VARCHAR2(30);
Begin
  Dyn_table_name := 'ORDER_ITEMS_FOR_'|| replace(trim(i_region_name),' ','_');
  Dyn_string := ' CREATE TABLE ' ||dyn_table_name||
 ' (order_id NUMBER(10) NOT NULL,
item_id VARCHAR2(10) NOT NULL,
unit_price NUMBER(11,2) NOT NULL,
quantity NUMBER) ';
   EXECUTE IMMEDIATE dyn_string ;

   dyn_string :=
            ' alter table ORDER_ITEMS_FOR_'||i_region_name|| ' add constraint '||
            'fk_oifor'||i_region_name||' foreign key (order_id) references '||
                   ' orders_for_'||i_region_name||'(order_id) ';
   EXECUTE IMMEDIATE dyn_string ;
   retcd := 0;
EXCEPTION WHEN OTHERS THEN
   retcd := SQLCODE;
   errmsg := 'ERR: Creating/Altering table '||dyn_table_name ||' - '||SQLERRM;
End;
/

This procedure does the following:

• Creates a dynamic table name concatenating the input region name with the constant ORDER_ITEMS_FOR_ and places it in a PL/SQL variable named dyn_table_name. This process is for the order items table for each region.

• Creates a dynamically generated CREATE TABLE statement using the dyn_table_name variable and places it in a PL/SQL variable named dyn_string. The table structure is the same as that of the ORDER_ITEMS table.

• Executes this dynamic string using the EXECUTE IMMEDIATE statement.

• Creates a second dynamically generated ALTER TABLE statement using the dyn_table_name variable and places it in the same variable dyn_string. This process is for specifying foreign key constraints on the corresponding ORDER_ITEMS_FOR_ table.

• Executes this dynamic string using the EXECUTE IMMEDIATE statement.

• Outputs a return code of 0 on success in the form. of an OUT parameter named retcd. On error it outputs the corresponding SQLCODE and SQLERRM in the form. of two OUT parameters, retcd and errmsg.

Next, here's the code for the create dyn_for_all procedure:

Create or replace Procedure create_dyn_for_all
                                       (retcd OUT NUMBER, errmsg OUT VARCHAR2)
Authid current_user
Is
  CURSOR csr_region IS
    SELECT region_name
    FROM   region_tab;
BEGIN
  FOR idx IN csr_region LOOP
    create_dyn_table(idx.region_name, retcd, errmsg);
    IF retcd <> 0 THEN
      EXIT;
    END IF;
    create_dyn_table2(idx.region_name, retcd, errmsg);
    IF retcd <> 0 THEN
      EXIT;
    END IF;
  END LOOP;
EXCEPTION WHEN OTHERS THEN
  Retcd := SQLCODE;
  Errmsg := SQLERRM;
END;
/

This procedure does the following:

• Declares a cursor for all the region names in the region_tab table.

• For each row in the cursor, it invokes the create_dyn_table and create_dyn_table2 procedures, passing the region name as an IN parameter to both. This creates the ORDERS_FOR_ and ORDER_ITEMS_FOR_ tables, where stands for each region name in the region_tab table.

• Outputs a return code of 0 on success in the form. of an OUT parameter named retcd. On error it outputs the corresponding SQLCODE and SQLERRM in the form. of two OUT parameters, retcd and errmsg.

Here's the code for executing this procedure:

DECLARE
  Retcd NUMBER;
  Errmsg VARCHAR2(32767);
BEGIN
  Create_dyn_for_all(retcd, errmsg);
  IF (retcd <> 0) THEN
    RAISE_APPLICATION_ERROR(-20102, errmsg);
  END IF;
EXCEPTION WHEN OTHERS THEN
  RAISE_APPLICATION_ERROR(-20103, SQLERRM);
END;
/

Here's how you verify the output of this PL/SQL block:

SQL> column object_name format a30;
SQL> select object_name, object_type from user_objects
  2 where object_name like 'ORDER%FOR%';

OBJECT_NAME                  OBJECT_TYPE
---------------------------- ----------------------
ORDERS_FOR_REGION1           TABLE
ORDERS_FOR_REGION2           TABLE
ORDERS_FOR_REGION3           TABLE
ORDERS_FOR_REGION4           TABLE
ORDER_ITEMS_FOR_REGION1      TABLE
ORDER_ITEMS_FOR_REGION2      TABLE
ORDER_ITEMS_FOR_REGION3      TABLE
ORDER_ITEMS_FOR_REGION4      TABLE

8 rows selected.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/15225049/viewspace-628433/，如需转载，请注明出处，否则将追究法律责任。