我什么时候需要在 Oracle SQL 中使用分号和斜线?
这周我们在我的公司就应该如何编写 SQL 脚本进行了一些辩论.
We have been having some debate this week at my company as to how we should write our SQL scripts.
背景:我们的数据库是 Oracle 10g(即将升级到 11).我们的 DBA 团队使用 SQLPlus 将我们的脚本部署到生产环境中.
Background: Our database is Oracle 10g (upgrading to 11 soon). Our DBA team uses SQLPlus in order to deploy our scripts to production.
现在，我们最近有一个部署失败，因为它同时使用了分号和正斜杠 (/).分号位于每条语句的末尾，斜线位于语句之间.
Now, we had a deploy recently that failed because it had used both a semicolon and a forward slash (/). The semicolon was at the end of each statement and the slash was between statements.
alter table foo.bar drop constraint bar1;
/
alter table foo.can drop constraint can1;
/
稍后在脚本中添加了一些触发器，创建了一些视图以及一些存储过程.同时具有 ; 和 / 导致每个语句运行两次导致错误(尤其是在插入上，它需要是唯一的).
There were some triggers being added later on in the script, some views created as well as some stored procedures. Having both the ; and the / caused each statement to run twice causing errors (especially on the inserts, which needed to be unique).
在 SQL Developer 中不会发生这种情况，在 TOAD 中不会发生这种情况.如果您运行某些命令，它们将在没有 / 的情况下工作.
In SQL Developer this does not happen, in TOAD this does not happen. If you run certain commands they will not work without the / in them.
在 PL/SQL 中，如果您有子程序(DECLARE、BEGIN、END)，使用的分号将被视为子程序的一部分，因此您必须使用斜杠.
In PL/SQL if you have a subprogram (DECLARE, BEGIN, END) the semicolon used will be considered as part of the subprogram, so you have to use the slash.
所以我的问题是:如果您的数据库是 Oracle，那么编写 SQL 脚本的正确方法是什么?既然您知道您的数据库是 Oracle，您应该始终使用 / 吗?
So my question is this: If your database is Oracle, what is the proper way to write your SQL script? Since you know that your DB is Oracle should you always use the /?
推荐答案
这是一个偏好问题，但我更喜欢看到一致使用斜杠的脚本 - 这样所有单元"的工作(创建一个 PL/SQL 对象、运行 PL/SQL 匿名块和执行 DML 语句)可以更容易地通过肉眼辨别出来.
It's a matter of preference, but I prefer to see scripts that consistently use the slash - this way all "units" of work (creating a PL/SQL object, running a PL/SQL anonymous block, and executing a DML statement) can be picked out more easily by eye.
此外，如果您最终转向使用 Ant 之类的东西进行部署，它将简化目标的定义，以具有一致的语句分隔符.
Also, if you eventually move to something like Ant for deployment it will simplify the definition of targets to have a consistent statement delimiter.
相关文章