Lab_04_Oracle %28PL_SQL%29 / Додатково_Теорія_01_Блоки_керуючі_структури_ PL_SQL

1. Освоєння PL/SQL. Блоки й керуючі структури

PL/SQL.

1.1. Теоретичні відомості

1.1.1. Мова PL/SQL

PL/SQL (PL – programmatic language) створений фірмою Oracle для розширення можливостей мови SQL при написанні збережених процедур і сценаріїв. От деякі конструкції останнього:

Блоки IF ... THEN.

Цикли WHILE ... DO і FOR ... NEXT.

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

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

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

PL/SQL – блочно-структурована мова. Це означає, що основні одиниці програм PL/SQL (анонімні блоки, процедури й функції) є логічними блоками, які можуть містити будь-яке число вкладених у них блоків. Структура блоку представлена на рис. 1.1.

[ DECLARE
-- Объявления переменных, констант, курсоров и т.п.	]
BEGIN
-- Тело основной программы, в которую могут вкладываться другие блоки
[ EXCEPTION
-- Обработчики исключительных ситуаций	]

Рис 1.1. Структура блоку PL/SQL

Обов'язковою є частина, що виконується лише у блоці (BEGIN), а вкладені блоки можна вкладати тільки у блок, що виконується і в частину обробки виняткових ситуацій.

У циклі лабораторних робіт коротко (на прикладах) розглянемо основні конструкції PL/SQL, які можуть використовуватися при обробці

даних БД (процедур і функцій, пакетів, створенні тригерів і їхнього налагодження й ін.). Деякі приклади будуть пов'язані зі створенням різних типів програм для перевірки правильності введення (зміни) прізвища, ім'я або по батькові й, якщо необхідно, їхнього коректування. Прізвище (ім'я) повинне складатися тільки з російських букв і символів тире та пробіл. Перша буква прізвища (ім'я або по батькові) повинна бути прописною, а інші – рядковими. У подвійних (потрійних) прізвищах та іменах кожна частина повинна починатися з великої букви (наприклад, СмирновСокольский, Жан-Жак Руссо, Гай Юлій Цезар).

PL/SQL – це мова третього покоління (3GL) як процедурне розширення ANSI-Стандарту мови SQL. Він містить у собі безліч структурних елементів, характерних для сучасної мови програмування. З його допомогою команди маніпулювання даними й завдання запитів мови SQL можуть бути включені безпосередньо в процедурні блоки, що робить PL/SQL потужнім інструментом обробки транзакцій.

1.1.2. Виконавче середовище PL/SQL

PL/SQL – це не самостійний продукт Oracle. Це технологія, реалізована в РСУБД і певних засобах розробки додатків Oracle. PL/ SQLБлоки посилають на пристрій обробки PL/SQL (PL/SQL engine), що може бути встановлене як у середовищі розробки додатків, так і в самому ядрі РСУБД. Який пристрій обробки використовується, залежить від того, де виконується PL/ SQL-Процедура.

Оброблювач PL/SQL виконує всі процедурні команди, а команди мови SQL посилає на оброблювач SQL-Команд, що функціонує в ядрі РСУБД Oracle (див. рис. 1.2). Таким чином, виробляється «фільтрація» SQLКоманд, а процедурна частина, що залишилася, виконується процедурним процесором, убудованим у пристрій обробки PL/SQL.

		РСУБД Oracle
Внешняя
программная	Блок	Процедурная	Обработчик
среда		часть	процедур PL/SQL
Блок	PL/SQL
PL/SQL		SQL

Обработчик SQL-команд

Коли блок PL/SQL обробляється ядром РСУБД, SQL-Команди в такому блоці не відділяються від процедурних команд до влучення на пристрій обробки, де команди SQL пересилаються на оброблювач SQL. Це означає, що для посилки блоку від додатка до ядра Oracle потрібна одна операція. Тим самим підвищується продуктивність функціонування системи, особливо в конфігурації «клієнт-сервер».

1.1.3. Блоки PL/SQL

