Использование переменных и некоторых управляющих операторов в 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’.