SQL Server进阶(11)可编程对象——变量、 批、流元素、 游标
变量
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
--------------------------------------------------------------------- -- Variables --------------------------------------------------------------------- -- Declare a variable and initialize it with a value DECLARE @i AS INT; SET @i = 10; GO -- Declare and initialize a variable in the same statement DECLARE @i AS INT = 10; GO -- Store the result of a subquery in a variable DECLARE @empname AS NVARCHAR(31); SET @empname = (SELECT firstname + N' ' + lastname FROM HR.Employees WHERE empid = 3); SELECT @empname AS empname; GO -- Using the SET command to assign one variable at a time DECLARE @firstname AS NVARCHAR(10), @lastname AS NVARCHAR(20); SET @firstname = (SELECT firstname FROM HR.Employees WHERE empid = 3); SET @lastname = (SELECT lastname FROM HR.Employees WHERE empid = 3); SELECT @firstname AS firstname, @lastname AS lastname; GO -- Using the SELECT command to assign multiple variables in the same statement DECLARE @firstname AS NVARCHAR(10), @lastname AS NVARCHAR(20); SELECT @firstname = firstname, @lastname = lastname FROM HR.Employees WHERE empid = 3; SELECT @firstname AS firstname, @lastname AS lastname; GO -- SELECT doesn't fail when multiple rows qualify DECLARE @empname AS NVARCHAR(31); SELECT @empname = firstname + N' ' + lastname FROM HR.Employees WHERE mgrid = 2; SELECT @empname AS empname; GO -- SET fails when multiple rows qualify DECLARE @empname AS NVARCHAR(31); SET @empname = (SELECT firstname + N' ' + lastname FROM HR.Employees WHERE mgrid = 2); SELECT @empname AS empname; GO
批
流元素
IF ... ELSE
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
-- The IF ... ELSE Flow Element IF YEAR(SYSDATETIME()) <> YEAR(DATEADD(day, 1, SYSDATETIME())) PRINT 'Today is the last day of the year.'; ELSE PRINT 'Today is not the last day of the year.'; GO
IF ELSE IF
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
-- IF ELSE IF IF YEAR(SYSDATETIME()) <> YEAR(DATEADD(day, 1, SYSDATETIME())) PRINT 'Today is the last day of the year.'; ELSE IF MONTH(SYSDATETIME()) <> MONTH(DATEADD(day, 1, SYSDATETIME())) PRINT 'Today is the last day of the month but not the last day of the year.'; ELSE PRINT 'Today is not the last day of the month.'; GO
语句块
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
-- Statement Block IF DAY(SYSDATETIME()) = 1 BEGIN PRINT 'Today is the first day of the month.'; PRINT 'Starting first-of-month-day process.'; /* ... process code goes here ... */ PRINT 'Finished first-of-month-day database process.'; END ELSE BEGIN PRINT 'Today is not the first day of the month.'; PRINT 'Starting non-first-of-month-day process.'; /* ... process code goes here ... */ PRINT 'Finished non-first-of-month-day process.'; END GO
WHILE
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
-- The WHILE Flow Element DECLARE @i AS INT = 1; WHILE @i <= 10 BEGIN PRINT @i; SET @i = @i + 1; END; GO
BREAK
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
-- BREAK DECLARE @i AS INT = 1; WHILE @i <= 10 BEGIN IF @i = 6 BREAK; PRINT @i; SET @i = @i + 1; END; GO
CONTINUE
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
-- CONTINUE DECLARE @i AS INT = 0; WHILE @i < 10 BEGIN SET @i = @i + 1; IF @i = 6 CONTINUE; PRINT @i; END; GO
An Example of Using IF and WHILE
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
-- An Example of Using IF and WHILE SET NOCOUNT ON; IF OBJECT_ID('dbo.Numbers', 'U') IS NOT NULL DROP TABLE dbo.Numbers; CREATE TABLE dbo.Numbers(n INT NOT NULL PRIMARY KEY); GO DECLARE @i AS INT = 1; WHILE @i <= 1000 BEGIN INSERT INTO dbo.Numbers(n) VALUES(@i); SET @i = @i + 1; END GO
游标
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
-- Example: Running Aggregations SET NOCOUNT ON; DECLARE @Result TABLE ( custid INT, ordermonth DATETIME, qty INT, runqty INT, PRIMARY KEY(custid, ordermonth) ); DECLARE @custid AS INT, @prvcustid AS INT, @ordermonth DATETIME, @qty AS INT, @runqty AS INT; DECLARE C CURSOR FAST_FORWARD /* read only, forward only */ FOR SELECT custid, ordermonth, qty FROM Sales.CustOrders ORDER BY custid, ordermonth; OPEN C; FETCH NEXT FROM C INTO @custid, @ordermonth, @qty; SELECT @prvcustid = @custid, @runqty = 0; WHILE @@FETCH_STATUS = 0 BEGIN IF @custid <> @prvcustid SELECT @prvcustid = @custid, @runqty = 0; SET @runqty = @runqty + @qty; INSERT INTO @Result VALUES(@custid, @ordermonth, @qty, @runqty); FETCH NEXT FROM C INTO @custid, @ordermonth, @qty; END CLOSE C; DEALLOCATE C; SELECT custid, CONVERT(VARCHAR(7), ordermonth, 121) AS ordermonth, qty, runqty FROM @Result ORDER BY custid, ordermonth; GO
2012支持的增强开窗函数
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
SELECT custid, ordermonth, qty, SUM(qty) OVER(PARTITION BY custid ORDER BY ordermonth ROWS UNBOUNDED PRECEDING) AS runqty FROM Sales.CustOrders ORDER BY custid, ordermonth;