ORACLE 在立即执行中批处理 DDL 语句
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1241611/
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 Batching DDL statements within a Execute Immediate
提问by Beta033
I'm trying to run multiple ddl statements within one Execute Immediate statement. i thought this would be pretty straight forward but it seems i'm mistaken.
我试图在一个 Execute Immediate 语句中运行多个 ddl 语句。我认为这会很直接，但似乎我错了。
The idea is this:
这个想法是这样的：
declare v_cnt number;
begin
select count(*) into v_cnt from all_tables where table_name='TABLE1' and owner = 'AMS';
if v_cnt = 0 then
execute immediate 'CREATE TABLE TABLE1(VALUE VARCHAR2(50) NOT NULL) ALTER TABLE TABLE1 ADD (MYVAL2 NVARCHAR2(10))';
end if;
end;
however this results in an error
但是这会导致错误
ORA-00911: invalid character ORA-06512: at line 10
ORA-00911: 无效字符 ORA-06512: 在第 10 行
Each of the statements within the batch run fine if i execute them by themselves. and if i take this statement and execute it, it will run fine (with the ; between the 2 statements). If i remove the ; between statements i get a different error about invalid option
如果我自己执行它们，批处理中的每个语句都可以正常运行。如果我接受这个语句并执行它，它会运行良好（在 ; 两个语句之间）。如果我删除; 在语句之间，我收到有关无效选项的不同错误
the plan is that i'll be able to build a table, export the table schema for this table including all it's alter statements, and then run the batch against another system as part of an install/update process.
计划是我将能够构建一个表，导出该表的表架构，包括它的所有更改语句，然后作为安装/更新过程的一部分针对另一个系统运行批处理。
So, how do i batch these DDL statements within a single execute immediate? Or is there a better way to do what i'm needing?
那么，我如何在单个立即执行中批处理这些 DDL 语句？或者有没有更好的方法来做我需要的事情？
I'm a bit of a Oracle newb, i must admit. Thank you all for your patience.
我是一个 Oracle 新手，我必须承认。感谢大家的耐心等待。
采纳答案by cagcowboy
Why do you need a single EXECUTE IMMEDIATE call? Surely just do it as 2 calls?
为什么需要一个 EXECUTE IMMEDIATE 调用？当然只是做 2 个电话吗？
Bear in mind that each DDL statement contains an implicit COMMIT, so there's no concurency benefit to doing it as a single call.
请记住，每个 DDL 语句都包含一个隐式 COMMIT，因此将其作为单个调用执行并没有并发优势。
Also, why not just set up the table correctly in the first call? You could do...
另外，为什么不在第一次通话中正确设置桌子？你可以做...
CREATE TABLE TABLE1(VALUE VARCHAR2(50) NOT NULL, MYVAL2 NVARCHAR2(10))
创建表 TABLE1(VALUE VARCHAR2(50) NOT NULL, MYVAL2 NVARCHAR2(10))
...instead of needing 2 calls.
...而不是需要 2 个电话。
Also, have you looked at DBMS_METADATA... it can generate DDL for objects such as tables for you.
另外，您是否看过 DBMS_METADATA ... 它可以为您生成诸如表之类的对象的 DDL。
回答by Shannon Severance
The semicolon is not part of Oracle's SQL syntax. SQL*Plus and other client side tools use semicolon to signal the end of a SQL Statement, but the server doesn't see it.
分号不是 Oracle 的 SQL 语法的一部分。SQL*Plus 和其他客户端工具使用分号来表示 SQL 语句的结束，但服务器看不到它。
We can force SQL*Plus to pass the semicolon to the DB:
我们可以强制 SQL*Plus 将分号传递给数据库：
SQL> set sqlterminator off
SQL> select * from user_tables;
2 /
select * from user_tables;
*
ERROR at line 1:
ORA-00911: invalid character
If i take this statement and execute it, it will run fine (with the ; between the 2 statements)The client tool you are using, breaks it into two calls to the DB.
如果我使用此语句并执行它，它将运行良好（在 2 个语句之间使用 ;）您正在使用的客户端工具将其分解为对数据库的两次调用。
So, I don't think it is possible to pass multiple statements inside an execute immediate.
所以，我认为不可能在立即执行中传递多个语句。
I suppose one could call execute immediate with a string containing an anonymous PL/SQL block, with individual calls to execute immediate inside it ... and I don't know what the point of doing that would be. ;)
我想人们可以使用包含匿名 PL/SQL 块的字符串调用立即执行，在其中单独调用立即执行......我不知道这样做的意义是什么。;)
