Вибір інформації з баз даних

Вибір інформації з однієї або декількох таблиць БД здійснюється за допомогою оператора SELECT, спрощений синтаксис має такий вигляд:

SELECT [DISTINCT] {* | {[tablename.] name | вираз } [псевдонім] [, {[tablename.] name | вираз} [псевдонім]]...} FROM {ім'я_таблиці | имя_представления | підзапит} [псевдонім] [, {ім'я_таблиці | имя_представления | підзапит} [псевдонім]...] [WHERE умова] [GROUP BY выражение1 [, выражение2... ] [HAVING умова] ] [{UNION | UNION ALL | INTERSECT | MINUS} оператор SELECT ] [ORDER BY выражение1 [ASC | DESC] [, выражение2 [ASC | DESC]]...]; Побудова списку вибору. В операторі SELECT при формуванні списку вибору, що складається з імен стовпців і виразів, для побудови виразів можуть використовуватися імена стовпців таблиць і подань, літерали, функції, що з'єднуються знаками арифметичних дій. Якщо необхідно вказати імена стовпців, які мають однакові ідентифікатори в двох різних таблицях, то до цих імен через точку необхідно приписати ім'я таблиці. При необхідності складного висловом можна присвоїти псевдонім, який буде використаний в подальшому тексті оператора. Всі імена таблиць, стовпців яких використовувалися для побудови виразів у списку

вибору, обов'язково повинні бути перелічені в тексті оператора після ключового слова FROM. Упорядкування рядків. Рядки, що повертаються SELECT-запиту, можуть бути впорядковані за зростанням або спаданням значень певних виразів. В якості виразу може використовуватися ім'я стовпця. Для реалізації цієї процедури використовується конструкція ORDER BY, в якій вказується перелік, що складається з одного або кількох виразів, розділених комами, за значеннями яких здійснюється впорядкування. За замовчуванням видобувні рядки сортуються за зростанням значень, зазначених у переліку виразів. Для того щоб задати інший варіант упорядкування рядків, після кожного виразу або групи виразів у переліку вказується або ASC (за зростанням), або DESC (за спаданням значень), а перерахування цих груп здійснюється через кому. Умова вибору рядків. В конструкції WHERE при побудові умови, яким повинні задовольняти обрані рядки, можна використовувати весь наявний у мові SQL набір операцій порівняння і логічних операцій. Для того щоб задати конкретне значення в умови, можна скористатися змінної підстановки, яка дозволяє ввести потрібне значення в момент виконання запиту. Змінна підстановки визначається наявністю символу & початку її імені. Підпорядкований. Підпорядкований, або вкладені запити, застосовуються для повернення групи рядків або безлічі значень, які будуть використані батьківським запитом. В залежності від форми побудови підзапит може виконуватися або один раз для батьківського запиту, або один раз для кожного рядка, витягнутої батьківським запитом. В останньому випадку такий підзапит називається корельованим підзапит. Характерною ознакою коррелированного подзапроса є наявність в його фразі WHERE посилань на стовпці батьківського запиту. Об'єднання рядків. В багатокомпонентному запиті можна певним чином об'єднати в єдине ціле групи рядків, видобувні окремо виконуваними запитами. Щоб задати порядок об'єднання, використовується одне з ключових слів конструкції UNION | UNION ALL | INTERSECT | MINUS.

П р і м е р и 1. Вибрати з таблиці BOOKS всю інформацію про книгах: SELECT * FROM BOOKS;

2. Вибрати з таблиці BOOKS всю інформацію про перших трьох книгах: SELECT * FROM BOOKS WHERE ROWNUM < 4; 3. Вибрати з таблиці BOOKS інформацію про книгах із зазначенням прізвища автора, назви і ціни і впорядкувати її за зростанням значень стовпця AUTHOR і спаданням значень стовпця PRICE; прізвища авторів і назви вивести великими літерами: SELECT UPPER(AUTHOR), UPPER(TITLE), PRICE FROM BOOKS ORDER BY AUTHOR ASC, PRICE DESC; 4. Вибрати з таблиці BOOKS інформацію (прізвище автора, назва) про книжки в жанрі «Роман»; SELECT AUTHOR, TITLE FROM BOOKS WHERE GENRE = 'Роман'; 5. Вибрати з таблиці BOOKS інформацію (прізвище автора, назва, жанр) про книжки в жанрі не «Роман»; а) SELECT AUTHOR, TITLE, GENRE FROM BOOKS WHERE GENRE! = 'Роман'; б) SELECT AUTHOR, TITLE, GENRE FROM BOOKS WHERE GENRE < > 'Роман'; в) SELECT AUTHOR, TITLE, GENRE FROM BOOKS MINUS SELECT AUTHOR, TITLE, GENRE FROM BOOKS WHERE GENRE = 'Роман'; 6. Вибрати з таблиці BOOKS інформацію (прізвище автора, назва, ціна) про книги вартістю більше 260 і менше 1000; SELECT AUTHOR, TITLE, PRICE FROM BOOKS WHERE PRICE BETWEEN 260 AND 1000; 7. Вибрати з таблиці BOOKS інформацію (прізвище автора, назва, жанр) про книжки у жанрах «Роман» і «Детектив»: а) SELECT AUTHOR, TITLE, GENRE FROM BOOKS WHERE GENRE = 'Роман' OR GENRE = 'Детектив'; б) SELECT AUTHOR, TITLE, GENRE FROM BOOKS WHERE GENRE IN ('Роман', 'Детектив'); в) SELECT AUTHOR, TITLE, GENRE FROM BOOKS WHERE GENRE = 'Роман' 144

UNION SELECT AUTHOR, TITLE, GENRE FROM BOOKS WHERE GENRE = 'Детектив'; 8. Вибрати з таблиці BOOKS інформацію (прізвище автора, назва, жанр) про книги жанрів «Роман», «Детектив» видавництва «Аст»: а) SELECT AUTHOR, TITLE, GENRE FROM BOOKS WHERE (GENRE = 'Роман' OR GENRE = 'Детектив') AND PUBLISH_HOUSE = 'Аст'; б) SELECT AUTHOR, TITLE, GENRE FROM BOOKS WHERE GENRE IN ('Роман', 'Детектив') AND PUBLISH_HOUSE = 'Аст'; 9. Вибрати з таблиці BOOKS інформацію (прізвище автора, назва книги, назва яких починається зі слова «Загибель»: а) SELECT AUTHOR, TITLE FROM BOOKS WHERE TITLE LIKE 'Загибель%'; б) SELECT AUTHOR, TITLE FROM BOOKS WHERE SUBSTR(TITLE, 1, 6) = 'Загибель'; 10. Вибрати з таблиці BOOKS інформацію (прізвище автора, назва, жанр) про книги, що відносяться до зазначеного жанру. Необхідне значення задати, використовуючи змінну підстановки: SELECT AUTHOR, TITLE, GENRE FROM BOOKS WHERE GENRE = '& GANR' ORDER BY AUTHOR; У відповідь на запит, що видається системою, набрати одне із значень стовпця GENRE (Роман, Фантастика, Детектив). 11. Вибрати з таблиці BOOKS інформацію про книгах, наявних в інших видавництвах, того ж жанру, що й у видавництві «Нова»: а) SELECT * FROM BOOKS WHERE GENRE IN (SELECT DISTINCT GENRE FROM BOOKS WHERE PUBLISH_HOUSE = 'Нова') AND PUBLISH_HOUSE < > 'Нова'; б) SELECT * FROM BOOKS WHERE GENRE = ANY (SELECT DISTINCT GENRE FROM BOOKS WHERE PUBLISH_HOUSE = 'Нова') AND PUBLISH_HOUSE < > 'Нова'; 12. Вибрати з таблиці BOOKS список прізвищ авторів, чиї книги є в кожному з видавництв: SELECT AUTHOR FROM BOOKS WHERE PUBLISH_HOUSE = 'Аст' INTERSECT

SELECT AUTHOR FROM BOOKS WHERE PUBLISH_HOUSE = 'Нова'; Групування рядків. Рядки, що повертаються SELECT-запиту, можуть бути об'єднані в групи на основі значень певного виразу для кожного рядка. Прикладом такого групування може бути об'єднання в групи книг одного жанру, інформація про яких є в таблиці BOOKS. Так як в таблиці присутні книги трьох жанрів, то будуть сформовані лише три групи рядків. Застосувавши до кожної групи функцію SUM стовпця, який містить значення ціни, можна одержати сумарну величину вартості книг по кожному жанру. Для здійснення групування використовується конструкція GROUP BY оператора SELECT, в якій вказується перелік, що складається з одного або кількох виразів, розділених комами, за значеннями яких здійснюється групування. Якщо оператор SELECT містить пункт GROUP BY, то список вилучених значень обмежений. Він може містити константи, групові функції, функцію SYSDATE і вирази, ідентичні зазначеним у пункті GROUP BY. На формування результуючих рядків можуть бути накладені певні умови. Щоб поставити таку умову, використовується ключове слово HAVING. П р і м е р и 1. Вибрати з таблиці BOOKS інформацію про кількість різних жанрів: SELECT COUNT(DISTINCT GENRE) FROM BOOKS; 2. Вибрати з таблиці BOOKS інформацію про кількість, сумарної вартості і максимальної вартості наявних книг: SELECT COUNT(CODE_BOOK), SUM(PRICE), MAX(PRICE) FROM BOOKS; 3. Вибрати з таблиці BOOKS по кожному видавництву інформацію про кількості і сумарної вартості виданих ним книг, згрупувавши її за жанрами: SELECT PUBLISH_HOUSE, GENRE, COUNT(CODE_BOOK), SUM(PRICE) FROM BOOKS GROUP BY PUBLISH_HOUSE, GENRE; 4. Вибрати з таблиці BOOKS інформацію про кількості та середньої вартості (округливши значення середньої вартості до двох знаків після коми) книг в тих жанрах, де кількість різних назв книг не менше 2:

