Лабораторна робота №13

Збережені процедури

 

Мета роботи: навчитися створювати збережені процедури в середовищі SQL Server Management Studio.

Теоретичні відомості

Збережені процедури – SQL-запит, що має параметри, тобто він виконується як звичайна процедура. Залежно від значення параметрів збереженої процедури, ми отримуємо той або інший результат запиту. У Microsofr SQL Server збережені процедури, реалізують динамічні запити, що виконуються на стороні сервера.

Розглянемо створення збережених процедур за допомогою команд мови SQL.

Щоб відобразити збережені процедури робочої БД на панелі «Оглядач об'єктів» треба вибрати пункт «Програмування», а в ньому – «Збережені процедури». Щоб створити нову процедуру за допомогою команд мови SQL треба клікнути лівою кнопкою миші на кнопці , що знаходиться на панелі інструментів. У робочій зоні вікна сервера з'явиться вкладка SQLQuery1.sql, де треба набрати код нової процедури, який має наступний синтаксис:

 

 

 Тут:

- procedure_name – ім'я нової збереженої процедури. Імена процедур повинні відповідати правилам, що пред'являються до ідентифікаторів, і мають бути унікальними.

- number – необов'язкове ціле число, що використовується для групування процедур з одним ім'ям. Усі згруповані процедури можна видалити, виконавши одну інструкцію DROP PROCEDURE.

- @parameter – параметр процедури. У інструкції CREATE PROCEDURE можна оголосити один або більше параметрів. При виконанні процедури значення кожного з оголошених параметрів має бути вказане користувачем, якщо для параметра не визначено значення за замовчуванням або значення не задане рівним іншому параметру. Збережена процедура, може мати не більше 2100 параметрів. Визначає ім'я параметра, знак @ як перший символ. Ім'я параметра повинне відповідати правилам для ідентифікаторів. Параметри є локальними в межах процедури; у різних процедурах можуть бути використані однакові імена параметрів.

- data_type – тип даних параметра. Усі типи даних можуть використовуватися в якості параметра збереженої процедури Transact-SQL. Можна використовувати визначуваний користувачем табличний тип, щоб оголосити параметр, що повертає табличне значення, в якості параметра збереженої процедури Transact-SQL.

- default – значення параметра за замовчуванням. Якщо визначене значення default, процедуру можна виконати без вказівки значення відповідного параметра. Значення за замовчуванням має бути константою або може дорівнювати NULL.

- OUTPUT показує, що параметр процедури є вихідним. Значення цього параметра можна отримати за допомогою інструкції EXECUTE. Використовують параметри OUTPUT для повернення значень коду, що викликав процедуру.

- READONLY вказує, що параметр не може бути оновлений або змінений в тексті процедури. Якщо тип параметра є визначеним користувачем табличним типом, має бути вказане ключове слово READONLY.

- RECOMPILE показує, що компонент Database Engine не кешує план виконання процедури і, що процедура компілюється під час виконання.

- EXECUTE AS визначає контекст безпеки, в якому має бути виконана збережена процедура.

- <sql_statement> – одна або декілька інструкцій мови SQL, які будуть включені до складу процедури.

Якщо параметри порівнюються з якимись полями або виразами, то вони повинні мати такий самий тип даних, як ці поля або вирази. Після створення процедура поміщається в розділ «Збережені процедури» поточної БД, на панелі «Оглядач об'єктів».

Щоб подивитися інформацію про збережену процедуру, необхідно виконати команду:

EXEC SP_HELPTEXT <Ім'я процедури>

Збережені процедури, можуть бути запущені наступною командою

EXEC <Ім'я процедури> [<Параметр1>, <Параметр2>, .]

Тут <Ім'я процедури> – ім'я виконуваної процедури; <Параметр1>, <Параметр2>… – значення параметрів.

 

Приклад: Створення збереженої проедури, яка виводить імена студентів із середнім балом, більшим заданої величини:

CREATE PROCEDURE СрБАЛ

@X Real

AS

SELECT *

FROM Студенти

WHERE

(Оцінка1 + Оцінка2 + Оцінка3)/3>@X

Команда виклику цієї процедури виглядає таким чином:

EXEC СрБАЛ 4

Команда виводить усіх студентів, у яких середній бал більше 4.

 

Приклад виконання лабораторної роботи

Для роботи з збереженими процедурами, в оглядачі об'єктів необхідно виділити теку «Програмування/Збережені процедури» бази даних (рис. 9.1).

Рис. 9.1 – Збережувані процедури в SQL Server Management Studio

 

Створимо процедуру, що обчислює середнє трьох чисел. Для створення нової збереженої процедури, треба клікнути правою кнопкою миші по теці «Збережені процедури», і в меню, що з'явилося, вибрати пункт «Створити збережену процедуру». З'явиться вікно коду нової збереженої процедури (рис. 9.2).

Рис. 9.2 – Створення нової збереженої процедури

 

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

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

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

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

2. Область визначення імені процедури і параметрів, що передаються в процедуру. Визначення параметрів має наступний синтаксис:

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

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

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

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

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

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

Наберемо код процедури, що обчислює середнє трьох чисел, як це показано на наступному рисунку.

 

Розглянемо код цієї процедури детальніше:

1. CREATE PROCEDURE MeanValue визначає ім'я створюваної процедури як «MeanValue»;

2. @Value1 Real = 0, @Value2 Real = 0, @Value3 Real = 0 – визначення трьох параметрів процедури Value1, Value2 і Value3. Тип параметрів – Real, значення за замовчунням дорівнюють 0;

3. SELECT 'Mean Value'= (@Value1+@Value2+@Value3)/3 - обчислення середнього і виведення результату з підписом «Середнє значення».

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

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

EXEC MeanValue 1, 7, 9

і натисніть кнопку  на панелі інструментів.

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

Тепер створимо збережену процедуру, для відбору художників з таблиці «Authors» за їх прізвищами. Для цього створимо нову збережену процедуру, по опису, приведеному вище, і наберемо наступний код нової процедури.

Перевіримо працездатність створеної збереженої процедури. Створимо новий порожній запит. У вікні, що з'явилося, з порожнім запитом наберемо команду EXEC [Artists by Name] 'Прізвище' і виконаємо запит.

На закінчення вирішимо складніше завдання: відображення імен художників, що народилися в XV столітті. Створимо нову збережену процедуру, і наберемо наступний код нової процедури.

Функція YEAR має наступний синтаксис YEAR(date) і повертає ціле число, що представляє рік вказаної дати date.

Отриманий за допомогою функції YEAR рік народження перевіряється на приналежність діапазону років, що належать до XV століття.

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

 

Завдання

1. Створити збережені процедури у відповідності із завданням для свого варіанту.