 |
|
EnterpriseDB: Dynamic SQL
Oracle Tips by
Burleson
|
Dynamic
SQL is SQL, either DML or DDL, that is not completely known to the
database until runtime. It may be dynamic because you don't have all
of the criteria until runtime or it may be that you want to achieve
maximum reusability from your code. Either way, dynamic SQL is very
powerful. Dynamic SQL can also be very hard to maintain so it's
important to only use dynamic SQL where your programs really benefit
from the added complexity.
There
are two types of dynamic SQL that are used in SPL: dynamic ref
cursors and execute immediate.
Dynamic Ref Cursors
A
dynamic ref cursor has a query string assigned to it at run time. The
steps to using a dynamic ref cursor are to create a weakly typed ref
cursor and then build the SQL statement. You associate the query
string to the ref cursor replacing the static query after the FOR
keyword with the query string:
DECLARE
v_curs SYS_REFCURSOR;
v_query_string VARCHAR2(200);
v_ename emp.ename%TYPE;
BEGIN
v_query_string := 'SELECT ename FROM emp';
OPEN v_curs FOR v_query_string;
FETCH v_curs INTO v_ename;
CLOSE v_curs;
DBMS_OUTPUT.PUT_LINE( 'Emp Name: ' || v_ename );
END;
INFO: Emp Name: SMITH
You can
associate variables with the query by giving them a name and preceding
them with a colon (:). This is called a bind variable. The variable
will be bound to the query at runtime.
DECLARE
v_curs SYS_REFCURSOR;
v_query_string VARCHAR2(200);
v_ename emp.ename%TYPE;
BEGIN
v_query_string := 'SELECT ename FROM emp WHERE empno
= :employee_number';
OPEN v_curs FOR v_query_string USING 7788;
FETCH v_curs INTO v_ename;
CLOSE v_curs;
DBMS_OUTPUT.PUT_LINE( 'Emp Name: ' || v_ename );
END;
INFO: Emp Name: SCOTT
You can
loop through a dynamic ref cursor in the same manner as a static
cursor. As a matter of a fact, you can use any of the features
available for a static cursor with a dynamic ref cursor.
Ref
cursors allow you to dynamically run select statements. They are the
way to go if you are passing data to external programs or if you are
looping through the data. The down side to dynamic ref cursors is
that they do not allow you to run dynamic DDL. There is a second way
to create dynamic statements that does allow DDL. This method is
called "execute immediate".
This
is an excerpt from the book "EnterpriseDB:
The Definitive Reference" by Rampant TechPress. |