SELECT GENRE, COUNT(DISTINCT TITLE), ROUND(AVG(PRICE), 2) FROM BOOKS GROUP BY GENRE HAVING COUNT(DISTINCT TITLE) > = 2; 5. Вибрати з таблиці BOOKS інформацію про мінімальну вартість книг в жанрі «Роман»: SELECT MIN(PRICE) FROM BOOKS WHERE GENRE = 'Роман'; 6. Вибрати з таблиці BOOKS інформацію (прізвище автора, назва, ціна) про найдешевшою книзі в жанрі «Роман»: SELECT AUTHOR, TITLE, PRICE FROM BOOKS WHERE PRICE = (SELECT MIN(PRICE) FROM BOOKS WHERE GENRE = 'Роман') AND GENRE = 'Роман'; 7. Вибрати з таблиці BOOKS інформацію (прізвище автора, назва, жанр, ціна) про книги, що мають максимальну вартість у своєму жанрі: а) SELECT AUTHOR, TITLE, GENRE, PRICE FROM BOOKS WHERE PRICE, GENRE) IN (SELECT MAX(PRICE), GENRE FROM BOOKS GROUP BY GENRE); б) SELECT AUTHOR, TITLE, BOOKS.GENRE, PRICE FROM BOOKS, (SELECT GENRE, MAX(PRICE) МАКС FROM BOOKS GROUP BY GENRE) P1 WHERE PRICE = МАКС AND BOOKS.GENRE = P1.GENRE; в) SELECT AUTHOR, TITLE, GENRE, PRICE FROM BOOKS P1 WHERE PRICE = (SELECT MAX(PRICE) FROM BOOKS WHERE BOOKS.GENRE = P1.GENRE); Третій запит містить коррелированный підзапит. Оскільки в своєму умови підзапит містить посилання на стовпець батьківського запиту, він буде виконуватися один раз для кожного рядка, витягуваної батьківським запитом. У першому і другому варіантах підзапит не є корельованим, він виконується тільки один раз для батьківського запиту. 8. Вибрати з таблиці BOOKS інформацію (прізвище автора, назва, ціна) про книги вартістю більше середньої вартості книг: SELECT AUTHOR, TITLE, PRICE FROM BOOKS WHERE PRICE > (SELECT AVG(PRICE) FROM BOOKS);

9. Вибрати з таблиці BOOKS список жанрів, у яких є найбільша кількість різних книг, із зазначенням кількості книг: SELECT GENRE, COUNT(DISTINCT TITLE) FROM BOOKS GROUP BY GENRE HAVING COUNT(DISTINCT TITLE) = (SELECT MAX(COUNT(DISTINCT TITLE)) FROM BOOKS GROUP BY GENRE); 10. Вибрати з таблиці BOOKS інформацію про книги (прізвище автора, назва), що належать до жанрів, у яких є найбільша кількість різних книг: SELECT AUTHOR, TITLE FROM BOOKS WHERE GENRE IN (SELECT GENRE FROM BOOKS GROUP BY GENRE HAVING COUNT(DISTINCT TITLE) = (SELECT MAX(COUNT(DISTINCT TITLE)) FROM BOOKS GROUP BY GENRE)); Вибір інформації з декількох таблиць (з'єднання). З'єднання – це SELECT-запит, що вибирає рядки з двох або більше таблиць. При цьому запит може витягувати будь-які стовпці з будь-якої таблиці. Якщо хоча б дві з цих таблиць мають однаково названі стовпці, то імена таких стовпців повинні уточнюватися іменами таблиць, записати перед іменами стовпців через точку. Більшість SELECT-запитів з сполуками містять умови, в яких порівнюються значення стовпців з різних таблиць. Такі умови називаються умовами з'єднання. Эквисоединение – це з'єднання з використанням в умови з'єднання операції рівності. Таким чином, эквисоединение витягує рядки з еквівалентними значеннями у зазначених звітах. Декартів добуток таблиць будується за відсутності в запиті умови з'єднання. У цьому випадку до кожної рядку першої таблиці приписується кожна рядок другої таблиці. Рефлексивне з'єднання сполучає таблицю саму з собою. При цьому таблиця з'являється в списку FROM двічі і повинна мати додаткове ім'я (псевдонім), щоб можна було однозначно ідентифікувати стовпці умови з'єднання. Зовнішнє з'єднання видає всі рядки, які задовольняють умові з'єднання, а також рядки однієї з таблиць, які не задовольняють умові з'єднання. Щоб записати запит, який виконує зовнішнє з'єднання таблиць A і B і видає всі рядки з таблиці A, застосуємо операцію зовнішнього з'єднання (+) до всіх стовпців із таблиці B в умовах з'єднання. Тоді для всіх рядків з

таблиці A, для яких немає відповідних рядків в таблиці B, система надасть рядок, що містить NULL у всіх виразах в списку стовпців, які містять стовпці таблиці B. П р і м е р и 1. Вибрати з таблиць BOOKS і BOOKS_DELIVERY інформацію про книгах, поставлених продавцям за період з 24.01.2006 за 12.02.2006, вказавши для виведеного значення дати спеціальний формат виводу: SELECT SALESMAN, AUTHOR, TITLE, TO_CHAR(DATE_DELIVERY, 'DD MONTH YYYY') FROM BOOKS, BOOKS_DELIVERY WHERE BOOKS.CODE_BOOK = BOOKS_DELIVERY.CODE_BOOK AND DATE_DELIVERY BETWEEN '24-01-06' AND '12-02-06' ORDER BY SALESMAN, AUTHOR; 2. Вибрати з таблиць BOOKS і BOOKS_DELIVERY за вказаною продавцю перелік видавництв і жанрів наявних у нього книг без повторення; щоб задати прізвище продавця, використовувати змінну підстановки: SELECT DISTINCT SALESMAN, PUBLISH_HOUSE, GENRE FROM BOOKS, BOOKS_DELIVERY WHERE BOOKS_DELIVERY.CODE_BOOK = BOOKS.CODE_BOOK AND SALESMAN = '& SALESMAN' ORDER BY PUBLISH_HOUSE, GENRE; 3. Вибрати з таблиць BOOKS і BOOKS_DELIVERY список продавців, у яких у наявності не менше 10 книг, вказавши дані про загальну кількість і сумарної вартості наявних у них книжок: SELECT SALESMAN, SUM(QUANTITY), SUM(PRICE*QUANTITY) FROM BOOKS, BOOKS_DELIVERY WHERE BOOKS_DELIVERY.CODE_BOOK = BOOKS.CODE_BOOK GROUP BY SALESMAN HAVING SUM(QUANTITY) > = 10; 4. Вибрати з таблиць BOOKS і BOOKS_DELIVERY по кожному видавництву інформацію про загальну кількість та сумарної вартості поставлених ними книг кожному продавцю: SELECT PUBLISH_HOUSE, SALESMAN, COUNT(QUANTITY), SUM(PRICE*QUANTITY) FROM BOOKS, BOOKS_DELIVERY WHERE BOOKS.CODE_BOOK = BOOKS_DELIVERY.CODE_BOOK GROUP BY PUBLISH_HOUSE, SALESMAN;

5. Вибрати з таблиць BOOKS і BOOKS_DELIVERY по кожній книзі, відомості про яку є в таблиці BOOKS, інформацію про кількість продавців, яким вона була поставлена: SELECT TITLE, AUTHOR, COUNT(SALESMAN) FROM BOOKS, BOOKS_DELIVERY WHERE BOOKS_DELIVERY.CODE_BOOK (+)= BOOKS.CODE_BOOK GROUP BY TITLE, AUTHOR; 6. Вибрати з таблиць BOOKS і BOOKS_DELIVERY по кожному продавцю інформацію про відсутніх у них книгах, загальний перелік яких знаходиться в таблиці BOOKS: SELECT SALESMAN, TITLE, PRICE FROM BOOKS, BOOKS_DELIVERY MINUS SELECT SALESMAN, TITLE, PRICE FROM BOOKS, BOOKS_DELIVERY WHERE BOOKS_DELIVERY.CODE_BOOK = BOOKS.CODE_BOOK; 7. Вибрати з таблиці BOOKS інформацію (назва, ціна) про трьох найдешевших книгах; передбачається, що у переліку книг не більше трьох різних книг з мінімальною ціною: SELECT А. TITLE, А. PRICE FROM BOOKS А, BOOKS WHERE А. PRICE > = Ст. PRICE GROUP BY А. TITLE, А. PRICE HAVING COUNT(Ст. TITLE) < = 3 ORDER BY А. TITLE; 8. Вибрати з таблиць BOOKS і BOOKS_DELIVERY інформацію (назва, прізвище автора) про книги, які не були поставлені в магазин для продажу: SELECT TITLE, AUTHOR FROM BOOKS WHERE NOT EXISTS (SELECT * FROM BOOKS_DELIVERY WHERE BOOKS_DELIVERY.CODE_BOOK = BOOKS.CODE_BOOK);

13. ОСНОВИ МОВИ PL/SQL

13.1. АЛФАВІТ Й ЛЕКСЕМИ МОВИ. СТРУКТУРА ПРОГРАМИ PL/SQL – це процедурний, блочно-структурований мова програмування, який є розширенням мови SQL СУБД Oracle. Він

надає ряд можливостей, які дозволяють створити великі багатофункціональні програми для роботи з БД. Алфавіт мови включає наступний набір символів: 1) англійські букви верхнього і нижнього регістрів A..Z, a..z; 2) арабські цифри 0..9; 3) символи + – * / < > =;:., ' ~! @ # $ % ^ & _⎪ () { } [ ]; 4) табуляцію, пробіли, символи повернення каретки. Лексеми (групи символів алфавіту) діляться на ідентифікатори, літерали, роздільники й коментарі. Ідентифікатори мають довжину до тридцяти символів і складаються з прописних і рядкових букв, цифр і знака підкреслення, причому першою повинна бути буква. Допускається, але не рекомендується використовувати спеціальні символи, такі як #, $. Деякі з ідентифікаторів у мові PL/SQL мають спеціальне синтаксичне значення. Такі ідентифікатори називаються зарезервованими і не повинні перевизначатися. Літерали – це явно заданий число, символ, рядок або логічне значення, не представлене ідентифікатором. Літерали поділяються на числові, рядкові й логічні. Числові літерали бувають двох типів: цілі та дійсні. Цілі рядки – це знакові числа без десяткової крапки (6; -14). Дійсні літерали – знакові цілі або дробові числа з десяткової точкою (6.667; -12.0). Допускається запис числових символів в експоненційної формі (1.0 Е–7; 2Е5). Рядкові літерали – це послідовність символів, укладені в одинарні лапки (апострофи). Всі рядкові літерали, за винятком порожній рядок (") має тип CHAR. Якщо у строковому литерале необхідно вказати одинарні лапки, то при записі вона просто подвоюється. Логічні літерали – це визначені значення TRUE, FALSE або NULL. NULL вказує на невідоме значення. Роздільник – це сукупність одного або двох символів, яка має певне значення в PL/SQL. Прості роздільники містять тільки один символ. До них відносяться, наприклад, знаки арифметичних операцій (+, –, *, /), знаки операцій відношення (=, >, <), ознака кінця виразу (;). Складові роздільники містять два символи. До них відносяться, наприклад, оператор присвоювання (: =), оператор конкатенації (||), операція зведення в ступінь (**), початок і кінець мітки (< < > >), оператор діапазону (..), операція відносини нерівно (< >,! =, ~=, ^=),

