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
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
How to perform repetitive processing
The syntax of the WHILE statement
WHILE expression
{statement|BEGIN...END}
[BREAK]
[CONTINUE]
Insert 100 rows of data: RAND
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
This procedure inserts rows by using while loop
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)
Use while loop to insert data
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>
Using a Subquery with a Single-Statement WHILE Loop
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)
WHILE @@FETCH_STATUS = 0
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>
While loop controlled by an aggregate function
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>
WHILE with AND operator
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
While with counter
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