在Oracle 12c中立即执行

Question

begin
execute immediate select ename, 
empno 
from  emp 
where ename !='James';
end;
/

获取

ORA-00904：执行上述代码时出现无效标识符错误。

有可能让多个execute immediate语句同时创建表、删除表和创建索引吗？

Answer 1

好吧，你写的是无效的，但类似的东西不是：

SQL> set serveroutput on
SQL> declare
2    l_ename emp.ename%type;
3    l_empno emp.empno%type;
4  begin
5    execute immediate 'select ename, empno from emp where ename = ''KING'''
6      into l_ename, l_empno;
7
8    dbms_output.put_line(l_ename ||' '|| l_empno);
9  end;
10  /
KING 7839
PL/SQL procedure successfully completed.
SQL>

你本可以不使用INTO子句，但是-结果将不会出现：

execute immediate 'select ename, empno from emp where ename = ''KING''';

多个execute immediate-为什么它们不可能？

SQL> begin
2    execute immediate 'create table test (id number, name varchar2(10))';
3    execute immediate 'create index i1_test_id on test (id)';
4    execute immediate 'drop table test';
5  end;
6  /
PL/SQL procedure successfully completed.
SQL>