我什么时候需要在 Oracle SQL 中使用分号和斜线？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1079949/
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
When do I need to use a semicolon vs a slash in Oracle SQL?
提问by amischiefr
We have been having some debate this week at my company as to how we should write our SQL scripts.
本周我们在我的公司就应该如何编写 SQL 脚本进行了一些辩论。
Background: Our database is Oracle 10g (upgrading to 11 soon). Our DBA team uses SQLPlus in order to deploy our scripts to production.
背景：我们的数据库是Oracle 10g（即将升级到11）。我们的 DBA 团队使用 SQLPlus 将我们的脚本部署到生产环境中。
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).
稍后在脚本中添加了一些触发器，创建了一些视图以及一些存储过程。有 the;和 the/导致每个语句运行两次导致错误（特别是在插入上，它需要是唯一的）。
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.
在 SQL Developer 中不会发生这种情况，在 TOAD 中不会发生这种情况。如果您运行某些命令，如果没有它们，它们将无法工作/。
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.
在 PL/SQL 中，如果您有子程序（DECLARE、BEGIN、END），所使用的分号将被视为子程序的一部分，因此您必须使用斜杠。
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 /?
所以我的问题是：如果您的数据库是 Oracle，那么编写 SQL 脚本的正确方法是什么？既然您知道您的数据库是 Oracle，您应该始终使用/?
采纳答案by dpbradley
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.
这是一个偏好问题，但我更喜欢看到始终使用斜杠的脚本 - 这样所有“单元”的工作（创建 PL/SQL 对象、运行 PL/SQL 匿名块和执行 DML 语句）都可以肉眼更容易辨认。
Also, if you eventually move to something like Ant for deployment it will simplify the definition of targets to have a consistent statement delimiter.
此外，如果您最终转向使用 Ant 之类的东西进行部署，它将简化目标的定义以具有一致的语句分隔符。
回答by a_horse_with_no_name
I know this is an old thread, but I just stumbled upon it and I feel this has not been explained completely.
我知道这是一个旧线程，但我只是偶然发现了它，我觉得这还没有完全解释清楚。
There is a huge difference in SQL*Plus between the meaning of a /and a ;because they work differently.
SQL*Plus 中 a/和 a的含义存在巨大差异，;因为它们的工作方式不同。
The ;ends a SQL statement, whereas the /executes whatever is in the current "buffer". So when you use a ;anda /the statement is actually executed twice.
在;结束一个SQL语句，而/无论是在当前执行“缓冲”。因此，当您使用 a;和a 时，/该语句实际上会执行两次。
You can easily see that using a /after running a statement:
您可以很容易地看到/在运行语句后使用 a ：
SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 18 12:37:20 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning and OLAP options
SQL> drop table foo;
Table dropped.
SQL> /
drop table foo
*
ERROR at line 1:
ORA-00942: table or view does not exist
In this case one actually notices the error.
But assuming there is a SQL script like this:
在这种情况下，实际上会注意到错误。
但假设有这样的 SQL 脚本：
drop table foo;
/
And this is run from within SQL*Plus then this will be very confusing:
这是从 SQL*Plus 中运行的，那么这将非常令人困惑：
SQL*Plus: Release 11.2.0.1.0 Production on Wed Apr 18 12:38:05 2012
Copyright (c) 1982, 2010, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning and OLAP options
SQL> @drop
Table dropped.
drop table foo
*
ERROR at line 1:
ORA-00942: table or view does not exist
The /is mainly required in order to run statements that have embedded ;like a CREATE PROCEDUREstatement.
该/要求主要是为了运行已嵌入语句;就像一个CREATE PROCEDURE声明。
回答by Mr_Moneybags
I wanted to clarify some more use between the ;and the /
我想澄清一下;和之间的更多用途/
In SQLPLUS:
在 SQLPLUS 中：
;means "terminate the current statement, execute it and store it to the SQLPLUS buffer"<newline>after a D.M.L. (SELECT, UPDATE, INSERT,...) statement or some types of D.D.L (Creating Tables and Views) statements (that contain no;), it means, store the statement to the buffer but do not run it./after entering a statement into the buffer (with a blank<newline>) means "run the D.M.L. or D.D.L. or PL/SQL in the buffer.RUNorRis a sqlsplus command to show/output the SQL in the buffer and run it. It will not terminate a SQL Statement./during the entering of a D.M.L. or D.D.L. or PL/SQL means "terminate the current statement, execute it and store it to the SQLPLUS buffer"
;表示“终止当前语句，执行它并将其存储到 SQLPLUS 缓冲区”<newline>在 DML (SELECT, UPDATE, INSERT,...) 语句或某些类型的 DDL（创建表和视图）语句（不包含;）之后，这意味着将语句存储到缓冲区但不运行它。/将语句输入缓冲区后（带有空格<newline>）表示“在缓冲区中运行 DML 或 DDL 或 PL/SQL。RUN或者R是一个 sqlsplus 命令，用于显示/输出缓冲区中的 SQL 并运行它。它不会终止 SQL 语句。/在输入 DML 或 DDL 或 PL/SQL 期间意味着“终止当前语句，执行它并将其存储到 SQLPLUS 缓冲区”
NOTE:Because ;are used for PL/SQL to end a statement ;cannot be used by SQLPLUS to mean "terminate the current statement, execute it and store it to the SQLPLUS buffer" because we want the whole PL/SQL block to be completely in the buffer, then execute it. PL/SQL blocks must end with:
注意：因为;用于 PL/SQL 结束语句;不能被 SQLPLUS 用来表示“终止当前语句，执行它并将其存储到 SQLPLUS 缓冲区”，因为我们希望整个 PL/SQL 块完全在缓冲，然后执行它。PL/SQL 块必须以：
END;
/
回答by jva
Almost all Oracle deployments are done through SQL*Plus (that weird little command line tool that your DBA uses). And in SQL*Plus a lone slash basically means "re-execute last SQL or PL/SQL command that I just executed".
几乎所有 Oracle 部署都是通过 SQL*Plus（您的 DBA 使用的那个奇怪的小命令行工具）完成的。在 SQL*Plus 中，单独的斜杠基本上意味着“重新执行我刚刚执行的最后一个 SQL 或 PL/SQL 命令”。
See
看
Rule of thumb would be to use slash with things that do BEGIN .. ENDor where you can use CREATE OR REPLACE.
经验法则是将斜杠用于BEGIN .. END可以使用的东西或可以使用的地方CREATE OR REPLACE。
For inserts that need to be unique use
对于需要独特用途的刀片
INSERT INTO my_table ()
SELECT <values to be inserted>
FROM dual
WHERE NOT EXISTS (SELECT
FROM my_table
WHERE <identify data that you are trying to insert>)
回答by Jerry
From my understanding, all the SQL statement don't need forward slash as they will run automatically at the end of semicolons, including DDL, DML, DCL and TCL statements.
根据我的理解，所有的SQL语句都不需要正斜杠，因为它们会在分号末尾自动运行，包括DDL、DML、DCL和TCL语句。
For other PL/SQL blocks, including Procedures, Functions, Packages and Triggers, because they are multiple line programs, Oracle need a way to know when to run the block, so we have to write a forward slash at the end of each block to let Oracle run it.
对于其他PL/SQL块，包括Procedures、Functions、Packages和Triggers，由于是多行程序，Oracle需要有办法知道什么时候运行块，所以我们必须在每个块的末尾写一个正斜杠来表示让 Oracle 运行它。
回答by Jonathan
I only use the forward slash once at the end of each script, to tell sqlplus that there is not more lines of code. In the middle of a script, I do not use a slash.
我只在每个脚本的末尾使用一次正斜杠，告诉 sqlplus 没有更多的代码行。在脚本中间，我不使用斜杠。