операція відносини менше або дорівнює (< =), операція відносини більше або дорівнює (> =). Коментарі містять пояснювальний текст і поділяються на однорядкові і багаторядкові. Однорядковий коментар являє собою рядок, що починається з двох символів дефіс (-). У багаторядковому коментарі текст полягає в спеціальні роздільники /* */. Структура програми представляє собою набір блоків PL/SQL, рекурсивно вкладених один в одного. Структура блоку: [< < мітка> > ] [DECLARE розділ оголошень] BEGIN виконуваний розділ [EXEPTION розділ обробки виключень] END[< < мітка> > ]; Обов'язковим повинен бути тільки виконуваний розділ, що містить оператори мови. Існують наступні типи блоків: анонімні, іменовані, тригери та підпрограми (процедури, функції, пакети). Анонімні блоки, на відміну від іменованих, не містять міток. Іменовані і анонімні блоки називаються динамічними блоками.

13.2. ТИПИ ДАНИХ І ОГОЛОШЕННЯ ЗМІННИХ Оголошення змінних здійснюється у розділі оголошень, при цьому крім ідентифікатора змінної повинен бути вказаний її тип. До основних типів даних мови PL/SQL належать скалярні і складові. Серед складових типів найбільший інтерес представляє тип RECORD (запис). Оголошення скалярних типів даних. Серед скалярних типів найбільш поширені числові, символьні, тип DATE і логічний (BOOLEAN) типи даних. Числові типи даних представлені в основному двома типами: BINARY_INTEGER та NUMBER. Всі інші числові типи є підмножинами цих двох типів. Тип змінних BINARY_INTEGER використовується для зберігання цілих знакових чисел в двійковому вигляді. Діапазон цього типу від -2147483647 до 2 147 483 647.

Тип змінних NUMBER використовується для зберігання чисел з фіксованою та плаваючою крапкою в діапазоні від 1Е–130 до 10Е125. Для оголошення чисел з плаваючою точкою можна просто вказати NUMBER. Для оголошення цілого числа вказується NUMBER(точність), а для оголошення числа з фіксованою точкою додатково вказується ще й масштаб, тобто NUMBER(точність, масштаб). Точність являє собою загальну кількість знаків і не перевершує 38 десяткових знаків, масштаб вказує порядок округлення і задається числом від -84 до 127. При позитивному значенні масштабу число округляється до зазначеної кількості цифр, що стоять праворуч від коми; при від'ємному значенні – до вказаної кількості цифр, що стоять ліворуч від десяткової коми. Наприклад: число 123.456 при значенні масштабу, що дорівнює 2, округлюється до 123.46, а при значенні, що дорівнює -2, до 100. До символьним типами даних в основному відносяться типи CHAR і VARCHAR2. Тип CHAR(довжина) використовується для зберігання послідовності символів фіксованої довжини не більше 32 767 байт. Слід мати на увазі, що в мові SQL максимальне значення аналогічного типу одно 2000 байт, тому не всі дані цього типу можна вставляти в стовпці таблиці з типом CHAR. Тип VARCHAR2(довжина) використовується для зберігання символьної послідовності змінної довжини. Обмеження на довжину становить 32 767 байт. Тип DATE використовується для зберігання значень дати і часу. Значення дати й часу зберігається у внутрішньому двійковому форматі і при приміщенні його в змінну символьного типу автоматично перетворюється в рядок, використовуючи формат дати, установлений за замовчуванням. Функція SYSDATE повертає поточне значення дати і часу. Тип BOOLEAN використовується для зберігання логічних значень TRUE, FALSE, NULL. Над такими змінними можна виконувати тільки логічні операції, причому в тризначній логіці. Скалярні змінні оголошуються явним і неявним чином. Явне оголошення змінної будь-якого з скалярних типів здійснюється за наступним правилом: ім'я_змінної [CONSTANT] тип [NOT NULL] [{: = ⎪ DEFAULT} значення]; При використанні ключового слова CONSTANT змінної повинно бути присвоєно значення, яке згодом не може бути змінено. Якщо вказано ключове слово NOT NULL, то змінну необдимо проініціалізувати, і згодом вона не може приймати значення NULL. Змінна ініціалізується значенням свого типу або з допомогою оператора присвоювання, або за допомогою ключового слова DEFAULT. Кожна змінна оголошується окремо. Неявне оголошення змінної скалярного типу здійснюється за допомогою спеціального атрибуту %TYPE, який дозволяє оголосити змінну, тип якого відповідає або іншого типу змінної, або типу стовпця таблиці БД. П р і м е р и A1 NUMBER; A11 NUMBER: =15; A12 NUMBER NOT NULL DEFAULT 15; A2 A1%TYPE; A3 BOOKS.PRICE%TYPE; Оголошуючи змінні, слід мати на увазі, що: 1) імена локальних змінних і формальних параметрів мають пріоритет перед іменами таблиць БД; 2) імена стовпців таблиці БД мають пріоритет перед іменами локальних змінних і формальних параметрів. Оголошення змінних типу RECORD (запис). Складовою тип запису визначає структуру, що містить деяку кількість змінних. Змінні можуть бути будь-якого типу, в тому числі і раніше визначеними записами. Посилання на окремі елементи цієї структури здійснюється за допомогою точкового нотації. Тип запису повинен бути визначений до того, як будуть оголошені змінні цього типу. Для явного визначення нового складеного типу даних використовується наступний загальний синтаксис: TYPE тип_записи IS RECORD (поле1 тип1 [NOT NULL] [{: = ⎪ DEFAULT } значення1], поле2 тип2 [NOT NULL] [{: = ⎪ DEFAULT } значення2],...); Для явного оголошення змінної-записи цього типу необхідно вказати ім'я змінної та ім'я типу. Допускається неявне визначення змінних типу запис, що виконується з допомогою атрибуту %ROWTYPE, що дозволяє визначати змінні-запис, структура яких ідентична структурі запису вказаної таблиці або структурі певної змінної-запису. Розглянемо змінну-запис, объявляемую явно: DECLARE

