October 2005
Intermediate to advanced
454 pages
14h 44m
English
Native Dynamic SQL (NDS) is also generally able to take advantage of soft-closed cursors and cursor reuse, but it does best when bind variables are used. Consider the following two procedures; they do the same thing except that one uses bind variables while the other uses concatenation.
CREATE OR REPLACE PROCEDURE bind ( p_on NUMBER ) AS
v_od DATE;
BEGIN
EXECUTE IMMEDIATE 'SELECT order_date ' ||
' FROM orders ' ||
' WHERE order_number = :v_on'
INTO v_od
USING p_on;
END;
CREATE OR REPLACE PROCEDURE concatenate ( p_on NUMBER ) AS
v_od DATE;
BEGIN
EXECUTE IMMEDIATE 'SELECT order_date ' ||
' FROM orders ' ||
' WHERE order_number = ' || p_on
INTO v_od;
END;First, I’ll execute the bind version three times:
SQL>BEGIN2FOR counter IN 1..3 LOOP3bind(counter);4END LOOP;5END;6 / PL/SQL procedure successfully completed.
The open cursors list shows one very familiar cursor:
SELECT order_date FROM orders
WHERE order_number = :v_onThe parse and execution counts are, as expected, 1 and 3:
SQL_TEXT PARSE_CALLS EXECUTIONS
------------------------------ ----------- ----------
SELECT order_date FROM orders 1 3
WHERE order_number = :v_onNow I’ll run the concatenation version three times.
SQL>BEGIN2FOR counter IN 1..3 LOOP3concatenate(counter);4END LOOP;5END;6/PL/SQL procedure successfully completed.
The open cursor list looks like this because only the very last one is kept around, hoping for re-execution.
SQL_TEXT ------------------------------ SELECT order_date ...