ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Native Dynamic SQL

Native Dynamic SQL

原创 Linux操作系统 作者:jack198409 时间:2009-04-16 14:42:55 0 删除 编辑
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.

Table 16-1. The four methods of dynamic SQL

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:

EXECUTE IMMEDIATE 'CREATE INDEX emp_ind_1 on emp (sal, hiredate)';

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

EXECUTE IMMEDIATE
   'UPDATE ' || l_table || ' SET sal = 10000 WHERE empno = 1506'

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:

EXECUTE IMMEDIATE
   'UPDATE ' || l_table || ' SET sal = :salary WHERE = :employee_id'
   USING 10000, 1506;

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:

EXECUTE IMMEDIATE
   'SELECT last_name, salary FROM employee
     WHERE department_id = :dept_id'
   USING 10
   INTO l_last_name, l_salary;

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:

OPEN l_cursor FOR
   'SELECT ' || l_column_list ||
     'FROM employee';

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/,如需转载,请注明出处,否则将追究法律责任。

下一篇: SQL Join 用法
请登录后发表评论 登录
全部评论

注册时间:2007-12-13

  • 博文量
    135
  • 访问量
    285213