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}

9. SQLServer : 语言元素-控制流 BEGIN...END{callout color="#60ef4d"}包括一系列的 Transact-SQL 语句，从而可以执行一组 Transact-SQL 语句。 BEGIN 和 END 是控制流语言的关键字。{/callout}{alert type="success"}-- 语法 BEGIN { sql_statement | statement_block } END --示例 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="#4defed"}BREAK 将退出当前 WHILE 循环。 如果当前循环嵌套在另一个循环中，BREAK 将只退出当前循环，并且将控制给予外部循环中的下一个语句。{/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}ELSE (IF...ELSE){alert type="success"}-- 语法 IF Boolean_expression { sql_statement | statement_block } [ ELSE { sql_statement | statement_block } ] -- 示例一 IF 1 = 2 PRINT 'Boolean_expression is true.' ELSE PRINT 'Boolean_expression is false.' ; GO -- 示例二 USE AdventureWorks2012; GO IF (SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ) > 5 PRINT 'There are more than 5 Touring-3000 bicycles.' ELSE PRINT 'There are 5 or less Touring-3000 bicycles.' ; GO -- 示例三 USE AdventureWorks2012; GO DECLARE @AvgWeight DECIMAL(8,2), @BikeCount INT IF (SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ) > 5 BEGIN SET @BikeCount = (SELECT COUNT(*) FROM Production.Product WHERE Name LIKE 'Touring-3000%'); SET @AvgWeight = (SELECT AVG(Weight) FROM Production.Product WHERE Name LIKE 'Touring-3000%'); PRINT 'There are ' + CAST(@BikeCount AS VARCHAR(3)) + ' Touring-3000 bikes.' PRINT 'The average weight of the top 5 Touring-3000 bikes is ' + CAST(@AvgWeight AS VARCHAR(8)) + '.'; END ELSE BEGIN SET @AvgWeight = (SELECT AVG(Weight) FROM Production.Product WHERE Name LIKE 'Touring-3000%' ); PRINT 'Average weight of the Touring-3000 bikes is ' + CAST(@AvgWeight AS VARCHAR(8)) + '.' ; END ; GO -- 示例四 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="#4d75ef"}将执行流更改到标签处。 跳过 GOTO 后面的 Transact-SQL 语句，并从标签位置继续处理。 GOTO 语句和标签可在过程、批处理或语句块中的任何位置使用。 GOTO 语句可嵌套使用。{/callout}{alert type="success"}-- 语法 Define the label: label: Alter the execution: GOTO label -- 示例 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="#4def93"}从查询或过程中无条件退出。 RETURN 的执行是即时且完全的，可在任何时候用于从过程、批处理或语句块中退出。 RETURN 之后的语句是不执行的。{/callout}{alert type="success"}-- 示例一 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; -- 示例二 USE AdventureWorks2012; GO CREATE PROCEDURE checkstate @param VARCHAR(11) AS IF (SELECT StateProvince FROM Person.vAdditionalContactInfo WHERE ContactID = @param) = 'WA' RETURN 1 ELSE RETURN 2; GO {/alert}THROW{callout color="#4da1ef"}引发异常，并将执行转移到 TRY...CATCH 构造的 CATCH 块。{/callout}{alert type="success"}-- 语法 THROW [ { error_number | @local_variable }, { message | @local_variable }, { state | @local_variable } ] [ ; ] -- error_number : 表示异常的常量或变量 error_number 为 int，并且必须大于或等于50000且小于或等于2147483647 -- message : 描述异常的字符串或变量。 message 为 nvarchar(2048) -- State : 在 0 到 255 之间的常量或变量，指示与消息关联的状态。 state 为 tinyint -- 示例一 THROW 51000, 'The record does not exist.', 1; -- 示例二 USE tempdb; GO CREATE TABLE dbo.TestRethrow ( ID INT PRIMARY KEY ); BEGIN TRY INSERT dbo.TestRethrow(ID) VALUES(1); -- Force error 2627, Violation of PRIMARY KEY constraint to be raised. INSERT dbo.TestRethrow(ID) VALUES(1); END TRY BEGIN CATCH PRINT 'In catch block.'; THROW; END CATCH; {/alert}TRY...CATCH{alert type="success"}-- 语法 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; {/alert}WHILE{callout color="#4dbfef"}设置重复执行 SQL 语句或语句块的条件。 只要指定的条件为真，就重复执行语句。 可以使用 BREAK 和 CONTINUE 关键字在循环内部控制 WHILE 循环中语句的执行。{/callout}{alert type="success"}-- 示例 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'; -- 示例二 WHILE ( SELECT AVG(ListPrice) FROM dbo.DimProduct) < $300 BEGIN UPDATE dbo.DimProduct SET ListPrice = ListPrice * 2; SELECT MAX ( ListPrice) FROM dbo.DimProduct IF ( SELECT MAX (ListPrice) FROM dbo.DimProduct) > $500 BREAK; END {/alert}