|
|
Dynamic SQL and Bulk
Operations
Oracle Tips by Burleson Consulting
|
The following Tip is from the
outstanding book "Oracle
PL/SQL Tuning: Expert Secrets for High Performance Programming" by
Dr. Tim Hall, Oracle ACE of the year, 2006:
The majority of the operations discussed in
the previous sections are also available via dynamic SQL when using
the following supported statements:
-
EXECUTE IMMEDIATE – This command can
include a BULK COLLECT INTO or a RETURNING BULK COLLECT INTO clause.
-
FETCH – This command can include a
BULK COLLECT INTO clause.
-
FORALL – The EXECUTE IMMEDIATE
statement can be called as part of a FORALL statement with
subscripted collection elements passed with the USING clause.
The collections specified in the USING and
RETURNING clauses must represent individual column types, while
collections of records are supported for BULK COLLECT INTO clauses of
queries.
The dynamic_forall.sql script provides an
example of using a FORALL statement to perform a bulk EXECUTE
IMMEDIATE statement.
dynamic_forall.sql
DECLARE
TYPE t_object_id_tab IS TABLE OF bulk_collect_test.object_id%TYPE;
l_tab
t_object_id_tab;
BEGIN
-- Populate collection use in forall.
SELECT object_id
BULK COLLECT INTO l_tab
FROM bulk_collect_test
WHERE rownum < 101;
FORALL i IN l_tab.first .. l_tab.last
EXECUTE IMMEDIATE
'UPDATE bulk_collect_test
SET object_id = object_id
WHERE object_id = :1'
USING l_tab(i);
END;
/
The dynamic_forall.sql script defines a
collection, populates it with some data from the test table and
performs a bulk dynamic update.
Both the EXECUTE IMMEDIATE and the FETCH
statements can be used populate collections from dynamic queries using
the BULK COLLECT clause, as shown in the dynamic_bulk_collect.sql
script listed below.
dynamic_bulk_collect.sql
SET
SERVEROUTPUT ON
DECLARE
TYPE t_bulk_collect_test_tab IS TABLE OF bulk_collect_test%ROWTYPE;
l_tab
t_bulk_collect_test_tab;
l_cursor SYS_REFCURSOR;
BEGIN
OPEN l_cursor FOR 'SELECT * FROM bulk_collect_test';
FETCH l_cursor
BULK COLLECT INTO l_tab;
CLOSE l_cursor;
DBMS_OUTPUT.put_line('Dynamic FETCH : ' || l_tab.count);
EXECUTE IMMEDIATE 'SELECT * FROM bulk_collect_test'
BULK COLLECT INTO l_tab;
DBMS_OUTPUT.put_line('Dynamic EXECUTE: ' || l_tab.count);
END;
/
The results of the dynamic_bulk_collect.sql
script show that both the FETCH and the EXECUTE IMMEDIATE methods
return the same results.
SQL> @dynamic_bulk_collect.sql
Dynamic FETCH : 61204
Dynamic EXECUTE: 61204
PL/SQL
procedure successfully completed.
The dynamic_returning_bulk_collect.sql script
shows how the RETURNING clause can be used in a dynamic bulk
operation.
dynamic_returning_bulk_collect.sql
SET
SERVEROUTPUT ON
DECLARE
TYPE t_object_id_tab IS TABLE OF bulk_collect_test.object_id%TYPE;
l_tab
t_object_id_tab;
BEGIN
EXECUTE IMMEDIATE
'DELETE FROM bulk_collect_test
RETURNING object_id INTO :1'
RETURNING BULK COLLECT INTO l_tab;
DBMS_OUTPUT.put_line('Deleted IDs : ' || l_tab.count || ' rows');
ROLLBACK;
END;
/
As expected the collection is populated with
the IDs of the deleted rows.
SQL> @dynamic_returning_bulk_collect.sql
Deleted IDs : 61204 rows
PL/SQL
procedure successfully completed.
SQL>
Finally, the
dynamic_forall_returning_bulk_collect.sql script combines most of
these options into a single script to show their interaction.
dynamic_forall_returning_bulk_collect.sql
SET
SERVEROUTPUT ON
DECLARE
TYPE t_object_id_tab IS TABLE OF bulk_collect_test.object_id%TYPE;
l_in_tab t_object_id_tab;
l_out_tab t_object_id_tab;
BEGIN
-- Populate collection use in forall.
EXECUTE IMMEDIATE
'SELECT object_id
FROM bulk_collect_test
WHERE rownum < 101'
BULK COLLECT INTO l_in_tab;
FORALL i IN l_in_tab.first .. l_in_tab.last
EXECUTE IMMEDIATE
'DELETE FROM bulk_collect_test
WHERE object_id = :1
RETURNING object_id INTO :2'
USING l_in_tab(i) RETURNING BULK COLLECT
INTO l_out_tab;
DBMS_OUTPUT.put_line('Starting IDs : ' || l_in_tab.count || ' rows');
DBMS_OUTPUT.put_line('Deleted IDs : ' || l_out_tab.count || '
rows');
ROLLBACK;END;
/
The results of the
dynamic_forall_returning_bulk_collect.sql script show that the input
collection is bound into the statement correctly and that the IDs of
the deleted rows are returned as expected.
SQL> @dynamic_forall_returning_bulk_collect.sql
Starting IDs : 100 rows
Deleted IDs : 100 rows
PL/SQL
procedure successfully completed.
These examples should give you a feel for what
is possible when combining dynamic SQL and bulk operations.
Follow the link for additional
information on
Late Binding and Runtime Binding in PL/SQL.
|