TYPE BOOK IS RECORD --вводиться тип запису – BOOK (AUTHOR VARCHAR2(15), --прізвище автора TITLE VARCHAR2(25), --назва книги PRICE NUMBER(6) --ціна); BOOK_FAN BOOK; --явне оголошення BOOK_ROM BOOK; --явне оголошення BOOK_POEM BOOK_FAN%ROWTYPE; --неявне оголошення Присвоювання значень елементів, що входять в запис, необхідно виконувати поелементно, використовуючи точкову позначення: BOOK_FAN.PRICE: =16000; Для присвоєння значень відразу всім полів запису або декільком з них можна скористатися однострочным оператором SELECT або оператором вибірки черговий рядки з відкритого курсору FETCH. Можливо присвоєння значень однієї змінної-запису інший за умови, що вони одного типу: BOOK_FAN: = BOOK_ROM; Однак слід мати на увазі, що змінна-запис, тип якої визначено явно, і змінна запис, тип якої визначається за допомогою атрибуту %ROWTYPE, завжди несумісні. Не допускається порівняння змінних-записів. Помилковою буде спроба передати в якості значень в команді INSERT запис повністю. Значення записи повинні передаватися поелементно: INSERT INTO TEMP VALUES (BOOK_FAN.AUTHOR, BOOK_FAN.TITLE, BOOK_FAN.PRICE);

13.3. ОПЕРАТОРИ Оператори мови PL/SQL представлені оператором присвоювання, умовним оператором IF і оператором циклу LOOP. Оператор присвоювання дозволяє задати змінної деяке значення і має наступний синтаксис: Змінна: = вираз;

Умовний оператор IF дозволяє перевірити деякий набір умов та виконати відповідні дії, має наступний синтаксис: IF логічний выражение1 THEN послідовність операторов1 ELSIF логічне выражение2 THEN послідовність операторов2 ELSE послідовність операторов3 ENF IF; Оператор циклу LOOP дозволяє повторити виконання заданої послідовності операторів необхідну кількість разів. Існують три форми запису оператора циклу LOOP. У першому варіанті умова завершення циклу знаходиться всередині тіла циклу і формується з допомогою ключових слів EXIT WHEN і. LOOP послідовність операторів [EXIT [WHEN умова]]; END LOOP; У другому варіанті повторення операторів здійснюється до тих пір, поки залишається істинним умова, зазначена в початковій рядку оператора LOOP. Додатково може бути сформована ще одна умова виходу з циклу за допомогою ключового слова EXIT. WHILE умова LOOP послідовність операторів [EXIT [WHEN умова]]; END LOOP; У третьому варіанті змінна циклу пробігає зазначений діапазон значень від нижньої до верхньої межі, збільшуючи кожен раз своє значення на одиницю, після чого здійснюється вихід з циклу. FOR переменная_цикла IN [REVERSE] нижняя_граница..верхняя_граница LOOP послідовність операторів END LOOP; Змінна циклу визначається системою неявно як змінна типу BINARY_INTEGER і не вимагає оголошення. Значеннями кордонів мо

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

13.4. КУРСОРИ Для виконання команди виберіть система виділяє певну область, куди поміщає крім службової інформації і самі обрані рядки (активний набір рядків). У мові PL/SQL і є спеціальна конструкція, яка дозволяє задати ім'я цієї області та здійснити доступ до що зберігається там інформації. Така конструкція називається курсором. Існують курсори двох типів: явні і неявні. Явний курсор повинен бути явно оголошено користувачем. Для його оголошення використовується наступний синтаксис: курсор имя_курсора [(список_параметров)] є Select...; Оператор вибору визначає набір вилучених рядків. Список параметрів може бути відсутнім. Якщо ж параметри використовуються, то вони описуються наступним чином: тип ім'я значення [{: =⎪ умовчанням} значення]; Якщо вказано параметр, то при відкритті курсору цей параметр можна не вказувати. Управління явним курсором здійснюється у двох варіантах: явно і неявно. При явній формі управління курсором необхідно відкрити курсор, явно вибрати рядки з активного набору і явно закрити курсор. Для того щоб відкрити курсор, використовується наступна конструкція: відкриті имя_курсора [(список_параметров)]; В момент відкриття курсору система виконує зазначений оператор вибору з урахуванням переданих значень параметрів, тобто вибирає відповідний набір рядків, і покажчик поточного запису встановлюється на перший рядок у цьому наборі. Однак рядка програмі не передаються. Щоб отримати рядка одну за одною, використовується оператор вибірки. Вибірка рядків допускається тільки в прямому напрямку; просування по набору рядків у зворотному напрямку неможливо. Синтаксис оператора вибірки: вибірка имя_курсора {имя_записи ⎪ список_столбцов};

При виконанні оператора вибірки вибирається черговий рядок, а покажчик поточного запису пересувається в наступний рядок, в наборі рядків. Якщо були обрані всі рядки, то при спробі нового зчитування помилка не виникає, але й рядок не вибирається. Для того щоб закрити курсор, необхідно виконати наступний оператор: закрити имя_курсора; Після закриття курсору всі спроби зчитування інформації приведуть до помилки. Для організації явного управління можна використовувати курсорні атрибути, які являють собою функції, які повертають певне значення в залежності від виконаних дій. До них відносяться: 1) %ISOPEN – повертає значення True, якщо курсор відкритий, і false, якщо курсор закрито; 2) %Знайшли – повертає значення true, якщо рядок знайдено, і false, якщо рядок не знайдено; 3) %елемент notfound – повертає значення true, якщо рядок не знайдено, і false, якщо рядок знайдено; 4) %за кількістю рядків – повертає числове значення, яке показує кількість обраних рядків у курсорі. При неявній формі управління курсором використовується спеціальна форма запису оператора цикл – цикли для з курсором. Відкриття, вибірка та закриття курсору в цьому випадку відбувається автоматично. Повертається змінна-рядок визначається неявно і на неї можна посилатися ззовні області видимості циклу. При неявній формі управління курсор може також мати параметри. Неявні курсори створюються і відкриваються системою при виконанні операторів вставки, оновлення та видалення, а також при виконанні однорядкового оператора вибрати. Неявний курсор називається SQL-курсором. Він має свої атрибути, аналогічні атрибутів явного курсору: SQL в%знайдені, SQL%елемент notfound, то SQL%rowcount в Проте в однорядковому операторі вибору в SQL можна використовувати атрибут%елемент notfound, тому що якщо при його виконанні інформація з таблиці не буде обрана, то сгенерируется виняток NO_DATA_FOUND. Атрибут SQL в%елемент notfound зазвичай використовується в операторах оновлення і видалення для перевірки, успішно чи ні виконані відповідні оператори.

Конструкція курсору дозволяє виконувати крім оперування наборами даних таблиць і модифікацію таблиць інформації. Для цього використовуються два додаткових конструкції: для оновлення [з столбец1, столбец2...] [аргумент nowait] вказується в операторі вибору, який визначає рядка формується активного набору, і де струм имя_курсора яка вказується в операторах оновлення або видалення, виконуваних у процесі вибірки й обробки рядків з активного набору. У звичайній ситуації рядка, визначаються активний набір, не блокуються, і будь-який інший користувач може здійснити їх модифікацію в БД. Якщо після цього закрити і знову відкрити курсор, то отримуєш доступ до нових, модифікованих рядками. При використанні варіанту для оновлення з переліком стовпців блокується доступ інших користувачів до зазначених стовпців, але й оновлювати інформацію можна тільки в цих стовпцях. Якщо ж використовується для оновлення переліку стовпців, то блокується вся таблиця, а значить, можна оновлювати інформацію, що зберігається в будь-яких стовпцях таблиці.

13.5. ОБРОБКА ВИНЯТКОВИХ СИТУАЦІЙ У мові PL/SQL і передбачені механізми перехоплення і обробки помилок, що виникають при виконанні програми. При виявленні помилки генерується виключна ситуація, обробка якої провадиться у розділі виключення. Існують два класи надзвичайних ситуацій: стандартні і визначаються користувачем. Стандартні виняткові ситуації діляться на два типи: ті, що мають і не мають певне ім'я. Мають певне ім'я виняткові ситуації крім коду мають ще і стандартне ім'я, яке використовується для ідентифікації виключення. Нижче наведено приклади деяких стандартних виняткових ситуацій, які зумовлені імена. 1) ZERO_DIVIDE – спроба ділення на нуль; 2) NO_DATA_FOUND – пропозиція вибрати...не повертає жодного рядка; 3) TOO_MANY_ROWS – пропозиція вибрати...повертає більше одного рядка;

4) INVALID_CURSOR – спроба виконання забороненої операції з курсором (наприклад, закриття невідкритого курсору); 5) CURSOR_ALREADY_OPEN – спроба відкриття вже відкритого курсору; 6) VALUE_ERROR – арифметична помилка, помилка перетворення, скорочення або обмеження; 7) INVALID_NUMBER – відмова перетворення рядка символів в число. Користувальницькі виняткові ситуації описуються в розділі заявляю, встановлюються у виконуваному розділі, а в розділі обробляються виключення. Опис користувальницької виняткової ситуації виконується завданням імені виключення і виключення фрази. Щоб згенерувати виняткову ситуацію і передати управління обробникові користувача виключної ситуації в разі виявлення помилки, використовується оператор имя_пользовательского_исключения підняти Для перехоплення виняткової ситуації будь-якого типу в розділ винятків повинна бути включена фраза, коли имя_исключения потім текст_обработчика_исключения; Тоді при виникненні відповідної помилки, замість припинення виконання програми і видачі типового повідомлення про помилку, буде виконуватися створений користувачем варіант обробки винятку. Якщо необхідно, щоб дві або більше виняткові ситуації оброблялися однаково, то вони повинні бути записані в одному операторі, коли, розділені ключовим словом або. Для перехоплення всіх виняткових ситуацій неописаних використовується спеціальний обробник іншим, який записується останнім у розділі виключення. Генерація виняткової ситуації з видачею відповідного повідомлення в робочу середу у разі виявлення помилки може бути виконана з допомогою наступного оператора: RAISE_APPLICATION_ERROR (errnum, errtext); де errnum – код помилки, який вибирається в діапазоні -20000.. -20999; errtext – символьна пояснюючий рядок довжиною до 512 байт. При виникненні виняткової ситуації і відсутності відповідного обробника в даному блоці система намагається знайти такий оброблювач в блоках, що охоплюють цей блок. При відсутності обробника система поверне помилку «необроблене виняток». 160

