execute immediate 博客分类: pl/sql execute immediateusing
EXECUTE IMMEDIATE Statement
The EXECUTE
IMMEDIATE
statement executes a dynamic SQL statement or anonymous PL/SQL block. You can use it to issue SQL statements that cannot be represented directly in PL/SQL, or to build up statements where you do not know all the table names, WHERE clauses, and so on in advance. For more information, see Chapter 7.
Syntax
Description of the illustration execute_immediate_statement.gif
Keyword and Parameter Description
bind_argument
An expression whose value is passed to the dynamic SQL statement, or a variable that stores a value returned by the dynamic SQL statement.
define_variable_name
A variable that stores a selected column value.
dynamic_string
A string literal, variable, or expression that represents a single SQL statement or a PL/SQL block. It must be of type CHAR
or VARCHAR2
, not NCHAR
or NVARCHAR2
.
INTO ...
Used only for single-row queries, this clause specifies the variables or record into which column values are retrieved. For each value retrieved by the query, there must be a corresponding, type-compatible variable or field in the INTO
clause.
record_name
A user-defined or %ROWTYPE
record that stores a selected row.
RETURNING INTO ...
Used only for DML statements that have a RETURNING
clause (without a BULK
COLLECT
clause), this clause specifies the bind variables into which column values are returned. For each value returned by the DML statement, there must be a corresponding, type-compatible variable in the RETURNING
INTO
clause.
USING ...
Specifies a list of input and/or output bind arguments. The parameter mode defaults to IN
.
Usage Notes
Except for multi-row queries, the dynamic string can contain any SQL statement (without the final semicolon) or any PL/SQL block (with the final semicolon). The string can also contain placeholders for bind arguments. You cannot use bind arguments to pass the names of schema objects to a dynamic SQL statement.
You can place all bind arguments in the USING
clause. The default parameter mode is IN
. For DML statements that have a RETURNING
clause, you can place OUT
arguments in the RETURNING
INTO
clause without specifying the parameter mode, which, by definition, is OUT
. If you use both the USING
clause and the RETURNING
INTO
clause, the USING
clause can contain only IN
arguments.
At run time, bind arguments replace corresponding placeholders in the dynamic string. Every placeholder must be associated with a bind argument in the USING
clause and/or RETURNING
INTO
clause. You can use numeric, character, and string literals as bind arguments, but you cannot use Boolean literals (TRUE
, FALSE
, and NULL
). To pass nulls to the dynamic string, you must use a workaround. See "Passing Nulls to Dynamic SQL".
Dynamic SQL supports all the SQL datatypes. For example, define variables and bind arguments can be collections, LOB
s, instances of an object type, and refs. Dynamic SQL does not support PL/SQL-specific types. For example, define variables and bind arguments cannot be Booleans or index-by tables. The only exception is that a PL/SQL record can appear in the INTO
clause.
You can execute a dynamic SQL statement repeatedly using new values for the bind arguments. You still incur some overhead, because EXECUTE
IMMEDIATE
re-prepares the dynamic string before every execution.
The string argument to the EXECUTE IMMEDIATE
command cannot be one of the national character types, such as NCHAR
or NVARCHAR2
.
Examples
The following PL/SQL block contains several examples of dynamic SQL:
DECLARE sql_stmt VARCHAR2(200); plsql_block VARCHAR2(500); emp_id NUMBER(4) := 7566; salary NUMBER(7,2); dept_id NUMBER(2) := 50; dept_name VARCHAR2(14) := 'PERSONNEL'; location VARCHAR2(13) := 'DALLAS'; emp_rec emp%ROWTYPE; BEGIN EXECUTE IMMEDIATE 'CREATE TABLE bonus (id NUMBER, amt NUMBER)'; sql_stmt := 'INSERT INTO dept VALUES (:1, :2, :3)'; EXECUTE IMMEDIATE sql_stmt USING dept_id, dept_name, location; sql_stmt := 'SELECT * FROM emp WHERE empno = :id'; EXECUTE IMMEDIATE sql_stmt INTO emp_rec USING emp_id; plsql_block := 'BEGIN emp_pkg.raise_salary(:id, :amt); END;'; EXECUTE IMMEDIATE plsql_block USING 7788, 500; sql_stmt := 'UPDATE emp SET sal = 2000 WHERE empno = :1 RETURNING sal INTO :2'; EXECUTE IMMEDIATE sql_stmt USING emp_id RETURNING INTO salary; EXECUTE IMMEDIATE 'DELETE FROM dept WHERE deptno = :num' USING dept_id; EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE'; END;
上一篇: do{...}while(0)的巧妙用处
下一篇: C语言排序去重