Мовою PL/SQL можна створювати й виконувати збережені на сервері Oracle іменовані блоки: процедури, функції й тригери або интерактивно виконувати анонімні (не іменовані) програмні блоки. Наприклад, можна на мережному комп'ютері створити анонімний блок у середовищі SQL*Plus і послати його на виконання серверу Oracle.

Структура блоку PL/SQL може включати три розділи: оголошення; тіло основної програми;

оброблювачі виняткових ситуацій.

Розділ оголошень блоку PL/SQL – це частина блоку, де оголошуються літерали, ідентифікатори, змінні, константи, коментарі, користувальницькі типи даних, які потім стають доступні у всіх інших частинах блоку. У тілі основної програми втримуються оператори блоку, що виконуються. У тілі блоку визначаються функціональні можливості програми. У розділі виняткових ситуацій перебувають оброблювачі виняткових ситуацій, тобто підпрограми обробки помилок для блоку.

Програми PL/SQL не обмежені одним блоком. Для кращої організації виконання підзавдань у програмі PL/SQL можна створити вкладені блоки в тілі програми й у розділі виняткових ситуацій блоку.

У наступних розділах коротко приводяться правила оголошення в блоках конструкцій різних типів.

Літерали

Літерали становлять послідовність будь-яких символів, укладених в одинарні лапки, наприклад:

'' – символ пробілу;

'разом вивчаємо ' – строкова константа;

'123456' – це строковий літерал, а не число;

'!"№;;%:?*()_+/ '-=\'~{}[]<>?/:;"'''@#$%^' – літерали можуть містити спеціальні символи. Якщо серед символів літерала зустрічаються одинарні лапки, то вона подвоюється;

''' ''' – тут літерал містить три символи – ' ' (одинарні лапки, пробіл, одинарні лапки).

Строкові літерали розпізнаються як тип CHAR (рядок символів фіксованої довжини) і можуть бути привласнені будь-якій змінній типу CHAR або VARCHAR2.

Ідентифікатори

Ідентифікатори (імена) представляють собою елементи програмування – такі, як змінні, константи, певні користувачі, типи даних, курсори, виняткові ситуації, таблиці баз даних, стовпці, процедури, функції й пакети. Ідентифікатор повинен починатися з букви й містити не більше 30 букв, цифр, символів підкреслення, знаків долара ($) або фунта (#). Ідентифікатори можна виділяти подвійними лапками для того, щоб відрізняти їх від інших ідентифікаторів, наприклад, від зарезервованих слів.

Змінні

Змінні в PL/SQL використовуються в додатках для обробки даних. Перш, ніж використовувати змінну, її треба оголосити. Оголошення змінної складається в призначенні їй ім'я, за яким іде назва типу даних, що повинен використовуватися.

У звичайних блоках PL/SQL оголошення змінних виконується в розділі перед оператором BEGIN. У збережених процедурах, функціях оголошення змінних утримується після ключових слів IS або AS.

Важливим моментом у використанні змінних є поняття – область дії змінної. Більшість змінних мають локальну область дії. Це значить, що вони визначені в розділі DECLARE блоку PL/SQL; існують, поки не досягнуть кінця цього блоку, а потім звільняються. Ті ж імена змінних можна вільно використовувати в наступному блоці.

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

Необхідно звернути увагу на те, що, крім типів даних мови SQL, в PL/SQL можуть використовуватися додаткові типи даних і їхні підтипи.

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

У PL/SQL підтримується можливість створення користувальницьких типів і підтипів.

Константи

Константи дозволяють привласнювати значення ідентифікатору для наступного посилання на нього в блоці. Будь-яка спроба змінити значення константи в блоці викличе помилку. Загальний вид оголошення констант наступний:

<ідентифікатор> CONSTANT <тип_даних> [<число_розрядів> [, <розрядів_після_коми>]] := <вираження>;

Наприклад: П_пі CONSTANT NUMBER(9,5) := 3.14159;

1.1.4.Керуючі структури

Ублоках PL/SQL є можливість керування послідовністю виконання команд як за умовою, так і через безумовні переходи.

Команди керування:

1)оператор GOTO робить безумовний перехід на мітку в програмі;