П р и м е р ы Создать программу, которая осуществляет в таблице BOOKS повышение цен на книги жанра «Фантастика». При этом при стоимости книги менее 2000 руб., цена увеличивается на 20 %, а при стоимости больше или равной 2000 руб. – на 10 %. Данная задача реализуется с помощью явно объявленного пользователем курсора. При этом в первых двух вариантах показываются возможности использования обычного курсора и курсора с параметром. Приведенное решение демонстрирует два варианта обработки явно объявленного курсора: явную и неявную формы. У явній формі обробки по завершенні перегляду рядків активного набору здійснюється вихід з циклу обробки. При цьому використовується курсорний атрибут %елемент notfound. В неявній формі обробки курсору використовується конструкція цикл для з курсором. Модифіковане значення ціни записується назад в таблицю книги з використанням конструкції, де струм, при цьому системі з допомогою конструкції для оновлення цін вказується, що буде здійснюватися оновлення значень стовпця таблиці ціною книги. У програмі неявним способом оголошені змінна типу запис зап і скалярна змінна NEW_ ціною. а) Використання звичайного курсору: оголосити курсор Кура це-явне оголошення курсору виберіть CODE_BOOK Кура, ціна від книг, де Жанр = 'Фантастика' для оновлення цін; зап готелі kur%ROWTYPE; --оголошення змінної-запису NEW_PRICE книг.Ціна%Тип; --оголошення змінної починається відкрите Кура; --явне відкриття курсору вибірка петлі Кура в зап; --вибірка поточного запису вихід коли Кура%елемент notfound; --вихід з циклу, якщо зап.Ціна < 2000 тоді --зміна ціни NEW_PRICE: = зап.ЦІНА*1.2; ЩЕ NEW_PRICE: = ЗАП.Ціна*1.1; кінець, якщо; оновлення книг ціна набору = NEW_PRICE --оновлення ціни

Де струм Кура; кінець циклу; закрити готелі kur; --явне закриття курсору фіксації; --завершення транзакції кінець; При запуску програми за допомогою SQL*плюс, необхідно зазначати у рядку, наступного за останнім оператором, косу риску (/), щоб програма виконалася.

б) Використання курсора з параметром: оголосити курсор Кура (GANR книг.Жанр%типу) --курсор має параметр виберіть CODE_BOOK, ціна від книг, де Жанр=GANR для оновлення цін; зап готелі kur%ROWTYPE; NEW_PRICE книг.Ціна%Тип; почати відкрийте Кура ('Фантастика'); -- значення параметра циклу вибірки Кура в зап; вихід, коли Кура%елемент notfound; якщо зап.ЦІНА < 2000 ТОДІ NEW_PRICE: = ЗАП.ЦІНА*1.2; ЩЕ NEW_PRICE: = ЗАП.Ціна*1.1; Кінець, якщо; оновлення книг ціна набору = NEW_PRICE де струм Кура; кінець циклу; закрити Кура; фіксації; кінець; в) Використання циклу з курсором: оголосити NEW_PRICE книг.Ціна%Тип; курсор Кура є Select CODE_BOOK, ціна від книг, де Жанр = 'Фантастика' для оновлення цін; почати

Для зх в Кура петлі --неявна обробка курсору, якщо зап.Ціна < 2000 тоді-зх мінлива неявно оголошується системою NEW_PRICE: = зап.Ціна*1.2; ЩЕ NEW_PRICE: = ЗАП.ЦІНА*1.1; КІНЕЦЬ, ЯКЩО; ОНОВЛЕННЯ КНИГ ЦІНА НАБОРУ = NEW_PRICE ДЕ СТРУМ КУРА; КІНЕЦЬ ЦИКЛУ; ФІКСАЦІЇ; КІНЕЦЬ;

13.6. ТРИГЕРИ БАЗИ ДАНИХ Тригер бази даних – це оформлене спеціальним чином іменований блок на PL/SQL, який зберігається в БД. Кожен тригер пов'язаний з певною таблицею і автоматично запускається при виконанні одного з тригерів dml-операторів (вставляти, видаляти, оновлювати) або їх сукупності над цією таблицею. Призначення тригерів. Тригери можуть бути використані: 1) для реалізації складних обмежень цілісності даних, які не можуть бути здійснені стандартним чином при створенні таблиці; 2) запобігання помилкових транзакцій; 3) виконання процедур комплексної перевірки прав доступу і секретності даних; 4) генерації деяких виразів на основі значень, наявних у стовпцях таблиць; 5) при реалізації складних бізнес-правил для обробки даних (можливість відстежити «ехо», тобто при зміні однієї таблиці оновлювати дані пов'язаних з нею таблиць). Створення і включення тригерів. Для створення і автоматичного включення тригера застосовується наступний загальний синтаксис: створити (або замінити] тригер имя_триггера {до | після} {вставка | видалення | оновлення [з список_столбцов]} на ім'я_таблиці [для кожного рядка] [, коли умова] < ЛП/SQL_блок >

При наявності ключових слів або замінити тригер створюється заново, якщо він вже існує. Конструкція перед | після вказує на момент запуску тригера. Варіант перед означає, що тригер буде запускатися перед виконанням активизирующего і dml-оператора; варіант після означає, що тригер буде запускатися після виконання активизирующего і dml-оператора. Конструкція вставки | видалення | оновлення [з список_столбцов] вказує тип активизирующего тригер dml-інструкція-оператора. Дозволяється, використовуючи логічну операцію або, задати сукупність активізують операторів, наприклад Insert або Delete. Якщо при використанні варіанту оновлення вказаний список стовпців, то тригер буде запускатися при модифікації одного з зазначених стовпців; якщо список стовпців відсутня, то тригер буде запускатися при зміні будь-якого з стовпців пов'язаної з тригером таблиці. Конструкція для кожного рядка вказує на характер впливу тригера: рядковий або операторний. Якщо конструкція для кожного рядка присутній, то тригер є строковим; при відсутності її тригер є операторным. Операторний тригер запускається один раз до або після виконання активизирующего тригер dml-інструкція-оператора незалежно від того, скільки рядків у зв'язаній таблиці з тригером піддається модифікації. Рядковий тригер запускається один раз для кожної з рядків, яка піддається модифікації тригерів dml-оператором, активізує тригер. З допомогою ключового слова, коли можна вказати додаткове обмеження на рядки пов'язаної з тригером таблиці, при модифікації яких може бути запущений тригер. Конструкція пл/SQL_блок представляє блок на PL/SQL, який запускається при активізації тригера. Класифікація тригерів. В основному розрізняють дванадцять типів тригерів. Тип тригера визначається поєднанням наступних трьох параметрів: 1) характером впливу тригера на рядки пов'язаної з ним таблиці (рядковий або операторний); 2) моментом запуску тригера: до (перед) або після (після) виконання активизирующего тригер dml-інструкція-оператора; 3) типом активизирующего тригер dml-інструкція-оператора (вставка, видалення, оновлення). Порядок активізації тригерів. Якщо в таблиці є кілька типів тригерів, то вони активізуються за наступною схемою:

• виконується операторний тригер перед (якщо їх кілька, то нічого не можна сказати про порядок їх виконання); 2) виконується рядковий тригер перед; 3) активізує виконується тригер dml-інструкція-оператор з наступною перевіркою всіх обмежень цілісності даних; 4) виконується рядковий тригер після з наступною перевіркою всіх обмежень цілісності даних; 5) виконується операторний тригер після. Тригерні предикати. Якщо в тригері вказується сукупність активізують тригер з dml-операторів (наприклад, Insert або Delete), то для розпізнавання того, який конкретно з тригерів dml-операторів виконується над таблицею пов'язаної з тригером, що використовуються тригерні предикати: вставка, видалення, оновлення. Вони являють собою логічні функції, що повертає значення True, якщо тип активизирующего оператора збігається з типом предиката, і false – у протилежному випадку. Для визначення одних і тих же дій у разі виконання різних операцій dml-операторів в умовному операторі тригерні предикати об'єднуються з допомогою логічних операцій. Псевдозаписи. Для строкових тригерів існують спеціальні конструкції, які дозволяють при виконанні операцій dml-операторів над рядком таблиці звертатися як до старих значень, які перебували в ній до модифікації, так і новим, які з'являться в рядку після її модифікації. Ці конструкції називаються псевдозаписями і позначаються старі і нові. Структура цих псевдозаписей ідентична структурі модифікується рядки таблиці, але оперувати можна тільки окремими полями псевдозаписи. Звернення до полів псевдозаписи відбувається за наступною схемою: перед старий або новий ставиться символ двокрапка (:), далі через крапку вказується назва поля. Значення, які беруть поля псевдозаписи при виконанні активізують з dml-операторів, визначаються наступним чином. 1. Оператор вставки – псевдозапись: новий еквівалентна вставляється рядку, а псевдозапись: старий у всіх полях має значення нуль. 2. Оператор Delete (видалити) – псевдозапись: старий еквівалентна видаляється рядку, а псевдозапись: нова у всіх полях має значення нуль. 3. Оператор оновлення – псевдозапись: новий еквівалентна рядку, отриманої в результаті модифікації, а псевдозапись: старий у всіх полях має вихідне значення рядка.

