用习惯了oracle后,习惯在每个语句结束后以(;)结尾。
但是在sql中有哪些情况是必须使用分号的呢?
从一些英文文献中发现:
There are two situations in which you must use the semicolon.
The first situation is where you use a Common Table Expression (CTE),
and the CTE is not the first statement in the batch.
The second is where you issue a Service Broker statement
and the Service Broker statement is not the first statement in the batch.
我们来解释一下上面的两种情况:
第一种:
declare @t table(id int)
insert into @t
select 1 union
select 3 union
select 4
;with cr as
(
select * from @t
)
select * from cr
/*
id
-----------
1
3
4
*/
这里with前面的分号是必须要有的。
第二种:
--创建主密钥:
create master key encryption by password = 'Pass.word';
--发送和接收
waitfor (
receive top(1)
@message_type = message_type_name,
@message_body = message_body,
@dialog = conversation_handle
from dbo.InventoryQueue
), timeout 2000;
为了确保分析器能知道SEND和RECEIVE正是在开始一个新命令,SEND或RECEIVE之前的命令必须以
分号(;)结束.