6. 4. Збережувані процедури

З точки зору клієнтських додатків, працюючих з БД, збережувані процедури (Stored Procedure) — це підпрограми, які виконуються на сервері. По відношенню до БД — це об'єкти, створювані і збережувані в БД. Вони можуть бути викликані з клієнтських додатків. При цьому одна процедура може бути використана в будь-якій кількості клієнтських додатків, що дозволяє суттєво зекономити трудові витрати на створення прикладного програмного забезпечення і ефективно вживати стратегію повторного використання кода. Так само як і будь-які процедури в стандартних мовах програмування, збережувані процедури можуть мати вхідні та вихідні параметри або не мати їх зовсім.

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

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

Ці оператори в значній мірі схожі з подібними операторами процедурно- орієнтованих мов програмування.

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

Для створення збережуваної процедури використовується оператор SQL CREATE PROCEDURE. По замовчуванню, виконати збережувану процедуру може тільки її власник, яким є власник БД, і автор процедури . Проте власник збережуваної процедури може делегувати права на її запуск іншим користувачам.

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

В MS SQL Server збережувана процедура створюється оператором:

CREATE PROC[EDURE] <ім'я процедури> [:<версія>]

[{@параметр1 тип даних}

[VARYING] [= <значення по замовчуванню>] [OUTPUT]]

[, . параметр N ...]

[ WITH

{ RECOMPILE

| ENCRYPTION

| RECOMPILE, ENCRYPTION}]

[FOR REPLICATION]

AS (Тіло процедури)

Тут необов'язкове ключове слово VARYING визначає задане значення по замовчуванню для визначеного раніше параметра. Ключове слово RECOMPILE визначає режим компіляції що сворюється збережуваною процедурою. Якщо задано ключове слово RECOMPILE, то процедура буде перекомпільовувана кожного разу при виклику на виконання. Це може різко уповільнити виконання процедури. Але, з іншого боку, якщо дані, оброблювані даною процедурою , настільки динамічні, що попередній план виконання, створений при її першому виклику, може бути абсолютно неефективним при наступних викликах, тоді варто застосувати даний параметр при створенні цієї процедури.

Ключове слово ENCRYPTION визначає режим, при якому вихідний текст збережуваної процедури не залишається в БД. Такий режим використовують для того, щоб зберегти авторське право на інтелектуальну продукцію, якою і є збережувані процедури. Часто такий режим використовують, коли поставляють готову базу замовнику і не хочуть, щоб вихідні тексти розроблених процедур були доступні адміністратору БД замовника.

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

Збережувана процедура може бути викликана декількома способами. Простий спосіб - це використання оператора:

EXEC <ім'я процедури> <значення вхідного параметра> <ім'я_змінної для вхідного параметра>.

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

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

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

ЕХЕС <ім'я процедури> <ім'я параметра1>=<значення параметра1>...

<ім'я параметраN>=<значення параметраN>...

Якщо ми задаємо параметри по іменам, то нам необв'язково задавати їх у

тому порядку, в якому вони описані при створенні процедури.