Процедура - це найбільш часто використовувана в базах даних програмна структура, що представляє собою оформлений особливим чином сценарій (вірніше, пакет), який зберігається в базі даних, а не в окремому файлі. Збережені процедури відрізняються від сценаріїв тим, що в них допускається використання вхідних і вихідних параметрів, а також значень, що повертаються, які фактично не можуть використовуватися в звичайному сценарії.
Процедура є просто ім'я, пов'язане з програмним кодом T-SQL, який зберігається і виконується на сервері. Вона може містити практично будь-які конструкції або команди, виконання яких підтримується в SQL Server. Процедури можна використовувати для зміни даних, повернення скалярних значень або цілих результуючих наборів. Збережені процедури, є основним інтерфейсом, який повинен використовуватися додатками для звернення до будь-яких даних в базах даних. Збережені процедури дозволяють не тільки управляти доступом до бази даних, але також ізолювати код бази даних для спрощення обслуговування.
Як серверні програми збережені процедури мають ряд переваг.
• Збережені процедури зберігаються в компілювати вигляді, тому виконуються швидше, ніж пакети або запити.
• Виконання обробки даних на сервері, а не на робочій станції, значно знижує навантаження на локальну мережу.
• Збережені процедури мають модульний вид, тому їх легко впроваджувати і змінювати. Якщо клієнтську програму викликає збережену процедуру для виконання деякої операції, то модифікація процедури в одному місці впливає на її виконання у всіх користувачів.
• Збережені процедури можна розглядати як важливий компонент системи безпеки бази даних. Якщо все клієнти здійснюють доступ до даних за допомогою збережених процедур, то прямий доступ до таблиць може бути заборонений, і всі дії користувачів будуть перебувати під контролем. Що ще важливіше, збережені процедури приховують від користувача структуру бази даних і дозволяють йому виконання тільки тих операцій, які запрограмовані в збереженій процедурі.
Управління збереженими процедурами
Збережені процедури управляються за допомогою інструкцій мови визначення даних (DDL) CREATE, ALTER і DROP.
Загальний синтаксис T-SQL коду для створення збереженої процедури має такий вигляд:
CREATE PROC | PROCEDURE
[ <@parameter>
[BEGIN]
Структура цього оператора відповідає основному синтаксису CREATE
Кожна процедура повинна мати унікальне в рамках бази даних ім'я (procedure_name), яка відповідає правилам для ідентифікаторів об'єктів.
Процедури можуть мати будь-яке число вхідних параметрів (@parametr) заданого типу даних (data_type), які використовуються всередині процедури як локальні змінні. При виконанні процедури для кожного з оголошених формальних параметрів повинні бути передані фактичні значення. Або ж для вхідного параметра може бути визначено значення за замовчуванням (default), яке повинно бути константою або дорівнювати NULL. В цьому випадку процедуру можна виконати без вказівки значення відповідного аргументу. Застосування вхідних параметрів необов'язково.
Можна також вказати вихідні параметри (помічені як OUTPUT), що дозволяють збереженій процедурі повернути одне або кілька скалярних значень в підпрограму, з якої вона була викликана. При створенні процедур можна задати три параметри. При створенні процедури з параметром ENCRYPTION SQL Server шифрує визначення процедури. При завданні параметра RECOMPILE SQL Server перекомпілюються збережену процедуру при кожному її запуску. Параметр EXECUTE AS визначає контекст безпеки для процедури.
В кінці визначення процедури, що слідом за ключовим словом AS має бути приведене безпосередньо тіло процедури (sql_statements) у вигляді коду з однієї або декількох інструкцій мови T-SQL.
Інструкція DROP видаляє збережену процедуру з бази даних. Інструкція ALTER змінює вміст всієї процедури, що. Для внесення змін краще використовувати інструкцію ALTER, а не комбінацію інструкцій видалення і створення, так як останній метод видаляє всі дозволи.
Приклад процедури, без параметрів
CREATE PROCEDURE spr_getOrders
SELECT IdOrd, IdCust, OrdDate
Щоб протестувати нову процедуру, відкрийте новий запит SQL Server і виконайте наступний код.
Команда EXECUTE або скорочено EXEC виконує зазначену процедуру, що зберігається.
Застосування вхідних параметрів
Процедура надає певні процедурні можливості (а якщо вона застосовується в інфраструктурі .NET, такі можливості стають дуже значними), а також забезпечує підвищення продуктивності, але в більшості обставин збережена процедура не дозволяє домогтися багато чого, якщо не передбачена можливість передати їй деякі дані, що вказують на те, які дії повинні бути виконані з її допомогою. Зокрема основна проблема, пов'язана з попередньою збереженої процедурою (spr_getOrders), полягає в її статичності. Якщо користувачам буде потрібно інформація про замовлення за інший період часу, то ця процедура їм не допоможе. Тому необхідно передбачити можливість передачі в неї відповідних вхідних параметрів, які дозволили б динамічно змінювати період вибірки.
@parameter_name [AS] datatype [= default | NULL]
У наступному прикладі збережена процедура spr_getOrders доповнюється двома вхідними параметрами, що дозволяють явно вказати період вибірки.
ALTER PROCEDURE [dbo]. [Spr_getOrders]
@Description = N'Новий товар ',
@IdProd = @IdProd OUTPUT
SELECT @IdProd as N '@ IdProd'
Зверніть увагу на те, що при виклику процедури передані значення не для всіх параметрів. Параметр InStock є необов'язковим, оскільки для нього зазначено значення за замовчуванням у вигляді нуля, яке і буде використано, в разі якщо для нього не буде явно надано інше значення. При цьому якби виклик збереженої процедури і передача значень відбувалися з використанням позиційних параметрів, то довелося б заповнювати кожну позицію в списку параметрів, щонайменше, до того, як зустрівся б останній параметр, для якого має бути передбачено значення.
Підтвердження успішного або невдалого завершення роботи за допомогою значень, що повертаються. Використання команди RETURN.
Будь-яка викликається на виконання збережена процедура повертає значення, незалежно від того, чи передбачений в ній повернення значення чи ні. За замовчуванням після успішного завершення процедури СУБД SQL Server автоматично повертає значення, рівне нулю.
Щоб передати деякий повертається значення з збереженої процедури назад в викликає код, досить застосувати оператор RETURN:
RETURN [<Целое число>]
Зверніть увагу на те, що повертається значення повинно бути обов'язково цілочисельним.
Значення, що повертаються призначені виключно для вказівки на успішне або невдале завершення процедури, що і дозволяють навіть позначити ступінь або характер успіху або невдачі. Використання повертається для повернення фактичних даних, таких як ідентифікаційне значення або дані про кількість рядків, порушених збереженої процедурою, розглядається як неприпустима практика програмування. Значення, що повертається 0 вказує на успішне виконання процедури і встановлено за замовчуванням. Компанія Microsoft зарезервувала значення від -99 до -1 для службового користування. Розробникам для повернення стану помилки користувачеві рекомендується використовувати значення -100 і менше.
Однією з найбільш важливих особливостей оператора RETURN є те, що його виконання призводить до безумовного завершення роботи і виходу з збереженої процедури. Це означає, що, незалежно від місцезнаходження оператора RETURN в коді збереженій процедурі, після його виконання більше не буде виконана жодна рядок коду. Під безумовним завершенням роботи мається на увазі, що діяння, передбачене оператором RETURN, здійснюється незалежно від того, в якому місці коду він виявляється. З іншого боку, допускається наявність в коді процедури, що кількох операторів RETURN, а виконання цих операторів відбувається, тільки якщо до цього призводить звичайна структура управління процесом виконання коду.
У попередньому прикладі при спробі додавання в таблицю Product інформації про новий товар з дублюючим назвою могла статися помилка, оскільки по найменуванню товару організовано обмеження унікальності. Розширимо збережену процедуру spr_addProduct, передбачивши попередню перевірку на наявність зазначеного товару в базі і індикацію про успішність операції через вихідний параметр.
ALTER PROCEDURE [dbo]. [Spr_addProduct]
Завдання для самостійної роботи: Створіть збережені процедури, що реалізують такі дії:
• Повернення списку всіх замовлень містять заданий товар (по IdProd).
• Визначення кількості клієнтів, які не мають жодного замовлення. Результат повинен повертатися через вихідний параметр.
• Видалення з бази даних інформації про певний клієнта (по IdCust). Якщо з даним клієнтом є пов'язані записи (замовлення) видалення повинно бути скасовано. Значення, що повертається повинно визначати успішність виконання операції.
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo]. [Spr_getOrder]
SELECT CodeGoods, CodeType, Categori, Mark, Price