oracle EXEC_SQL、EXECUTE IMMEDIATE、DBMS_SQL 和内联 SQL 的区别

Difference between EXEC_SQL, EXECUTE IMMEDIATE, DBMS_SQL and inline SQL

提问by Addison

I've been going over some PL/SQL (In Oracle SQL Developer), and have seen several different formats of SQL being called.

我已经阅读了一些 PL/SQL（在 Oracle SQL Developer 中），并且看到了几种不同格式的 SQL 被调用。

For the consistency and speed of current and future code, I'd like to know which is the preferred choice.

对于当前和未来代码的一致性和速度，我想知道哪个是首选。

There are four types I've seen.

我见过四种。

1) Plain DDL:

1) 普通 DDL：

CREATE TABLE newtable AS SELECT * FROM pSource;

2) Execute Immediate (Native Dynamic SQL):

2）立即执行（本机动态SQL）：

statement := 'CREATE TABLE newtable AS SELECT * FROM ' || pSource;
EXECUTE IMMEDIATE statement;

3) EXEC_SQL:

3) EXEC_SQL:

EXEC_SQL('CREATE TABLE newtable AS SELECT * FROM ' || pSource);

4) DBMS_SQL:

4）DBMS_SQL：

cursor := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cursor, 'CREATE TABLE newtable AS SELECT * FROM ' || pSource, DBMS_SQL.NATIVE);
numRows := DBMS_SQL.EXECUTE(cursor);

Are there any particular advantages/disadvantages/restrictions between these different ways of calling?

这些不同的呼叫方式之间有什么特别的优点/缺点/限制吗？

回答by mmmmmpie

1) You can't execute straight DDL inside of a PL/SQL block.

1) 不能在 PL/SQL 块内直接执行 DDL。

BEGIN
   CREATE TABLE TEST AS (
      SELECT * FROM FND_USER
    );
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

Yields:

产量：

PLS-00103: Encountered the symbol "CREATE" when expecting one of the following:

2) EXECUTE IMMEDIATE(and its sister DBMS_SQL) are used to execute SQL inside of a PL/SQL block. These differ from "regular" SQL in that they actually use a completely different SQL engine (in PL/SQL's case it runs in the oracleprocess) to compute. This is why so many of us preach "if you can do it in SQL don't do it in PL/SQL".
Even these two options differ between how. EXECUTE IMMEDIATEis quick and easy but kind of dumb. DBMS_SQLis a little more complex but gives the developer a lot more control.
For instance this example that essentially describes the columns of a table:

2）EXECUTE IMMEDIATE（及其姊妹DBMS_SQL）用于在PL/SQL块内执行SQL。它们与“常规”SQL 的不同之处在于它们实际上使用完全不同的 SQL 引擎（在 PL/SQL 的情况下，它在oracle进程中运行）进行计算。这就是为什么我们中的许多人鼓吹“如果你能用 SQL 做到，就不要用 PL/SQL 做到”。
即使这两个选项在 how 之间也有所不同。EXECUTE IMMEDIATE快速简便，但有点愚蠢。DBMS_SQL有点复杂，但给了开发人员更多的控制权。
例如，这个示例本质上描述了表的列：

declare
  c number;
  d number;
  col_cnt integer;
  f boolean;
  rec_tab dbms_sql.desc_tab;
  col_num number;
  procedure print_rec(rec in dbms_sql.desc_rec) is
  begin
    dbms_output.new_line;
    dbms_output.put_line('col_type            =    '
                         || rec.col_type);
    dbms_output.put_line('col_maxlen          =    '
                         || rec.col_max_len);
    dbms_output.put_line('col_name            =    '
                         || rec.col_name);
    dbms_output.put_line('col_name_len        =    '
                         || rec.col_name_len);
    dbms_output.put_line('col_schema_name     =    '
                         || rec.col_schema_name);
    dbms_output.put_line('col_schema_name_len =    '
                         || rec.col_schema_name_len);
    dbms_output.put_line('col_precision       =    '
                         || rec.col_precision);
    dbms_output.put_line('col_scale           =    '
                         || rec.col_scale);
    dbms_output.put('col_null_ok         =    ');
    if (rec.col_null_ok) then
      dbms_output.put_line('true');
    else
      dbms_output.put_line('false');
    end if;
  end;
begin
  c := dbms_sql.open_cursor;

  dbms_sql.parse(c, 'select * from fnd_user', dbms_sql.native);

  d := dbms_sql.execute(c);

  dbms_sql.describe_columns(c, col_cnt, rec_tab);

/*
 * Following loop could simply be for j in 1..col_cnt loop.
 * Here we are simply illustrating some of the PL/SQL table
 * features.
 */
  col_num := rec_tab.first;
  if (col_num is not null) then
    loop
      print_rec(rec_tab(col_num));
      col_num := rec_tab.next(col_num);
      exit when (col_num is null);
    end loop;
  end if;

  dbms_sql.close_cursor(c);
end;
/

Source
Since DBMS_SQLallows us to open and manipulate the cursor in which the PL/SQL block is operating inside of the result would be very difficult to reproduce in an EXECUTE IMMEDIATEblock (difficulty level: no selecting from ALL_TAB_COLSthis is just meant to be informative:).

源
因为DBMS_SQL允许我们打开和操作 PL/SQL 块在其中运行的游标，结果将很难在EXECUTE IMMEDIATE块中重现（难度级别：不从中进行选择ALL_TAB_COLS只是为了提供信息：）。

3)EXEC_SQLis a forms specific version of the above DBMS_SQL. Use it wisely. :)

3)EXEC_SQL是上述表格的特定版本DBMS_SQL。明智地使用它。:)

Hereis a great breakdown of the above and hereis Tom Kyte breaking it down like only he can.

这是对上述内容的一个很好的细分，这是汤姆·凯特 (Tom Kyte) 尽可能地分解它。