12. SQLServer : 控制流 BEGIN...END{alert type="info"}-- BEGIN 和 END 定义一系列一起执行的 Transact-SQL 语句。 如果不包括 BEGIN...END 块，则将执行两个 ROLLBACK TRANSACTION 语句，并返回两条 PRINT 消息 USE AdventureWorks2012 GO BEGIN TRANSACTION GO IF @@TRANCOUNT = 0 BEGIN SELECT FirstName, MiddleName FROM Person.Person WHERE LastName = 'Adams'; ROLLBACK TRANSACTION; PRINT N'Rolling back the transaction two times would cause an error.'; END; ROLLBACK TRANSACTION; PRINT N'Rolled back the transaction.'; GO /* Rolled back the transaction. */ {/alert}BREAK{callout color="#a07df2"}BREAK 将退出当前 WHILE 循环。 如果当前循环嵌套在另一个循环中，BREAK 将只退出当前循环，并且将控制给予外部循环中的下一个语句。BREAK 通常位于 IF 语句中。{/callout}{alert type="info"}WHILE (1=1) BEGIN IF EXISTS (SELECT * FROM ##MyTempTable WHERE EventCode = 'Done') BEGIN BREAK; -- 'Done' row has finally been inserted and detected, so end this loop. END PRINT N'The other process is not yet done.'; -- Re-confirm the non-done status to the console. WAITFOR DELAY '00:01:30'; -- Sleep for 90 seconds. END{/alert}CONTINUE{callout color="#dcb174"}重新开始 WHILE 循环。 在 CONTINUE 关键字之后的任何语句都将被忽略。 CONTINUE 通常由一个 IF 测试打开，但并不始终这样。{/callout}{alert type="info"}DECLARE @m INT =1 WHILE (@m <= 10) BEGIN PRINT N'开始:'+ cast(@m as nvarchar(255)) IF @m = 3 BEGIN Set @m+=2 continue; END ELSE BEGIN Set @m+=1 END PRINT '@m:'+ cast(@m as nvarchar(255)) END select @m {/alert}IF...ELSE{alert type="info"}DECLARE @Number INT; SET @Number = 50; IF @Number > 100 PRINT 'The number is large.'; ELSE BEGIN IF @Number < 10 PRINT 'The number is small.'; ELSE PRINT 'The number is medium.'; END ; GO {/alert}GOTO{callout color="#beea6c"}将执行流更改到标签处。 跳过 GOTO 后面的 Transact-SQL 语句，并从标签位置继续处理。 GOTO 语句和标签可在过程、批处理或语句块中的任何位置使用。 GOTO 语句可嵌套使用。{/callout}{alert type="info"}DECLARE @Counter int; SET @Counter = 1; WHILE @Counter < 10 BEGIN SELECT @Counter SET @Counter = @Counter + 1 IF @Counter = 4 GOTO Branch_One --Jumps to the first branch. IF @Counter = 5 GOTO Branch_Two --This will never execute. END Branch_One: SELECT 'Jumping To Branch One.' GOTO Branch_Three; --This will prevent Branch_Two from executing. Branch_Two: SELECT 'Jumping To Branch Two.' Branch_Three: SELECT 'Jumping To Branch Three.'; {/alert}RETURN{callout color="#7496fb"}从查询或过程中无条件退出。 RETURN 的执行是即时且完全的，可在任何时候用于从过程、批处理或语句块中退出。 RETURN 之后的语句是不执行的。{/callout}{alert type="info"}CREATE PROCEDURE findjobs @nm sysname = NULL AS IF @nm IS NULL BEGIN PRINT 'You must give a user name' RETURN -- 之后的代码不会再执行 END ELSE BEGIN SELECT o.name, o.id, o.uid FROM sysobjects o INNER JOIN master..syslogins l ON o.uid = l.sid WHERE l.name = @nm END; {/alert}THROW{alert type="info"}-- 语法 THROW [ { error_number | @local_variable }, -- 状态 { message | @local_variable }, -- 错误信息 { state | @local_variable } ] -- 状态码 0 - 255 [ ; ] -- 示例 THROW 51000, 'The record does not exist.', 6; {/alert}TRY...CATCH{callout color="#84e3f0"} 如果 TRY 块内部发生错误，则会将控制传递给 CATCH 块中包含的另一个语句组。{/callout}{alert type="info"}-- 语法 BEGIN TRY { sql_statement | statement_block } END TRY BEGIN CATCH [ { sql_statement | statement_block } ] END CATCH [ ; ] -- 示例 BEGIN TRY -- Generate a divide-by-zero error. SELECT 1/0; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErrorNumber ,ERROR_SEVERITY() AS ErrorSeverity ,ERROR_STATE() AS ErrorState ,ERROR_PROCEDURE() AS ErrorProcedure ,ERROR_LINE() AS ErrorLine ,ERROR_MESSAGE() AS ErrorMessage; END CATCH; GO {/alert}WAITFOR{callout color="#8475f5"}阻止执行批处理、存储过程或事务，直到已过指定时间或时间间隔，或者指定语句发生修改或至少返回一行为止。{/callout}{alert type="info"}-- 语法 WAITFOR { DELAY 'time_to_pass' | TIME 'time_to_execute' | [ ( receive_statement ) | ( get_conversation_group_statement ) ] [ , TIMEOUT timeout ] } {/alert}WAITFOR TIME : 当时间某一刻时执行语句{alert type="info"}-- 在晚上 10:20 (22:20) 在 msdb 数据库中执行 sp_update_job 存储过程 EXECUTE sp_add_job @job_name = 'TestJob'; BEGIN WAITFOR TIME '22:20'; EXECUTE sp_update_job @job_name = 'TestJob', @new_name = 'UpdatedJob'; END; GO -- 只能是[时间]格式 BEGIN WAITFOR TIME '15:29:40' SELECT getdate() END{/alert}WAITFOR DELAY : 延迟指定时间之后运行语句{alert type="info"}-- 在两小时的延迟后执行存储过程 BEGIN WAITFOR DELAY '02:00'; BEGIN EXECUTE sp_helpdb; END END; GO {/alert}WHILE{callout color="#eccda2"}设置重复执行 SQL 语句或语句块的条件。 只要指定的条件为真，就重复执行语句。 可以使用 BREAK 和 CONTINUE 关键字在循环内部控制 WHILE 循环中语句的执行。{/callout}{alert type="info"}USE AdventureWorks2012; GO WHILE (SELECT AVG(ListPrice) FROM Production.Product) < $300 BEGIN UPDATE Production.Product SET ListPrice = ListPrice * 2 SELECT MAX(ListPrice) FROM Production.Product IF (SELECT MAX(ListPrice) FROM Production.Product) > $500 BREAK ELSE CONTINUE END PRINT 'Too much for the market to bear';{/alert}