Bulk Query
You use BULK COLLECT to bulk bind queries. Here's the syntax:
... BULK COLLECT INTO collection_name [, collection_name] ...
where collection_name is the name of the index-by table, nested table, or VARRAY.
You can use BULK COLLECT in SELECT … INTO, FETCH INTO, and RETURNING INTO clauses.
| Tip� | You can reference multiple collections in the INTO list. |
I first discuss BULK COLLECT using a SELECT … INTO statement. I follow this with a discussion of BULK COLLECT in FETCH INTO and RETURNING INTO clauses.
Using BULK COLLECT in SELECT … INTO
You can use BULK COLLECT in a SELECT … INTO statement. Here's the syntax:
SELECT column_name BULK COLLECT INTO collection_name;
where column_name is the name of the column being fetched by the SELECT and collection_name is the name of the index-by table, nested table, or VARRAY.
As an example, consider the update_dyn_global procedure presented in Chapter 7. I've reproduced it here for your reference:
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;
/
You can rewrite this procedure using BULK COLLECT. I call this modified procedure update_dyn_global_bulk. Here's the code:
Create or Replace Procedure update_dyn_global_bulk
(retcd OUT NUMBER,
errmsg OUT VARCHAR2)
authid current_user
Is
TYPE NameTbl IS TABLE OF region_tab.region_name%TYPE;
Region_names NameTbl;
BEGIN
SELECT region_name BULK COLLECT INTO region_names
FROM region_tab ORDER BY region_name;
FOR i IN region_names.FIRST..region_names.LAST LOOP
update_dyn_for_all_orders(region_names(i), 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 an index-by table type named NameTbl of type region_tab.region_name%TYPE and then declares an index-by table named region_names of this type.
Bulk queries the column region_name into this region_names index-by table using the SELECT … BULK COLLECT INTO statement.
For each element in the region_names index-by table, it invokes the update_dyn_for_all_orders procedure, passing the region name as an input parameter.
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.
The primary advantage of using BULK COLLECT is that it maximizes performance by reducing the number of context switches between the SQL and PL/SQL engines.
The SQL engine bulk binds entire database columns. So, column values corresponding to all the rows are loaded into the collection. However, you can restrict the number of rows to be loaded by using ROWNUM while selecting the records. Here's the code to do so:
SELECT region_name BULK COLLECT INTO region_names WHERE ROWNUM < 51;
| Tip� | The SQL engine automatically initializes and extends referenced collections in the BULK COLLECT clause. It starts filling the collections at index 1 and inserts elements consecutively, overwriting any existing values. The resulting collection is a dense collection without any nonexisting intermediate elements. |
Using BULK COLLECT in Fetching
You can also use BULK COLLECT when fetching multiple rows from a cursor. Here's the syntax:
FETCH cursor_name BULK COLLECT INTO collection_name;
where cursor_name is the name of the cursor being fetched and collection_name is the name of the index-by table, nested table, or VARRAY.
I've altered the procedure update_dyn_global_bulk created earlier as follows to illustrate bulk fetch from a cursor into a collection. I call this modified procedure update_dyn_global_bulk2. Here's the code:
Create or Replace Procedure update_dyn_global_bulk2
(retcd OUT NUMBER,
errmsg OUT VARCHAR2)
authid current_user
Is
TYPE NameTbl IS TABLE OF region_tab.region_name%TYPE;
Region_names NameTbl;
CURSOR csr_region_names IS
SELECT region_name FROM region_tab ORDER BY region_name;
BEGIN
OPEN csr_region_names;
FETCH csr_region_names BULK COLLECT INTO region_names;
FOR i IN region_names.FIRST..region_names.LAST LOOP
update_dyn_for_all_orders(region_names(i), retcd, errmsg);
IF retcd <> 0 THEN
EXIT;
END IF;
END LOOP;
CLOSE csr_region_names;
EXCEPTION WHEN OTHERS THEN
Retcd := SQLCODE;
Errmsg := SQLERRM;
END;
/
This procedure does the following:
Declares an index-by table type named NameTbl of type region_tab.region_name%TYPE and then declares an index-by table named region_names of this type.
Declares a cursor named csr_region_names to SELECT an ordered set of region names from the region_tab table.
Opens the cursor and bulk fetches the cursor into the region_names index- by table using the FETCH … BULK COLLECT INTO statement.
For each element in the region_names index-by table, it invokes the update_dyn_for_all_orders procedure, passing the region name as an input parameter.
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.
| Tip� | You can bulk fetch into one or more collections. As of Oracle9i Release 2, you can bulk fetch into a collection of records (for example, an index-by table of records). This is also true when you use native dynamic SQL. |
| Tip� | You can't use the SELECT … BULK COLLECT statement in a FORALL statement. |
Using BULK COLLECT in RETURNING INTO
You can also use BULK COLLECT in a FORALL statement as part of the RETURNING INTO clause. Here's the syntax:
FORALL index IN lower_bound..upper_bound [SAVE EXCEPTIONS]
sql_statement
RETURNING column_name INTO collection_name;
where index is the index of collection element; lower_bound and upper_bound are the starting and ending values of the index numbers for accessing the elements of the collection; and sql_statement is an INSERT, UPDATE, or DELETE statement. Also, column_name is the name of the modified column being returned into a collection named collection_name. Here's an example:
DECLARE
Type region_id_tbl IS TABLE of NUMBER INDEX BY BINARY_INTEGER;
Type region_name_tbl IS TABLE of VARCHAR2(20) INDEX BY BINARY_INTEGER;
region_ids region_id_tbl;
region_names region_name_tbl;
out_region_names region_name_tbl;
ret_code NUMBER;
ret_errmsg VARCHAR2(1000);
Procedure load_regions_bulk_bind
(region_ids IN region_id_tbl,
region_names IN region_name_tbl,
retcd OUT NUMBER,
errmsg OUT VARCHAR2)
Is
BEGIN
-- clean up the sec_region_tab table initially.
DELETE FROM sec_region_tab;
FORALL i IN region_ids.FIRST..region_ids.LAST
INSERT INTO sec_region_tab values (region_ids(i), region_names(i));
Retcd := 0;
EXCEPTION WHEN OTHERS THEN
Retcd := SQLCODE;
Errmsg := SQLERRM;
END;
BEGIN
FOR i IN 1..5 LOOP
Region_ids(i) := i;
Region_names(i) := 'REGION'||i;
END LOOP;
Load_regions_bulk_bind(region_ids, region_names, ret_code, ret_errmsg);
IF (ret_code <> 0) THEN
RAISE_APPLICATION_ERROR(-20111, SQLERRM);
END IF;
FORALL i IN 1..5
UPDATE sec_region_tab
SET region_name = 'NEW '||region_name
WHERE region_id = region_ids(i)
RETURNING region_name BULK COLLECT INTO out_region_names;
FOR i in out_region_names.FIRST..out_region_names.LAST LOOP
DBMS_OUTPUT.PUT_LINE(out_region_names(i));
END LOOP;
EXCEPTION WHEN OTHERS THEN
RAISE_APPLICATION_ERROR(-20112, SQLERRM);
END;
/
Here's the output of the preceding program:
This program declares two index-by table types named region_id_tbl and region_name_tbl of the NUMBER and VARCHAR2 types, respectively. Then it declares two index-by tables named region_ids and region_names of these two index-by table types, respectively. It also declares a second region names index- by table named out_region_names to hold the multiple region names returned by a RETURNING INTO clause, and it declares a local procedure named load_regions_bulk_bind that takes as input two index-by tables of types region_id_tbl and region_name_tbl and does the following:
Cleans up the sec_region_tab table initially.
INSERTS into the sec_region_tab table using the bulk DML FORALL statement taking the input for region_id from the region_ids index-by table and the input for region_name from the region_names index-by table. This single statement inserts five rows into the sec_region_tab table in a single database call.
COMMITS the inserted rows.
The code 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. The body of the PL/SQL block populates the two index-by tables region_ids and region_names with five region IDs and five region names. Then it invokes the load_regions_bulk_bind procedure that performs the bulk bind.
This code then updates the region_name column in the sec_region_tab table with a new value for all the rows and uses the RETURNING … BULK COLLECT INTO clause to return the updated column values into the out_region_names index-by table. Finally, it prints the values in the out_region_names index-by table.
| Tip� | As of Oracle9i Release 2, BULK COLLECT into a PL/SQL table of records in RETURNING INTO is allowed. |
Host Arrays and Bulk DML
The use of host arrays is recommended for client-side programs to exchange data between client-side programs and the database. Anonymous PL/SQL blocks with an enclosing BEGIN … END are used to bulk bind input and output host arrays.
Host arrays are arrays declared in a host language such as C or C++. To identify host arrays in a PL/SQL program you specify them by prefixing the array name with a colon (:). Here's an example:
EXEC SQL EXECUTE
BEGIN
FORALL i IN :lower_bound..:upper_bound
DELETE FROM region_tab WHERE region_id = :region_ids(i);
... ...
END;
where :region_ids is the host array declared in Pro*C or the host environment.
| Tip� | Since host arrays are not PL/SQL arrays, you can't use methods such as FIRST and LAST with host arrays. |
No comments:
Post a Comment