Oracle:“立即执行"是什么意思? [英] Oracle: What does 'execute immediate' mean?

问题描述

PL/SQL 中的立即执行在与 DML 和 DDL 语句一起使用时有什么意义?立即执行是否像 DDL 语句那样隐式提交到数据库?

What is the significance of execute immediate in PL/SQL when used with DML and DDL statements? Does execute immediate implicitly commit to a database like DDL statements do ?

以下是我遇到的一个例子:

The following is an example I have encountered:

SELECT 'TRUNCATE TABLE BD_BIDS_APPR_DET' INTO V_SQL1 FROM DUAL;
EXECUTE IMMEDIATE V_SQL1;

SELECT 'TRUNCATE TABLE BD_BIDS_SYS_DET' INTO V_SQL1 FROM DUAL;
EXECUTE IMMEDIATE V_SQL1;

SELECT 'TRUNCATE TABLE BD_BIDS_EXT_DET' INTO V_SQL1 FROM DUAL;
EXECUTE IMMEDIATE V_SQL1;

推荐答案

用于运行原生 动态 SQL.

对于 DML，您可以在运行编译时不可用的语句时使用它，例如如果列列表基于用户的选择.

For DML you'd use it when running statements that you don't have available at compile time, e.g. if the column list is based on a selection from the user.

在您的情况下，使用它是因为 DDL 无法从 PL/SQL 中作为静态 SQL 运行.只有某些查询、DML 和 TCL 命令有效.其他任何事情都必须被视为动态.

In your case it's being used because DDL cannot be run as static SQL from within PL/SQL. Only certain query, DML and TCL commands are valid. Anything else has to be treated as dynamic.

我认为很少需要从 PL/SQL 块使用 DDL.TRUNCATE 可能是合理的；如果您发现任何动态创建或丢弃对象的内容，那么这可能更令人担忧，因为它可能表明数据模型不理想.

I'd say it's rare to need to use DDL from a PL/SQL block. TRUNCATE might be reasonable; if you find anything creating or dropping objects on the fly then that might be more of a concern as it can suggest a suboptimal data model.

EXECUTE IMMEDIATE 本身不会自动提交；但是如果你执行 DDL 那么它的行为就像你在 PL/SQL 之外运行它一样，所以它会在你的情况下提交，是的.

EXECUTE IMMEDIATE itself does not automatically commit; but if you execute DDL then that will behave the same as if you ran it outside PL/SQL, so it will commit in your case, yes.

顺便说一句，我不确定为什么您的代码使用中间变量来保存语句；如果您想显示它可能要运行的内容，这很有用，但您似乎没有这样做.您可以这样做:

Incidentally, I'm not sure why your code is using an intermediate variable to hold the statement; that's useful if you want to display what it's going to run maybe, but you don't seem to be doing that. What you have could be done as:

EXECUTE IMMEDIATE 'TRUNCATE TABLE BD_BIDS_EXT_DET';

即根本不使用 V_SQL_1.

这篇关于Oracle:“立即执行"是什么意思?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！