当前位置：文江博客话题详情

Oracle：DBMS_UTILITY.EXEC_DDL_STATEMENT 与 EXECUTE IMMEDIATE

发布于 2024-11-28 05:16:39 字数 92 浏览 2 评论 0原文

DBMS_UTILITY.EXEC_DDL_STATMENT 和 EXECUTE IMMEDIATE 之间有哪些区别？

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

九厘米的零° 2024-12-05 05:16:39

从根本上讲，它们做同样的事情，即提供一种在 PL/SQL 中执行 DDL 语句的机制，而本机不支持这种机制。如果我没记错的话，EXEC_DDL_STATEMENT 在 Oracle 7 版本的 DBMS_UTILITY 包中可用，而本机动态 SQL (EXECUTE IMMEDIATE) 仅在 8 中引入。

有一些差异。 EXECUTE IMMEDIATE 主要是执行动态 SQL（如其 NDS 别名所示）。我们可以将它用于 DDL 的事实是顺便说一下的。而 EXEC_DDL_STATEMENT() - 正如建议的 - 只能执行 DDL。

但保留 DBMS_UTILITY 版本不仅仅是为了向后兼容，它还有一个我们无法使用 EXECUTE IMMEDIATE 实现的巧妙技巧 - 以分布式方式运行 DDL。我们可以从本地数据库运行此语句，以在远程数据库上创建一个表（前提是我们的用户在那里具有必要的权限）：

SQL>  exec DBMS_UTILITY.EXEC_DDL_STATEMENT@remote_db('create table t1 (id number)');

我不推荐这样做，只是说它可以完成。

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.

There are a couple of differences. EXECUTE IMMEDIATE is mainly about executing dynamic SQL (as its NDS alias indicates). the fact that we can use it for DDL is by-the-by. Whereas EXEC_DDL_STATEMENT() - as the suggests - can only execute 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):

SQL>  exec DBMS_UTILITY.EXEC_DDL_STATEMENT@remote_db('create table t1 (id number)');

I'm not recommending this, just saying it can be done.

回复收藏 0 原文

双马尾 2024-12-05 05:16:39

我意识到我迟到了 9 年才回复，但还有一个额外的区别。

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;

I realize I am 9 years late to the reply but there is one additional difference.

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.

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

回复收藏 0 原文

~没有更多了~