Konspekt_lektsiy_BD_amp_amp_SD

В прикладі запит логічно розбито на вісім підзапитів, які використовуються як віртуальні таблиці в окремих підзапитах:

Підтримка механізму об’єктно-реляційних зв’язків в PostgreSQL

Об’єктно-реляційні зв’язки між таблицями, створення яких було розглянуто у відповідному параграфі (див. стор. 64), накладають певні обмеження на маніпулювання даними в таких таблицях.

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

INSERT INTO Master

VALUES(2, 'Мікулінська', 'Марія', 'Геннадіївна', 1, 'ОІ', 1, NULL, NULL, 'Тема12');

рядок по студенту буде видно і в таблиці Student.

Для управлення видимістю рядків у таблиці-предку, які були створені в таблицінащадку, використовується оператор ONLY, що передує імені таблиці.

Наприклад, для отримання вмісту тільки таблиці-предка Student без зачіпання таблицінащадка Master використовується запит:

SELECT * FROM ONLY Student;

Наприклад, для оновлення рядків тільки таблиці Student без зачіпання рядків таблиці Master використовується запит:

UPDATE ONLY Student SET SecondName = 'Глава' WHERE kod = 1;

А для поновлення рядків таблиці Student і рядків таблиці Master використовується стандартний запит:

UPDATE Student SET SecondName = 'Глава'WHERE Kod = 1;

Демонстраційний приклад

Запишіть SQL-запити з використанням підзапитів для маніпулювання даними з таблиць, що створені у завданні „Демонстраційний приклад“ розд. „Мова визначення даних

(МВД, DDL) SQL. Частина 1“.

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

Розв’язання.

SELECT DISTINCT V.destination, C.name FROM voyage V, class C WHERE C.voyage=V.id_voyage AND C.id_class NOT IN

Кафедра економічної кібернетики та інформаційних технологій. Одеський національний політехнічний університет

П.2. Виберіть з бази даних пункт призначення рейсу, дату вильоту та назву класу салону, на якій продано всі квитки.

Розв’язання.

П.3. Виберіть з бази даних напрямок рейсу, клас та кількість квитків, що залишилися у продажу. Результат відсортуйте за напрямком рейсу.

Розв’язання.

WITH count_ticket AS

(SELECT class, COUNT(id_ticket) AS kolvo FROM ticket GROUP BY class), count_place AS

(SELECT c.id_class, c.name, v.destination, quantity_place FROM class c, voyage v

WHERE c.voyage=v.id_voyage)

SELECT c.destination, c.name, quantity_place-kolvo AS tail FROM count_place c, count_ticket K

WHERE c.id_class=k.class ORDER BY c.destination;

Результуюча таблиця.

Кафедра економічної кібернетики та інформаційних технологій. Одеський національний політехнічний університет

1.Універсальність:

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

2.Ефективність:

зменшення навантаження на мережу каналу зв’язку між клієнтом і вузлом-сервером, тому що обробка записів виконується безпосередньо на сервері;

зменшення навантаження на вузол-сервер, тому що окремі етапи оптимізації коду процедури можуть здійснюватися не під час виконання процедури, а під час її реєстрації в СУБД.

3.Безпека:

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

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

незалежності і цілісності даних.

Існує 2 типи збережених процедур: процедури вибору і виконувані процедури. Відрі-

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

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

CREATE PROCEDURE процедура

і складаються з заголовка процедури й тіла процедури. Заголовок процедури містить:

ім’я збереженої процедури, яке повинне бути унікальним серед імен процедур і таблиць у БД;

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

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

Завдяки цьому збережені процедури можуть використовуватись як табличні вирази в команді SELECT (замість відношення).

Тіло процедури складається з:

необов’язкового списку локальних змінних із вказівкою типів даних;

блока інструкцій, вкладеного між операторами BEGIN і END (не у всіх СУБД). Більшість СУБД і стандарт SQL підтримують багаторівневе вкладення блоків.

Мова збережених процедур і тригерів SQL містить:

•команди ММД SQL: INSERT, UPDATE, DELETE і SELECT;

•оператори і вирази SQL;

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

змінна = вираз — операція присвоєння значення виразу локальній змінній, вхідному або вихідному параметру;

INTO список змінних — присвоєння змінним або параметрам результату роботи ко-

манди SELECT;

SUSPEND — повертає вихідні параметри викликаючій програмі і припиняє збережену процедуру доти, поки викликаюча програма не зажадає наступний кортеж. Не рекоме-

ндується використовувати у виконуваних процедурах;

FOR SELECT-вираз DO складений_оператор —

складений_оператор — інструкція або блок інструкцій, який буде виконаний для кожного рядка, повернутого SELECT-виразом, де SELECT-вираз — звичайна команда SELECT, за винятком того, що наприкінці команди має обов’язково бути присутнім

Кафедра економічної кібернетики та інформаційних технологій. Одеський національний політехнічний університет

оператор INTO;

IF(умова) THEN складений_оператор [ELSE складений_оператор]

WHILE(умова) DO складений_оператор - цикл із передперевіркою;

