《Oracle PL/SQL开发指南》学习笔记27——动态SQL（Dynamic SQL）（章节回顾、测试）

动态SQL语句是一种强大的技术，通过它可以在程序运行时写和执行查询，修改DDL和DML语句。

本地动态SQL（Native Dynamic SQL (NDS)）

Review Section
This section has described the following points about Native Dynamic SQL:
1. NDS lets you create dynamic programs at runtime.
2. NDS supports concatenating (or gluing) strings together to make a statement.
3. NDS statements built through concatenation should use the dbms_assert package functions to prevent SQL injection attacks.
4. NDS supports bind variables, which are like parameters scattered throughout a statement. If you think of NDS statements as long strings, you refer to bind variables in the order they occur when reading the string from the left to the right.
5. The EXECUTE IMMEDIATE statement dispatches an NDS statement for execution.
6. The EXECUTE IMMEDIATE statement support IN, IN OUT, and OUT mode parameters, and is the preferred solution when calling NDS statements that include PL/SQL anonymous blocks.
7. The RETURNING INTO clause supports OUT mode parameters for INSERT, UPDATE,
and DELETE statements.
8. The OPEN system_reference_cursor FOR nds_statement lets you create dynamic SELECT lists easily.

 

DBMS_SQL Package

Review Section
This section has described the following points about the dbms_sql package, which supports dynamic SQL statements:
1. The dbms_sql package may require grants to access the dbms_sql and dbms_sys_sql packages.
2. The dbms_sql package supports static SQL statements that process DDL and DML statements. These dynamic statements are known as Method 1.
2. The dbms_sql package supports dynamic SQL statements that process DML statements with bind variables. These dynamic statements are known as Method 2.
3. The dbms_sql package supports dynamic SQL statements with a set of known inputs and outputs. They process DML statements with bind variables and a RETURNING INTO clause. These dynamic statements are known as Method 3.
4. The dbms_sql package supports dynamic SQL statements with a set of runtimedetermined inputs and outputs. They process DML statements with bind variables and a RETURNING INTO clause. These dynamic statements are known as Method 4.
5. The dbms_sql package supports converting LONG, LONG RAW, and RAW data types.

 

Mastery Check

The mastery check is a series of true-or-false and multiple-choice questions that let you confirm
how well you understand the material in the chapter. You may check Appendix I for answers to
these questions.

True or False:
1. NDS supports dynamic DDL statements with bind variables.
False. NDS doesn’t support bind variables in dynamic DDL statements. You must use concatenation to create dynamic DDL statements.

2. NDS supports static DDL statements.
True. NDS supports static DDL statements, but you’re more likely to create statements by concatenating values into them.
3. NDS supports dynamic DML statements with bind variables.
True. NDS supports dynamic DML statements with bind variables.
4. NDS supports dynamic SELECT statements with a known set of columns.
True. NDS supports dynamic SELECT statements with a known set of columns in the
SELECT list. It opens the dynamic statement into a system reference cursor.
5. NDS supports dynamic PL/SQL anonymous blocks.
True. NDS supports dynamic PL/SQL anonymous blocks and it can pass IN-only, IN
OUT, or OUT-only mode variables.
6. NDS supports string literals with an embedded colon (:).
False. NDS doesn’t support an embedded colon (:), and you have to use a CHR(58) to
put one into the context of a dynamic statement.
7. NDS statements with an unknown number of inputs rely on the dbms_sql package.
True. NDS statements with an unknown number of inputs rely on the dbms_sql package.
8. Without NDS, you must explicitly use the dbms_sql package to open a cursor.
True. When you’re not using NDS, you must explicitly open a cursor with the dbms_sql
package.
9. With an unknown set of dynamic inputs, you must parse, execute, and fetch results with
functions and procedures found in the dbms_sql package.
True. You must parse, execute, and fetch results from the dbms_sql package to run
dynamic statements with an unknown set of input parameters.
10. You only need to define columns and bind variables to retrieve SELECT-list values from a
dynamic query with the dbms_sql package.
False. You need to define columns and bind variables and map the column values to
SELECT-list values.
Multiple Choice:
11. Which of the following are procedures in the dbms_sql package? (Multiple answers
possible)
A. bind_array
B. bind_variable
C. fetch_rows
D. is_open
E. parse
A, B, and E are correct. bind_array, bind_variable, and parse are procedures.
fetch_rows and is_open are functions.

12. Which of the following are functions in the dbms_sql package? (Multiple answers possible)
A. bind_array
B. execute_and_fetch
C. fetch_rows
D. is_open
E. parse
B, C, and D are correct. execute_and_fetch, fetch_rows, and is_open are
functions. bind_array and parse are procedures.
13. Which of the following are package constants? (Multiple answers possible)
A. The NATIVE constant
B. The V6 constant
C. The V7 constant
D. The V8 constant
E. All of the above
A, B, and C are correct. The NATIVE, V6, and V7 are constants of the dbms_sql package.
You should always use NATIVE from Oracle 7 forward.
14. Which of the following are dbms_sql-supported base scalar types for collections?
(Multiple answers possible)
A. The BLOB data type
B. The CLOB data type
C. The BINARY_DOUBLE data type
D. The BINARY_FLOAT data type
E. The TIMESTAMP data type
A, B, C, D, and E are correct. All of the data types are supported base types of the dbms_
sql Attribute Data Type (ADT) collections.
15. Which of the following dbms_sql functions or procedures execute a query? (Multiple
answers possible)
A. The parse_and_execute procedure
B. The parse_and_execute function
C. The execute function
D. The execute_and_fetch function
E. The execute_fetch_all function
C and D are correct. execute and execute_and_fetch are the only functions that
execute a dynamic query in the dbms_sql package.