Юрчишин В.М., Клим Б.В. і інші. Організація баз даних

Подождите немного. Документ загружается.

помилку. Якщо неявний курсор взагалі не повертає рядків, то

збуджується інша виняткова ситуація NO_DATA_FOUND і теж

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

Нижче наведено три приклади вибірки даних з таблиці Pidpr за

допомогою неявного курсора. Програма використовує дві змінні:

Kod і Naz, призначені для запам’ятовування відповідних їм значень

полів Kodpp і Nazpp таблиці Pidpr. У першому прикладі запит SELECT

використовує один запис таблиці при Kodpp=2, тому помилки нема:

SQL> DECLARE

2 Kod NUMBER;

3 Naz CHAR(50);

4 BEGIN

5 SELECT Kodpp, Nazpp INTO Kod, Naz

6 FROM Pidpr WHERE Kodpp=2;

7 DBMS_OUTPUT.PUT_LINE(TO_CHAR(Kod)||’ ’||Naz);

8 END;

9 /

Прикарпаттрансгаз

У поданому нижче другому прикладі дані за умовою Kodpp=8

відсутні, тому виникає помилка NO_DATA_FOUND:

5 SELECT Kodpp, Nazpp INTO Kod, Naz

6 FROM Pidpr WHERE Kodpp=8;

DECLARE

ERROR at line 1:

ORA-01403: no data found

ORA-06512: at line 5

Третій приклад провокує помилку TOO_MANY_ROWS через

повернення більше, ніж одного запису.

5 SELECT Kodpp, Nazpp INTO Kod, Naz

6 FROM Pidpr WHERE Kodpp IN(1,2);

DECLARE

ERROR at line 1:

ORA-01422: exact fetch returns more than requested number of rows

ORA-06512: at line 5

121

Курсорний цикл FOR дозволяє використовувати курсор у межах

циклу. Як правило, він використовується тоді, коли обробляється

кожен рядок запиту. В найпростішому варіанті неявний курсорний

цикл FOR являє собою запит, вбудований в опис циклу. Наведемо

приклад його використання для видачі всіх записів таблиці Pidpr.

SQL> DECLARE

2 BEGIN

3 FOR rec IN (SELECT Kodpp, Nazpp FROM Pidpr)LOOP

4 DBMS_OUTPUT.PUT_LINE(TO_CHAR(rec.Kodpp)||' '||rec.Nazpp);

5 END LOOP;

6 END;

7 /

1 Харкiвтрансгаз

2 Прикарпаттрансгаз

3 Київтрансгаз

4 Львiвтрансгаз

5 Експорттрансгаз

6 Гадячгазпром

Явний курсор оголошується в секції DECLARE за допомогою

ключового слова CURSOR:

CURSOR ім’я_курсора IS запит;

Нехай явний курсор буде мати ім’я Curs. Тоді приклад його

використання для рішення вищенаведеної задачі матиме такий

вигляд:

SQL> DECLARE

2 CURSOR Curs IS SELECT Kodpp, Nazpp FROM Pidpr;

3 BEGIN

4 FOR rec IN Curs LOOP

5 DBMS_OUTPUT.PUT_LINE(TO_CHAR(rec.Kodpp)||' '||rec.Nazpp);

6 END LOOP;

7 END;

8 /

Результати виконання програми цього прикладу будуть такими

ж, як і попереднього.

При використанні явного курсора (наприклад, для одержання

вибірки з таблиці) його необхідно відкрити командою:

OPEN ім’я_курсора;

122

Вибірка даних з курсора в набір змінних відповідних типів

може бути виконана командою FETCH у такий спосіб:

FETCH ім’я_курсора INTO список_імен_змінних;

Найпростішим способом визначення набору змінних, у які

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

RECORD, основаної на типі курсора. Тоді поля цієї змінної

автоматично змінюються, якщо змінюється вираз SELECT.

У PL/SQL для курсорів передбачені спеціальні методи

%NOTFOUND або %FOUND, які приймають протилежні булеві

