Разница между 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) Вы не можете выполнить прямой DDL внутри блока PL/SQL.

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) используются для выполнения SQL внутри блока PL/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 это форма конкретной версии выше DBMS_SQL, Использовать его с умом.:)

Вот большая разбивка вышесказанного, а вот Том Кайт разбивает его, как только он может.