Single-Row SELECTS

The statement

EXECUTE IMMEDIATE  INTO 
USING ;

is used to execute dynamic single-row SELECTS.

Suppose you want to update the order_tab information with the latest total quantity and total price for a given order for all possible regions. In this case, you require a dynamic SELECT that returns a single row, the summation of quantity and price for an input order_id.

The procedure update_dyn_table uses a dynamic SELECT that returns a single row. Here's the code for this procedure:

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_quantit
   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;
/

This procedure does the following:

• Creates two dynamic table names concatenating the input region name with the constants ORDER_ITEMS_FOR_ and ORDERS_FOR_ and places them in two PL/SQL variables named dyn_table_name1 and dyn_table_name2. This process is for the order items table and the orders table for each region, respectively.

• Creates a dynamically generated SELECT statement using the dyn_table_name1 variable and places it in a PL/SQL variable named dyn_query_string. This process is for selecting the total quantity and total price from the ORDER_ITEMS_FOR_ table for each input order_id. It uses a bind variable called :input_order_id for the input order_id. Each SELECT returns only one row corresponding to the input order_id.

• Creates a dynamically generated UPDATE statement using the dyn_table_name2 variable and places it in a PL/SQL variable named dyn_upd_string1. This process is for updating the total_qty and total_price columns in the ORDERS_FOR_ table for each order_id. It uses a bind variable called :input_order_id for the input order_id. It also uses two more bind variables named :total_qty and :total_price for setting the values of the total quantity and total price for each order.

• Executes these two dynamic strings using the EXECUTE IMMEDIATE statement. This statement retrieves the total quantity and total price for each order_id from the ORDER_ITEMS_FOR_ table and updates the corresponding record in the ORDERS_FOR_ 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.

Multirow SELECTS

Multirow SELECTS are handled in a separate manner from single-row SELECTS. With static SQL, multirow SELECTS were handled either by declaring explicit cursors or using cursor variables. Also, the cursor variables used could be instances of strong or weak REF CURSORs. With native dynamic SQL, you don't need to use a cursor variable with hard-coded queries. Prior to Oracle8i, cursor variables were restricted in that they could only be opened for static queries. For example, the following declaration was allowed:

DECLARE
    TYPE dynamic_rc IS REF CURSOR;
    v_rc dynamic_rc;
BEGIN
    OPEN v_rc FOR SELECT order_id FROM order_items_for_region1;
    ...
    OPEN v_rc FOR SELECT order_id FROM order_items_for_region2;
    ...
END;

This way of using OPEN has the disadvantage of opening the cursor variable multiple times for each region involved if all the regions have to be processed. As you can see from the previous example, this involves writing and maintaining a lot of code. With native dynamic SQL, the cursor variable has to be opened once only. Here's what a portion of this code looks like:

DECLARE
    TYPE dynamic_rc IS REF CURSOR;
    v_rc dynamic_rc;
    Dyn_table_name VARCHAR2(100);
    Dyn_query_string VARCHAR2(1000);
BEGIN
    Dyn_table_name := 'ORDER_ITEMS_FOR_'||i_region_name;
    Dyn_query_string := 'SELECT order_id FROM '||dyn_table_name;
    OPEN v_rc FOR dyn_query_string;

    ...
END;

Here, i_region_name is assumed to be a variable that's assigned dynamically with a region name.

The way to handle multirow queries with native dynamic SQL is to use a weak REF CURSOR. A weak REF CURSOR points to a work area in the System Global Area (SGA) for holding a resultset. A weak type REF CURSOR is declared and OPENed for the dynamic query string. The normal FETCHing in a LOOP and the CLOSE operation follow the opening of the cursor. Here are the steps:

1. Declare a weak REF CURSOR type.

2. Declare a cursor variable of this type.

3. Construct the dynamic query string.

4. Open the cursor variable for this dynamic query string. Any values for bind variables specified in the USING clause are evaluated in this step.

5. FETCH in a loop the rows one by one and process them. Fetching into records of type %ROWTYPE or user-defined records is allowed.

6. Close the cursor variable.

The statements

OPEN  FOR ;
OPEN  FOR 
USING ;
FETCH  INTO ;
CLOSE ;

are used to execute dynamic multirow SELECTS.

As an example, consider the procedure update_dyn_table created earlier, which accepts a particular region_name and order_id. You can extend this procedure to be processed for all orders and in a particular region. Here's the code:

Create or replace Procedure update_dyn_for_all_orders
                           (i_region_name VARCHAR2,
                            retcd OUT NUMBER,
                            errmsg OUT VARCHAR2)
Is
  Dyn_table_name VARCHAR2(100);
  Dyn_query_string VARCHAR2(1000);
  Type csr_dyn IS REF CURSOR;
   csr_dyn1 csr_dyn;
  v_order_id NUMBER;
BEGIN
  Dyn_table_name := 'ORDER_ITEMS_FOR_'||replace(trim(i_region_name),' ','_');
  Dyn_query_string := 'SELECT order_id FROM '||dyn_table_name;
  OPEN csr_dyn1 FOR dyn_query_string;
  LOOP
    FETCH csr_dyn1 INTO v_order_id;
    EXIT WHEN csr_dyn1%NOTFOUND;
    update_dyn_table(i_region_name, v_order_id, 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 type definition of type REF CURSOR named csr_dyn and a cursor variable named csr_dyn1 as an instance of the csr_dyn type. This section of the code is for processing a multirow query and is later opened for a query.

• Creates a dynamic table name by 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 SELECT statement using the dyn_table_name variable and places it in a PL/SQL variable named dyn_query_string. This process is for selecting all order_id column values from the ORDER_ITEMS_FOR_ table. This SELECT returns multiple rows from each ORDER_ITEMS_FOR_ table.

• Executes this dynamic string using the OPEN-FOR and FETCH … INTO statements. First the REF CURSOR variable is opened for the dynamic SELECT. Next, the cursor variable is fetched into a local variable named v_order_id. Then, for each row returned by the query, update_dyn_table is executed, passing the input region name and the fetched order_id as inputs. This procedure retrieves the total quantity and total price for each order_id from the ORDER_ITEMS_FOR_ table and updates the corresponding record in the ORDERS_FOR_ 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.

You can further extend this procedure to encompass all orders in all regions. I call this procedure update_dyn_global. The logic for this procedure doesn't require dynamic SQL. For all regions in the region_tab, it loops through to execute update_dyn_for_all_orders listed previously with the region_name pointing to the current cursor row as the first input parameter. Here's the code:

Create or replace Procedure update_dyn_global
                          (retcd OUT NUMBER,
                           errmsg OUT VARCHAR2)
Is
  CURSOR csr_region IS
     SELECT region_name FROM region_tab;
BEGIN
   FOR idx IN csr_region LOOP
    update_dyn_for_all_orders(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 update_dyn_for_all_orders procedure, passing the region name as an IN parameter to both.

• 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.