SQL Server/T-SQL/Transact SQL/While — различия между версиями
Admin (обсуждение | вклад) м (1 версия) |
Admin (обсуждение | вклад) м (1 версия) |
(нет различий)
| |
Текущая версия на 10:20, 26 мая 2010
Содержание
Looping with the WHILE Statement
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
Use while loop to insert data
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)
While Loop Demo
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>
while loop with counter
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
While with aggregate function
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>