我什么时候需要在 Oracle SQL 中使用分号和斜杠? [英] When do I need to use a semicolon vs a slash in 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.

这篇关于我什么时候需要在 Oracle SQL 中使用分号和斜杠?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！