如何在存储过程中写动态SQL

Question

在oracle8.0.5中：
用dbms_sql来实现
在oracle8.1.5中：
用execute immediate来实现
如：
tsql:='select * from '|| t_item ||'where fitemid=' || fitemid;
execute immediate tsql;

Answer 1

下面讲述了两种方式：
1、Native Dynamic SQL;
2、DBMS_SQL。
请看下面说明：Using the EXECUTE IMMEDIATE Statement
The EXECUTE IMMEDIATE statement prepares (parses) and immediately executes a dynamic SQL 
statement or an anonymous PL/SQL block. The syntax is
 EXECUTE IMMEDIATE dynamic_string
 [INTO {define_variable[, define_variable]... | record}]
 [USING [IN | OUT | IN OUT] bind_argument[, [IN | OUT | IN OUT] bind_argument]...]
 [{RETURNING | RETURN} INTO bind_argument[, bind_argument]...];
where :
 dynamic_string is a string expression that represents a SQL statement or PL/SQL block;
 define_variable is a variable that stores a selected column value, and record is a user-
 defined or %ROWTYPE record that stores a selected row;
 input bind_argument is an expression whose value is passed to the dynamic SQL statement or
 PL/SQL block;
 output bind_argument is a variable that stores a value returned by the dynamic SQL statement
 or PL/SQL block. Except for multi-row queries, the dynamic string can contain any SQL statement (without the
 terminator) or any PL/SQL block (with the terminator). The string can also contain
 placeholders for bind arguments. However, you cannot use bind arguments to pass the names of
 schema objects to a dynamic SQL statement. Instead, you must embed parameters in the dynamic
 string, then pass the names of schema objects to those parameters. Used only for single-row queries, the INTO clause specifies the variables or record into
 which column values are retrieved. Used only for DML statements that hava a RETURNING clause (without a BULK COLLECT clause),
 the RETURNING INTO clause specified the variables into which column values are returned. 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. How to pass the NULL : simply replace the keyword NULL with an uninitialized variable.Using the OPEN-FOR, FETCH, and CLOSE Statements
You use three statements to process a dynamic multi-row query: OPEN-FOR, FETCH, and CLOSE.
First, you OPEN a cursor variable FOR a multi-row query. Then, you FETCH rows from the result
set one at a time. When all the rows are processed, you CLOSE the cursor variable.Native Dynamic SQL vs. the DBMS_SQL Package
 Note:
  To use native dynamic SQL, the COMPATIBLE initialization parameter must be set to 8.1.0 or
  higher.
  Realease 8.0 of Oracle : Default 8.0.0; Can be set to 8.0.x only; Cannot be set to any
  Oracle7 or lower, any release higher than current, including 8.1.0 or higher.
  Release 8.1 of Oracle : Default 8.0.0; Can be set to 8.1.x or 8.0.x; Cannot be set to any
  Oracle7 or lower, any release higher than current, including 8.2.0 or higher. Advantages of Native Dynamic SQL
  1. Ease of Use
  2. Performance Improvements : 1.5 to 3 times
  3. Performance Tip
  4. Support for User-Defined Types
     Native Dynamic SQL supports all of the types supported by static SQL in PL/SQL.
     Therefore, native dynamic SQL provides support for user-defined types, such as user-
     defined objects, collections, and REFs. The DBMS_SQL package does not support these
     user-defined types.
  5. Support for Fetching Into Records Advantages of the DBMS_SQL Package
  1. Support for Client-Side Programs
  2. Support for DESCRIBE
  3. Support for Bulk Dynamic SQL
  4. Multiple Row Updates and Delete with a RETURNING Clause
  5. Support for SQL Statements Large than 32KB
  6. Reuse of SQL Statements