Використання циклів і операторів розгалуження в збережуваних процедурах

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

SELECT {*|<Список полів>}
FROM <Список таблиць>
[WHERE <Критерій відбору>]
INTO змінна [, змінна,...];

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

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

CREATE PROCEDURE c_count

RETURNS (k INTEGER)
AS
BEGIN
SELECT COUNT(id)
FROM Cars
INTO :k;
END

Якщо запит повертає більше одного рядка, то його необхідно оформити у вигляді конструкції FOR SELECT...DO, яка організовує цикл для обробки кожного рядка, що повертається.

FOR
SELECT{*|<Список полів>}
FROM <Список таблиць>
INTO змінна [, змінна,...];
DO
BEGIN
<оператори>
END

Розглянемо більш складний приклад з використанням вищерозглянутої конструкції. Для всіх автомобілів марки Mercedes, які містяться в таблиці Cars, збільшити ціну на 10% (нагадаємо, що редагування даних в таблиці бази даних здійснюється оператором UPDATE).

CREATE PROCEDURE change_price
RETURNS (out_id INTEGER,  out_model VARCHAR(25), out_price INTEGER)
AS
BEGIN
FOR
SELECT c.id, c.model, c.price
FROM Cars c
WHERE c.model LIKE «Mercedes%»
INTO :out_id, :out_model, :out_price
DO
BEGIN
out_price = out_price + (out_price/100)*10;
UPDATE Cars SET price = :out_price
WHERE id = :out_id;
SUSPEND;
END
END

Зверніть увагу, що в даній процедурі перед змінними out_id, out_model, out_price стоїть двокрапка, це зроблено для того, щоб відрізнити їх від полів, що використовуються у запиті.

Крім команди FOR SELECT... DO, що організовує цикл по записах будь-якої вибірки, існує інший вид циклу — WHILE...DO, що дозволяє організувати цикл на основі перевірки будь-яких умов. Також в збережуваних процедурах, крім циклів, використовується умовний оператор IF...THEN...ELSE, який забезпечує в підпрограмі декілька варіантів рішення тієї чи іншої проблеми.

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

CREATE PROCEDURE factorial(n INTEGER)
RETURNS (f INTEGER)
AS
DECLARE VARIABLE i INTEGER;
BEGIN
IF (n < 2) THEN
BEGIN
f = 1;
END
ELSE
BEGIN
i = 1;
f = 1;
WHILE (i <= n) DO
BEGIN
f = f * i;
i = i + 1;
END
END
SUSPEND;
END

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

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