Включення, вимикання і видалення тригерів. Зберігається в БД тригер можна тимчасово відключити, не видаляючи його з БД. Для цього використовується наступна команда: ALTER TRIGGER имя_триггера DISABLE; Включити тригер через деякий проміжок часу можна, використовуючи команду ALTER TRIGGER имя_триггера ENABLE; Заборонити або дозволити запуск всіх тригерів, пов'язаних з певною таблицею, можна за допомогою команди ALTER TABLE ім'я_таблиці {DISABLE | ENABLE} ALL TRIGGERS; де варіант DISABLE використовується для відключення, а варіант ENABLE – для включення всіх тригерів даної таблиці. Знищення тригера, тобто видалення тригера з БД здійснюється за допомогою наступної команди: DROP TRIGGER имя_триггера; Отримання інформації про тригерах. Тригери зберігаються в БД, тому інформацію про них можна отримати з подання словника даних USER_TRIGGERS, наприклад, наступною командою: SELECT * FROM USER_TRIGGERS; П р і м е р и 1. Створити тригер, який перед вставкою черговий рядка в таблицю BOOKS_DELIVERY перевіряє наявність зазначеного коду книги в таблиці BOOKS. При відсутності зазначеного коду книги в таблиці BOOKS має генеруватися виключення з видачею відповідного повідомлення. Додавання нових рядків у таблицю BOOKS_DELIVERY виконується оператором INSERT. Оскільки тригер повинен запускатися перед виконанням кожного оператора INSERT, отже, він повинен бути рядковим BEFORE-тригером. Для збереження цілісності даних необхідно перевірити, чи є вносяться коди книг і в таблиці BOOKS. Для цього з допомогою однорядкового оператора SELECT здійснюється вибірка інформації з таблиці BOOKS, де умови вибірки використовується поле CODE_BOOK псевдозаписи: new. Якщо кількість рядків з даними кодом книги в таблиці BOOKS виявиться рав

вим нулю, буде згенеровано виняток і видано відповідне повідомлення. Створення тригера TR1 виконується введенням наступного оператора: CREATE OR REPLACE TRIGGER TR1 INSERT BEFORE ON BOOKS_DELIVERY FOR EACH ROW DECLARE QUANTITY NUMBER(4); BEGIN SELECT COUNT(*) INTO QUANTITY FROM BOOKS WHERE CODE_BOOK =: NEW.CODE_BOOK; IF QUANTITY = 0 THEN RAISE_APPLICATION_ERROR (-20212, 'В таблиці BOOKS немає інформації про цю книгу'); END IF; END TR1; При створенні тригера за допомогою SQL*PLUS, необхідно зазначати у рядку, наступного за останнім оператором, косу риску (/), щоб оператор CREATE... TRIGGER виконався. Дія тригера TR1 може бути перевірено виконанням наступного оператора, який здійснює вставку рядка в таблицю BOOKS_DELIVERY і тим самим викликає активізацію тригера TR2: INSERT INTO BOOKS_DELIVERY VALUES (21, 15, 'Іванов В. І. ', 15, '20-01-06'); Оскільки код книги 15 відсутня в таблиці BOOKS, то буде згенеровано виняток і видано відповідне повідомлення. 2. Створити тригер, який забороняє вносити в таблицю BOOKS рядки зі значенням поля PRICE більше, ніж 5000 руб., а також здійснювати збільшення ціни книг, інформація про які зберігається в таблиці BOOKS, більш ніж на 20 %. При порушенні цієї вимоги має генеруватися виключення з видачею відповідного повідомлення. Так як внесення нових рядків у таблицю BOOKS здійснюється в результаті виконання оператора INSERT, а значення поля PRICE в таблиці BOOKS, що містить ціну книжки, може бути змінено в результаті виконання оператора UPDATE, то в тригері вказується сукупність запускають DML-операторів. Оскільки тригер повинен запускатися перед виконанням кожного з вказаних DML-операторів,

отже, він є строковим BEFORE-тригером. Так як дії, що виконуються тригером, різні для кожного з запускають DML-операторів, модифікуючих таблицю BOOKS, то для розпізнавання типу DML-оператора використовуються відповідні тригерні предикати INSERTING і UPDAITING. Внаслідок того, що при вставці нових рядків перевірки має бути піддано нове значення поля PRICE, а при модифікації значення поля PRICE нове значення має порівнюватися зі старим значенням, необхідно використовувати псевдозаписи: new і: old. Створення тригера TR2 виконується введенням наступного оператора: CREATE OR REPLACE TRIGGER TR2 BEFORE INSERT OR UPDATE OF PRICE ON BOOKS FOR EACH ROW BEGIN IF INSERTING THEN IF: NEW.PRICE > 5000 THEN RAISE_APPLICATION_ERROR (-20102, 'В таблицю BOOKS не можна вносити записи з ціною книги > 5000'); END IF; END IF; IF UPDATING THEN IF: NEW.PRICE >: OLD.PRICE*1.2 THEN RAISE_APPLICATION_ERROR (-20103, 'В таблиці BOOKS не можна змінювати ціну книги більш ніж на 20 %'); END IF; END IF; END TR2; Дію тригера TR2 може бути перевірено виконанням наступних операторів, які, здійснюючи вставку рядків у таблицю BOOKS і оновлення рядків у таблиці BOOKS, тим самим викликають його активізацію. Оператор вставки рядків у таблицю BOOKS, що викликає активізацію тригера TR2: INSERT INTO BOOKS VALUES (21, 'Дюна', 'Герберт Ф.', 5268, 'Аст', 'Фантастика'); Оператор оновлення рядків у таблиці BOOKS, що викликає активізацію тригера TR2:

UPDATE BOOKS SET PRICE = 6000; Оскільки ці оператори порушують вимоги, що пред'являються до значення і модифікації ціни книг, то у всіх випадках буде згенеровано виняток і видано відповідне повідомлення. 3. Створити тригер, який в створену таблицю STAT, що містить стовпці назва видавництва – PUBLISH_H, кількість книг в жанрі «Роман» – QUANTITY_ROM, кількість книг в жанрі «Фантастика» – QUANTITY_FAN, при кожній модифікації таблиці BOOKS формує і заносить у відповідні стовпці таблиці STAT сумарна кількість книг по кожній з видавництв у розрізі зазначених тематик: «Роман» і «Фантастика». Модифікація таблиці BOOKS здійснюється виконанням наступних DML-операторів: INSERT, DELETE або оператора UPDATE, модифікуючий значення стовпця GENRE в таблиці BOOKS. Так як дії по формуванню інформації таблиці STAT виконуються після виконання кожного з модифікуючих таблицю BOOKS операторів, то за типом це операторний AFTER-тригер. Оскільки дії, що виконуються тригером, однакові для всіх типів активізують його операторів, то тригерні предикати не використовуються. Перед створенням тригера повинна бути створена таблиця STAT. Створення таблиці STAT може бути виконано введенням наступної сукупності операторів: DROP TABLE STAT; CREATE TABLE STAT (PUBLISH_H VARCHAR2(15), QUANTITY_ROM NUMBER(7), QUANTITY_FAN NUMBER(7)); Створення тригера TR3 виконується введенням наступного оператора: CREATE OR REPLACE TRIGGER TR3 AFTER INSERT OR DELETE OR UPDATE OF GENRE ON BOOKS DECLARE CURSOR V1 IS SELECT PUBLISH_HOUSE, COUNT(TITLE) QUANTITY1 FROM BOOKS WHERE GENRE = 'Роман'

GROUP BY PUBLISH_HOUSE; CURSOR V2 IS SELECT PUBLISH_HOUSE, COUNT(TITLE) QUANTITY2 FROM BOOKS WHERE GENRE = 'Фантастика' GROUP BY PUBLISH_HOUSE; BEGIN DELETE FROM STAT; FOR Z1 IN V1 LOOP INSERT INTO STAT VALUES(Z1.PUBLISH_HOUSE, Z1.QUANTITY1, 0); END LOOP; FOR Z1 IN V2 LOOP UPDATE STAT SET QUANTITY_FAN = Z1. QUANTITY2 WHERE PUBLISH_H = Z1.PUBLISH_HOUSE; IF SQL%NOTFOUND THEN INSERT INTO STAT VALUES(Z1.PUBLISH_HOUSE, 0, Z1.QUANTITY2); END IF; END LOOP; END TR3; Дія тригера може бути перевірено виконанням наступних операторів, які, здійснюючи вставку рядків у таблицю BOOKS, видалення рядків і оновлення рядків у таблиці BOOKS, тим самим викликають активізацію тригера TR3. Оператори вставки рядків у таблицю BOOKS, що викликають активізацію тригера TR3: INSERT INTO BOOKS VALUES (46, 'Єретики Дюни', 'Герберт Ф.', 368, 'Аст', 'Фантастика'); INSERT INTO BOOKS VALUES(42, 'Інгвар і Вільха', 'Нікітін Ю', 168, 'Аст', 'Роман'); Оператори видалення рядків з таблиці BOOKS, що викликають активізацію тригера TR3: DELETE BOOKS WHERE AUTHOR = 'Герберт Ф.'; DELETE BOOKS WHERE TITLE = 'Козаки'; Оператор модифікації рядків у таблиці BOOKS, що викликають активізацію тригера TR3: UPDATE BOOKS SET GENRE='Фантастика' WHERE TITLE = 'Інгвар і Вільха';