значення. Метод %NOTFOUND повертає значення TRUE, якщо

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

відкриття курсора до першої команди FETCH методи %FOUND і

%NOTFOUND приймають невизначене значення (UNKNOWN). Це

треба мати на увазі при організації циклу з використанням

оператора WHILE. Якщо, наприклад, застосовується перевірка на

істинність %FOUND, то цикл не буде виконаний жодного разу,

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

Метод %ROWCOUNT повертає число рядків, вибраних після

відкриття курсора.

При оголошенні змінних можна використовувати спеціальні

атрибути %TYPE і %ROWTYPE. Атрибут %TYPE призначений для

визначення типу змінної або константи, які залежать від типу поля

таблиці. Атрибут %ROWTYPE застосовується, як правило, під час

оголошення змінної-запису, яка має таку ж структуру, як і рядок

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

Використання атрибутів %TYPE і %ROWTYPE застерігає можливі

помилки при зміні типів даних полів таблиць або їх структури.

Розглянемо приклад використання курсорів. Складемо

програму для видачі звіту за умовою задачі 4, поставленої в розділі

3.1. Вона матиме такий вигляд:

SQL> declare --+++

2 s number(8,3);

3 kod int;

4 nazgaz char(50);

5 cursor kursgz is select nazpr from Gazpr where kodpr=kod;

6 begin

7 kod:=&input_data;

8 open kursgz;

9 fetch kursgz into nazgaz;

123

10 dbms_output.put_line(' Сумарний об’єм транспортованого газу ');

11 dbms_output.put_line(' за поточний рік станом на ' || to_char(sysdate));

12 dbms_output.put_line(' Газопровід: '|| nazgaz);

13 dbms_output.put_line('.');

14 dbms_output.put_line(' Назва підприємства Сумарний об’єм газу');

15 dbms_output.put_line('________________________________________');

16 for kurspp in (select *from pidpr) loop

17 s:=0;

18 for kursob in (select *from oblik) loop

19 if (kurspp.kodpp=kursob.kodpp and kursob.kodpr=kod

20 and trunc(kursob.data,'yy')=trunc(sysdate,'yy')) then s:=s+kursob.kil;

21 end if;

22 end loop;

23 if(s>0) then dbms_output.put_line(to_char(kurspp.nazpp)||' '||to_char(s));

24 end if;

25 end loop;

26 end;

27 /

Enter value for input_data: 2

Сумарний об’єм транспортованого газу

за поточний рік станом на 09.11.2006

Газопровід: Україна

Назва підприємства Сумарний об’єм газу

________________________________________

Львівтрансгаз 2

Експорттрансгаз 1

У блоці declare цієї програми оголошені такі змінні:

s – сума значень поля kil таблиці Oblik;

kod – код газопроводу, значення якого буде введено під час

виконання програми;

nazgaz – назва газопроводу, код якого буде введено;

kursgz – курсорного типу, вона міститиме лише одне значення

поля nazpr: назву газопроводу таблиці Gazpr того запису, де

його код буде дорівнювати значенню змінної kod.

У виконавчому блоці, який починається зі слова begin (рядок 6),

передбачено ввід під час виконання програми значення змінної kod.

Зауважимо, що в цьому прикладі форма відсутня, тому код

газопроводу не може бути з неї взятий. У 8-му рядку програми

відкривається курсор kursgz за допомогою оператора open, а нижче

оператор fetch присвоює значення змінній nazgaz. Рядки 10 – 15

служать для формування та виводу заголовка звіту.

124

Структура цієї програми практично збігається з показаною на

рисунку 3.21. Вона теж має два вкладені цикли з розгалуженням у

внутрішньому циклі. Обидва цикли являють собю курсорні цикли

for. Зовнішний цикл (рядки 16 – 25) служить для перебору всіх назв

підприємств, внутрішний (рядки 18 – 22) – записів таблиці Oblik та

накопичення суми s за заданою умовою Зауважимо, що ні курсор

