Теоретичні відомості. Збережені процедури (stored procedures) дозволяють реалізувати значну частину логіки програми на рівні бази даних й у такий спосіб підвищити продуктивність

Збережені процедури (stored procedures) дозволяють реалізувати значну частину логіки програми на рівні бази даних й у такий спосіб підвищити продуктивність усієї програми, централізувати обробку даних і зменшити кількість коду, необхідного для виконання поставлених задач.

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

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

Користувачі можуть здійснювати доступ до бази даних, не знаючи деталей архітектури таблиць і без безпосереднього доступу до даних таблиць, – вони просто запускають процедури, які виконують необхідні завдання. Тим самим збережені процедури допомагають забезпечувати дотримання бізнес-правил.

Збережені процедури можуть приймати вхідні параметри, використовувати локальні змінні й повертати дані. Збережені процедури можуть повертати дані за допомогою вхідних параметрів, а також можуть повертати коди завершення та результуючі набори з операторів SELECT.

Є три типи збережених процедур: системні збережені процедури, розширені збережені процедури й прості обумовлені користувачем збережені процедури. Системні збережені процедури надає СУБД. Вони використаються, наприклад, для висновку на екран інформації про бази даних і користувачах. Розширені збережені процедури є бібліотеками, що підключаються динамічно (DLL), які може динамічно завантажувати й виконувати СУБД. Прості обумовлені користувачем збережені процедури створюються користувачем і настроюються для виконання тих задач, які потрібні даному користувачеві.

Оператор CREATE PROCEDURE має наступний синтаксис (для СУБД Mіcro Soft SQL Server):

CREATE PROC[EDURE] <Ім'я_процедури>

[ {@<ім'я_параметра> <тип даних>} ] [= <по_умовчанню>] [OUTPUT][......n]

AS <onepaтоp(и) розширеного SQL>

Щоб задати вхідні параметри в збереженій процедурі, необхідно вказати список цих параметрів із символом @ перед ім'ям кожного параметра, тобто @ім'я_параметра.

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

Для повернення значення параметра збереженої процедури у викликаючу програму використається ключове слово OUTPUT після імені цього параметра.

При звертанні до збереженої процедури можна також задати вхідне значення у вихідному параметрі. Це означає, що це значення буде уведено в збережену процедуру, де це значення може бути змінене або використане для операцій; потім нове значення повертається в викликаючу програму.

Для створення локальних змінних використається ключове слово DECLARE. При створенні локальної змінної потрібно задати для неї ім'я й тип даних, а також поставити перед ім'ям змінної символ @. При оголошенні змінної їй спочатку привласнюється значення NULL.

Можна використати оператори BEGІN TRANSACTІON, COMMІT й ROLLBACK у збереженій процедурі, що містить більше одного оператора. Це дозволяє вказувати, які оператори повинні бути згруповані як одна транзакція.

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

Можна також одержувати дані зі збереженої процедури за допомогою оператора SELECT, що перебуває усередині цієї процедури.