Лабораторная работа: Хранимые процедуры

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

Рис. 10.1.

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

Рис. 10.2.

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

Область настройки параметров синтаксиса процедуры. Позволяет настраивать некоторые синтаксические правила, используемые при наборе кода процедуры. В нашем случае это:

SET ANSI_NULLS ON — включает использование значений NULL (Пусто) в кодировке ANSI,.

SET QUOTED_IDENTIFIER ON — включает возможность использования двойных кавычек для определения идентификаторов;

Область определения имени процедуры (Procedure_Name) и параметров передаваемых в процедуру (@Param1, @Param2). Определение параметров имеет следующий синтаксис:

@ =.

Параметры разделяются между собой запятыми;

Начало тела процедуры, обозначается служебным словом «BEGIN» ;

Тело процедуры, содержит команды языка программирования запросов T-SQL;

Конец тела процедуры, обозначается служебным словом «END» .

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

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

Рис. 10.3.

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

CREATE PROCEDURE [Среднее трех величин] - определяет имя создаваемой процедуры как «Среднее трех величин» ;

@Value1 Real = 0, @Value2 Real = 0, @Value3 Real = 0 — определяют три параметра процедуры Value1, Value2 и Value3. Данным параметрам можно присвоить дробные числа (Тип данных Real), значения по умолчанию равны 0;

SELECT 'Среднее значение'=(@Value1+@Value2+@Value3)/3 — вычисляет среднее и выводит результат с подписью «Среднее значение» .

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

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

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

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

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

Рис. 10.4.

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

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

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

CREATE PROCEDURE [Отображение студентов по ФИО] - определяет имя создаваемой процедуры как «Отображение студентов по ФИО» ;

@FIO Varchar (50)='' - определяют единственный параметр процедуры FIO. Параметру можно присвоить текстовые строки переменной длины, длинной до 50 символов (Тип данных Varchar (50)), значения по умолчанию равны пустой строке;

SELECT * FROM dbo. Студенты WHERE ФИО=@FIO — отобразить все поля (*) из таблицы студенты (dbo.Студенты), где значение поля ФИО равно значению параметра FIO (ФИО=@FIO).

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

Проверим работоспособность созданной хранимой процедуры. Создайте новый пустой запрос. В появившемся окне с пустым запросом наберите команду EXEC [Отображение студентов по ФИО] 'Иванов А.И.' и нажмите кнопку на панели инструментов (рис. 10.6).

Рис. 10.6.

В нижней части окна с кодом появиться результат выполнения хранимой процедуры «Отображение студентов по ФИО» (рис. 10.6).

Теперь перейдем к более сложной задаче — отобразить студентов, у которых средний балл выше заданного. Создайте новую хранимую процедуру и наберите код новой процедуры как на рис. 10.7.

Рис. 10.7.

Рассмотрим код процедуры «Отображение студентов по среднему баллу» более подробно (рис. 10.7):

CREATE PROCEDURE [Отображение студентов по среднему баллу] - определяет имя создаваемой процедуры как «Отображение студентов по среднему баллу» ;

@Grade Real=0 — определяют параметр процедуры Grade. Параметру можно присвоить дробные числа (Тип данных Real), значения по умолчанию равны 0;

SELECT * FROM [Запрос Студенты+Оценки] WHERE ([Оценка первого экзамена]+[Оценка второго экзамена]+[Оценка третьего экзамена])/3>@Grade — отобразить все поля (*) из запроса «Запрос Студенты+Оценки» (Запрос Студенты+Оценки), где средний балл больше чем значение параметра Grade (([Оценка первого экзамена]+[Оценка второго экзамена]+[Оценка третьего экзамена])/3>@Grade).

Выполним вышеописанный код и закроем окно с кодом, как описано выше. Проверим, как работает запрос, описанный выше. Для этого, создайте новый запрос и в нем наберите команду EXEC [Отображение студентов по среднему баллу] 3.5 и выполните ее (Смотри выше) (рис. 10.8).

Рис. 10.8.

В нижней части окна с кодом появиться результат выполнения хранимой процедуры «Отображение студентов по среднему баллу» (рис. 10.8).

В заключение решим более сложную задачу — отображение студентов старше заданного возраста. При чем возраст будет автоматически вычисляться в зависимости от даты рождения.

Создадим новую хранимую процедуру и наберем код новой процедуры как представлено на рис. 10.9.

Рис. 10.9.

Рассмотрим код создаваемой процедуры «Отображение студентов по возрасту» более подробно (рис. 10.9):

CREATE PROCEDURE [Отображение студентов по возрасту] - определяет имя создаваемой процедуры как «Отображение студентов по возрасту» ;

@Age int=0 — определяют параметр процедуры Age. Параметру можно присвоить целые числа (Тип данных int), значения по умолчанию равны 0;

ФИО, [Запрос Студенты+Специальности]. Дата рождения], 'Возраст'=DATEDIFF (yy,[Запрос Студенты+Специальности]. Дата рождения], GETDATE ()) — отображает из запроса «Запроса Студенты+Специальности» (FROM [Запрос Студенты+Специальности]) поля «ФИО» (ФИО) и «Дата рождения» ([Запрос Студенты+Специальности]. Дата рождения]), а также отображает возраст студента ('Возраст') в годах (yy), вычисленный исходя из его даты рождения и текущей даты (DATEDIFF (yy,[Запрос Студенты+Специальности]. Дата рождения], GETDATE ())). Более того, выводятся студенты возраст которых больше определенного в параметре «Age» (DATEDIFF (yy,[Запрос Студенты+Специальности]. Дата рождения], GETDATE ())>@Age).

Замечание: Встроенная функция DATEDIFF вычисляющая количество периодов между двумя датами, имеет следующий синтаксис: DATEDIFF (,).

Выполним код запроса «Отображение студентов по возрасту», а затем закроем окно с кодом, как описано выше. Проверим, как работает запрос. Для этого, создадим новый запрос и в нем наберем команду EXEC [Отображение студентов по возрасту] 26 и выполните ее. Должен появиться результат аналогичный результату, представленному на рис. 10.10.

Рис. 10.10.

На этом мы заканчиваем описание хранимых процедур и переходим к рассмотрению пользовательских функций. В итоге, обозреватель объектов должен иметь вид как на рис. 10.11.

Рис. 10.11.