Oracle：DBMS_UTILITY.EXEC_DDL_STATEMENT 与 EXECUTE IMMEDIATE
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6973003/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle: DBMS_UTILITY.EXEC_DDL_STATEMENT vs EXECUTE IMMEDIATE
提问by Revious
Which are the differences between DBMS_UTILITY.EXEC_DDL_STATEMENTand EXECUTE IMMEDIATE?
DBMS_UTILITY.EXEC_DDL_STATEMENT和之间有哪些区别 EXECUTE IMMEDIATE？
回答by APC
Fundamentally they do the same thing, which is to provide a mechanism to execute DDL statements in PL/SQL, which isn't supported natively. If memory serves me well, the EXEC_DDL_STATEMENT was available in the Oracle 7 version of the DBMS_UTILITY package, whereas Native Dynamic SQL (EXECUTE IMMEDIATE) was only introduced in 8.
从根本上说，它们做同样的事情，即提供一种在 PL/SQL 中执行 DDL 语句的机制，而本机不支持这种机制。如果我没记错的话，EXEC_DDL_STATEMENT 在 DBMS_UTILITY 包的 Oracle 7 版本中可用，而 Native Dynamic SQL (EXECUTE IMMEDIATE) 只在 8 中引入。
There are a couple of differences. EXECUTE IMMEDIATE is mainly about executing dynamic SQL (as its NDS name indicates). the fact that we can use it for DDL is by-the-by.
有几个不同之处。EXECUTE IMMEDIATE 主要是关于执行动态 SQL（正如它的 NDS 名称所示）。我们可以将它用于 DDL 的事实是不言而喻的。
But the DBMS_UTILITY version isn't retained just for backwards compatibility, it has one neat trick we cannot do with EXECUTE IMMEDIATE - running DDL in a distributed fashion. We can run this statement from our local database to create a table on a remote database (providing our user has the necessary privileges there):
但是保留 DBMS_UTILITY 版本不仅仅是为了向后兼容，它还有一个我们无法用 EXECUTE IMMEDIATE 实现的巧妙技巧——以分布式方式运行 DDL。我们可以从本地数据库运行此语句以在远程数据库上创建一个表（前提是我们的用户在那里拥有必要的权限）：
SQL> exec DBMS_UTILITY.EXEC_DDL_STATEMENT@remote_db('create table t1 (id number)');
I'm not recommending this, just say it can be done.
我不推荐这个，只是说它可以做到。
回答by user11754489
I realize I am 9 years late to the reply but there is one additional difference.
我意识到我的回复晚了 9 年，但还有一个区别。
dbms_utility.exec_ddl_statement will not execute anything but DDL. If you try to do say an insert, it will not do it. It will also not return an error either so you won't know that you did not insert.
dbms_utility.exec_ddl_statement 除了 DDL 不会执行任何东西。如果你试图说一个插入，它不会这样做。它也不会返回错误，因此您不会知道您没有插入。
-- drop table kevtemp1;
create table kevtemp1 (a integer);
insert into kevtemp1 values (1);
commit;
begin
insert into kevtemp1 values (2);
end;
/
commit;
begin
DBMS_UTILITY.EXEC_DDL_STATEMENT('insert into kevtemp1 values (3)');
end;
/
commit;
select * from kevtemp1;
