SQL Server/T-SQL Tutorial/Transact SQL/Variable
Содержание
- 1 A SQL script that uses variables
- 2 A variable can be assigned a value from a subquery
- 3 Global variable names begin with an @@ prefix.
- 4 Local variables used for searches
- 5 Pass variable to a user-defined function
- 6 SELECT LEFT(@FullName, 5)
- 7 Testing the ISNULL function
- 8 Use aggregate function with variables
- 9 Use if statement to check a variable
- 10 Use Local Variables in where clause
- 11 Use one variables in like function
- 12 Use right function with a variable
- 13 Varchar type variable
- 14 Working with Variables
- 15 XML type variable
A SQL script that uses variables
5>
6>
7> create table Billings (
8> BankerID INTEGER,
9> BillingNumber INTEGER,
10> BillingDate datetime,
11> BillingTotal INTEGER,
12> TermsID INTEGER,
13> BillingDueDate datetime ,
14> PaymentTotal INTEGER,
15> CreditTotal INTEGER
16>
17> );
18> 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> DECLARE @MaxBilling money, @MinBilling money
4> DECLARE @PercentDifference decimal(8,2)
5> DECLARE @BillingCount int, @BankerIDVar int
6>
7> SET @BankerIDVar = 95
8> SET @MaxBilling = (SELECT MAX(BillingTotal) FROM Billings
9> WHERE BankerID = @BankerIDVar)
10> SELECT @MinBilling = MIN(BillingTotal), @BillingCount = COUNT(*)
11> FROM Billings
12> WHERE BankerID = @BankerIDVar
13> SET @PercentDifference = (@MaxBilling - @MinBilling) / @MinBilling * 100
14>
15> PRINT "Maximum Billing is $" + CONVERT(varchar,@MaxBilling,1) + "."
16> PRINT "Minimum Billing is $" + CONVERT(varchar,@MinBilling,1) + "."
17> PRINT "Maximum is " + CONVERT(varchar,@PercentDifference) +
18> "% more than minimum."
19> PRINT "Number of Billings: " + CONVERT(varchar,@BillingCount) + "."
20>
21> GO
Number of Billings: 0.
1>
2> drop table Billings;
3> GO
A variable can be assigned a value from a subquery
7> CREATE TABLE authors(
8> au_id varchar(11),
9> au_lname varchar(40) NOT NULL,
10> au_fname varchar(20) NOT NULL,
11> phone char(12) NOT NULL DEFAULT ("UNKNOWN"),
12> address varchar(40) NULL,
13> city varchar(20) NULL,
14> state char(2) NULL,
15> zip char(5) NULL,
16> contract bit NOT NULL
17> )
18> GO
1> insert authors values("1", "Joe", "Abra", "111 111-1111", "6 St.", "Berkeley", "CA", "11111", 1)
2> insert authors values("2", "Jack", "Majo", "222 222-2222", "3 St.", "Oakland" , "CA", "22222", 1)
3> insert authors values("3", "Pink", "Cherry", "333 333-3333", "5 Ln.", "Vancouver", "BC", "33333", 1)
4> insert authors values("4", "Blue", "Albert", "444 444-4444", "7 Av.", "Vancouver", "BC", "44444", 1)
5> insert authors values("5", "Red", "Anne", "555 555-5555", "6 Av.", "Regina", "SK", "55555", 1)
6> insert authors values("6", "Black", "Michel", "666 666-6666", "3 Pl.", "Regina", "SK", "66666", 1)
7> insert authors values("7", "White", "Sylvia", "777 777-7777", "1 Pl.", "Rockville", "MD", "77777", 1)
8> insert authors values("8", "Yellow","Heather","888 888-8888", "3 Pu", "Vacaville", "CA", "88888", 0)
9> insert authors values("9", "Gold", "Dep", "999 999-9999", "5 Av.", "Oakland", "CA", "99999", 0)
10> insert authors values("10", "Siler", "Dean", "000 000-0000", "4 Av.", "Oakland", "CA", "00000", 1)
11> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2> -- First batch using a straight SELECT:
3> DECLARE @firstname varchar(20)
4> SELECT @firstname = au_fname
5> FROM authors
6> WHERE au_lname = "Greene"
7> SELECT @firstname
8> GO
--------------------
NULL
(1 rows affected)
1>
2> -- Second batch using a subquery:
3> DECLARE @firstname varchar(20)
4> SELECT @firstname = ( SELECT au_fname
5> FROM authors
6> WHERE au_lname = "Greene")
7> SELECT @firstname
8> GO
--------------------
NULL
(1 rows affected)
1>
2> drop table authors;
3> GO
1>
Global variable names begin with an @@ prefix.
5> Create table Eq (EqId int identity(1,1),
6> Make varchar(50),
7> Model varchar(50),
8> EqTypeId int)
9> GO
1>
2> Declare @intEqId int
3> Insert into Eq(Make, Model, EqTypeId)
4> Values ("ACME", "Turbo", 2)
5> Select @intEqId = @@identity
6> GO
(1 rows affected)
1>
2>
3>
4> drop table Eq;
5> GO
1>
Local variables used for searches
19> CREATE TABLE employee(
20> id INTEGER NOT NULL PRIMARY KEY,
21> first_name VARCHAR(10),
22> last_name VARCHAR(10),
23> salary DECIMAL(10,2),
24> start_Date DATETIME,
25> region VARCHAR(10),
26> city VARCHAR(20),
27> managerid INTEGER
28> );
29> 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>
4> DECLARE @ID int
5> DECLARE @MinStandardCost money
6> DECLARE @MaxStandardCost money
7>
8> SET @MinStandardCost = 3396
9> SET @MaxStandardCost = 10000
10>
11> SELECT ID, Start_Date, salary
12> FROM Employee
13> WHERE Salary BETWEEN @MinStandardCost and @MaxStandardCost
14>
15> drop table employee;
16> GO
ID Start_Date salary
----------- ----------------------- ------------
1 2005-03-22 00:00:00.000 5890.00
2 2003-07-21 00:00:00.000 4789.00
3 2001-12-01 00:00:00.000 6678.00
4 2006-03-03 00:00:00.000 5567.00
5 2004-07-02 00:00:00.000 4467.00
6 2002-05-19 00:00:00.000 6456.00
7 2008-03-18 00:00:00.000 5345.00
8 2007-07-17 00:00:00.000 4234.00
9 2001-04-16 00:00:00.000 6123.00
(9 rows affected)
Pass variable to a user-defined function
6> CREATE FUNCTION dbo.ufnIsOdd (@n int)
7> RETURNS bit
8> AS
9> BEGIN
10> RETURN (@n % 2)
11> END
12> GO
1>
2> DECLARE @numb int
3> SET @numb = 6
4> SELECT @numb "Number", dbo.ufnIsOdd (@numb) "Is_odd = 1"
5> SET @numb = 7
6> SELECT @numb "Number", dbo.ufnIsOdd (@numb) "Is_odd = 1"
7> GO
Number Is_odd = 1
----------- ----------
6 0
(1 rows affected)
Number Is_odd = 1
----------- ----------
7 1
(1 rows affected)
1>
SELECT LEFT(@FullName, 5)
4> DECLARE @FullName VarChar(25)
5> SET @FullName = "George Washington"
6> SELECT LEFT(@FullName, 5)
7> GO
-----
Georg
(1 rows affected)
Testing the ISNULL function
4>
5> DECLARE @intTest int
6> SELECT ISNULL(@intTest,5)
7> SELECT @intTest = 37
8> SELECT ISNULL(@intTest,5)
9> GO
-----------
5
(1 rows affected)
-----------
37
(1 rows affected)
1>
Use aggregate function with variables
3> CREATE TABLE roysched(
4> title_id varchar(20),
5> lorange int NULL,
6> hirange int NULL,
7> royalty int NULL
8> )
9> GO
1>
2>
3> insert roysched values("1", 0, 10000, 10)
4> insert roysched values("2", 10001, 20000, 12)
5> insert roysched values("3", 20001, 30000, 14)
6> insert roysched values("4", 30001, 40000, 16)
7> insert roysched values("5", 40001, 50000, 18)
8>
9> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3> DECLARE @min_range int, @hi_range int -- Variables declared
4> SELECT @min_range=MIN(lorange),
5> @hi_range=MAX(hirange) FROM roysched -- Variables assigned
6> SELECT @min_range, @hi_range -- Values of variables
7> -- returned as result
8>
9> GO
----------- -----------
0 50000
(1 rows affected)
1>
2> drop table roysched;
3> GO
Use if statement to check a variable
4> CREATE TABLE #VersionTable(
5> [Index] int PRIMARY KEY,
6> Name varchar(30),
7> Internal_Value int,
8> Character_Value varchar(250)
9> )
10> GO
1>
2> INSERT INTO #VersionTable
3> EXEC master..xp_msver
4>
5> DECLARE @Version int
6>
7> SELECT @Version = (SELECT Internal_Value
8> FROM #VersionTable
9> WHERE Name = "ProductVersion")
10> IF (@Version) >= 524288
11> PRINT "Running SS2K Beta 2 or Later"
12> ELSE
13> IF @Version >= 458752
14> PRINT "Running 7.0 RTM or Later"
15> ELSE
16> PRINT "Running Pre 7.0 RTM Version"
17> GO
(20 rows affected)
(20 rows affected)
Running SS2K Beta 2 or Later
1> DROP TABLE #VersionTable
2> GO
Use Local Variables in where clause
5>
6> CREATE TABLE titles(
7> title_id varchar(20),
8> title varchar(80) NOT NULL,
9> type char(12) NOT NULL,
10> pub_id char(4) NULL,
11> price money NULL,
12> advance money NULL,
13> royalty int NULL,
14> ytd_sales int NULL,
15> notes varchar(200) NULL,
16> pubdate datetime NOT NULL
17> )
18> GO
1>
2> insert titles values ("1", "Secrets", "popular_comp", "1389", $20.00, $8000.00, 10, 4095,"Note 1","06/12/94")
3> insert titles values ("2", "The", "business", "1389", $19.99, $5000.00, 10, 4095,"Note 2","06/12/91")
4> insert titles values ("3", "Emotional", "psychology", "0736", $7.99, $4000.00, 10, 3336,"Note 3","06/12/91")
5> insert titles values ("4", "Prolonged", "psychology", "0736", $19.99, $2000.00, 10, 4072,"Note 4","06/12/91")
6> insert titles values ("5", "With", "business", "1389", $11.95, $5000.00, 10, 3876,"Note 5","06/09/91")
7> insert titles values ("6", "Valley", "mod_cook", "0877", $19.99, $0.00, 12, 2032,"Note 6","06/09/91")
8> insert titles values ("7", "Any?", "trad_cook", "0877", $14.99, $8000.00, 10, 4095,"Note 7","06/12/91")
9> insert titles values ("8", "Fifty", "trad_cook", "0877", $11.95, $4000.00, 14, 1509,"Note 8","06/12/91")
10> GO
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
(1 rows affected)
1>
2>
3>
4> DECLARE @limit money
5> SET @limit = $10
6> SELECT * FROM titles
7> WHERE price <= @limit
8> GO
title_id title type pub_id price advance royalty ytd_sales notes
pubdate
-------------------- -------------------------------------------------------------------------------- ------------ ------ --------------------- --------------------- ----------- ----------- ----------
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------
--------------
3 Emotional psychology 0736 7.9900 4000.0000 10 3336 Note 3
1991-06-1
2 00:00:00.000
(1 rows affected)
1>
2> drop table titles;
3> GO
Use one variables in like function
5>
6> create table Bankers(
7> BankerID Integer,
8> BankerName VARCHAR(20),
9> BankerContactLName VARCHAR(20),
10> BankerContactFName VARCHAR(20),
11> BankerCity VARCHAR(20),
12> BankerState VARCHAR(20),
13> BankerZipCode VARCHAR(20),
14> BankerPhone VARCHAR(20)
15> )
16> GO
1>
2> insert into Bankers values (1, "ABC Inc.","Joe","Smith","Vancouver","BC","11111","111-111-1111");
3> GO
(1 rows affected)
1> insert into Bankers values (2, "DEF Inc.","Red","Rice", "New York", "DE","22222","222-222-2222");
2> GO
(1 rows affected)
1> insert into Bankers values (3, "HJI Inc.","Kit","Cat", "Paris", "CA","33333","333-333-3333");
2> GO
(1 rows affected)
1> insert into Bankers values (4, "QWE Inc.","Git","Black","Regina", "ER","44444","444-444-4444");
2> GO
(1 rows affected)
1> insert into Bankers values (5, "RTY Inc.","Wil","Lee", "Toronto", "YU","55555","555-555-5555");
2> GO
(1 rows affected)
1> insert into Bankers values (6, "YUI Inc.","Ted","Larry","Calgary", "TY","66666","666-666-6666");
2> GO
(1 rows affected)
1> insert into Bankers values (7, "OIP Inc.","Yam","Act", "San Franc","FG","77777","777-777-7777");
2> GO
(1 rows affected)
1> insert into Bankers values (8, "SAD Inc.","Hit","Eat", "Orland", "PO","88888","888-888-8888");
2> GO
(1 rows affected)
1> insert into Bankers values (9, "DFG Inc.","Sad","Lee", "Wisler", "PL","99999","999-999-9999");
2> GO
(1 rows affected)
1> insert into Bankers values (0, "GHJ Inc.","Bit","Lee", "Ticker", "MN","00000","000-000-0000");
2> GO
(1 rows affected)
1>
2>
3> DECLARE @alpha nchar(1)
4> SET @alpha = "b"
5>
6>
7> SELECT *
8> FROM Bankers
9> WHERE BankerName LIKE @alpha + "%"
10> GO
BankerID BankerName BankerContactLName BankerContactFName BankerCity BankerState BankerZipCode BankerPhone
----------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
(0 rows affected)
1>
2> drop table Bankers;
3> GO
Use right function with a variable
5> DECLARE @FullName VarChar(25)
6> SET @FullName = "George Washington"
7> SELECT RIGHT(@FullName, 5)
8> GO
-----
ngton
(1 rows affected)
Varchar type variable
5> CREATE TABLE employee(
6> id INTEGER NOT NULL PRIMARY KEY,
7> first_name VARCHAR(10),
8> last_name VARCHAR(10),
9> salary DECIMAL(10,2),
10> start_Date DATETIME,
11> region VARCHAR(10),
12> city VARCHAR(20)
13> );
14> GO
1> INSERT INTO employee VALUES (1, "Jason" , "Martin", 5890,"2005-03-22","North","Vancouver");
2> GO
(1 rows affected)
1>
2>
3> INSERT INTO employee VALUES (2, "Alison", "Mathews",4789,"2003-07-21","South","Utown");
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO employee VALUES (3, "James" , "Smith", 6678,"2001-12-01","North","Paris");
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO employee VALUES (4, "Celia" , "Rice", 5567,"2006-03-03","South","London");
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO employee VALUES (5, "Robert", "Black", 4467,"2004-07-02","East","Newton");
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO employee VALUES (6, "Linda" , "Green" , 6456,"2002-05-19","East","Calgary");
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO employee VALUES (7, "David" , "Larry", 5345,"2008-03-18","West","New York");
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO employee VALUES (8, "James" , "Cat", 4234,"2007-07-17","West","Regina");
4> GO
(1 rows affected)
1>
2>
3> INSERT INTO employee VALUES (9, "Joan" , "Act", 6123,"2001-04-16","North","Toronto");
4> GO
(1 rows affected)
1>
2>
3>
4> select * from employee;
5> GO
id first_name last_name salary start_Date region city
----------- ---------- ---------- ------------ ----------------------- ---------- --------------------
1 Jason Martin 5890.00 2005-03-22 00:00:00.000 North Vancouver
2 Alison Mathews 4789.00 2003-07-21 00:00:00.000 South Utown
3 James Smith 6678.00 2001-12-01 00:00:00.000 North Paris
4 Celia Rice 5567.00 2006-03-03 00:00:00.000 South London
5 Robert Black 4467.00 2004-07-02 00:00:00.000 East Newton
6 Linda Green 6456.00 2002-05-19 00:00:00.000 East Calgary
7 David Larry 5345.00 2008-03-18 00:00:00.000 West New York
8 James Cat 4234.00 2007-07-17 00:00:00.000 West Regina
9 Joan Act 6123.00 2001-04-16 00:00:00.000 North Toronto
(9 rows affected)
1>
2>
3>
4> DECLARE @Shifts varchar(20)
5> SET @Shifts = ""
6> SELECT @Shifts = @Shifts + s.first_Name + ","
7> FROM employee s
8> ORDER BY s.start_Date;
9>
10> SELECT @Shifts;
11> GO
--------------------
Joan,James,Linda,Ali
(1 rows affected)
1>
2>
3>
4> drop table employee;
5> GO
Working with Variables
Every variable that you use must be declared, together with its datatype, in a DECLARE statement.
You can declare more than one variable at a time in a single DECLARE as long as you separate the variables with commas.
Syntax for the DECLARE Statement
DECLARE
{@local_variable [AS] data_type}
[,...n]
XML type variable
4> DECLARE @Book XML
5> SET @Book =
6> CAST("<Book name="SQL Server 2000 Fast Answers">
7~ <Chapters>
8~ <Chapter id="1"> chapter 1 </Chapter>
9~ <Chapter id="2"> chapter 2 </Chapter>
10~ <Chapter id="3"> chapter 3 </Chapter>
11~ <Chapter id="4"> chapter 4 </Chapter>
12~ </Chapters>
13~ </Book>" as XML)
14> GO
1>
2>