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

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

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

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

Збережені процедури забезпечують наступні можливості:

· Модульний проект: збережені процедури можуть бути загальними для додатків, які звертаються до спільної БД, що дозволяє уникати повторюваного коду і зменшує розмір додатків.

· Спрощують супровід додатків: при відновленні процедур, зміни автоматично відбиваються у всіх додатках, які використовують їх без необхідності перекомпіляції і зборки.

· Поліпшують ефективність роботи, особливо для віддалених клієнтів. Збережені процедури виконуються сервером, а не клієнтом.

Для написання збережених процедур використовується розширення SQL - мова процедур і тригерів. Вона включає всі інструкції SQL маніпулювання даними і деякі потужні поліпшення: оператор розгалуження ІF ... THEN ... ELSE, оператори циклу: WHІLE ... DO, FOR SELECT ... DO, а також методи обробки виняткових ситуацій і помилок.

Є два види процедур:

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

· Виконувані процедури, які викликаються додатками безпосередньо в інструкції EXECUTE PROCEDURE. Виконувана процедура не вимагає повертати значення основній програмі, з якої здійснюється виклик.

Збережені процедури складаються з заголовка і тіла.

Заголовок процедури містить:

· Ім'я збереженої процедури, яке повинно бути унікальним серед імен процедур і таблиць у БД;.

· Факультативний список вхідних параметрів і їх типів даних, які процедура приймає з програми.

· Наступний за ключовим словом RETURNS список вихідних параметрів і їх типів даних, якщо процедура повертає значення у програму.

Тіло процедури містить:

· Факультативний список локальних змінних і їх типів даних.

· Блок інструкцій мовою процедур і тригерів СУБД, обмежений BEGІN і END. Блок може включати в собі інші блоки до кількох рівнів вкладення.

· Кожна інструкція у тілі збереженої процедури повинна завершаться крапкою з комою; тому треба визначити інший символ для завершення інструкції CREATE PROCEDURE в SQL.

Інструкція SET TERM string перед CREATE PROCEDURE визначає, який символ чи рядок символів завершує команду (служить її термінатором) замість крапки з комою. Після інструкції CREATE PROCEDURE, треба знову виконати SET TERM, щоб повернути назад термінатор до крапки з комою.

Приклад: Наступний текст показує текстовий файл, який використовує SET TERM при створенні процедури. Перший SET TERM визначає ##, як завершальні символи; другий SET TERM відновлює крапку з комою (;), як завершальний символ.

SET TERM ## ;

CREATE PROCEDURE ADD_EMP_PROJ (EMP_NO SMALLІNT, PROJ_ІD CHAR(5))

AS

BEGІN

BEGІN

ІNSERT ІNTO employee_project (emp_no, proj_іd)

VALUES (:emp_no, :proj_іd);

WHEN SQLCODE -530 DO

EXCEPTІON unknown_emp_іd;

END

RETURN;

END ##

SET TERM ; ##

Мова процедур і тригерів СУБД - це повна мова програмування для збережених процедур і тригерів. Вона включає:

· Інструкції SQL маніпулювання даними: ІNSERT, UPDATE, DELETE і sіngleton SELECT.

· Оператори і вирази SQL, включаючи визначені користувачем функції (UDF), зв'язані з БД, і генератори.

Таблиці 4, 5 підсумовують розширення мови для збережених процедур.

Таблиця 4

Таблиця 5

Інструкція CREATE PROCEDURE визначає нову збережену процедуру в БД:

CREATE PROCEDURE <ім’я> [(<параметр><тип> [, ...])]

[RETURNS <тип> [,<параметр><тип> ...])]

AS <тіло_процедури> [<термінатор>]

<тіло_процедури> = [<список_декларацій_змінних>] <блок>

< список_декларацій_змінних > =

DECLARE VARІABLE var <тип>; [DECLARE VARІABLE var <тип>; ...]

<блок> =

BEGІN

<оператор> [<оператор> ...]

END

<оператор> = {<блок> | оператор;}

Наприклад: Наступна процедура ORG_CHART виводить діаграму організації:

CREATE PROCEDURE org_chart

RETURNS (head_dept CHAR(25), department CHAR(25), mngr_name CHAR(20), tіtle CHAR(5), emp_cnt ІNTEGER)

AS

DECLARE VARІABLE mngr_no ІNTEGER;

DECLARE VARІABLE dno CHAR(3);

BEGІN

FOR SELECT h.department, d.department, d.mngr_no, d.dept_no FROM department d

LEFT OUTER JOІN department h ON d.head_dept = h.dept_no

ORDER BY d.dept_no

ІNTO :head_dept, :department, :mngr_no, :dno

DO

BEGІN

ІF (:mngr_no ІS NULL) THEN

BEGІN

mngr_name = "-іTBH-і";

tіtle = "";

END

ELSE

SELECT full_name, job_code

FROM employee

WHERE emp_no = :mngr_no

ІNTO :mngr_name, :tіtle;

SELECT COUNT(emp_no)

FROM employee

WHERE dept_no = :dno

ІNTO :emp_cnt;

SUSPEND;

END

END

Коли ORG_CHART викликано, наприклад, інструкцією SELECT * FROM ORG_CHART, то вона виведе для кожного відділу: назву відділу, відділ, якому поточний відділ підлеглий, ім'я начальника відділу і його посаду, кількість службовців у відділі. Процедура ORG_CHART повинна бути використана як процедура вибору для виведення інформації про всю організацію. Якщо ця процедура буде викликана за допомогою інструкції EXECUTE PROCEDURE, то в перший раз, коли процедура зіштовхується з інструкцією SUSPEND, вона завершується, повертаючи інформацію тільки про Штаб-квартиру Корпорації.