SQL Server/T-SQL/Transact SQL/While — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
| |
Текущая версия на 13:20, 26 мая 2010
Содержание
Looping with the WHILE Statement
<source lang="sql">
WHILE <Boolean expression>
<sql statement> |
[BEGIN
<statement block>
[BREAK]
<sql statement> | <statement block>
[CONTINUE]
END]
WHILE 1 = 1 BEGIN
WAITFOR TIME "01:00"
RAISERROR("Statistics Updated for Database", 1, 1) WITH LOG
END
</source>
Use while loop to insert data
<source lang="sql">
6> IF OBJECT_ID("dbo.Nums") IS NOT NULL 7> DROP TABLE dbo.Nums; 8> GO 1> CREATE TABLE dbo.Nums(n INT NOT NULL PRIMARY KEY); 2> DECLARE @max AS INT, @rc AS INT; 3> SET @max = 1000000; 4> SET @rc = 1; 5> 6> INSERT INTO Nums VALUES(1); 7> WHILE @rc * 2 <= @max 8> BEGIN 9> INSERT INTO dbo.Nums SELECT n + @rc FROM dbo.Nums; 10> SET @rc = @rc * 2; 11> END 12> 13> INSERT INTO dbo.Nums 14> SELECT n + @rc FROM dbo.Nums WHERE n + @rc <= @max; 15> 16> DECLARE @s AS DATETIME, @e AS DATETIME; 17> SET @s = "20060101"; 18> SET @e = "20061231"; 19> 20> SELECT @s + n - 1 AS dt 21> FROM dbo.Nums 22> WHERE n <= DATEDIFF(day, @s, @e) + 1; 23> GO (1 rows affected) (2 rows affected) (4 rows affected) (8 rows affected) (16 rows affected) (32 rows affected) (64 rows affected) (128 rows affected) (256 rows affected) (512 rows affected) (1024 rows affected) (2048 rows affected) (4096 rows affected) (8192 rows affected) (16384 rows affected) (32768 rows affected) (65536 rows affected) (131072 rows affected) (262144 rows affected) (475712 rows affected) dt
2006-01-01 00:00:00.000 2006-01-02 00:00:00.000 2006-01-03 00:00:00.000 2006-01-04 00:00:00.000 2006-01-05 00:00:00.000 2006-01-06 00:00:00.000 2006-01-07 00:00:00.000 2006-01-08 00:00:00.000 2006-01-09 00:00:00.000 2006-01-10 00:00:00.000 2006-01-11 00:00:00.000 2006-01-12 00:00:00.000 2006-01-13 00:00:00.000 2006-01-14 00:00:00.000 2006-01-15 00:00:00.000 2006-01-16 00:00:00.000 2006-01-17 00:00:00.000 2006-01-18 00:00:00.000 2006-01-19 00:00:00.000 2006-01-20 00:00:00.000 2006-01-21 00:00:00.000 2006-01-22 00:00:00.000 2006-01-23 00:00:00.000 2006-01-24 00:00:00.000 2006-01-25 00:00:00.000 2006-01-26 00:00:00.000 2006-01-27 00:00:00.000 2006-01-28 00:00:00.000 2006-01-29 00:00:00.000 2006-01-30 00:00:00.000 2006-01-31 00:00:00.000 2006-02-01 00:00:00.000 2006-02-02 00:00:00.000 2006-02-03 00:00:00.000 2006-02-04 00:00:00.000 2006-02-05 00:00:00.000 2006-02-06 00:00:00.000 2006-02-07 00:00:00.000 2006-02-08 00:00:00.000 2006-02-09 00:00:00.000 2006-02-10 00:00:00.000 2006-02-11 00:00:00.000 2006-02-12 00:00:00.000 2006-02-13 00:00:00.000 2006-02-14 00:00:00.000 2006-02-15 00:00:00.000 2006-02-16 00:00:00.000 2006-02-17 00:00:00.000 2006-02-18 00:00:00.000 2006-02-19 00:00:00.000 2006-02-20 00:00:00.000 2006-02-21 00:00:00.000 2006-02-22 00:00:00.000 2006-02-23 00:00:00.000 2006-02-24 00:00:00.000 2006-02-25 00:00:00.000 2006-02-26 00:00:00.000 2006-02-27 00:00:00.000 2006-02-28 00:00:00.000 2006-03-01 00:00:00.000 2006-03-02 00:00:00.000 2006-03-03 00:00:00.000 2006-03-04 00:00:00.000 2006-03-05 00:00:00.000 2006-03-06 00:00:00.000 2006-03-07 00:00:00.000 2006-03-08 00:00:00.000 2006-03-09 00:00:00.000 2006-03-10 00:00:00.000 2006-03-11 00:00:00.000 2006-03-12 00:00:00.000 2006-03-13 00:00:00.000 2006-03-14 00:00:00.000 2006-03-15 00:00:00.000 2006-03-16 00:00:00.000 2006-03-17 00:00:00.000 2006-03-18 00:00:00.000 2006-03-19 00:00:00.000 2006-03-20 00:00:00.000 2006-03-21 00:00:00.000 2006-03-22 00:00:00.000 2006-03-23 00:00:00.000 2006-03-24 00:00:00.000 2006-03-25 00:00:00.000 2006-03-26 00:00:00.000 2006-03-27 00:00:00.000 2006-03-28 00:00:00.000 2006-03-29 00:00:00.000 2006-03-30 00:00:00.000 2006-03-31 00:00:00.000 2006-04-01 00:00:00.000 2006-04-02 00:00:00.000 2006-04-03 00:00:00.000 2006-04-04 00:00:00.000 2006-04-05 00:00:00.000 2006-04-06 00:00:00.000 2006-04-07 00:00:00.000 2006-04-08 00:00:00.000 2006-04-09 00:00:00.000 2006-04-10 00:00:00.000 2006-04-11 00:00:00.000 2006-04-12 00:00:00.000 2006-04-13 00:00:00.000 2006-04-14 00:00:00.000 2006-04-15 00:00:00.000 2006-04-16 00:00:00.000 2006-04-17 00:00:00.000 2006-04-18 00:00:00.000 2006-04-19 00:00:00.000 2006-04-20 00:00:00.000 2006-04-21 00:00:00.000 2006-04-22 00:00:00.000 2006-04-23 00:00:00.000 2006-04-24 00:00:00.000 2006-04-25 00:00:00.000 2006-04-26 00:00:00.000 2006-04-27 00:00:00.000 2006-04-28 00:00:00.000 2006-04-29 00:00:00.000 2006-04-30 00:00:00.000 2006-05-01 00:00:00.000 2006-05-02 00:00:00.000 2006-05-03 00:00:00.000 2006-05-04 00:00:00.000 2006-05-05 00:00:00.000 2006-05-06 00:00:00.000 2006-05-07 00:00:00.000 2006-05-08 00:00:00.000 2006-05-09 00:00:00.000 2006-05-10 00:00:00.000 2006-05-11 00:00:00.000 2006-05-12 00:00:00.000 2006-05-13 00:00:00.000 2006-05-14 00:00:00.000 2006-05-15 00:00:00.000 2006-05-16 00:00:00.000 2006-05-17 00:00:00.000 2006-05-18 00:00:00.000 2006-05-19 00:00:00.000 2006-05-20 00:00:00.000 2006-05-21 00:00:00.000 2006-05-22 00:00:00.000 2006-05-23 00:00:00.000 2006-05-24 00:00:00.000 2006-05-25 00:00:00.000 2006-05-26 00:00:00.000 2006-05-27 00:00:00.000 2006-05-28 00:00:00.000 2006-05-29 00:00:00.000 2006-05-30 00:00:00.000 2006-05-31 00:00:00.000 2006-06-01 00:00:00.000 2006-06-02 00:00:00.000 2006-06-03 00:00:00.000 2006-06-04 00:00:00.000 2006-06-05 00:00:00.000 2006-06-06 00:00:00.000 2006-06-07 00:00:00.000 2006-06-08 00:00:00.000 2006-06-09 00:00:00.000 2006-06-10 00:00:00.000 2006-06-11 00:00:00.000 2006-06-12 00:00:00.000 2006-06-13 00:00:00.000 2006-06-14 00:00:00.000 2006-06-15 00:00:00.000 2006-06-16 00:00:00.000 2006-06-17 00:00:00.000 2006-06-18 00:00:00.000 2006-06-19 00:00:00.000 2006-06-20 00:00:00.000 2006-06-21 00:00:00.000 2006-06-22 00:00:00.000 2006-06-23 00:00:00.000 2006-06-24 00:00:00.000 2006-06-25 00:00:00.000 2006-06-26 00:00:00.000 2006-06-27 00:00:00.000 2006-06-28 00:00:00.000 2006-06-29 00:00:00.000 2006-06-30 00:00:00.000 2006-07-01 00:00:00.000 2006-07-02 00:00:00.000 2006-07-03 00:00:00.000 2006-07-04 00:00:00.000 2006-07-05 00:00:00.000 2006-07-06 00:00:00.000 2006-07-07 00:00:00.000 2006-07-08 00:00:00.000 2006-07-09 00:00:00.000 2006-07-10 00:00:00.000 2006-07-11 00:00:00.000 2006-07-12 00:00:00.000 2006-07-13 00:00:00.000 2006-07-14 00:00:00.000 2006-07-15 00:00:00.000 2006-07-16 00:00:00.000 2006-07-17 00:00:00.000 2006-07-18 00:00:00.000 2006-07-19 00:00:00.000 2006-07-20 00:00:00.000 2006-07-21 00:00:00.000 2006-07-22 00:00:00.000 2006-07-23 00:00:00.000 2006-07-24 00:00:00.000 2006-07-25 00:00:00.000 2006-07-26 00:00:00.000 2006-07-27 00:00:00.000 2006-07-28 00:00:00.000 2006-07-29 00:00:00.000 2006-07-30 00:00:00.000 2006-07-31 00:00:00.000 2006-08-01 00:00:00.000 2006-08-02 00:00:00.000 2006-08-03 00:00:00.000 2006-08-04 00:00:00.000 2006-08-05 00:00:00.000 2006-08-06 00:00:00.000 2006-08-07 00:00:00.000 2006-08-08 00:00:00.000 2006-08-09 00:00:00.000 2006-08-10 00:00:00.000 2006-08-11 00:00:00.000 2006-08-12 00:00:00.000 2006-08-13 00:00:00.000 2006-08-14 00:00:00.000 2006-08-15 00:00:00.000 2006-08-16 00:00:00.000 2006-08-17 00:00:00.000 2006-08-18 00:00:00.000 2006-08-19 00:00:00.000 2006-08-20 00:00:00.000 2006-08-21 00:00:00.000 2006-08-22 00:00:00.000 2006-08-23 00:00:00.000 2006-08-24 00:00:00.000 2006-08-25 00:00:00.000 2006-08-26 00:00:00.000 2006-08-27 00:00:00.000 2006-08-28 00:00:00.000 2006-08-29 00:00:00.000 2006-08-30 00:00:00.000 2006-08-31 00:00:00.000 2006-09-01 00:00:00.000 2006-09-02 00:00:00.000 2006-09-03 00:00:00.000 2006-09-04 00:00:00.000 2006-09-05 00:00:00.000 2006-09-06 00:00:00.000 2006-09-07 00:00:00.000 2006-09-08 00:00:00.000 2006-09-09 00:00:00.000 2006-09-10 00:00:00.000 2006-09-11 00:00:00.000 2006-09-12 00:00:00.000 2006-09-13 00:00:00.000 2006-09-14 00:00:00.000 2006-09-15 00:00:00.000 2006-09-16 00:00:00.000 2006-09-17 00:00:00.000 2006-09-18 00:00:00.000 2006-09-19 00:00:00.000 2006-09-20 00:00:00.000 2006-09-21 00:00:00.000 2006-09-22 00:00:00.000 2006-09-23 00:00:00.000 2006-09-24 00:00:00.000 2006-09-25 00:00:00.000 2006-09-26 00:00:00.000 2006-09-27 00:00:00.000 2006-09-28 00:00:00.000 2006-09-29 00:00:00.000 2006-09-30 00:00:00.000 2006-10-01 00:00:00.000 2006-10-02 00:00:00.000 2006-10-03 00:00:00.000 2006-10-04 00:00:00.000 2006-10-05 00:00:00.000 2006-10-06 00:00:00.000 2006-10-07 00:00:00.000 2006-10-08 00:00:00.000 2006-10-09 00:00:00.000 2006-10-10 00:00:00.000 2006-10-11 00:00:00.000 2006-10-12 00:00:00.000 2006-10-13 00:00:00.000 2006-10-14 00:00:00.000 2006-10-15 00:00:00.000 2006-10-16 00:00:00.000 2006-10-17 00:00:00.000 2006-10-18 00:00:00.000 2006-10-19 00:00:00.000 2006-10-20 00:00:00.000 2006-10-21 00:00:00.000 2006-10-22 00:00:00.000 2006-10-23 00:00:00.000 2006-10-24 00:00:00.000 2006-10-25 00:00:00.000 2006-10-26 00:00:00.000 2006-10-27 00:00:00.000 2006-10-28 00:00:00.000 2006-10-29 00:00:00.000 2006-10-30 00:00:00.000 2006-10-31 00:00:00.000 2006-11-01 00:00:00.000 2006-11-02 00:00:00.000 2006-11-03 00:00:00.000 2006-11-04 00:00:00.000 2006-11-05 00:00:00.000 2006-11-06 00:00:00.000 2006-11-07 00:00:00.000 2006-11-08 00:00:00.000 2006-11-09 00:00:00.000 2006-11-10 00:00:00.000 2006-11-11 00:00:00.000 2006-11-12 00:00:00.000 2006-11-13 00:00:00.000 2006-11-14 00:00:00.000 2006-11-15 00:00:00.000 2006-11-16 00:00:00.000 2006-11-17 00:00:00.000 2006-11-18 00:00:00.000 2006-11-19 00:00:00.000 2006-11-20 00:00:00.000 2006-11-21 00:00:00.000 2006-11-22 00:00:00.000 2006-11-23 00:00:00.000 2006-11-24 00:00:00.000 2006-11-25 00:00:00.000 2006-11-26 00:00:00.000 2006-11-27 00:00:00.000 2006-11-28 00:00:00.000 2006-11-29 00:00:00.000 2006-11-30 00:00:00.000 2006-12-01 00:00:00.000 2006-12-02 00:00:00.000 2006-12-03 00:00:00.000 2006-12-04 00:00:00.000 2006-12-05 00:00:00.000 2006-12-06 00:00:00.000 2006-12-07 00:00:00.000 2006-12-08 00:00:00.000 2006-12-09 00:00:00.000 2006-12-10 00:00:00.000 2006-12-11 00:00:00.000 2006-12-12 00:00:00.000 2006-12-13 00:00:00.000 2006-12-14 00:00:00.000 2006-12-15 00:00:00.000 2006-12-16 00:00:00.000 2006-12-17 00:00:00.000 2006-12-18 00:00:00.000 2006-12-19 00:00:00.000 2006-12-20 00:00:00.000 2006-12-21 00:00:00.000 2006-12-22 00:00:00.000 2006-12-23 00:00:00.000 2006-12-24 00:00:00.000 2006-12-25 00:00:00.000 2006-12-26 00:00:00.000 2006-12-27 00:00:00.000 2006-12-28 00:00:00.000 2006-12-29 00:00:00.000 2006-12-30 00:00:00.000 2006-12-31 00:00:00.000 (365 rows affected)
</source>
While Loop Demo
<source lang="sql">
1> create table employee( 2> ID int, 3> name nvarchar (10), 4> salary int, 5> start_date datetime, 6> city nvarchar (10), 7> region char (1)) 8> GO 1> 2> insert into employee (ID, name, salary, start_date, city, region) 3> values (1, "Jason", 40420, "02/01/94", "New York", "W") 4> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (2, "Robert",14420, "01/02/95", "Vancouver","N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (3, "Celia", 24020, "12/03/96", "Toronto", "W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (4, "Linda", 40620, "11/04/97", "New York", "N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (5, "David", 80026, "10/05/98", "Vancouver","W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (6, "James", 70060, "09/06/99", "Toronto", "N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (7, "Alison",90620, "08/07/00", "New York", "W") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (8, "Chris", 26020, "07/08/01", "Vancouver","N") 3> GO (1 rows affected) 1> insert into employee (ID, name, salary, start_date, city, region) 2> values (9, "Mary", 60020, "06/09/02", "Toronto", "W") 3> GO (1 rows affected) 1> 2> select * from employee 3> GO ID name salary start_date city region
---------- ----------- ----------------------- ---------- ------
1 Jason 40420 1994-02-01 00:00:00.000 New York W
2 Robert 14420 1995-01-02 00:00:00.000 Vancouver N
3 Celia 24020 1996-12-03 00:00:00.000 Toronto W
4 Linda 40620 1997-11-04 00:00:00.000 New York N
5 David 80026 1998-10-05 00:00:00.000 Vancouver W
6 James 70060 1999-09-06 00:00:00.000 Toronto N
7 Alison 90620 2000-08-07 00:00:00.000 New York W
8 Chris 26020 2001-07-08 00:00:00.000 Vancouver N
9 Mary 60020 2002-06-09 00:00:00.000 Toronto W
(9 rows affected) 1> 2> -- Looping 3> 4> DECLARE @Counter Int 5> SET @Counter = 1 6> WHILE @Counter < 4 7> BEGIN 8> PRINT "Loop" 9> PRINT "SubCategory" 10> + CONVERT(VarChar(10), @Counter) + ":" 11> SELECT Name, ID, Salary 12> FROM Employee 13> WHERE ID = @Counter 14> 15> SET @Counter = @Counter + 1 16> END 17> GO Loop SubCategory1: Name ID Salary
----------- -----------
Jason 1 40420 (1 rows affected) Loop (1 rows affected) SubCategory2: Name ID Salary
----------- -----------
Robert 2 14420 (1 rows affected) Loop (1 rows affected) SubCategory3: Name ID Salary
----------- -----------
Celia 3 24020 (1 rows affected) 1> 2> drop table employee 3> GO 1>
</source>
while loop with counter
<source lang="sql">
28> CREATE TABLE newtable (a int) 29> GO 1> INSERT INTO newtable VALUES (10) 2> INSERT INTO newtable VALUES (20) 3> INSERT INTO newtable VALUES (30) 4> GO (1 rows affected) (1 rows affected) (1 rows affected) 1> DECLARE @counter int 2> SET @counter = 1 3> WHILE @counter < 1000 BEGIN 4> UPDATE newtable SET a = a + 1 5> SET @counter = @counter + 1 6> END 7> GO 1> 2> drop table newtable; 3> GO
</source>
While with aggregate function
<source lang="sql">
2> 3> 4> 5> 6> CREATE TABLE Product( 7> ProductID int NOT NULL, 8> Name nvarchar(25) NOT NULL, 9> ProductNumber nvarchar(25) , 10> Color nvarchar(15) NULL, 11> StandardCost money NOT NULL, 12> Size nvarchar(5) NULL, 13> Weight decimal(8, 2) NULL, 14> ProductLine nchar(20) NULL, 15> SellStartDate datetime NOT NULL, 16> SellEndDate datetime NULL 17> ) 18> GO 1> insert into Product values(1,"Product A", "1","Red",123.123,"1",1,"ProductLine A","1999-03-22","2000-03-22"); 2> GO (1 rows affected) 1> insert into Product values(2,"Product B", "2","Yellow",234.234,"1",3,"ProductLine B","2000-03-22","2001-03-22"); 2> GO (1 rows affected) 1> insert into Product values(3,"Product C", "3","Pink",345.345,"1",3,"ProductLine V","2001-09-22","2006-02-22"); 2> GO (1 rows affected) 1> insert into Product values(4,"Product D", "4","White",456.456,"1",4,"ProductLine D","2002-08-22","2006-03-22"); 2> GO (1 rows affected) 1> insert into Product values(5,"Product E", "5","Black",567.567,"1",5,"ProductLine E","2003-01-22","2003-04-22"); 2> GO (1 rows affected) 1> insert into Product values(6,"Product F", "6","Blue",678.678,"1",6,"ProductLine W","2004-02-22","2005-05-22"); 2> GO (1 rows affected) 1> insert into Product values(7,"Product G", "7","Drak",789.789,"1",7,"ProductLine Q","2005-03-22","2006-03-22"); 2> GO (1 rows affected) 1> insert into Product values(8,"Product H", "8","Gray",234.123,"1",8,"ProductLine F","2006-04-22","2006-09-22"); 2> GO (1 rows affected) 1> insert into Product values(9,"Product I", "9","Red",543.123,"1",9,"ProductLine R","2007-05-22","2008-03-22"); 2> GO (1 rows affected) 1> insert into Product values(0,"Product J", "0","Gold",765.123,"1",0,"ProductLine J","2008-06-22","2009-03-22"); 2> GO (1 rows affected) 1> 2> 3> WHILE (SELECT AVG(StandardCost) FROM Product) < $1200 4> BEGIN 5> UPDATE Product SET StandardCost = StandardCost * 1.25 6> SELECT MAX(StandardCost) FROM Product 7> IF (SELECT MAX(StandardCost) FROM Product) > $4000 8> -- Greatest price is too high, quit. 9> BREAK 10> ELSE 11> -- Prices are within range, continue to loop. 12> CONTINUE 13> END 14> PRINT "Done." 15> GO (10 rows affected)
987.2363
(10 rows affected)
1234.0454
(10 rows affected)
1542.5568
(10 rows affected)
1928.1960
(10 rows affected)
2410.2450
(1 rows affected) Done. 1> 2> 3> drop table Product; 4> GO 1> 2>
</source>
