SQL Server/T-SQL Tutorial/Transact SQL/while
Содержание
- 1 A script that tests and adjusts credit amounts with a WHILE loop
- 2 How to perform repetitive processing
- 3 Insert 100 rows of data: RAND
- 4 This procedure inserts rows by using while loop
- 5 Use while loop to insert data
- 6 Using a Subquery with a Single-Statement WHILE Loop
- 7 WHILE @@FETCH_STATUS = 0
- 8 While loop controlled by an aggregate function
- 9 WHILE with AND operator
- 10 While with counter
A script that tests and adjusts credit amounts with a WHILE loop
<source lang="sql">
16> 17> 18> create table Billings ( 19> BankerID INTEGER, 20> BillingNumber INTEGER, 21> BillingDate datetime, 22> BillingTotal INTEGER, 23> TermsID INTEGER, 24> BillingDueDate datetime , 25> PaymentTotal INTEGER, 26> CreditTotal INTEGER 27> 28> ); 29> GO 1> 2> INSERT INTO Billings VALUES (1, 1, "2005-01-22", 165, 1,"2005-04-22",123,321); 3> GO (1 rows affected) 1> INSERT INTO Billings VALUES (2, 2, "2001-02-21", 165, 1,"2002-02-22",123,321.); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (3, 3, "2003-05-02", 165, 1,"2005-04-12",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (4, 4, "1999-03-12", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (5, 5, "2000-04-23", 165, 1,"2005-04-17",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (6, 6, "2001-06-14", 165, 1,"2005-04-18",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (7, 7, "2002-07-15", 165, 1,"2005-04-19",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (8, 8, "2003-08-16", 165, 1,"2005-04-20",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (9, 9, "2004-09-17", 165, 1,"2005-04-21",123,321); 2> GO (1 rows affected) 1> INSERT INTO Billings VALUES (0, 0, "2005-10-18", 165, 1,"2005-04-22",123,321); 2> GO (1 rows affected) 1> 2> 3> WHILE (SELECT SUM(BillingTotal - CreditTotal - PaymentTotal) FROM Billings) >= 50000 4> BEGIN 5> UPDATE Billings 6> SET CreditTotal = CreditTotal + .01 7> WHERE BillingTotal - CreditTotal - PaymentTotal > 0 8> IF (SELECT MAX(CreditTotal) FROM Billings) > 3000 9> BREAK 10> ELSE --(SELECT MAX(CreditTotal) FROM Billings) <= 3000 11> CONTINUE 12> END 13> GO 1> SELECT BillingDate, BillingTotal, CreditTotal 2> FROM Billings 3> GO BillingDate BillingTotal CreditTotal
------------ -----------
2005-01-22 00:00:00.000 165 321 2001-02-21 00:00:00.000 165 321 2003-05-02 00:00:00.000 165 321 1999-03-12 00:00:00.000 165 321 2000-04-23 00:00:00.000 165 321 2001-06-14 00:00:00.000 165 321 2002-07-15 00:00:00.000 165 321 2003-08-16 00:00:00.000 165 321 2004-09-17 00:00:00.000 165 321 2005-10-18 00:00:00.000 165 321 (10 rows affected) 1> 2> drop table Billings; 3> GO</source>
How to perform repetitive processing
<source lang="sql">
The syntax of the WHILE statement WHILE expression
{statement|BEGIN...END}
[BREAK]
[CONTINUE]</source>
Insert 100 rows of data: RAND
<source lang="sql">
5> CREATE TABLE random_data 6> ( 7> col1 int PRIMARY KEY, 8> col2 int, 9> col3 char(15) 10> ) 11> GO 1> 2> DECLARE @counter int, @col2 int, @col3 char(15) 3> 4> 5> SELECT @counter=0, @col2=RAND(@@spid + cpu + physical_io) 6> FROM master..sysprocesses where spid=@@spid 7> 8> WHILE (@counter < 1000) 9> BEGIN 10> SELECT @counter=@counter + 10, 11> @col2= 12> CASE 13> WHEN CONVERT(int, RAND() * 1000) % 2 = 1 14> THEN (CONVERT(int, RAND() * 100000) % 10000 * -1) 15> ELSE CONVERT(int, RAND() * 100000) % 10000 16> END, 17> @col3= 18> CHAR((CONVERT(int, RAND() * 1000) % 26 ) + 65) -- 65 is "A" 19> + CHAR((CONVERT(int, RAND() * 1000) % 26 ) + 65) 20> + CHAR((CONVERT(int, RAND() * 1000) % 26 ) + 65) 21> + CHAR((CONVERT(int, RAND() * 1000) % 26 ) + 65) 22> + REPLICATE(CHAR((CONVERT(int, RAND() * 1000) % 26 ) 23> + 65), 11) 24> 25> INSERT random_data VALUES (@counter, @col2, @col3) 26> END 27> GO 1> 2> drop table random_data; 3> GO</source>
This procedure inserts rows by using while loop
<source lang="sql">
6> CREATE TABLE works_on (emp_no INTEGER NOT NULL, 7> project_no CHAR(4) NOT NULL, 8> job CHAR (15) NULL, 9> enter_date DATETIME NULL) 10> GO 1> 2> 3> declare @i integer, @j integer 4> declare @job char(20), @enter_date datetime 5> declare @project_no char(4) 6> declare @dept_no char(4) 7> set @i = 1 8> set @j = 1 9> set @job = "Analyst" 10> set @enter_date = GETDATE() 11> set @dept_no = "d1" 12> while @i < 3001 13> begin 14> while @j < 5 15> begin 16> if (@j = 1) set @dept_no = "d1" 17> else if (@j = 2) set @dept_no = "d2" 18> else if (@j = 3) set @dept_no = "d3" 19> else set @dept_no = "d4" 20> insert into works_on 21> values (@i, @dept_no, @job, @enter_date) 22> set @j = @j+1 23> end 24> set @i = @i+1 25> set @j = 1 26> end 27> GO (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected) (1 rows affected)</source>
Use while loop to insert data
<source lang="sql">
6> DECLARE @T TABLE 7> ( 8> col1 INT NOT NULL PRIMARY KEY, 9> col2 INT NOT NULL, 10> filler CHAR(200) NOT NULL DEFAULT("a"), 11> UNIQUE(col2, col1) 12> ); 13> INSERT INTO @T(col1, col2) 14> SELECT n, (n - 1) % 100 + 1 FROM dbo.Nums 15> WHERE n <= 100; 16> 17> SELECT * FROM @T WHERE col1 = 1; 18> 19> SELECT * FROM @T WHERE col1 <= 50; 20> 21> SELECT * FROM @T WHERE col2 = 1; 22> 23> SELECT * FROM @T WHERE col2 <= 2; 24> 25> SELECT * FROM @T WHERE col2 <= 50; 26> GO (100 rows affected) col1 col2 filler
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1 a
(1 rows affected) col1 col2 filler
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1 a
2 2 a
3 3 a
4 4 a
5 5 a
6 6 a
7 7 a
8 8 a
9 9 a
10 10 a
11 11 a
12 12 a
13 13 a
14 14 a
15 15 a
16 16 a
17 17 a
18 18 a
19 19 a
20 20 a
21 21 a
22 22 a
23 23 a
24 24 a
25 25 a
26 26 a
27 27 a
28 28 a
29 29 a
30 30 a
31 31 a
32 32 a
33 33 a
34 34 a
35 35 a
36 36 a
37 37 a
38 38 a
39 39 a
40 40 a
41 41 a
42 42 a
43 43 a
44 44 a
45 45 a
46 46 a
47 47 a
48 48 a
49 49 a
50 50 a
(50 rows affected) col1 col2 filler
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1 a
(1 rows affected) col1 col2 filler
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1 a
2 2 a
(2 rows affected) col1 col2 filler
----------- --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 1 a
2 2 a
3 3 a
4 4 a
5 5 a
6 6 a
7 7 a
8 8 a
9 9 a
10 10 a
11 11 a
12 12 a
13 13 a
14 14 a
15 15 a
16 16 a
17 17 a
18 18 a
19 19 a
20 20 a
21 21 a
22 22 a
23 23 a
24 24 a
25 25 a
26 26 a
27 27 a
28 28 a
29 29 a
30 30 a
31 31 a
32 32 a
33 33 a
34 34 a
35 35 a
36 36 a
37 37 a
38 38 a
39 39 a
40 40 a
41 41 a
42 42 a
43 43 a
44 44 a
45 45 a
46 46 a
47 47 a
48 48 a
49 49 a
50 50 a
(50 rows affected) 1></source>
Using a Subquery with a Single-Statement WHILE Loop
<source lang="sql">
5> 6> CREATE TABLE employee( 7> id INTEGER NOT NULL PRIMARY KEY, 8> first_name VARCHAR(10), 9> last_name VARCHAR(10), 10> salary DECIMAL(10,2), 11> start_Date DATETIME, 12> region VARCHAR(10), 13> city VARCHAR(20), 14> managerid INTEGER 15> ); 16> GO 1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver",3); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown",4); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris",5); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London",6); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton",7); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary",8); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina",9); 2> GO (1 rows affected) 1> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto",10); 2> GO (1 rows affected) 1> 2> select * from employee; 3> GO id first_name last_name salary start_Date region city managerid
---------- ---------- ------------ ----------------------- ---------- -------------------- -----------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver 3
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown 4
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris 5
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London 6
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton 7
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary 8
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York 9
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina 9
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto 10
(9 rows affected) 1> 2> 3> WHILE (SELECT AVG (Salary) FROM Employee) < $10000 4> UPDATE Employee 5> SET 6> Salary = Salary * 1.05 7> 8> 9> 10> 11> drop table employee; 12> GO (9 rows affected) (9 rows affected) (9 rows affected) (9 rows affected) (9 rows affected) (9 rows affected) (9 rows affected) (9 rows affected) (9 rows affected) (9 rows affected) (9 rows affected) (9 rows affected) (9 rows affected)</source>
WHILE @@FETCH_STATUS = 0
<source lang="sql">
4> 5> 6> DROP PROCEDURE pr_updateindex 7> GO 1> CREATE PROCEDURE pr_updateindex 2> AS 3> SET NOCOUNT ON 4> DECLARE getindex_curs CURSOR 5> FOR 6> SELECT name FROM sysobjects WHERE type = "U" 7> DECLARE @holdtable varchar(30) 8> DECLARE @message varchar(40) 9> DECLARE @dynamic varchar(51) 10> OPEN getindex_curs 11> FETCH NEXT FROM getindex_curs into @holdtable 12> WHILE @@FETCH_STATUS = 0 BEGIN 13> SELECT @dynamic = "UPDATE STATISTICS " + @holdtable 14> SELECT @message = "Updating " + @holdtable 15> EXEC (@dynamic ) 16> PRINT @message 17> FETCH NEXT FROM getindex_curs into @holdtable 18> END 19> CLOSE getindex_curs 20> GO 1> 2> EXEC pr_updateindex 3> 4> 5></source>
While loop controlled by an aggregate function
<source lang="sql">
3> CREATE TABLE Product( 4> ProductID int NOT NULL, 5> Name nvarchar(25) NOT NULL, 6> ProductNumber nvarchar(25) , 7> Color nvarchar(15) NULL, 8> StandardCost money NOT NULL, 9> Size nvarchar(5) NULL, 10> Weight decimal(8, 2) NULL, 11> ProductLine nchar(20) NULL, 12> SellStartDate datetime NOT NULL, 13> SellEndDate datetime NULL 14> ) 15> 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> 2> 3> insert into Product values(2,"Product B", "2","Yellow",234.234,"1",3,"ProductLine B","2000-03-22","2001-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(3,"Product C", "3","Pink",345.345,"1",3,"ProductLine V","2001-09-22","2006-02-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(4,"Product D", "4","White",456.456,"1",4,"ProductLine D","2002-08-22","2006-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(5,"Product E", "5","Black",567.567,"1",5,"ProductLine E","2003-01-22","2003-04-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(6,"Product F", "6","Blue",678.678,"1",6,"ProductLine W","2004-02-22","2005-05-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(7,"Product G", "7","Drak",789.789,"1",7,"ProductLine Q","2005-03-22","2006-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(8,"Product H", "8","Gray",234.123,"1",8,"ProductLine F","2006-04-22","2006-09-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(9,"Product I", "9","Red",543.123,"1",9,"ProductLine R","2007-05-22","2008-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(0,"Product J", "0","Gold",765.123,"1",0,"ProductLine J","2008-06-22","2009-03-22"); 4> GO (1 rows affected) 1> 2> 3> 4> 5> WHILE (SELECT AVG(StandardCost) FROM Product) < $1200 6> BEGIN 7> UPDATE Product SET StandardCost = StandardCost * 1.25 8> SELECT MAX(StandardCost) FROM Product 9> IF (SELECT MAX(StandardCost) FROM Product) > $4000 10> 11> BREAK 12> ELSE 13> 14> CONTINUE 15> END 16> PRINT "Done." 17> 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>
WHILE with AND operator
<source lang="sql">
1> CREATE TABLE Orders ( 2> OrderID int NOT NULL , 3> CustomerID nchar (5) NULL , 4> EmployeeID int NULL , 5> OrderDate datetime NULL , 6> RequiredDate datetime NULL , 7> ShippedDate datetime NULL , 8> ShipVia int NULL , 9> Freight money NULL DEFAULT (0), 10> ShipName nvarchar (40) NULL , 11> ShipAddress nvarchar (60) NULL , 12> ShipCity nvarchar (15) NULL , 13> ShipRegion nvarchar (15) NULL , 14> ShipPostalCode nvarchar (10) NULL , 15> ShipCountry nvarchar (15) NULL 16> ) 17> GO 1> 2> 3> INSERT INTO Orders VALUES (10248,"1",5,"7/4/1996","8/1/2001","7/16/2001",3,32.38,"V","A","R", NULL,N"51100","France") 9> go 1> 2> 3> create PROCEDURE spCursorScope 4> AS 5> DECLARE @Counter int, 6> @OrderID int, 7> @CustomerID varchar(5) 8> DECLARE CursorTest cursor 9> LOCAL 10> FOR 11> SELECT OrderID, CustomerID 12> FROM Orders 13> 14> SELECT @Counter = 1 15> OPEN CursorTest 16> FETCH NEXT FROM CursorTest INTO @OrderID, @CustomerID 17> PRINT "Row " + CONVERT(varchar,@Counter) + " has an OrderID of " + 18> CONVERT(varchar,@OrderID) + " and a CustomerID of " + @CustomerID 19> 20> WHILE (@Counter<=5) AND (@@FETCH_STATUS=0) 21> BEGIN 22> SELECT @Counter = @Counter + 1 23> FETCH NEXT FROM CursorTest INTO @OrderID, @CustomerID 24> PRINT "Row " + CONVERT(varchar,@Counter) + " has an OrderID of " + 25> CONVERT(varchar,@OrderID) + " and a CustomerID of " + @CustomerID 26> END 27> GO 1> 2> drop PROCEDURE spCursorScope; 3> GO 1> 2> drop table Orders; 3> GO</source>
While with counter
<source lang="sql">
8> CREATE TABLE Product( 9> ProductID int NOT NULL, 10> Name nvarchar(25) NOT NULL, 11> ProductNumber nvarchar(25) , 12> Color nvarchar(15) NULL, 13> StandardCost money NOT NULL, 14> Size nvarchar(5) NULL, 15> Weight decimal(8, 2) NULL, 16> ProductLine nchar(20) NULL, 17> SellStartDate datetime NOT NULL, 18> SellEndDate datetime NULL 19> ) 20> 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> 2> 3> insert into Product values(2,"Product B", "2","Yellow",234.234,"1",3,"ProductLine B","2000-03-22","2001-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(3,"Product C", "3","Pink",345.345,"1",3,"ProductLine V","2001-09-22","2006-02-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(4,"Product D", "4","White",456.456,"1",4,"ProductLine D","2002-08-22","2006-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(5,"Product E", "5","Black",567.567,"1",5,"ProductLine E","2003-01-22","2003-04-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(6,"Product F", "6","Blue",678.678,"1",6,"ProductLine W","2004-02-22","2005-05-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(7,"Product G", "7","Drak",789.789,"1",7,"ProductLine Q","2005-03-22","2006-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(8,"Product H", "8","Gray",234.123,"1",8,"ProductLine F","2006-04-22","2006-09-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(9,"Product I", "9","Red",543.123,"1",9,"ProductLine R","2007-05-22","2008-03-22"); 4> GO (1 rows affected) 1> 2> 3> insert into Product values(0,"Product J", "0","Gold",765.123,"1",0,"ProductLine J","2008-06-22","2009-03-22"); 4> GO (1 rows affected) 1> 2> 3> 4> 5> 6> 7> DECLARE @Counter Int 8> SET @Counter = 1 9> WHILE @Counter < 4 10> BEGIN 11> PRINT "SubCategory " 12> SELECT Name, ProductID 13> FROM Product 14> WHERE ProductID = @Counter 15> SET @Counter = @Counter + 1 16> END 17> GO SubCategory Name ProductID
-----------
Product A 1 (1 rows affected) SubCategory Name ProductID
-----------
Product B 2 (1 rows affected) SubCategory Name ProductID
-----------
Product C 3 (1 rows affected) 1> 2> 3> drop table Product; 4> GO</source>
