Практична робота 5: Збережені процедури в середовищі SQL Server Management Studio

Мета: навчитися працювати з збереженими процедурами

 

Перейдемо до створення збережених процедур. Для роботи з збереженими процедурами в оглядачі об'єктів необхідно виділити папку "Програмирование / Хранимые процедуры" бази даних "Студент" (рис. 5.1).

Рис. 5.1.

Створюємо процедуру, яка обчислює середнє трьох чисел. Для створення нової збереженої процедури клацніть ПКМ по папці "Хранимые процедуры" і в меню виберіть пункт "Создать хранимую процедуру". З'явитися вікно коду нової збереженої процедури (рис. 5.2).

Рис. 5.2.

Збережена процедура має наступну структуру:

1.   Область налаштування параметрів синтаксису процедури. Дозволяє налаштовувати деякі синтаксичні правила, використовувані при наборі коду процедури. У нашому випадку це:

·         SET ANSI_NULLS ON - включає використання значень NULL  в кодуванні ANSI,

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

2.   Область визначення імені процедури (Procedure_Name) і параметрів переданих в процедуру (@ Param1, @ Param2). Визначення параметрів має наступний синтаксис:

@ <Ім'я параметра> <Тип даних> = <Значення за замовчуванням>

Параметри розділяються між собою комами;

3.   Початок тіла процедури, позначається службовим словом "BEGIN";

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

5.   Кінець тіла процедури, позначається службовим словом "END".

Зауваження: У коді зеленим кольором виділяються коментарі. Вони не обробляються сервером і виконують функцію пояснень до коду. Рядки коментарів починаються з підрядка "-". Далі в коді, ми не будемо відображати коментарі, вони будуть згорнуті. Зліва від розділу з коментарями стоятиме знак "+", клацнувши по якому можна розгорнути коментар.

Наберемо код процедури обчислює середнє трьох чисел (рис. 5.3).

Рис. 5.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 - обчислює середнє і виводить результат з підписом "Середнє значення".

Решта фрагменти коду розглянуті вище.

Для створення процедури, виконаємо вищеописаний код, натиснувши кнопку

(Виконати) на панелі інструментів. У нижній частині вікна з кодом з'явитися повідомлення "Выполнение команд успешно завершено.". Закрийте вікно з кодом, клацнувши мишею по кнопці закриття, розташованої у верхньому правому куті вікна з кодом процедури.

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

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

на панелі інструментів.

Рис. 5.4.

У нижній частині вікна з кодом з'явиться результат виконання нової збереженої процедури: Среднее значение 5,66667 (рис. 5.4).

Тепер створимо збережену процедуру для відбору студентів з таблиці студенти по їх "ФИО". Для цього створіть нову збережену процедуру, як це описано вище, і наберіть код нової процедури (рис. 5.5).

Рис. 5.5.

Розглянемо код процедури "Отображение студентов по ФИО" більш докладно:

1.   CREATE PROCEDURE [Отображение студентов по ФИО] - визначає ім'я створюваної процедури як "Відображення студентів по ПІБ";

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

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

Виконаємо вищеописаний код і закриємо вікно з кодом, як описано вище.

Перевіримо працездатність створеної збереженої процедури. Створіть новий порожній запит. У вікні з порожнім запитом наберіть команду EXEC [Отображение студентов по ФИО] 'Иванов И.И.' і натисніть кнопку

на панелі інструментів.

Рис. 5.6.

У нижній частині вікна з кодом з'явитися результат виконання процедури, що "Отображения студентов по ФИО" (рис. 5.6).

 

Тепер перейдемо до більш складної задачі - відобразити студентів, у яких середній бал вище заданого. Створіть нову збережену процедуру і наберіть код нової процедури (рис. 5.7).

Рис. 5.7.

Розглянемо код процедури "Отображение студентов по среднему баллу" більш докладно:

1.   CREATE PROCEDURE [Отображение студентов по среднему баллу] - визначає ім'я створюваної процедури як "Відображення студентів за середнім балом";

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

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

Виконаємо вищеописаний код і закриємо вікно з кодом, як описано вище. Перевіримо, як працює запит, описаний вище. Для цього, створіть новий запит і в ньому наберіть команду EXEC [Отображение студентов по среднему баллу] 3.5 і виконайте її (рис.5.8).

Рис. 5.8.

У нижній частині вікна з кодом з'явитися результат виконання процедури "Отображение студентов по среднему баллу ".

На закінчення вирішимо більш складне завдання - відображення студентів старше заданого віку. При чому вік буде автоматично обчислюватися в залежності від дати народження. Створимо нову збережену процедуру і наберемо код нової процедури

(рис. 5.9).

Рис.5.9.

Розглянемо код створюваної процедури "Отображение студентов по возрасту" більш докладно:

1.   CREATE PROCEDURE [Отображение студентов по возрасту] - визначає ім'я створюваної процедури як "Відображення студентів за віком";

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

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

Зауваження: Вбудована функція DATEDIFF обчислює кількість періодів між двома датами, має наступний синтаксис: DATEDIFF (<період>, <початкова дата>, <кінцева дата>)

Виконаємо код запиту "Отображения студентов по возросту", а потім закриємо вікно з кодом, як описано вище. Перевіримо, як працює запит. Для цього, створимо новий запит і в ньому наберемо команду EXEC [Отображение студентов по возрасту] 26 і виконайте її(рис. 5.10).

 

Рис. 5.10.