kurspp – вибірка з таблиці pidpr, ні kursob – з таблиці Oblik не

оголошені, бо вони є параметрами циклів. Поля обох курсорів

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

елемента курсора (до його поля) перед назвою поля ставиться ім’я

курсора через точку.

Зміст одержаного тут звіту той же, що й

Ob_sum

(рисунок 3.13).

4.6.4 Обробка виняткових ситуацій

Обробка виняткових ситуацій є хорошим тоном

програмування. Виконання будь-якої програми не повинно

завершуватися аварійно. Програміст повинен передбачити всі

можливі неполадки і прийняти всі можливі міри для того, щоб їх

усунути або обійти. При цьому, звичайно, потрібно видати

користувачу відповідне повідомлення про вид помилки та як вона

була оброблена.

Таблиця 4.3. Перелік окремих виняткових ситуацій

Виняткова

ситуація

Опис виняткової ситуації

LOGIN_DENIED

Неуспішне підключення до сервера (наприклад,

введено помилковий пароль)

NOT_LOGGED_ON

Спроба виконати дію без підключення до

сервера Oracle

INVALID_CURSOR

Посилання на неприпустимий курсор чи

неприпустима операція з курсором

NO_DATA_FOUND

Не знайдені дані, які відповідають оператору

SELECT INTO

OTHERS

Універсальна обробка помилок

TOO_MANY_ROWS

Оператор SELECT INTO повертає більше ніж один

рядок

VALUE_ERROR

Арифметична помилка

125

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

об’єктивних причин або бути спровокованою програмістом з

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

якої виняткової ситуації відбувається автоматична передача

керування в фрагмент блоку EXCEPTION програми. У цьому блоці

можна запрограмувати як обробку конкретної помилки, так задіяти

механізм універсальної обробки помилок за допомогою ключового

слова OTHERS. Якщо блок EXCEPTION відсутній, то відбувається

аварійний останов програми, що, як уже було вище сказано,

недопустимо. Перелік і опис окремих виняткових ситуацій PL/SQL

показані в таблиці 4.3.

Зауважимо, що видача повідомлень про помилки – мінімальна

їх обробка, яка, хоча й попереджує аварійний останов програми,

проте не завжди дозволяє продовжувати її виконання.

Розглянемо такий приклад: спростимо програму, яка видала

звіт у попередньому розділі 4.6.3, для цього замінимо її

внутрішний цикл (оператори 17 – 22) таким одним запитом:

17 select sum(oblik.kil) as suma into s

18 from oblik

19 where kurspp.kodpp=kursob.kodpp

20 and kursob.kodpr=kod

21 and trunc(oblik.data, 'yy')=trunc(sysdate, 'yy')

22 group by oblik.kodpp;

Така програма буде працювати, але лише в тому випадку, коли

результати виконання запиту не будуть порожніми (при s>0), тобто

таблиця Oblik буде мати дані про всі підприємства, які

задовільняють умову запиту. У нашому прикладі, і на подібні

випадки слід розраховувати в реальних промислових умовах,

маємо потрібні дані лише для двох підприємств: Львівтрансгаз і

Експорттрансгаз, це видно з виданого програмою звіту. Тому в тих

випадках, коли s=0, програма видасть помилку NO_DATA_FOUND і

зупиниться.

Можна запропонувати обробку цієї помилки за допомогою,

наприклад, такого блока EXCEPTION програми:

26 exception

27 when no_data_found then

28 dbms_output.put_line(‘Немає даних’);

126

Але це нічого не дасть, перший же випадок, коли s=0,

спричинить видачу напису Немає даних і зупинку програми,

незважаючи на те, що в таблиці Oblik є ще й інші дані, які

задовільняють умови запиту. Спроба продовжити її виконання і

повернутися назад всередину циклу, наприклад, за допомогою

оператора GOTO, не буде успішною, бо ввійти в тіло циклу можна

лише через його заголовок, тобто виконати цикл спочатку.