Перегляд інформації в таблиці STAT можна виконати наступним оператором: SELECT * FROM STAT;

13.7. ХРАНИМЫЕ ПРОЦЕДУРЫ И ФУНКЦИИ Функции и процедуры (подпрограммы) представляют собой оформленные специальным образом именованные блоки PL/SQL, которые могут быть вызваны для выполнения и которым могут быть переданы параметры. Как правило, процедуры и функции реализуют определенное законченное действие над некоторыми объектами БД. Типы процедур и функций. Существуют два вида процедур и функций: локальные и хранимые. Локальные процедуры и функции могут использоваться только в тех блоках, где они определены. Хранимые процедуры и функции компилируются и хранятся в БД в скомпилированном виде. При необходимости они могут быть вызваны для выполнения анонимными и именованными блоками PL/SQL, процедурами и функциями обоих видов, триггерами, а также из интерактивной среды SQL* PLUS. Помимо этого хранимая функция может быть вызвана и в операторе SQL. Создание хранимых процедур и функций. Для создания хранимой процедуры используется следующий общий синтаксис: CREATE [OR REPLACE] PROCEDURE имя_процедуры [(параметр1 [, параметр2,...])] IS [раздел локальных объявлений] BEGIN исполняемый раздел [ EXCEPTION раздел обработки исключений] END [имя процедуры]; Для створення збереженої функції використовується наступний загальний синтаксис: CREATE [OR REPLACE] FUNCTION имя_функции [(параметр1[, параметр2,...]] RETURN тип_данных IS [розділ локальних оголошень] BEGIN виконуваний розділ [ EXCEPTION розділ обробки виключень] END [ім'я функції]; Збережені процедури і функції, що викликаються блоками PL/SQL, процедурами і функціями, тригерами, викликаються завданням імені функції або процедури із зазначенням списку фактичних параметрів. Якщо викликається функція, то вона повинна бути частиною виразу; процедура викликається як окремий оператор. Для виклику з SQL* PLUS збереженої процедури використовується наступна форма запису: EXECUTE имя_процедуры (список_фактических_параметров); Оскільки функція інтерактивного редактора не може бути викликана безпосередньо, для її виклику необхідно використовувати блок PL/SQL, анонімний або іменований, або оператор SQL. Параметри процедур і функцій (підпрограм). Для передачі інформації в підпрограму використовуються параметри. Змінні або вирази, перераховані в списку параметрів у специфікації підпрограми, називаються формальними параметрами, а перераховані в списку параметрів при виклику підпрограми називаються фактичними аргументами. При виклику підпрограми фактичні аргументи обчислюються і результуючі значення присвоюються формальним параметрам, причому виробляються необхідні перетворення типів, тому формальні параметри та фактичні аргументи повинні мати сумісні типи. Список параметрів являє собою перерахування цих параметрів через кому. Кожен формальний параметр може бути описаний наступним синтаксисом: ім'я значення [вид] тип [{: = ⎪ DEFAULT} значення]; Параметр вид визначає режим передачі параметра. Є три режими передачі параметрів: IN (за замовчуванням), OUT IN OUT. Вони використовуються для позначення відповідності вхідних, вихідних і модифікуються параметрів. Бажано не використовувати режими OUT IN OUT при написанні функцій, щоб уникнути побічних ефектів. Фактичний аргумент, що вказує на місці IN-параметра, повинен бути константою, литералом, проинициализированной змінною або виразом, і на відміну від OUT - IN OUT-параметрів IN-параметр може мати значення за замовчуванням. Якщо параметр передається з варіантом IN, то в підпрограмі йому не можна присвоювати значення. На місці OUT - або IN OUT-параметра може бути вказана тільки змінна. Як і змінні, OUT-параметри ініціалізуються

NULL-значенням, і тип OUT-параметра не може бути підтипом, визначеним як NOT NULL. В іншому випадку генерується виключення VALUE_ERROR. Якщо при виконанні процедури або функції виникають виняткові ситуації, то управління передається в викликає блок. Коли здійснюється нормальний вихід з підпрограми, то фактичним OUT - IN OUT-аргументів присвоюються значення, а якщо виникають необроблені виключення, то значення не присвоюються. Параметр тип визначає дійсний тип даних для параметра. В якості типу параметра можуть використовуватися практично всі основні типи даних мови. Однак якщо використовуються типи CHAR, VARCHAR2 або NUMBER, то не можна не вказувати розмірність для цих типів даних, а для типу NUMBER – точність і масштаб. Порядок завдання параметрів. При виклику підпрограм можна записати список фактичних аргументів, використовуючи або позиційну, іменовану, змішану нотації, або передачу параметрів за замовчуванням: 1) позиційне позначення – це передача списку параметрів простим перерахуванням, причому типи, кількість і порядок проходження параметрів повинні відповідати оголошеним раніше; 2) у іменованої нотації стрілка => використовується як оператор асоціації, який пов'язує формальний параметр зліва від стрілки з фактичним аргументом праворуч від неї. При іменованої нотації параметри можуть зазначатися в будь-якому порядку; 3) нотації можуть змішуватися (змішана нотація), але в цьому випадку позиційне вказівка параметрів повинно передувати іменованого; 4) існує можливість передачі параметрів за замовчуванням. При цьому формальним параметрам повинні бути присвоєні значення або оператором присвоювання, або через ключове слово DEFAULT, і вони в списку фактичних параметрів повинні бути записані останніми. Підпрограми і залежності. Перекомпіляція підпрограм. Збережені функції і процедури зберігаються в скомпільованому вигляді в БД. При цьому, як правило, їх виконання зачіпає деякі об'єкти БД. Для забезпечення достовірності роботи таких процедур або функцій система постійно відстежує для кожної процедури або функції стан об'єктів, з якими вона пов'язана. Якщо якийсь із пов'язаних з нею об'єктів піддається модифікації за допомогою оператора DDL, то процедура або функція оголошується системою недійсною або недостовірної (INVALID). У цьому випадку процедуру або

функцію, оголошену недостовірної, треба обов'язково перекомпілювати. Для того щоб перекомпілювати збережену процедуру, використовується команда: ALTER PROCEDURE имя_процедуры COMPILE; Команда ALTER FUNCTION перекомпилирует збережену функцію: ALTER FUNCTION имя_функции COMPILE; Видалення підпрограм з БД здійснюється наступною командою: DROP {PROCEDURE ⎪ FUNCTION} имя_подпрограммы; Отримання інформації про збережених процедурах і функціях. Інформацію про процедури та функції можна отримати з подання словника даних USER_OBJECTS, наприклад, наступною командою: SELECT * FROM USER_OBJECTS; П р і м е р и 1. Створити збережену процедуру, що збільшує вартість зазначеної книги в таблиці BOOKS на 10 %. Параметр: код книги. Варіант 1. CREATE OR REPLACE PROCEDURE INCREASE (CODE_BOOK BOOKS.CODE_BOOK%TYPE) AS Q NUMBER(1): = 0; BEGIN SELECT COUNT(*) INTO Q FROM BOOKS WHERE BOOKS.CODE_BOOK = INCREASE.CODE_BOOK; IF Q < > 0 THEN UPDATE BOOKS SET PRICE = PRICE + PRICE*0.1 WHERE BOOKS.CODE_BOOK = INCREASE.CODE_BOOK; ELSE RAISE_APPLICATION_ERROR(-20105, 'В таблиці BOOKS відсутня книга з вказаним кодом'); END IF; END INCREASE; Виконання процедури INCREASE (варіант 1) реалізується оператором EXECUTE INCREASE (1);

Варіант 2. CREATE OR REPLACE PROCEDURE INCREASE (CODE_BOOK BOOKS.CODE_BOOK%TYPE) AS BEGIN UPDATE BOOKS SET PRICE = PRICE*1.1 WHERE BOOKS.CODE_BOOK = INCREASE.CODE_BOOK; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR(-20105, 'В таблиці BOOKS відсутня книга з вказаним кодом'); END IF; END INCREASE; Виконання процедури INCREASE (варіант 2) реалізується оператором EXECUTE INCREASE (1); 2. Створити збережену процедуру, яка при вступі книг до продавця або додає зазначену кількість до кількості вже наявних у продавця таких же книг, оновлюючи відповідний запис в таблиці BOOKS_DELIVERY, або вставляє новий запис у таблицю BOOKS_DELIVERY, якщо у продавця книг такого найменування немає. Параметри: код книги, кількість одиниць, прізвище продавця. Створення процедури: CREATE OR REPLACE PROCEDURE ADD_BOOKS (CODE_BOOK NUMBER, QUANTITY NUMBER, SALESMAN VARCHAR2) IS Q NUMBER(5): = 0; BEGIN SELECT COUNT(*) INTO Q FROM BOOKS_DELIVERY WHERE BOOKS_DELIVERY.CODE_BOOK = ADD_BOOKS.CODE_BOOK AND BOOKS_DELIVERY.SALESMAN = ADD_BOOKS.SALESMAN; IF Q < > 0 THEN UPDATE BOOKS_DELIVERY SET QUANTITY = QUANTITY + ADD_BOOKS.QUANTITY WHERE BOOKS_DELIVERY.CODE_BOOK = ADD_BOOKS.CODE_BOOK AND

