什么时候需要在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屋！