oracle 游标 内联,oracle – EXEC_SQL,EXECUTE IMMEDIATE,DBMS_SQL和内联SQL之间的区别

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;

/

Source

由于DBMS_SQL允许我们打开和操作PL / SQL块在结果中运行的游标,因此很难在EXECUTE IMMEDIATE块中重现(难度级别：没有从ALL_TAB_COLS中选择这只是为了提供信息： ).

3)EXEC_SQL是上述DBMS_SQL的表单特定版本.明智地使用它. 🙂

Here是上面的一个很大的细分,而here就是Tom Kyte,就像他能做到的那样.