BOOKS_DELIVERY.SALESMAN = ADD_BOOKS.SALESMAN; ELSE INSERT INTO BOOKS_DELIVERY VALUES (CODE_OP.NEXTVAL, ADD_BOOKS.CODE_BOOK, ADD_BOOKS.SALESMAN, ADD_BOOKS.QUANTITY, SYSDATE); END IF; END ADD_BOOKS; Виконання процедури ADD_BOOKS реалізується наступними операторами: EXECUTE ADD_BOOKS(1, 10, 'Іванов В. І.'); EXECUTE ADD_BOOKS(5, 10, 'Іванов В. І.'); При першому виклику процедури здійснюється додавання 10 книг до тих, що вже є у зазначеного продавця. При другому виклику в таблицю BOOKS_DELIVERY вставляється нова запис. 3. Створити збережену функцію, яка перевіряє, чи для зазначеного продавця загальна кількість наявних у нього книг в певний діапазон, і якщо немає, то видає відповідне повідомлення. Параметр: прізвище продавця. Мінімальне та максимальне значення для перевірки встановлюються в тілі функції з допомогою змінних MIN_QUANTITY і MAX_QUANTITY. Створення функції: CREATE OR REPLACE FUNCTION TEST_B (SALESMAN VARCHAR2) RETURN BOOLEAN IS MIN_QUANTITY NUMBER(5): =10; MAX_QUANTITY NUMBER(5): = 100; Q NUMBER(5); BEGIN SELECT SUM(QUANTITY) INTO Q FROM BOOKS_DELIVERY WHERE BOOKS_DELIVERY.SALESMAN = TEST_B.SALESMAN; RETURN (Q > = MIN_QUANTITY AND Q < = MAX_QUANTITY); END TEST_B; Виклик функції TEST_B здійснюється з допомогою тестуючої програми, наведеної нижче. DECLARE ADD_SALESMAN VARCHAR2(20); 176

ADD_QUANTITY NUMBER(5); ADD_CODE_BOOK NUMBER(5); BEGIN ADD_SALESMAN: = 'Іванов В. І.'; ADD_QUANTITY: = 20; ADD_CODE_BOOK: = 3; IF TEST_B(ADD_SALESMAN) THEN UPDATE BOOKS_DELIVERY SET QUANTITY = QUANTITY + ADD_QUANTITY WHERE BOOKS_DELIVERY.CODE_BOOK = ADD_CODE_BOOK AND BOOKS_DELIVERY.SALESMAN = ADD_SALESMAN; END IF; END; Програма додає зазначену кількість (змінна ADD_QUANTITY) книг зазначеного продавця (змінна ADD_SALESMAN), якщо загальна кількість наявних у нього книг не перевершує 100 і не менше 10.

13.8. ПАКЕТИ Пакет (package) – це сукупність процедур, функцій та інших програмних об'єктів, призначена для вирішення певного класу задач. Пакет дозволяє об'єднати і зберігати як окрему одиницю в базі даних кілька різних елементів: процедур, функцій, типів даних, змінних, констант, курсорів, винятків. В цілому пакет можна розглядати як іменований розділ оголошень блоку. Розміщення типів, змінних, курсорів, процедур і функцій в заголовку пакета дозволяє посилатися на них з інших блоків PL/SQL. На відміну від процедур і функцій, які можуть бути як локальними, так і збереженими, пакети ніколи не бувають локальними, а бувають тільки збереженими. Пакети також не можна вкладати один в одного, можна викликати і пакетів не можна передавати параметри. Структура пакета. Пакет, або модуль, що складається з двох різних частин: заголовка, або специфікації, пакету (package) і тіла (package body), кожна з яких зберігається окремо в словнику даних. Заголовок пакета описує його інтерфейс, тобто всі ті елементи, які можуть бути доступні всім користувачам пакету. Декларовані в специфікації пакету об'єкти називаються загальними (public). Серед них були

діють опису загальнодоступних типів і об'єктів та специфікації загальнодоступних функцій і процедур. До загальних об'єктів можна звертатися як ззовні пакета, так і з інших об'єктів в пакеті. Заголовок пакета створюється наступною структурою: CREATE [OR REPLACE] PACKAGE ім'я_пакету {IS|AS} --визначення типу; --визначення змінної, константи; --оголошення курсору; --оголошення виняткової ситуації; --оголошення функції; --оголошення процедури END [ім'я_пакету]; Наявність у заголовку модуля всіх перерахованих вище елементів необов'язково. Наприклад, заголовок модуля може складатися лише з оголошень процедур і функцій. Елементи пакету можуть розміщуватися в ньому в будь-якому порядку, але якщо якісь елементи пакету посилаються на інші об'єкти того ж пакету, то останні повинні бути оголошені до посилань на ці об'єкти. Оголошення всіх процедур і функцій повинні бути попередніми, тобто на відміну від розділу оголошень блоку, де можуть знаходитися як попередні оголошення, так і реальні тексти процедур та функцій, тут містяться тільки оголошення підпрограм. Створимо заголовок пакета BOOKS_DELIV, помістивши в нього опису двох процедур і функції з розділу 13.7. CREATE OR REPLACE PACKAGE BOOKS_DELIV IS PROCEDURE INCREASE (CODE_BOOK NUMBER); PROCEDURE ADD_BOOKS (CODE_BOOK NUMBER, QUANTITY NUMBER, SALESMAN VARCHAR2); FUNCTION TEST_B (SALESMAN VARCHAR2) RETURN BOOLEAN; END BOOKS_DELIV; Тіло пакету – це об'єкт словника даних, який зберігається окремо від заголовка пакета. Успішна компіляція тіла пакету можлива лише за умови успішної компіляції заголовка пакета. Тіло пакета створюється наступною структурою: CREATE [OR REPLACE] PACKAGE BODY ім'я {IS|AS} --опису закритих типів і об'єктів; --визначення локальних функцій і процедур;

--визначення загальнодоступних функцій і процедур; END [ім'я]; Тіло пакета реалізує специфікацію пакета. У ньому мають бути описані всі процедури і функції, попередньо оголошені в заголовку пакету. При цьому обов'язково повинні збігатися назви підпрограм, набір параметрів, порядок їх проходження і опис. Крім цього, можна також оголосити та визначити додаткові об'єкти пакету, які називаються особистими (private) або закритими. Так як особисті об'єкти оголошуються в тілі пакета, а не в його специфікації, до них можна звертатися тільки з об'єктів пакета. Доступ до них з інших блоків PL/SQL неможливий. Тіло пакету не є обов'язковою його частиною і при відсутності в заголовку оголошень процедур і функцій, тіло може бути відсутнім. У цьому випадку в заголовку пакету можна оголосити змінні, типи, курсори і виняткові ситуації, які будуть розглядатися як глобальні змінні і можуть не оголошуватися використовують їх в інших процедурах і функціях. Створимо тіло пакета BOOKS_DELIV, помістивши в нього визначення двох процедур і функції з розділу 13.7. CREATE OR REPLACE PACKAGE BODY BOOKS_DELIV IS PROCEDURE INCREASE (CODE_BOOK NUMBER) AS BEGIN UPDATE BOOKS SET PRICE = PRICE*1.1 WHERE BOOKS.CODE_BOOK = INCREASE.CODE_BOOK; IF SQL%NOTFOUND THEN RAISE_APPLICATION_ERROR(-20105, 'В таблиці BOOKS відсутня книга з вказаним кодом'); END IF; END INCREASE; PROCEDURE ADD_BOOKS (CODE_BOOK NUMBER, QUANTITY NUMBER, SALESMAN VARCHAR2) IS Q NUMBER(5): = 0; BEGIN SELECT COUNT(*) INTO Q FROM BOOKS_DELIVERY WHERE BOOKS_DELIVERY.CODE_BOOK = ADD_BOOKS.CODE_BOOK AND BOOKS_DELIVERY.SALESMAN = ADD_BOOKS.SALESMAN; IF Q < > 0 THEN

UPDATE BOOKS_DELIVERY SET QUANTITY = QUANTITY + ADD_BOOKS.QUANTITY WHERE BOOKS_DELIVERY.CODE_BOOK = ADD_BOOKS.CODE_BOOK AND BOOKS_DELIVERY.SALESMAN = ADD_BOOKS.SALESMAN; ELSE INSERT INTO BOOKS_DELIVERY VALUES (CODE_OP.NEXTVAL, ADD_BOOKS.CODE_BOOK, ADD_BOOKS.SALESMAN, ADD_BOOKS.QUANTITY, SYSDATE); END IF; END ADD_BOOKS; FUNCTION TEST_B (SALESMAN VARCHAR2) RETURN BOOLEAN IS MIN_QUANTITY NUMBER(5): =10; MAX_QUANTITY NUMBER(5): = 100; Q NUMBER(5); BEGIN SELECT SUM(QUANTITY) INTO Q