2)команда IF забезпечує виконання за умовою;

3)базовий цикл LOOP слугує для циклічного виконання послідовності команд без перевірки в операторі циклу умов виходу з нього;

4)цикл FOR забезпечує циклічне виконання набору команд на основі контролю лічильника ітерацій;

5)цикл WHILE призначається для ітеративного керування діями з перевіркою в операторі циклу умов виходу;

6)цикл CURSOR FOR необхідний для циклічної обробки рядків

курсору;

7)оператор EXIT служить для переривання роботи циклу й виходу з

нього;

8)групове зв'язування FORALL забезпечує прискорену циклічну обробку однорідної групи рядків таблиці.

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

1.2. Приклади складання скриптів на мові PL/SQL.

1. Структура блоків PL/SQL

Кожний програмний модуль PL/SQL містить один або більше блоків. Ці блоки можуть бути повністю розділені або вбудовані один в одного. Таким чином, один блок може становити деяку частину іншого блоку, що, в свою чергу, може бути частиною цілого програмного модуля.

У загальному випадку блок може бути або «анонімним», або «підпрограмою».

Анонімні блоки – блоки без імені. Ці блоки описуються в тім місці додатка, де вони повинні виконуватися й передаватися на пристрій виконання PL/ SQL-Процедур у момент роботи додатка. Анонімні блоки можуть бути вбудовані в програми, що використовують прекомпілятор, у програми OCI (Oracle Call Interface – набір процедур для інтерфейсу ядра Oracle з мовами програмування C/C++, Fortaran, Cobol і ін.), а також в

SQL*Plus.

Підпрограми – пойменовані PL/SQL-блоки. Вони можуть бути описані як процедури або функції. У другому випадку повинне бути певне значення, що повертається функцією (оператор RETURN).

Вивчати можливості мови PL/SQL зручніш за все за допомогою анонімних блоків. А потім можна буде розглянути особливості реалізації блоків у вигляді процедур і функцій.

Кожний закінчений набір операторів PL/SQL повинен бути організований у блок. Мінімум, що для цього потрібн, – ключові слова BEGIN і END, відповідно, відкриваючий і закриваючий блок:

BEGIN

-- оператори блоку

END;

Оператори BEGIN і END визначають границі тіла блоку, їхнє завдання обов'язково, наприклад:

SQL> SET SERVEROUTPUT ON SQL> BEGIN

2DBMS_OUTPUT.enable;

3DBMS_OUTPUT.put_line('Вивчайте Java і Oracle!');

4 END; 5/

Вивчайте Java і Oracle!

PL/SQL procedure successfully completed.

Якщо в блоці повинні бути визначені які-небудь локальні об'єкти, наприклад змінні, то повинна бути також задана секція DECLARE:

DECLARE

--оголошення об'єктів блоку

BEGIN

--дії блоку

END;

Секція DECLARE не обов'язкова, і використовується для оголошення об'єктів на рівні блоків і вбудованих у нього внутрішніх блоків, наприклад, локальних змінних, як показано в наступному прикладі:

SQL> DECLARE

2П_число NUMBER(5,1);

3BEGIN

4П_число := 367.4;

5DBMS_OUTPUT.enable;

6DBMS_OUTPUT.put_line('Число дорівнює = ' || П_число);

7END;

8/

Число дорівнює = 367,4

PL/SQL procedure successfully completed.

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

DECLARE

--оголошення об'єктів блоку

BEGIN

--дії блоку

EXCEPTION

-- обробка особливих ситуацій блоку

END;

У наступному прикладі показано, як секція обробки особливих ситуацій використовується для перехоплення помилки переповнення розрядної сітки числового стовпця таблиці й завдання дій, які будуть виконуються у випадку виникнення виняткової ситуації. Секція обробки виключень необов'язкова й ставиться безпосередньо перед оператором END – ознакою кінця блоку. (Питання з обробки особливих ситуацій будуть докладно розглянуті пізніше).