EXCEPTION виняток — генерує пойменовану виняткову ситуацію виняток — визначену користувачем помилку, яка може бути оброблена оператором WHEN;

WHEN{помилка[, помилка[, …]] | ANY} DO складений_оператор — обробка помилок,

вказаних у списку, або будь-яких помилок, якщо вказано ANY. Ця інструкція, якщо є присутньою, вказується наприкінці збереженої процедури безпосередньо перед END;

EXIT — перехід на кінець збереженої процедури (останній END), наприклад, для переривання циклу;

EXECUTE PROCEDURE процедура [параметр[, параметр[, …]]] [RETURNING_VALUES [параметр[, параметр[, …]]] — виконує збережену процеду-

ру з ім’ям процедура, вхідними параметрами, переліченими після імені, і яка повертає значення в параметрах, перелічених після RETURNING_VALUES.

Вхідними і вихідними параметрами повинні бути змінні, визначені усередині поточної процедури. Завдяки цій інструкції можливі вкладені виклики процедур і рекурсії.

/* коментар */ — для коментарів.

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

Значення команди SET TERM буде роз’яснено далі у розділі „Сценарії“. Поки що майте на увазі необхідність використання цих команд до та після команди CREATE PROCEDURE.

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

ний вираз в команді SELECT:

SELECT * FROM GroupInfo;

що дасть такий результат:

Кафедра економічної кібернетики та інформаційних технологій. Одеський національний політехнічний університет

Інший приклад використання збереженої процедури, якою можна скористатися в прикладі поділу групи:

SELECT Spec, MAX(GNum) FROM GroupInfo GROUP BY Spec;

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

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

Скористатися цією збереженою процедурою можна, задавши параметри в явному вигляді. Наприклад:

SELECT * FROM StudGroup(‘ОС’, 941);

Не менш ефективним буде виклик цієї збереженої процедури в підзапиті. Наприклад, для розв’язання попередньої задачі:

GROUP BY S.Spec, GNum;

Виконувані процедури, як видно з назви, повинні виконувати які-небудь дії. Наприклад, збережена процедура, яка видаляє усіх випускників з таблиці Student і їхній рейтинг із таб-

лиці Rating:

SET TERM ; ^

CREATE PROCEDURE RemoveDiploma (GNum INT) AS

Кафедра економічної кібернетики та інформаційних технологій. Одеський національний політехнічний університет

Тому що ця збережена процедура не повертає значення і, більш того, знищує при цьому кортежі, то використовувати її в команді SELECT не можна. Для виклику таких процедур використовується команда розширення SQL — EXECUTE PROCEDURE процедура.

Завдання.

1.Змінити процедуру для видалення всіх груп даного випуску.

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

Особливості побудови збережених процедур в СУБД PostgreSQL

Мова програмування PLpg/SQL СУБД PostgreSQL

Більшість сучасних СУБД для програмування збережених процедур пропонує розширення стандарту SQL. СУБД Oracle була однією з перших промислових СУБД, що впровадила мову програмування PL/SQL не тільки як мову програмування процедур, але і як мову програмування додатків БД. По своїх можливостях PL/SQL можна порівнювати з більшістю алгоритмічних мов третього покоління (ANSI C, Pascal, Basic). СУБД PostgreSQL при створенні мови PLpg/SQL запозичила від СУБД Oracle більшість мовних конструкцій мови програмування PL/SQL.

Структура підпрограм PLpg/SQL

На відміну від збережених процедур стандартного SQL, наведених вище, та, навіть, на відміну від СУБД Oracle, яка містить два типи підпрограм — процедури (аналогічні стандартним) і функції, PLpg/SQL підтримує тільки користувальницькі функції.

Для створення такої функції використовується наступний синтаксис:

CREATE OR REPLACE FUNCTION Ім’я_функції

(Ім’я_параметра Вид_передачі_значення Тип_параметра, …) RETURN Тип_значення_яке_повертається

AS $$

PLpg/SQL програма, яка повинна завершуватися оператором

RETURN Значення_яке_повертається; $$ LANGUAGE plpgsql;

Можливі два види передачі значення в параметри функції:

IN — значення параметра передається у функцію (передача за значенням)

OUT — значення параметра може бути повернуте з функції (передача по посиланню) Розглянемо можливості мови PLpg/SQL для програмування збережених процедур. Аналогічно стандартному SQL структура збереженої процедури, яка створюється

Кафедра економічної кібернетики та інформаційних технологій. Одеський національний політехнічний університет

PLpg/SQL, містить три блоки: декларативний блок, блок, який виконується, блок обробки винятків — умов, які викликають помилки або попередження про помилки:

DECLARE

// Оголошення

BEGIN

//Виконання команд обробки даних

EXCEPTION

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

END;

Блок, який виконується, є обов’язковим, а два інших блоки можуть бути відсутніми. Проте важливою відмінністю діалекту PLpg/SQL від стандарту є те, що всі його функції

!належать тільки до категорії процедур вибору, а тому завжди повинні повертати значення. Внаслідок цього для виклику функцій в PLpg/SQL використовується тільки команда SELECT.

Змінні в PLpg/SQL

Змінна в PLpg/SQL може мати будь-який тип даних, властивий стандартному SQL, такий як NUMBER, CHAR, DATE, або властивий діалекту PLpg/SQL, такий як BOOLEAN. Наприклад, необхідно оголосити змінну з ім’ям Part_No так, щоб вона могла зберігати 4- хзначні числові значення, і змінну з ім’ям In_Stock, що може приймати булеве значення TRUE або FALSE. Оголошуються змінні цього прикладу так:

…Tax NUMBER(4), Bonus DEC(4, 2), Valid BOOLEAN…

Крім того, в PLpg/SQL є можливість оголошувати записи і таблиці, використовуючи складні типи даних цього діалекту: RECORD і TABLE.

Присвоєння значень змінним у цьому розширенні нічим не відрізняється від стандарту

SQL.

По-перше, це оператор присвоювання ‘:=‘. Наприклад: Tax := Price * Tax_Rate;

Bonus := Current_Salary * 0.10; Valid := FALSE.

По-друге, це введення в змінну значення з БД за допомогою фрази INTO команди SELECT або FETCH. Наприклад: обчислити 10% премії при виплаті зарплати співробітника:

SELECT Salary * 0.10 INTO Bonus FROM Lecturer;

Після цього значення змінної Bonus можна використовувати в інших обчисленнях, або внести його в таблицю БД.

Атрибути

Змінні в PLpg/SQL можуть бути так званими „атрибутами“, тобто властивостями, які дозволяють посилатися на тип даних, який має один зі стовпців таблиць БД, не повторюючи його оголошення. Синтаксис оголошення змінної як атрибута наступний:

Атрибут%TYPE

Наприклад, таблиця Books містить стовпець із ім’ям Title. Щоб дати змінній My_Title той же тип даних, що й у стовпця Title, не знаючи точного визначення цього стовпця в БД, досить указати наступну інструкцію:

…My_Title Books...Title%TYPE;…

Кафедра економічної кібернетики та інформаційних технологій. Одеський національний політехнічний університет

Таке оголошення змінної має дві переваги:

―немає необхідності знати точний тип даних стовпця Title;

―якщо визначення стовпця Title у БД зміниться, наприклад, збільшиться його довжина, тип даних змінної My_Title зміниться відповідно під час виконання.

Також можна використовувати інший вид атрибута із синтаксисом:

Атрибут%ROWTYPE

ВPLpg/SQL для групування даних використовуються записи. Запис складається з декількох стовпців, у яких можуть зберігатися значення даних. Атрибут %ROWTYPE позначає тип запису, що представляє рядок у таблиці. Такий запис, тобто змінна, оголошена з атрибутом %ROWTYPE, може зберігати цілий рядок даних, отриманий з таблиці або через курсор (який буде розглянутий у наступному параграфі).

Стовпці в рядку таблиці та відповідні стовпці в запису мають однакові імена і типи даних. У наступному прикладі оголошується запис з ім’ям Dept_Rec:

…Dept_Rec Dept%ROWTYPE;…

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

My_DeptNo := Dept_Rec.DeptNo;

Управляючі структури

ВPLpg/SQL, як і в інших мовах програмування, існують команди передачі управління, до яких належать оператор умовного переходу і оператори циклів.

ВPLpg/SQL вони звуться управляючими структурами: умовного управління (IF- THEN-ELSE) і ітеративного управління FOR-LOOP, WHILE-LOOP.

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

'Вам необхідно ліквідувати заборгованості у кількості ' ||

Кафедра економічної кібернетики та інформаційних технологій. Одеський національний політехнічний університет

negotive_mark_count);

