- •Лекция 6. Алгоритмические конструкции t-sql. Хранимые процедуры. Переменные в Transact-sql
- •Объявление переменной
- •Циклическая конструкция
- •Конструкция waitfor
- •Блок try/catch
- •Комментарии
- •Функции Transact-sql
- •Скалярные функции
- •Математические функции
- •Строковые функции
- •Функции для работы с датами
- •Конфигурационные функции
- •Системные функции
- •Функции преобразования типов
- •Отладка кода в ManagementStudio
- •Хранимые процедуры
- •Понятие хранимой процедуры
- •Типы хранимых процедур
- •Создание, изменение и удаление хранимых процедур
- •Выполнение хранимой процедуры
Выполнение хранимой процедуры
Для выполнения хранимой процедуры используется команда:
[[ EXEC [ UTE] имя_процедуры
[[@имя_параметра=]{значение | @имя_переменной}
[OUTPUT ]|[DEFAULT ]][,...n]
Если вызов хранимой процедуры не является единственной командой в пакете, то присутствие команды EXECUTE обязательно. Более того, эта команда требуется для вызова процедуры из тела другой процедуры или триггера.
Использование ключевого слова OUTPUT при вызове процедуры разрешается только для параметров, которые были объявлены при создании процедуры с ключевым словом OUTPUT.
Когда же при вызове процедуры для параметра указывается ключевое слово DEFAULT, то будет использовано значение по умолчанию. Естественно, указанное слово DEFAULT разрешается только для тех параметров, для которых определено значение по умолчанию.
Из синтаксиса команды EXECUTE видно, что имена параметров могут быть опущены при вызове процедуры. Однако в этом случае пользователь должен указывать значения для параметров в том же порядке, в каком они перечислялись при создании процедуры. Присвоить параметру значение по умолчанию, просто пропустив его при перечислении нельзя. Если же требуется опустить параметры, для которых определено значение по умолчанию, достаточно явного указания имен параметров при вызове хранимой процедуры. Более того, таким способом можно перечислять параметры и их значения в произвольном порядке.
Отметим, что при вызове процедуры указываются либо имена параметров со значениями, либо только значения без имени параметра. Их комбинирование не допускается.
Пример. Процедура без параметров. Разработать процедуру для получения списка студентов всех групп.
CREATE PROCEDURE StudByGroup
AS
SELECT Gr.nameGr as Наименование_группы,
sNameSt+' '+NameSt+' '+lNameSt as ФИО
FROM Student INNER JOIN Gr on (Student.idGr=Gr.idGr)
Для обращения к процедуре можно использовать команды:
EXECUTE StudByGroup;
EXEC StudByGroup;
StudByGroup;
Возвращает набор данных.
Пример. Процедура без параметров. Создать процедуру для уменьшения стоимости обучения на факультете Информационных систем и защиты информации на 10%.
CREATE PROCEDURE SetPrice
AS
UPDATE Student
SET priceKurs=priceKurs*0.9
WHERE nameFacult like 'Информационных систем%';
PRINT 'Цена успешно изменена';
Процедура не возвращает никаких данных, но выводит сообщение.
Пример. Процедура с входным параметром. Создать процедуру для поиска студента по фамилии
CREATE PROCEDURE FindStud
@x varchar(20)
AS
SELECT * FROM Student
WHERE sNameSt = @x
Для обращения к процедуре можно использовать команды:
EXEC FindStud 'Иванов'
EXEC FindStud @x='Иванов'
FindStud @x='Иванов'
Пример. Процедура с входными параметрами. Создать процедуру для уменьшения стоимости обучения в соответствии с указанным % на указанном факультете.
CREATE PROCEDURE SetPriceUser
@f VARCHAR(200), @p FLOAT
AS
UPDATE Student
SET priceKurs=priceKurs*(1-@p)
WHERE nameFacult = @f;
PRINT 'Цена успешно уменьшена на '+CAST(@p*100 as varchar(10))+'% для факультета '+@f;
Вызов процедуры:
SetPriceUser @f='Информационных систем и защиты информации', @p=0.05
Пример. Процедура с входными параметрами и значениями по умолчанию. Создать процедуру для уменьшения стоимости обучения в соответствии с указанным % на указанном факультете. По умолчанию уменьшать на 10%.
CREATE PROCEDURE SetPriceUser10
@f VARCHAR(200), @p FLOAT = 0.1
AS
UPDATE Student
SET priceKurs=priceKurs*(1-@p)
WHERE nameFacult = @f;
PRINT 'Цена успешно уменьшена на '+CAST(@p*100 as varchar(10))+'% для факультета '+@f;
Вызов процедуры:
SetPriceUser10 @f='Информационных систем и защиты информации'
SetPriceUser10 @f='Информационных систем и защиты информации', @p=0.07
Пример. Процедура с входными и выходными параметрами. Создать процедуру для определения общей стоимости товаров, проданных за конкретный месяц.
CREATE PROC CountStudInGroup
@group VARCHAR(20),
@cnt INTEGER =NULL OUTPUT
AS
SELECT @cnt=COUNT(idSt)
FROM Student JOIN Gr ON (Student.idGr=Gr.idGr)
WHERE Gr.nameGr=@group
Обращение к процедуре и получение значения выходного параметра:
DECLARE @countStud INTEGER
EXEC CountStudInGroup 'ИС-11', @countStud OUTPUT
SELECT @countStud