Использование переменных и некоторых управляющих операторов в Transact-SQL
Приведенныe ниже коды следует вводить вокне редактора запросов, переключившись на базу данныхPostavki.
1.Объявление локальных переменных и присвоение переменным значения.
Имя локальной переменной в Transact-SQL должно начинаться с символа @. Для объявления переменной используется команда DECLARE. Существует несколько способов присвоения переменной значения:
а) использование ключевого слова SET.
DECLARE @name CHAR(20), @a BIGINT
SET @name ='Документ'
SET @a =528670
PRINT @a;
б) с помощью команды SELECT.
DECLARE @a BIGINT, @name CHAR(20), @c CHAR(20)
SELECT @name='Документ'
SELECT @a=COUNT(*)
FROM PostDet.tblS
SELECT @a As N;
в) сочетание ключевого слова SET и запроса.
DECLARE @z INT
SET @z=(SELECT SUM(QTY) FROM PostDet.tblSP)
PRINT @z;
г) тип переменной - таблица.
DECLARE @t TABLE (S CHAR (5), STATUS INT)
INSERT INTO @t (S, STATUS) VALUES ('S1', 10)
SELECT * FROM @t;
2. Команда IF…ELSE. Передача управления согласно условию.
а) проверка существования определенной записи в таблице tblS перед обновлением информации.
DECLARE @MiCHAR CHAR(5)
SET @MiCHAR= (SELECT S FROM PostDet.tblS WHERE S='S1')
IF @MiCHAR ='S1'
PRINT 'Record find'
ELSE
PRINT 'Record notfind' ;
б) увеличить на 30 штук объемы всех поставок, если их средняя величина не превышает 350 штук.
IF ( SELECT AVG (QTY) FROM PostDet.tblSP )< 350
UPDATE PostDet.tblSP
SET QTY = QTY + 30
SELECT* FROM PostDet.tblSP ;
в) если среднее количество деталей, поставляемых поставщиком S1, меньше среднего количества деталей, поставляемых поставщиком S2, то увеличить поставки деталй поставщика S1 на 50 штук.
IF ( SELECT AVG (QTY)
FROM PostDet.tblSP
WHERE S = 'S1') <
( SELECT AVG (QTY)
FROM PostDet.tblSP
WHERE S ='S2')
UPDATE PostDet.tblSP
SET QTY=QTY + 30
WHERE S ='S1'
SELECT * FROM PostDet.tblSP;
3. Оператор цикла WHILE.
а) отобразить значения счетчика. Тело цикла выполняется до тех пор, пока условие цикла не станет ложным.
DECLARE @MiCounter INT
SET @MiCounter=1
WHILE @MiCounter < 10
BEGIN
PRINT CONVERT(CHAR(2), @MiCounter)
SET @MiCounter= @MiCounter+1
END
б) увеличивать размеры всех поставок на 10 штук до тех пор, пока средняя величина поставок поставщика S2 меньше 400 штук.
WHILE ( SELECT AVG(QTY) FROM PostDet.tblSP WHERE S = 'S2' ) < 400
BEGIN
PRINT 'Мы увеличиваем размеры ваших поставок'
UPDATE PostDet.tblSP
SET QTY = QTY +10
END
SELECT* FROM PostDet.tblSP;
4. Оператор CREATE PROCEDURE.
а)создание хранимой процедуры, подсчитывающей среднее арифметическое кол-ва поставляемых деталей, указанного поставщика.
CREATE PROCEDURE PostDet. SUP_AVG @AvgPost INT output,
@NumPost CHAR (10)
AS
SELECT @AvgPost= AVG(QTY)
FROM PostDet.tblSP
WHERE S = @NumPost;
Протестируйте созданную процедуру.
DECLARE @AvgSup INT
EXEC PostDet. SUP_AVG @AvgSup OUTPUT, S1
SELECT @AvgSup As AVGQTY;
б) создание хранимой процедуры добавления в таблицу tblS записи поставщика с номером S5.
CREATE PROCEDURE PostDet. ADD_SUP
@RS CHAR(6),
@RSNAME CHAR(9),
@RSTATUS INTEGER,
@RCITY CHAR(8)
AS
IF EXISTS (SELECT*
FROM PostDet.tblS
WHERE S= @RS
AND SNAME = @RSNAME)
PRINT 'Пост с таким ном уже существует'
ELSE
BEGIN
INSERT INTO PostDet.tblS VALUES (@RS, @RSNAME, @RSTATUS, @RCITY)
PRINT 'Номер уже добавлен'
END;
Для выполнения процедуры используйте следующий текст:
PostDet. ADD_SUP ‘S5‘, ‘Smith‘, 40, ‘London ‘;
в) создание хранимой процедуры поиска поставщиков, у которых в имени имеется буква “c”.
CREATE PROCEDURE PostDet.PrLike @XName varchar (50)
AS
BEGIN
SELECT *
FROM PostDet.tblS
WHERE SName LIKE @XName;
END
Вызов процедуры: PostDet.PrLike '%c%';
Самостоятельно создайте и выполните хранимую процедуру ‘Определить имена поставщиков детали с номером ‘P4’.
Создание XML документа и запроса.
В MS SQLServer существует тип данных XML, а также операторы
Transact SQL для управления XML данными.