Oracle：“立即执行”是什么意思？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/18375990/
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: What does 'execute immediate' mean?
提问by Abhishek Singh
What is the significance of execute immediatein PL/SQL when used with DML and DDL statements? Does execute immediateimplicitly commit to a database like DDL statements do ?
execute immediate当与 DML 和 DDL 语句一起使用时，PL/SQL 中的意义是什么？是否execute immediate像 DDL 语句那样隐式提交到数据库？
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;
回答by Alex Poole
It is for running native dynamic SQL.
它用于运行本机动态 SQL。
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.
对于 DML，您可以在运行编译时无法使用的语句时使用它，例如，如果列列表基于用户的选择。
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.
在您的情况下，它被使用是因为 DDL 不能从 PL/SQL 中作为静态 SQL 运行。只有某些查询、DML 和 TCL 命令有效。其他任何事情都必须被视为动态的。
I'd say it's rare to need to use DDL from a PL/SQL block. TRUNCATEmight 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.
我会说很少需要从 PL/SQL 块使用 DDL。TRUNCATE可能是合理的；如果您发现任何动态创建或删除对象的内容，那么这可能更令人担忧，因为它可能会建议一个次优的数据模型。
EXECUTE IMMEDIATEitself 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.
EXECUTE IMMEDIATE本身不会自动提交；但是如果你执行 DDL 那么它的行为就像你在 PL/SQL 之外运行它一样，所以它会在你的情况下提交，是的。
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';
i.e. without using V_SQL_1at all.
即V_SQL_1根本不使用。
回答by Gregory Stern
It provides end-to-end support when executing a dynamic SQL statement or an anonymous PL/SQL block. It substitutes the unknown values, executes the statement, returns the data, and then releases the resources.
它在执行动态 SQL 语句或匿名 PL/SQL 块时提供端到端支持。它替换未知值，执行语句，返回数据，然后释放资源。
EXECUTE IMMEDIATE [dynamic SQL string statement without terminator]
[INTO {define_variable [, define_variable] ... | record}]
[USING [IN|OUT|IN OUT] bind_argument [, [IN|OUT|IN OUT] bind_arguments] ];
define_variable is a PL/SQL variable or record that captures the result set of the SELECT query
define_variable 是一个 PL/SQL 变量或记录，用于捕获 SELECT 查询的结果集
Bind arguments are the actual values or local variables which would replace the bind variables in the SQL string statement.
绑定参数是实际值或局部变量，它们将替换 SQL 字符串语句中的绑定变量。
