Хранимые процедуры

Цель: научиться работать с хранимыми процедурами

Перейдем к созданию хранимых процедур. Для работы с хранимыми процедурами в обозревателе объектов необходимо выделить папку "Programmability/Stored Procedures" базы данных "Students" ( рис. 10.1).

Создадим процедуру, вычисляющую среднее трех чисел. Для создания новой хранимой процедуры щелкните ПКМ по папке "Stored Procedures" ( рис. 10.1) и в появившемся меню выберите пункт "New Stored Procedure". Появиться окно кода новой хранимой процедуры ( рис. 10.2).

Хранимая процедура имеет следующую структуру ( рис. 10.2):

1. Область настройки параметров синтаксиса процедуры. Позволяет настраивать некоторые синтаксические правила, используемые при наборе кода процедуры. В нашем случае это:
   • SET ANSI_NULLS ON - включает использование значений NULL (Пусто) в кодировке ANSI,
   • SET QUOTED_IDENTIFIER ON - включает возможность использования двойных кавычек для определения идентификаторов;
2. Область определения имени процедуры ( Procedure_Name ) и параметров передаваемых в процедуру ( @Param1, @Param2 ). Определение параметров имеет следующий синтаксис:

   @<Имя параметра> <Тип данных> = <Значение по умолчанию>

   Параметры разделяются между собой запятыми;
3. Начало тела процедуры, обозначается служебным словом "BEGIN" ;
4. Тело процедуры, содержит команды языка программирования запросов T-SQL;
5. Конец тела процедуры, обозначается служебным словом "END".

Замечание: В коде зеленым цветом выделяются комментарии. Они не обрабатываются сервером и выполняют функцию пояснений к коду. Строки комментариев начинаются с подстроки "--". Далее в коде, мы не будем отображать комментарии, они будут свернуты. Слева от раздела с комментариями будет стоять знак "+", щелкнув по которому можно развернуть комментарий.

Наберем код процедуры вычисляющей среднее трех чисел, как это показано на рис. 10.3.

Рассмотрим код данной процедуры более подробно ( рис. 10.3):

1. CREATE PROCEDURE [Среднее трех величин] - определяет имя создаваемой процедуры как "Среднее трех величин";
2. @Value1 Real = 0, @Value2 Real = 0, @Value3 Real = 0 - определяют три параметра процедуры Value1, Value2 и Value3. Данным параметрам можно присвоить дробные числа (Тип данных Real), значения по умолчанию равны 0;
3. SELECT 'Среднее значение'=(@Value1+@Value2+@Value3)/3 - вычисляет среднее и выводит результат с подписью "Среднее значение".

Остальные фрагменты кода рассмотрены выше ( рис. 10.2).

Для создания процедуры, выполним вышеописанный код, нажав кнопку

(Выполнить) на панели инструментов. В нижней части окна с кодом появиться сообщение "Command(s) completed successfully.". Закройте окно с кодом, щелкнув мышью по кнопке закрытиярасположенной в верхнем правом углу окна с кодом процедуры.

Проверим работоспособность созданной хранимой процедуры. Для запуска хранимой процедуры необходимо создать новый пустой запрос, нажав на кнопку

(Новый запрос) на панели инструментов. В появившемся окне с пустым запросом наберите команду EXEC [Среднее трех величин] 1, 7, 9 и нажмите кнопкуна панели инструментов ( рис. 10.4).

В нижней части окна с кодом появится результат выполнения новой хранимой процедуры: Среднее значение 5,66667 ( рис. 10.4).

Теперь создадим хранимую процедуру для отбора студентов из таблицы студенты по их "ФИО". Для этого создайте новую хранимую процедуру, как это описано выше, и наберите код новой процедуры как на рис. 10.5.

Рассмотрим код процедуры "Отображение студентов по ФИО" более подробно ( рис. 10.5):

1. CREATE PROCEDURE [Отображение студентов по ФИО] - определяет имя создаваемой процедуры как "Отображение студентов по ФИО";
2. @FIO Varchar(50)='' - определяют единственный параметр процедуры FIO. Параметру можно присвоить текстовые строки переменной длины, длинной до 50 символов (Тип данных Varchar(50)), значения по умолчанию равны пустой строке;
3. SELECT * FROM dbo.Студенты WHERE ФИО=@FIO - отобразить все поля (*) из таблицы студенты (dbo.Студенты), где значение поля ФИО равно значению параметра FIO (ФИО=@FIO).

Выполним вышеописанный код и закроем окно с кодом, как описано выше.