Native Dynamic SQL

About the Four Dynamic SQL Methods

Now that you've been introduced to the two basic statements used to implement native dynamic SQL in PL/SQL, it's time to take a step back and review the four distinct types, or methods, of dynamic SQL, listed in Table 16-1, and the NDS statements you will need to implement those methods.

Type

Description

NDS statements used

Method 1

No queries; just DDL statements and UPDATEs, INSERTs, or DELETEs, which have no bind variables

EXECUTE IMMEDIATE without USING and INTO clauses

Method 2

No queries; just UPDATEs, INSERTs, or DELETEs, with a fixed number of bind variables

EXECUTE IMMEDIATE with a USING clause

Method 3 single row queried

Queries (SELECT statements) with a fixed numbers of columns and bind variables, retrieving a single row of data

EXECUTE IMMEDIATE with USING and INTO clauses

Method 3 multiple rows queried

Queries (SELECT statements) with a fixed numbers of columns and bind variables, retrieving or more rows of data

EXECUTE IMMEDIATE with USING and BULK COLLECT INTO clauses or OPEN FOR with dynamic string

Method 4

A statement in which the numbers of columns selected (for a query) or the number of bind variables set are not known until runtime

EXECUTE IMMEDIATE, but in this case you will be executing a dynamic PL/SQL block, rather than a SQL string; you can also use DBMS_SQL!

1 Method 1

The following DDL statement is an example of Method 1 dynamic SQL:

And this UPDATE statement is also Method 1 dynamic SQL because its only variation is in the table name; there are no bind variables:

2 Method 2

I now replace both of my hardcoded values with placeholders (a colon preceded by an identifier) in the previous DML statement (indicated by the colon); I then have Method 2 dynamic SQL:

You can see that the USING clause contains the values that will be bound into the SQL string after parsing and before execution.

3 Method 3

A Method 3 dynamic SQL statement is a query with a fixed number of bind variables (or none). This likely is the type of dynamic SQL you will most often be writing. Here is an example:

I am querying just two columns from the employee table and depositing them into the two local variables with the INTO clause. I also have a single bind variable. Because the numbers of these items are static at the time of compilation, we have Method 3 dynamic SQL.

4 Method 4

Finally, let's consider the last and most complex scenario: Method 4 dynamic SQL. Consider this very generic query:

At the time I compile my code, I don't have any idea how many columns will be queried from the employee table. This leaves me with quite a challenge: how do I write the FETCH INTO statement to handle that variability? Your choices are twofold: either fall back on DBMS_SQL to write relatively straightforward, though voluminous code, or switch to dynamic PL/SQL block execution.

Fortunately for many of you, scenarios requiring Method 4 dynamic SQL are rare. If, you run into it, however, you should read the later sections, "Implement Method 4 Dynamic SQL in NDS" and "Meet Method 4 Dynamic SQL Requirements."

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10768286/viewspace-590637/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10768286/viewspace-590637/