Використання збережуваної процедури в SQL

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

Основою могутніх можливостей, закладених у збережувану процедуру, є процедурна мова програмування, що має у своєму складі як модифіковані речення звичайного SQL, такі, як INSERT, UPDATE і SELECT, так і засоби організації розгалужень і циклів (IF, WHILE), а також засоби обробки помилок і виняткових ситуацій. Мова збережуваних процедур дозволяє реалізувати складні алгоритми роботи з даними, а завдяки орієнтованості на роботу з реляційними даними збережувані процедури виходять значно компактнішими аналогічних процедур на традиційних мовах.

Існують два типи збережуваних процедур:

  1. Процедури вибору: як результат своєї роботи повертають набір даних або повідомлення про помилку.
  2. Процедури дії: за загальним призначенням не відрізняються від процедур (підпрограм) в алгоритмічних мовах, таких як Паскаль, основна відмінность полягає в синтаксисі мови і здійснюють з базою даних деяку дію.

Обидва типи збережуваних процедур створюються командою CREATE PROCEDURE, яка має наступний формат:

CREATE PROCEDURE <ім'я процедури> [(<вхідний параметр> <тип> [, <вхідний параметр> <тип>...])]
[RETURNS (<вихідний параметр> <тип> [, <вихідний параметр> <тип> ...])]
AS
[DECLARE VARIABLE <ім'я змінна> <тип>; [DECLARE VARIABLE <ім'я змінна> <тип>; ...]]
BEGIN
<оператори>;
END

Як бачите, після команди CREATE PROCEDURE вказується ім'я для створюваної процедури (повинно бути унікальним у межах бази даних), після чого, у дужках через кому перелічуються вхідні параметри та їх тип. Список вхідних параметрів є необов'язковою частиною оператора CREATE PROCEDURE, бувають випадки, коли всі дані для своєї роботи процедура одержує за допомогою запитів до таблиць усередині тіла процедури.

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

Далі міститься ключове слово AS після якого слідує тіло процедури (нагадаємо, що до слова AS ми оголошували заголовок процедури). Тіло збережуваної процедури являє собою перелік описів її внутрішніх (локальних) змінних (оголошуються з допомогою DECLARE VARIABLE <ім'я змінної> <тип>;)  і блок операторів, охоплений операторними дужками BEGIN END.

Приклад: створимо процедуру дії, яка на вході приймає два цілих числа і повертає їх суму.

CREATE PROCEDURE suma (a INTEGER, b INTEGER)
RETURNS (c INTEGER)
AS
BEGIN
c = a + b;
END

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

Приклад: створимо процедуру вибору, яка повертає список автомобілів, які підлягають деякому критерію відбору.

CREATE PROCEDURE select_cars_by_model (in_model VARCHAR (25))
RETURNS (out_model VARCHAR (25) , out_color VARCHAR (10), out_year INTEGER, out_price INTEGER)
AS
BEGIN
SELECT model, color, year, price
FROM Cars
WHERE model =: in_model
INTO :out_model, :out_color, :out_year, : out_price;
SUSPEND;
END

У процедурах вибору повинен бути фрагмент INTO :імена змінних, яких визначає значення, що повертаються процедурою в якості результатів. Крім того, має бути присутнім службове слово SUSPEND, яке після відправлення кожного сформованого набору даних (у нашому разі :out_model, :out_color, :out_year, : out_price) робить паузу, поки програма що викликає процедеру не повідомить, що набір даних прийнятий, після чого формує наступний набір. Таким чином, наявність SUSPEND в поєднанні з циклом забезпечує циклічну відправку багатьох наборів даних (записів).

Збережувану процедуру викликають за допомогою команди EXECUTE PROCEDURE. При наявності вхідних параметрів необхідно задати їх значення, наприклад:

EXECUTE PROCEDURE suma(1, 2);

Матеріал був корисним, поділись в соціальних мережах:

Якщо тобі сподобалась дана тема, залиш свій коментар