Для вирішення проблеми застосуємо функцію, тіло якої буде

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

циклу. Тоді помилка буде зупиняти лише цю функцію, а головна

програма продовжить своє виконання. Нижче показаний варіант

такої програми.

create or replace function suma(kodp in int, kodg in int) -- Функція suma

return number

s number (8,3);

begin

select sum(oblik.kil) into s from oblik

where oblik.kodpr=kodg and oblik.kodpp=kodp

and trunc(oblik.data,'yy')=trunc(sysdate, 'yy')

group by oblik.kodpp;

return s;

exception

when no_data_found then s:=0;

return s;

end;

declare -- Анонімний блок, який викликає функцію suma

s number(8,3);

kod int;

nazgaz char(50);

cursor kursgz is select nazpr from Gazpr where kodpr=kod;

begin

kod:=&input_data;

open kursgz;

fetch kursgz into nazgaz;

dbms_output.put_line('. Zvit');

dbms_output.put_line('pro sumu');

dbms_output.put_line('ctanom na '||to_char(sysdate));

dbms_output.put_line('gazopr '||nazgaz);

dbms_output.put_line('.');

dbms_output.put_line('Pidpr Kilk');

dbms_output.put_line('________________________________________');

for kurspp in (select *from pidpr) loop

s:=suma(kurspp.kodpp, kod);

if(s>0) then dbms_output.put_line(to_char(kurspp.nazpp)||' '||to_char(s));

127

end if;

end loop;

end;

Функція suma має такі два вхідні параметри: kodp і kodg, вони

служать для взяття з головної програми значень відповідно коду

підприємства і коду газопроводу. Решту даних беруться з запиту,

який відкривається в її тілі.

Ця функція повертає в головну програму значення змінної s

двох видів: число sum(oblik.kil) – сума в полі kil таблиці Oblik або 0

після виходу з блоку обробки виключної ситуації, якщо вибірка

виявиться поржньою.

Зрозуміло, що ця програма має навчальний характер і може

бути вдосконалена. Наприклад, у ній слід передбачити видачу

відповідного повідомлення в тому випадку, якщо хоч якась із

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

З метою перевірки працездатності програми під час її

виготовлення та її реакцію на помилку можна оголосити потрібну

виняткову ситуацію у блоці DECLARE за допомогою оператора:

ім’я_виняткової_ситуації EXCEPTION;

Оператор генерації виняткової ситуації в програмі має вигляд:

RAISE ім’я_виняткової_ситуації;

4.6.5 Тригери

Тригер – це процедура, яка автоматично запускається при

виникненні подій, пов’язаних з виконанням операцій вставки,

вилучення чи модифікації даних таблиці. Подія, яка керує

запуском тригера, описується у вигляді логічних умов. Тобто

кожній з цих операцій передує перевірка умов. Коли виникає подія,

яка відповідає умовам тригера, сервер Огасlе автоматично запускає

тригер.

Тригери особливо корисні там, де необхідно забезпечити

складний контроль захисту даних або спеціальний аудит. Вони

можуть також здійснювати контроль цілісності даних, якщо

потрібна більш складна перевірка, ніж та, яку можуть забезпечити

декларативні обмеження цілісності, наприклад, CONSTRAINT.

128

Для того, щоб створити тригер, необхідно мати системний

привілей CREATE TRIGGER. Для створення тригера в схемі,

відмінної від поточної схеми користувача, потрібен системний

привілей CREATE ANY TRIGGER.

Оператор визначення тригера має такий синтаксис:

CREATE [OR REPLACE ] TRIGGER [ім'я_схеми.] ім'я_тригера

BEFORE або AFTER

INSERT або DELETE або UPDATE [OF перелік_імен_полів]

[OR INSERT або DELETE або UPDATE [OF перелік_імен_полів]

ON [ім'я_схеми.]ім'я_таблиці або ім'я_представлення

[FOR EACH ROW] [WHEN умова]

тіло_програми_мовою_PL/SQL;

Ключове слово OR REPLACE означає безумовне заміщення

старого тексту тригера. Якщо ключове слово OR REPLACE не

зазначено і тригер уже визначений, то заміщення старого коду

тригера не відбувається і повертається повідомлення про помилку.

Ключові слова BEFORE чи AFTER означають виконання коду

тригера до або після операторів маніпулювання даними, які

ініціювали запуск тригера. Наприклад, якщо тригер застосовується

для перевірки повноважень користувача на право виконання

операції, то, звичайно, потрібно запускати тригер до виконання

операції (із ключовим словом BEFORE). Якщо тригер застосовують

для аудиту, то розумно його запускати після виконання операції (із

ключовим словом AFTER).

Слова INSERT, DELETE або UPDATE визначають конкретний

оператор, який запускає тригер. Необов’язкове ключове слово OR

приєднує додатковий оператор, який теж запускає тригер. В умові

запуску тригера повинен бути обов’язково вказаний хоча б один

оператор із трьох. Якщо перелік операторів, які запускають тригер,

включає оператор UPDATE, то в умовах його спрацювання можна

задавати конкретні назви полів.

Ключове слово ON задає ім’я таблиці або представлення,

асоційовані з тригером.

Необов’язкове ключове слово FOR EACH ROW визначає тригер,

як рядковий. Код тригера може бути асоційований або з операцією

над таблицею в цілому, або з рядком, над яким виконується

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

рядкові. Як правило, операторні тригери використовуються для

перевірки правил, які оперують таблицею в цілому, а рядкові – для

перевірки обмежень цілісності під час вставки рядків.

129

Умова запуску рядкового тригера може додатково

уточнюватися логічною умовою. Необов’язкове ключове слово

WHEN задає додаткову логічну умову, яка звужує область подій,

при настанні яких тригер запускається.

Іноді виникає необхідність відключення тригерів, наприклад,

при проведенні масових операцій над даними. Для цього служить

оператор ALTER TRIGGER, який може переключати режим,

дозволяючи чи забороняючи запуск тригера, за допомогою

параметра DISABLE або ENABLE.

Наведемо деякі відомості про обробку виняткових ситуацій у

Oracle. Для аварійного завершення програм PL/SQL застосовується

процедура RAISE_APPLICATION_ERROR. За її допомогою можна

обробити до 1000 зумовлених користувачем помилок з номерами в

діапазоні від -20000 до -20999. Виклик процедури

RAISE_APPLICATION_ERROR спричиняє генерацію виняткової

ситуації і завершення виконання програми, яка викликала

процедуру. При цьому в середовище, яке викликало програму,

повертається номер помилки і текстове повідомлення про її тип.

Розглянемо приклад, подібний до вже розгляненого нами в

розділі 4.5.6. Нехай кількість ниток газопроводу таблиці Gazpr не

може бути більшою за 15 (Kilnyt<16). Спроба вставки даних у

таблицю, які не задовольняють умову тригера, наприклад при Kilnyt

=100, буде відхилена системою.

Нижче подано протокол створення тригера TrigKilnyt. Він має

спрацьовувати під час вводу заздалегідь невірних даних у таблицю

Gazpr. Передбачена стандартна обробка помилки з номером 20002 і

видача діагностичного повідомлення про те, що число не може

мати такого значення. Тригер спрацьовує під час спроби занесення

в таблицю Gazpr значення поля Kilnyt, більшого за 15, і не

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

дані в таблицю не заносяться, бо він має ключове слово BEFORE.

Звернемо увагу на змінну :new.Kilnyt, вона містить значення поля

Kilnyt, яке буде введено і яке контролюється.

SQL> CREATE OR REPLACE TRIGGER TrigKilnyt

2 BEFORE INSERT ON Gazpr FOR EACH ROW

3 BEGIN

4 IF :new.Kilnyt>15 THEN

5 RAISE_APPLICATION_ERROR(-20002,'Число ниток не може бути >15');

6 END IF;

7 END;

130