END IF; RETURN 1;

END;$$ LANGUAGE 'plpgsql';

Для звернення до цієї функції треба виконати такий запит: SELECT Попередження_if_else();

Як було вже відзначено, в PostgreSQL всі функції повинні що-небудь повертати. Через те, що від цієї функції не очікується жодного результата, створюємо його штучно – повертаємо значення 1 як символ того, що функція успішно виконалася. Тобто при виклику за допомогою команди SELECT побачимо у результаті значення 1, а в таблиці Letters відповідні записи. Якщо в студента з кодом 5 взагалі немає оцінок або всі оцінки позитивні, додасться запис з кількістю заборгованостей, рівною 0.

Для циклу FOR-LOOP задається інтервал цілих чисел, а дії усередині циклу виконуються один раз для кожного цілого в цьому інтервалі. Наприклад: наступна програма визначає кількість академічних заборгованостей студентів з номерами від 1 до 7 та у відповідності з припустимою кількістю генерує для кожного студента повідомлення:

VALUES(StudentKod, Name ||

'! Ваша кількість заборгованостей =' || negotive_mark_count ||

', що перевищує припустиму кількість!');

ELSE

INSERT INTO Letters (kod, content) VALUES(StudentKod, Name ||

'! Вам необхідно ліквідувати заборгованості у кількості '|| negotive_mark_count);

END IF;

END LOOP;

RETURN 1; END;$$ LANGUAGE 'plpgsql';

При виклику цієї функції за допомогою наступної інструкції:

Кафедра економічної кібернетики та інформаційних технологій. Одеський національний політехнічний університет