SQL> DECLARE

2П_число NUMBER(38);

3BEGIN

4П_число := 10E33;

5INSERT INTO служ (оклад) VALUES (П_число);

6EXCEPTION

7WHEN OTHERS THEN

8DBMS_OUTPUT.enable;

9DBMS_OUTPUT.put_line('Занадто велике число');

10 END; 11/

Занадто велике число

PL/SQL procedure successfully completed.

Блоки можуть бути вкладені один у одного скрізь, де дозволене завдання команди (хоча не рекомендується робити блоки усередині циклів).

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

DECLARE

--оголошення об'єктів зовнішнього блоку

BEGIN

--дії в зовнішньому, головному блоці

...

DECLARE

-- оголошення об'єктів зовнішнього блоку

BEGIN

--дії у внутрішньому блоці

EXCEPTION

--обробка помилок внутрішнього блоку

END;

...

EXCEPTION

WHEN <ім'я_виключення_1> THEN

-- обробка особливих ситуацій у їхньому власному блоці <оператори>;

...

WHEN <ім'я_виключення_N> THEN

BEGIN

--обробка особливих ситуацій

EXCEPTION

--обробка помилок самого оброблювача виключень

END;

END;

2. Використання команд DML в PL/SQL

Команди DML INSERT, UPDATE і DELETE можуть бути задані в PL/ SQL-Блоках без будь-яких обмежень. Блокування таблиць або рядків, що виникають у результаті виконання цих команд, відміняються по закінченні транзакції звичайним шляхом, наприклад:

SQL> SET SERVEROUTPUT ON SQL> DECLARE

2sal NUMBER;

3empno NUMBER(4) := 7788;

4BEGIN

5DBMS_OUTPUT.enable;

6SELECT оклад INTO sal FROM служ WHERE служ_номер = 7788;

7DBMS_OUTPUT.put_line('Оклад до відновлення: ' || sal);

8UPDATE служ SET оклад = 9000

9WHERE служ_номер = empno;

10SELECT оклад INTO sal FROM служ WHERE служ_номер = 7788;

11DBMS_OUTPUT.put_line('Оклад після відновлення: ' || sal);

12ROLLBACK;

13SELECT оклад INTO sal FROM служ WHERE служ_номер = 7788;

14DBMS_OUTPUT.put_line('Оклад після відкоту: ' || sal);

15END;

16/

Оклад до відновлення: 3000 Оклад після відновлення: 9000 Оклад після відкоту: 3000

Процедура PL/SQL успішно завершена.

У розглянутому прикладі виробляється виправлення командою UPDATE рядків зі значенням 7788 стовпця «служ_номер». Успіх виконання команди не залежить від того, скільки рядків задовольняють критерію пошуку – будь-то вся таблиця або один рядок.

3. Керування транзакціями

УСУБД Oracle транзакція починається з першої команди, що випливає за командою COMMIT або ROLLBACK (тобто відразу після закриття попередньої транзакції), і закінчується наступним завданням команди COMMIT або ROLLBACK. Ці дії можуть бути задані в самому PL/SQL блоці або як результат подій у зовнішнім середовищі (наприклад, закінчення сеансу роботи в середовищі SQL*Plus автоматично закриває транзакцію).

В PL/SQL доступні наступні команди керування транзакціями, хоча зовнішнє середовище, у якій використовується PL/SQL, може накладати деякі обмеження на їхнє завдання:

COMMIT [WORK];

ROLLBACK [TO <контрольна_крапка>]; SAVEPOINT <ім'я_контрольної_крапки>;

SET TRANSACTION <рівень_ізоляції_транзакцій>;

Команди зовнішнього блокування, що діють до закінчення транзакції, можуть також бути включені в блок (маються на увазі команди LOCK

TABLE, SELECT ... FOR UPDATE).

Унаступному прикладі команда ROLLBACK задається в зовнішнє стосовно PL/SQL-блоку середовище (це може бути, наприклад, SQL*Plus),

аконтрольна точка, до якої виробляється відкат транзакції, стоїть