Dynamic SQL is a programming methodology for generating and running SQL statements at run time. It is useful when writing general-purpose and flexible programs like ad hoc query systems, when writing programs that must run database definition language (DDL) statements, or when you do not know at compilation time the full text of a SQL statement or the number or data types of its input and output variables. For example, dynamic SQL lets you create a procedure that operates on a table whose name is not known until runtime.
PL/SQL provides two ways to write dynamic SQL:
- Native dynamic SQL, a PL/SQL language (that is, native) feature for building and running dynamic SQL statements
- DBMS_SQL package, an API for building, running, and describing dynamic SQL statements
When You Need Dynamic SQL
In PL/SQL, you need dynamic SQL to run:
- SQL whose text is unknown at compile time. For example, a SELECT statement that includes an identifier that is unknown at compile time (such as a table name) or a WHERE clause in which the number of subclauses is unknown at compile time.
- SQL that is not supported as static SQL. That is, any SQL construct not included in "Description of Static SQL".
- Successful compilation verifies that static SQL statements reference valid database objects and that the necessary privileges are in place to access those objects.
- Successful compilation creates schema object dependencies.
Native dynamic SQL processes most dynamic SQL statements with the EXECUTE IMMEDIATE statement.
If the dynamic SQL statement is a SELECT statement that returns multiple rows, native dynamic SQL gives you these choices:
- Use the EXECUTE IMMEDIATE statement with the BULK COLLECT INTO clause.
- Use the OPEN FOR, FETCH, and CLOSE statements.
1. EXECUTE IMMEDIATE Statement
The EXECUTE IMMEDIATE statement is the means by which native dynamic SQL processes most dynamic SQL statements.
If the dynamic SQL statement is self-contained (that is, if it has no placeholders for bind variables and the only result that it can possibly return is an error), then the EXECUTE IMMEDIATE statement needs no clauses.
If the dynamic SQL statement includes placeholders for bind variables, each placeholder must have a corresponding bind variable in the appropriate clause of the EXECUTE IMMEDIATE statement, as follows:
- If the dynamic SQL statement is a SELECT statement that can return at most one row, put out-bind variables (defines) in the INTO clause and in-bind variables in the USING clause.
- If the dynamic SQL statement is a SELECT statement that can return multiple rows, put out-bind variables (defines) in the BULK COLLECT INTO clause and in-bind variables in the USING clause.
- If the dynamic SQL statement is a DML statement without a RETURNING INTO clause, other than SELECT, put all bind variables in the USING clause.
- If the dynamic SQL statement is a DML statement with a RETURNING INTO clause, put in-bind variables in the USING clause and out-bind variables in the RETURNING INTO clause.
- If the dynamic SQL statement is an anonymous PL/SQL block or a CALL statement, put all bind variables in the USING clause.
- Every bind variable that corresponds to a placeholder for a subprogram parameter has the same parameter mode as that subprogram parameter (as in Example 1) and a data type that is compatible with that of the subprogram parameter.
- No bind variable has a data type that SQL does not support (such as BOOLEAN in Example 2).
Example 1 Invoking Subprogram from Dynamic PL/SQL Block
-- Subprogram that dynamic PL/SQL block invokes:
CREATE OR REPLACE PROCEDURE create_dept (
deptid IN OUT NUMBER,
dname IN VARCHAR2,
mgrid IN NUMBER,
locid IN NUMBER
) AS
BEGIN
deptid := departments_seq.NEXTVAL;
INSERT INTO departments (
department_id,
department_name,
manager_id,
location_id
)
VALUES (deptid, dname, mgrid, locid);
END;
/
DECLARE
plsql_block VARCHAR2(500);
new_deptid NUMBER(4);
new_dname VARCHAR2(30) := 'Advertising';
new_mgrid NUMBER(6) := 200;
new_locid NUMBER(4) := 1700;
BEGIN
-- Dynamic PL/SQL block invokes subprogram:
plsql_block := 'BEGIN create_dept(:a, :b, :c, :d); END;';
/* Specify bind variables in USING clause.
Specify mode for first parameter.
Modes of other parameters are correct by default. */
EXECUTE IMMEDIATE plsql_block
USING IN OUT new_deptid, new_dname, new_mgrid, new_locid;
END;
/
- Example 2: Unsupported Data Type in Native Dynamic SQL. BOOLEAN variable are not supported in EXECUTE IMMEDIATE. Below operation will give error.
SQL> CREATE OR REPLACE FUNCTION f (x INTEGER)
2 RETURN BOOLEAN
3 AS
4 BEGIN
5 RETURN TRUE;
6 END f;
7 /
Function created.
SQL> DECLARE
2 dyn_stmt VARCHAR2(200);
3 b1 BOOLEAN;
4 BEGIN
5 dyn_stmt := 'BEGIN :b := f(5); END;';
6 EXECUTE IMMEDIATE dyn_stmt USING OUT b1;
7 -- DBMS_OUTPUT.PUT_LINE(b1);
8 END;
9 /
EXECUTE IMMEDIATE dyn_stmt USING OUT b1;
*
ERROR at line 6:
ORA-06550: line 6, column 42:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 6, column 5:
PL/SQL: Statement ignored
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO HI
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
DECLARE
a_null CHAR(1); -- Set to NULL automatically at run time
BEGIN
EXECUTE IMMEDIATE 'UPDATE emp3 SET comm = :x where empno= 7521' USING a_null;
END;
/
SQL> select * from emp3 where empno=7521;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO HI
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 30
2 RETURN BOOLEAN
3 AS
4 BEGIN
5 RETURN TRUE;
6 END f;
7 /
Function created.
SQL> DECLARE
2 dyn_stmt VARCHAR2(200);
3 b1 BOOLEAN;
4 BEGIN
5 dyn_stmt := 'BEGIN :b := f(5); END;';
6 EXECUTE IMMEDIATE dyn_stmt USING OUT b1;
7 -- DBMS_OUTPUT.PUT_LINE(b1);
8 END;
9 /
EXECUTE IMMEDIATE dyn_stmt USING OUT b1;
*
ERROR at line 6:
ORA-06550: line 6, column 42:
PLS-00457: expressions have to be of SQL types
ORA-06550: line 6, column 5:
PL/SQL: Statement ignored
- Example 3: Uninitialized Variable Represents NULL in USING Clause
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO HI
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
DECLARE
a_null CHAR(1); -- Set to NULL automatically at run time
BEGIN
EXECUTE IMMEDIATE 'UPDATE emp3 SET comm = :x where empno= 7521' USING a_null;
END;
/
SQL> select * from emp3 where empno=7521;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO HI
---------- ---------- --------- ---------- --------- ---------- ---------- ---------- ----------
7521 WARD SALESMAN 7698 22-FEB-81 1250 30
OPEN FOR, FETCH, and CLOSE Statements
- Use an OPEN FOR statement to associate a cursor variable with the dynamic SQL statement. In the USING clause of the OPEN FOR statement, specify a bind variable for each placeholder in the dynamic SQL statement.
The USING clause cannot contain the literal NULL. To work around this restriction, use an uninitialized variable where you want to use NULL, as in Example 7-3. - Use the FETCH statement to retrieve result set rows one at a time, several at a time, or all at once.
- Use the CLOSE statement to close the cursor variable.
DECLARE
TYPE EmpCurTyp IS REF CURSOR;
v_emp_cursor EmpCurTyp;
emp_record employees%ROWTYPE;
v_stmt_str VARCHAR2(200);
v_e_job employees.job%TYPE;
BEGIN
-- Dynamic SQL statement with placeholder:
v_stmt_str := 'SELECT * FROM employees WHERE job_id = :j';
-- Open cursor & specify bind variable in USING clause:
OPEN v_emp_cursor FOR v_stmt_str USING 'MANAGER';
-- Fetch rows from result set one at a time:
LOOP
FETCH v_emp_cursor INTO emp_record;
EXIT WHEN v_emp_cursor%NOTFOUND;
END LOOP;
-- Close cursor:
CLOSE v_emp_cursor;
END;
/
Repeated Placeholder Names in Dynamic SQL Statements
If you repeat placeholder names in dynamic SQL statements, be aware that the way placeholders are associated with bind variables depends on the kind of dynamic SQL statement.
Dynamic SQL Statement is Not Anonymous Block or CALL Statement
If the dynamic SQL statement does not represent an anonymous PL/SQL block or a CALL statement, repetition of placeholder names is insignificant. Placeholders are associated with bind variables in the USING clause by position, not by name.
For example, in this dynamic SQL statement, the repetition of the name :x is insignificant:
sql_stmt := 'INSERT INTO payroll VALUES (:x, :x, :y, :x)';
In the corresponding USING clause, you must supply four bind variables. They can be different; for example:
EXECUTE IMMEDIATE sql_stmt USING a, b, c, d;
The preceding EXECUTE IMMEDIATE statement runs this SQL statement:
INSERT INTO payroll VALUES (a, b, c, d)
To associate the same bind variable with each occurrence of :x, you must repeat that bind variable; for example:
EXECUTE IMMEDIATE sql_stmt USING a, a, b, a;
The preceding EXECUTE IMMEDIATE statement runs this SQL statement:
INSERT INTO payroll VALUES (a, a, b, a)
Dynamic SQL Statement is Anonymous Block or CALL Statement
If the dynamic SQL statement represents an anonymous PL/SQL block or a CALL statement, repetition of placeholder names is significant. Each unique placeholder name must have a corresponding bind variable in the USING clause. If you repeat a placeholder name, you need not repeat its corresponding bind variable. All references to that placeholder name correspond to one bind variable in the USING clause.
In Example 5, all references to the first unique placeholder name, :x, are associated with the first bind variable in the USING clause, a, and the second unique placeholder name, :y, is associated with the second bind variable in the USING clause, b.
Example 5 Repeated Placeholder Names in Dynamic PL/SQL Block
CREATE PROCEDURE calc_stats (
w NUMBER,
x NUMBER,
y NUMBER,
z NUMBER )
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(w + x + y + z);
END;
/
DECLARE
a NUMBER := 4;
b NUMBER := 7;
plsql_block VARCHAR2(100);
BEGIN
plsql_block := 'BEGIN calc_stats(:x, :x, :y, :x); END;';
EXECUTE IMMEDIATE plsql_block USING a, b; -- calc_stats(a, a, b, a)
END;
/
DBMS_SQL Package
The DBMS_SQL package defines an entity called a SQL cursor number. Because the SQL cursor number is a PL/SQL integer, you can pass it across call boundaries and store it.
You must use the DBMS_SQL package to run a dynamic SQL statement when you do not know either of these until run time:
- SELECT list
- What placeholders in a SELECT or DML statement must be bound
- The dynamic SQL statement retrieves rows into records.
- You want to use the SQL cursor attribute %FOUND, %ISOPEN, %NOTFOUND, or %ROWCOUNT after issuing a dynamic SQL statement that is an INSERT, UPDATE, DELETE, MERGE, or single-row SELECT statement.
- DBMS_SQL.TO_REFCURSOR Function
- DBMS_SQL.TO_CURSOR_NUMBER Function
DBMS_SQL.TO_REFCURSOR Function
The DBMS_SQL.TO_REFCURSOR function converts a SQL cursor number to a weak cursor variable, which you can use in native dynamic SQL statements.
Before passing a SQL cursor number to the DBMS_SQL.TO_REFCURSOR function, you must OPEN, PARSE, and EXECUTE it (otherwise an error occurs).
After you convert a SQL cursor number to a REF CURSOR variable, DBMS_SQL operations can access it only as the REF CURSOR variable, not as the SQL cursor number. For example, using the DBMS_SQL.IS_OPEN function to see if a converted SQL cursor number is still open causes an error.
Example 6 uses the DBMS_SQL.TO_REFCURSOR function to switch from the DBMS_SQL package to native dynamic SQL.
Example 6 Switching from DBMS_SQL Package to Native Dynamic SQL
CREATE OR REPLACE TYPE vc_array IS TABLE OF VARCHAR2(200);
/
CREATE OR REPLACE TYPE numlist IS TABLE OF NUMBER;
/
CREATE OR REPLACE PROCEDURE do_query_1 (
placeholder vc_array,
bindvars vc_array,
sql_stmt VARCHAR2
)
IS
TYPE curtype IS REF CURSOR;
src_cur curtype;
curid NUMBER;
bindnames vc_array;
empnos numlist;
depts numlist;
ret NUMBER;
isopen BOOLEAN;
BEGIN
-- Open SQL cursor number:
curid := DBMS_SQL.OPEN_CURSOR;
-- Parse SQL cursor number:
DBMS_SQL.PARSE(curid, sql_stmt, DBMS_SQL.NATIVE);
bindnames := placeholder;
-- Bind variables:
FOR i IN 1 .. bindnames.COUNT LOOP
DBMS_SQL.BIND_VARIABLE(curid, bindnames(i), bindvars(i));
END LOOP;
-- Run SQL cursor number:
ret := DBMS_SQL.EXECUTE(curid);
-- Switch from DBMS_SQL to native dynamic SQL:
src_cur := DBMS_SQL.TO_REFCURSOR(curid);
FETCH src_cur BULK COLLECT INTO empnos, depts;
-- This would cause an error because curid was converted to a REF CURSOR:
-- isopen := DBMS_SQL.IS_OPEN(curid);
CLOSE src_cur;
END;
/
DBMS_SQL.TO_CURSOR_NUMBER Function
The DBMS_SQL.TO_CURSOR_NUMBER function converts a REF CURSOR variable (either strong or weak) to a SQL cursor number, which you can pass to DBMS_SQL subprograms.
Before passing a REF CURSOR variable to the DBMS_SQL.TO_CURSOR_NUMBER function, you must OPEN it.
After you convert a REF CURSOR variable to a SQL cursor number, native dynamic SQL operations cannot access it.
Example 7 uses the DBMS_SQL.TO_CURSOR_NUMBER function to switch from native dynamic SQL to the DBMS_SQL package.
Example 7 Switching from Native Dynamic SQL to DBMS_SQL Package
CREATE OR REPLACE PROCEDURE do_query_2 (
sql_stmt VARCHAR2
)
IS
TYPE curtype IS REF CURSOR;
src_cur curtype;
curid NUMBER;
desctab DBMS_SQL.DESC_TAB;
colcnt NUMBER;
namevar VARCHAR2(50);
numvar NUMBER;
datevar DATE;
empno NUMBER := 100;
BEGIN
-- sql_stmt := SELECT ... FROM employees WHERE employee_id = :b1';
-- Open REF CURSOR variable:
OPEN src_cur FOR sql_stmt USING empno;
-- Switch from native dynamic SQL to DBMS_SQL package:
curid := DBMS_SQL.TO_CURSOR_NUMBER(src_cur);
DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);
-- Define columns:
FOR i IN 1 .. colcnt LOOP
IF desctab(i).col_type = 2 THEN
DBMS_SQL.DEFINE_COLUMN(curid, i, numvar);
ELSIF desctab(i).col_type = 12 THEN
DBMS_SQL.DEFINE_COLUMN(curid, i, datevar);
-- statements
ELSE
DBMS_SQL.DEFINE_COLUMN(curid, i, namevar, 50);
END IF;
END LOOP;
-- Fetch rows with DBMS_SQL package:
WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP
FOR i IN 1 .. colcnt LOOP
IF (desctab(i).col_type = 1) THEN
DBMS_SQL.COLUMN_VALUE(curid, i, namevar);
ELSIF (desctab(i).col_type = 2) THEN
DBMS_SQL.COLUMN_VALUE(curid, i, numvar);
ELSIF (desctab(i).col_type = 12) THEN
DBMS_SQL.COLUMN_VALUE(curid, i, datevar);
-- statements
END IF;
END LOOP;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(curid);
END;
/
Difference Between DBMS_SQL and EXECUTE IMMEDIATE
0 comments:
Post a Comment