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

Question

我已经介绍了一些PL/SQL(在Oracle SQL Developer中)，并看到了几种不同格式的SQL被调用。

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

我见过四种类型。

1) 普通DDL:

CREATE TABLE newtable AS SELECT * FROM pSource;

2) 立即执行(本机动态SQL):

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

3) EXEC_SQL:

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

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);

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

Answer 1

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

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

收益率：

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

2)EXECUTE IMMEDIATE(及其姊妹DBMS_SQL)用于在PL/SQL块内部执行SQL。这些与"常规"SQL的不同之处在于，它们实际上使用完全不同的SQL引擎(在PL/SQL的情况下，它在oracle进程中运行)进行计算。这就是为什么我们中的许多人宣扬"如果你能在SQL中做到，就不要在PL/SQL中做到">
即使是这两个选项也有不同的方式。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;
/

来源
由于DBMS_SQL允许我们打开和操作PL/SQL块在其中操作的光标，因此在EXECUTE IMMEDIATE块中很难再现结果(难度级别：没有从ALL_TAB_COLS中进行选择，这只是为了提供信息：)。

3)EXEC_SQL是上述CCD_。明智地使用它。：)

以下是对以上内容的详细分析，下面是Tom Kyte对其进行的详细分析。