3.8 Агрегатні функції

PostgreSQL, як і багато інших реляційні СУБД, підтримує агрегатні функції. Агрегатна функція проводить обчислення над одиничним результатом від безлічі записів. Наприклад, є агрегати для обчислення count(кількості), sum (суми), avg (середнього арифметичного), max (максимального значення) і min (мінімального значення) списку записів.

Агрегатні функції застосовуються в запитах для групування даних, вони такі:

avg(вираз) - середнє арифметичне значення;

count(вираз) - кількість записів зі значенням відмінним від NULL;

mах(вираз) - максимальне значення;

min(вираз) - мінімальне значення;

stddev(вираз) - середньоквадратичне відхилення;

sum(вираз) - сума;

variаnce(вираз) - дисперсія.

3.9 Курсори SQL

Курсор мовою SQL являє собою вказівник на підсумковий набір даних, виданих командою SELECT. Курсор виконується тільки в транзакційному блоці. Його використання надає такі переваги:

- економія оперативної пам'яті за рахунок зберігання тільки потрібних даних (вибірки). Це особливо вигідно під час використання громіздких таблиць;

- зменшення навантаження на зовнішні носії пам'яті при багаторазовому читанні вибірки, включаючи читання одних і тих же даних. Це дозволяє зменшити кількість звертань до зовнішньої пам'яті при багаторазовому використанні одних і тих же даних. Зменшується навантаження й на мережу в багатокористувацьких системах;

- збільшення швидкості звертання до даних за рахунок уникнення блокування.

Оголошення курсора відбувається тільки в складі транзакційного блока, який починається з команди BEGIN. В SQL команда оголошення курсора одночасно виконує його відкриття, вона має такий вигляд:

DECLARE курсор [ BINARY ] [ INSENSITIVE ] [ SCROLL ]

CURSOR FOR запит

[ FOR { READ ONLY | UPDATE [ OF ім'я_таблиці]}]

Тут:

курсор - ім'я нового курсора;

BINARY означає, що вихідні дані повинні повертатися в двійковому форматі замість стандартного ASCII-коду;

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

SCROLL забезпечує прокрутку даних, тобто дозволяє багаторазове читання записів;

CURSOR FOR запит - запит, підсумковий набір якого стає доступним через курсор;

READ ONLY дозволяє використовувати курсор тільки для читання даних, цей режим установлений за замовчуванням;

UPDATE - курсор використовується для редагування таблиць;

OF ім'я_таблиці - ім'я_таблиці, яка може оновлюватися під час використання курсора.

У поданому нижче прикладі створено транзакцію командою BEGIN і відкрито курсор gazprovid, який буде містити всі записи і всі поля таблиці gazpr:

BEGIN;

DECLARE gazprovid CURSOR FOR SELECT * FROM gazpr;

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

FETCH [FORWARD | BACKWARD | RELATIVE] [число | ALL | NEXT | PRIOR]

{IN | FROM} курсор

У цьому оголошенні курсор - ім'я курсора, з якого відбувається вибірка записів. Курсор завжди встановлений на поточну позицію підсумкового набору. Напрям вибірки визначається ключовими словами FORWARD і BACKWARD, за замовчуванням використовується пряма вибірка (FORWARD). Слово RELATIVE число означає зміщення на задану кількість записів відносно поточної позиції курсора. У команді замість RELATIVE може використовуватися ключове слово ABSOLUTE - абсолютне позиціонування, тобто переміщення до заданого запису.

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

ALL означає, що команда повертає всі записи, починаючи з поточної позиції,

NEXT (використовується за замовчуванням) - наступний запис від поточної позиції,

PRIOR запис, який знаходиться перед поточним.

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

Нижче у прикладі вибираються перші чотири записи підсумкового набору, на який посилається курсор gazprovid. Напрямок не вказаний, тому за замовчанням використовується ключове слово FORWARD. Далі команда FETCH з ключовим словом NEXT вибирає п'ятий запис, після чого вона з ключовим словом PRIOR знову повертається до четвертого запису.

FETCH 4 FROM gazprovid;

FETCH NEXT FROM gazprovid;

FETCH PRIOR FROM gazprovid;

Число записів може бути як додатнім, так і відємним. При додатньому значенні відлік ведеться в напрямку, заданому відповідним параметром (якщо параметр не вказаний, за замовчуванням використовується параметр FORWARD).

При від'ємній кількості записів відлік ведеться в напрямку, протележному заданому. Наприклад, конструкція FORWARD -5 еквівалентна конструкції BACKWARD 5. Якщо кількість запрошених записів перевищує кількість записів у базі, команда FETCH вибирає всі існуючі записи у вказаному напрямку.

У подальшому прикладі виготовляється курсор gazprovid, який повертає дані з таблиці gazpr. Наступна команда вибирає перші два його записи:

BEGIN;

FETCH FORWARD 2 IN gazprovid;

У нижченаведеному прикладі конструкція BACKWARD -2 також вибирає два записи в прямому напрямку:

FETCH BACKWARD - 2 IN gazprovid;

А ця команда демонструє вибірку в зворотному напрямку через курсор gazprovid:

FETCH BACKWARD 3 IN gazprovid;

FETCH PRIOR IN gazprovid; -- на 1 назад

FETCH NEXT IN gazprovid; -- на 1 вперед

FETCH 2 IN gazprovid; -- те ж, що NEXT 2

Спроба вибірки нуля записів із ключовим словом RELATIVE видає поточний запис, у деяких ранніх версіях SQL спричиняє помилку

ERROR: FETCH/RELATIVE at current position is not supported.

Це пов'язано з тим, що відповідно до стандарту SQL92 команда FETCH RELATIVE FROM курсор повинна забезпечувати повторну вибірку запису в поточній позиції курсора. У деяких версіях PostgreSQL цей синтаксис не підтримується. Без ключового слова RELATIVE число 0 інтерпретується як запит на вибірку всіх записів. Але з ключовим словом RELATIVE PostgreSQL припускає, що використовується синтаксис SQL92, і замість того, щоб повернути всі записи, виводить повідомлення про помилку.

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

MOVE [FORWARD | BACKWARD | RELATIVE] [число |ALL | NEXT | PRIOR] {IN | FROM} курсор

Як видно з цього оголошення, синтаксис команди MOVE дуже близький до синтаксису команди FETCH. Втім, команда MOVE ніяких записів не повертає і лише переміщає поточну позицію курсора. Зміщення задається цілочисловою константою або ключовими словами ALL (переміщення в заданому напрямку на максимально можливу відстань), NEXT або PRIOR.

Закриття курсора забезпечується командою CLOSE. Курсор також автоматично закривається при виході з транзакційного блоку, в якому він знаходиться, при фіксації транзакції командою COMMIT або при її відкоті командою ROLLBACK.

Команда CLOSE має такий вигляд (курсор - ім'я курсора, який закривається):

CLOSE курсор

4. Основні елементи мови PLpgSQL

4.1 Структура мови, функції користувача

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

Структура мови. Мова PL/pgSQL відносно проста, кожен її логічно відокремлений фрагмент коду існує у вигляді функції. До певної міри програми подібні до написаних мовою С: всі змінні обов'язково оголошуются перед використанням, функції отримують аргументи при виклику і повертають потрібні значення при закінченні роботи та ін.

Регістр символів в іменах функцій PL/pgSQL не регламентований. У ключових словах і ідентифікаторах допускається використання довільних комбінацій символів верхнього та нижнього регістрів.

Програмний блок. При першому виготовленні функції створюється мова програмування командою CREATE LANGUAGE, зазвичайно це plpgsql. Виготовити програмний блок (функцію) можна командою SQL CREATE FUNCTION, у її складі може міститися інструкція OR REPLACE, яка дозволяє редагувати функцію.

Функція являє собою блок, який містить секцію DECLARE - оголошення даних та BEGIN - команди, які виконуються. Закінчується програмний блок словом END. Загальний вигляд програмного блока:

CREATE LANGUAGE мова_програмування;

CREATE OR REPLACE FUNCTION ім'я_функції (перелік_типів_аргументів)

RETURNS тип_поверненого_значення

AS $$ DECLARE оголошення даних; BEGIN команди; END; $$

LANGUAGE 'мова_програмування';

Коментарі мови PL/pgSQL подібні до мови С++, вони є двох типів. Коментарі першого типу - однорядкові, починаються з двох дефісів, другого - блокові, беруться у знаки /* та */.

В оголошенні задаються імена і типи змінних, кожне оголошення або команда завершується символом крапки з комою. Крім типів даних, визначених мовою SQL, мова PL/pgSQL має ще три додаткові типи, які дозволяють працювати з таблицями та іншими об'єктами БД (вибирати їхні значення), це:

· RECORD - для записів без вказання типів полів;

· %ROWTYPE - для записів, тип яких відповідає типам полів конкретного об'єкта;

· %TYPE - для змінної, тип якої відповідає типу конкретного поля об'єкта (таблиці).

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

Змінні можуть бути ініціалізовані під час оголошення. Подібно до мови С в оголошенні можна використовувати модифікатор CONSTANT. Модифікатор NOT NULL означає, що змінна обов'язково повинна бути ініціалізова під час оголошення. Аргументи, прийняті функцією, можна спеціально не оголошувати, тоді їхні значення по черзі присвоюються ідентифікаторам, які утворюються автоматично і складаються із знака долара та порядкового номера (зліва направо). Першому аргументу відповідає ідентифікатор $1, другому - $2 і т. д. Максимально допустима кількість аргументів дорівнює 16, тому ідентифікатори аргументів лежать у інтервалі від $1 до $16. Якщо аргументів є декілька, то з метою уникнення плутанини їх варто замінити псевдонімами - ідентифікаторами нутрішніх змінних функції за допомогою слова ALIAS, наприклад, так:

ідентифікатор_змінної_1 ALIAS FOR $1; ідентифікатор_змінної_2 ALIAS FOR $2; і т. д.

Присвоєння значень змінним виконує оператор := подібно до мови Pascal. Мова PL/pgSQL призначена в основному для роботи з таблицями БД, тому має варіант запиту - команду SELECT INTO, яка також дозволяє присвоювати змінним результати його виконання. Ця команда застосовується в основному для запам'ятовування записів (полів) вибірки змінним, оголошених з типами RECORD, %ROWTYPE і %TYPE (для поля). Якщо команда SELECT INTO видає своє значення звичайній змінній, то її тип повинен відповідати типу відповідного запису (поля) вибірки. Наголосимо на тому, що вона повинна повертати лише одне якесь значення (адже змінна то одна). Синтаксис команди SELECT INTO:

SELECT INTO перелік_імен_змінних перелік_імен_полів

FROM джерело

секції_команди_SELECT;

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

Виклик функції (звертання до функції) забезпечує команда:

SELECT ім'я_функції(перелік_дійсних_параметрів);

4.2 Розгалуження, цикли, масиви

Розгалуження забезпечують команди:

IF умова THEN команда; END IF;

IF умова THEN команда; ELSE команда; END IF;

IF умова THEN команда; ELSE IF умова THEN командa; END IF;

Розгалуження можна застосувати для перевірки на відсутність значення змінної, виданого командою SELECT INTO, для цього його слід його порівняти з IS NULL.

Результат буде такий: Нема газу

або, якщо SELECT premia(3);, то: Преміальне нарахування = 15 грн

У PL/pgSQL реалізовано три типи циклів: LOOP, WHILE і FOR.

Цикл LOOP виконується доти, поки не буде досягнено ключове слово EXIT. За ключовим словом EXIT може міститися секція WHEN з виразом логічного типу, яка визначає умову виходу із циклу. Без цієї секції матимемо просто зациклення. Цикл LOOP має такий вигляд:

LOOP

команди (тіло_циклу);

EXIT [WHEN умова_виходу_з_циклу];

END LOOP;

Цикл WHILE містить умову свого завершення, він виглядає так:

WHILE умова_виходу_з_циклу LOOP

команди (тіло_циклу);

END LOOP;

Нижче показаний варіант функції kvadrat() з циклом WHILE:

Цикли FOR у якості параметра використовує змінну цілого типу, він виглядає так:

FOR змінна IN [REVERSE] вираз_1 .. вираз_2 [BY приріст] LOOP

тіло_циклу;

END LOOP;

Цей цикл забезпечує виконання свого тіла при кожному значенні змінної (параметра, керівної змінної), межі якої визначаються виразами вираз1 .. вираз2. На початку циклу параметр ініціалізується значенням вираз1 і, якщо не задано приріст, то за замовчуванням він збільшується на 1 після кожного виконання. Якщо в заголовку циклу вказано слово REVERSE, то параметр не збільшується, а зменшується. Керівну змінну циклу не обов'язково оголошувати у блоці DECLARE, якщо вона в програмі більше ніде не використовується.

Тіло циклу FOR може мати команду EXIT [WHEN умова_виходу_з_циклу], яка забезпечує передчасне його завершення (подібно до циклу типу LOOP). Крім того, для продовження циклу з нарощеним значенням параметра тіло може містити команду CONTINUE [WHEN умова_продовження_циклу] (подібно до мови С).

Цикл FOR можна використовувати для читання запитів, тоді він має такий вигляд:

FOR змінна_типу_RECORD_або_%ROWTYPE IN запит LOOP

тіло_циклу;

END LOOP;

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

Масиви оголошуються наступним чином:

<Ім'я_масиву> <тип_елемента масиву> array(<початкова довжина>).

Масиви є динамічними, тобто при виході за межі масиву, масив розширюється до необхідної довжини.

4.3 Курсори

Оголошення курсорної змінної. Всі доступи до курсора в Pl/pgSQL проходять через курсорні змінні, які мають спеціальний тип даних refcursor. Один із способів створення курсорної змінної є просто її оголошення як змінної типу refcursor. Інший спосіб - оголошення курсора, яке має такий синтаксис:

ім'я [[ NO ] SCROLL ] CURSOR [( аргументи ) ] FOR запит;

FOR можна замінити на IS для сумісності з Oracle. Якщо SCROLL вказаний, то курсор буде здатний до прокрутки назад; якщо вказано NO SCROLL, то зворотна вибірка буде відхилена, якщо ні одна специфікація не вказана, то дозвіл на читання вибірки назад буде залежати від запиту. Аргументи, перелік яких дається через кому, визначають імена, які повинні бути замінені значеннями параметрів запиту. Фактичні значення для заміни цих імен буде уточнено після відкриття курсора. Приклади:

DECLARE

curs1 REFCURSOR;

curs2 CURSOR FOR SELECT * FROM джерело;

curs3 CURSOR (ключ INTEGER) IS SELECT * FROM джерело WHERE поле = ключ;

Всі ці три змінні мають тип refcursor, але перша може бути використана з будь-яким запитом, друга має зв'язаний з нею запит і, нарешті, - зв'язаний з нею параметризований запит (ключ може бути замінений цілим значенням параметра при відкритті курсора.) Змінна curs1 називається незв'язаною, оскільки вона не прив'язана ні до якого конкретного запиту.

Відкриття курсора. Перед використанням курсора для отримання рядків його необхідно відкрити. Це еквівалентно дії команди SQL DECLARE CURSOR. Pl/pgSQL має три форми відкриття, дві з яких незв'язані з курсорними змінними, а третя використовує зв'язану змінну курсора.

Змінні, пов'язані з курсором, можуть також використовуватися без явного відкриття курсора, через оператор FOR, описаний у попередньому розділі.

Відкритя для запиту (можливе також відкриття для виконання - EXECUTE)

OPEN unbound_cursorvar [[ NO ] SCROLL ] FOR запит;

Змінну курсора буде відкрито і з урахуванням зазначеного запиту. Курсор не може бути відкритим під час оголошення. Він повинен бути оголошений як незв'язана змінна курсора (тобто, як проста змінна refcursor). Запит повинен бути SELECT або інший, який повертає рядки (наприклад, EXPLAIN). Запит PL/pgSQL такий же, як і інші команди SQL: в PL/pgSQL імена змінних замінюються, і план запиту кешується для можливого повторного використання. Варіанти SCROLL і NO SCROLL мають те ж значення, що й для зв'язаного курсора.

Відкриття з'вязаного курсора

OPEN bound_cursorvar [(argument_values)];

Ця форма використовується для відкриття курсора, якого запит був зв'язаний зі змінною під час оголошення. Курсор не може бути відкритий під час оголошення. Список фактичних параметрів - значення аргументів повинно задаватися тільки тоді, коли курсор був оголошений для прийому аргументів. Ці значення будуть замінені у запиті. План запиту для зв'язаного курсора завжди вважається кешованим, у цьому випадку немає еквівалента відкриття для EXECUTE. Тут немає SCROLL і NO SCROLL, поведінка курсора за замовчуванням визначена як прокручування. Заміна змінної робиться як у запиті зв'язаного курсора, є два способи передачі значень у курсор: або з явного аргумента відкриття, чи неявно, посилаючись на змінну PL/pgSQL у запиті (варіант INTO). Однак, тільки змінні, оголошені перед зв'язаним курсором, будуть замінені. В будь-якому випадку значення для передачі визначається у момент відкриття.

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

Команда FETCH:

FETCH [напрямок {FROM | IN}] курсор INTO ціль;

FETCH витягує наступний рядок з курсора в ціль, яка може бути рядком змінних, записом або розділеним комами списком простих змінних, так же як і в SELECT INTO. Як і в SELECT INTO, спеціальна змінна може бути перевірена на наявність - FOUND. Напрямок може бути будь-яким із варіантів дозволених командою SQL FETCH, крім тих, які можуть видавати більше, ніж однин рядок, а саме: NEXT, PRIOR, FIRST, LAST, ABSOLUTE count, RELATIVE count, FORWARD, або BACKWARD. Напрямок NEXT опускається. Переміщення курсора, команда MOVE:

MOVE [напрямок {FROM | IN}] курсор;

MOVE переміщує курсор без отримання будь-яких даних. MOVE працює так же, як у команді FETCH, за винятком того, що курсор не повертає рядок. Як і в SELECT INTO та FETCH, відбувається перевірка чи існує рядок, на який потрібно перейти. Напрямок може бути будь-яким із дозволених у команді SQL FETCH, а саме: NEXT, PRIOR, FIRST, LAST, ABSOLUTE count, RELATIVE count, ALL, FORWARD [count | ALL], або BACKWARD [count | ALL]. Напрямок NEXT приймається за замовчуванням

Редагування таблиці, команда UPDATE / DELETE WHERE CURRENT OF:

UPDATE таблиця SET ... WHERE CURRENT OF курсор;

DELETE FROM таблиця WHERE CURRENT OF курсор;

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

Закритя курсора: CLOSE курсор;

5. Практична частина

Завдання:

Задача 1. Спроектувати та утворити базу даних Palyvo для зберігання та обробки інформації, яка містить такі дані про забезпеченість паливно-мастильними матеріалами:

назва регіону;

назва області;

назва нафтобази;

дата представлення даних;

кількість бензину, тис. т;

кількість дизпалива, тис. т;

кількість машинного масла, тис. т.

Утворити таблиці БД. Рекомендації до побудови таблиць:

Регіон: код регіону, назва регіону;

Область: код регіону, код області, назва області;

Нафтобаза: код області, код нафтобази, назва нафтобази;

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

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

Задача 2. Підготувати і занести в таблиці контрольні дані.

Задача 3. Виготовити запити, де відібрати дані для звітів.

Задача 4. Виготовити звіт про розподіл кількості нафтобаз, областей по категоріях. Навпроти назв областей установити назви нафтобаз, розділених пробілом, установити значення кількості нафтобаз у даній області.

Задача 5. Виготовити запит про розподіл кількості нафтових областей по категоріях. Навпроти назв регіонів і областей установити значення кількості нафтових баз для кожної категорії.

Задача 6. Виготовити звіт з видачею областей, регіонів, нафтобаз, та ресурсів на них, використовуючи курсор.

Задача 7. Утворити запит про вартість палива яке є на нафтобазі. Використовуючи курсори.

Задача 8. Виготовити запити з відомостями про всі нафтобази. Забезпечити відповідний напис для баз про ввивіз палива в листопаді. Використовуючи курсори.

Задача 9. Скласти програму для видачі звіту за умовою задачі 6, використати курсор.

5.1 Завдання

Спроектувати та утворити базу даних Palyvo для зберігання та обробки інформації, яка містить такі дані про забезпеченість паливно-мастильними матеріалами:

назва регіону;

назва області;

назва нафтобази;

дата представлення даних;

кількість бензину, тис. т;

кількість дизпалива, тис. т;

кількість машинного масла, тис. т.

Розв'язок

CREATE TABLE region--створення таблиці регіон

(

kod_reg integer NOT NULL,

n_reg character(80),

CONSTRAINT region_pkey PRIMARY KEY (kod_reg),

CONSTRAINT region_n_reg_key UNIQUE (n_reg)

)

CREATE TABLE oblast --створення таблиці область

(

kod_reg integer,

kod_obl integer NOT NULL,

n_obl character(80),

CONSTRAINT oblast_pkey PRIMARY KEY (kod_obl),

CONSTRAINT oblast_n_obl_key UNIQUE (n_obl)

)

CREATE TABLE baza --створення таблиці база

(

kod_obl integer,

kod_b integer NOT NULL,

n_b character(80),

CONSTRAINT baza_pkey PRIMARY KEY (kod_b),

CONSTRAINT baza_n_b_key UNIQUE (n_b)

)

CREATE TABLE paluvo--створення таблиці паливо

(

kod_b integer NOT NULL,

data date,

kilk_b integer, -- кількість бензину

kilk_d integer, -- кількість дизпалива

kilk_m integer, -- кількість машинного масла

CONSTRAINT paluvo_pkey PRIMARY KEY (kod_b)

)

--створення додаткових обмежень

ALTER TABLE paluvo ADD CONSTRAINT kilk_b CHECK (kilk_b> 0);

ALTER TABLE paluvo ADD CONSTRAINT kilk_d CHECK (kilk_d> 0);

ALTER TABLE paluvo ADD CONSTRAINT kilk_m CHECK (kilk_m> 0);

Результати виконання:

5.2 Завдання

Підготувати і занести в таблиці контрольні дані

Розв'язок задачі

-- Вставляємо контрольні дані в таблицю `Регіони'

insert into region values(1, 'Передкарпатська нафтогазоносна область');

insert into region values(2, 'Днiпровсько-Донецька нафтогазоносна область');

insert into region values(3, 'Причорноморсько-Кримська газонафтоносна впадина');

-- Вставляємо контрольні дані в таблицю `області'

insert into oblast values(1,1,'Івано-Франківська');

insert into oblast values(2,2,'Дніпропетровська');

insert into oblast values(3,3,'Одесська');

insert into oblast values(1,4,'Львівська');

insert into oblast values(2,5,'Донецька');

insert into oblast values(3,6,'Миколаївська');

insert into oblast values(1,7,'Закарпатська');

insert into oblast values(2,8,'Харківська');

insert into oblast values(3,9,'Херсонська');

insert into oblast values(1,10,'Чернівецька');

insert into oblast values(2,11,'Запорізька');

-- Вставляємо контрольні дані в таблицю `бази'

insert into baza values(1,1,'Майдан НБ');

insert into baza values(2,7,'Чернiїв НБ');

insert into baza values(3,12,'Яворів НБ');

insert into baza values(4,11,'Есхар НБ');

insert into baza values(5,13,'Берізка НБ');

insert into baza values(6,5,'Проспект НБ');

insert into baza values(7,3,'Паливо НБ');

insert into baza values(8,4,' Хотин НБ');

insert into baza values(8,10,'Міжгір'я НБ');

insert into baza values(9,6,'Запоріжжя НБ');

insert into baza values(10,2,'Вільно НБ');

insert into baza values(11,8,'Микантил НБ');

insert into baza values(11,9,'Бурманськ НБ');

-- Вставляємо контрольні дані в таблицю `паливо'

insert into paluvo values(1,'01-10-2012',145,250,333);

insert into paluvo values(2,'02-10-2010',123,321,531);

insert into paluvo values(3,'04-10-2005',455,562,145);

insert into paluvo values(4,'07-11-2007',754,125,645);

insert into paluvo values(5,'18-11-2003',874,985,121);

insert into paluvo values(6,'17-10-2008',158,247,833);

insert into paluvo values(7,'13-10-2009',844,347,175);

insert into paluvo values(8,'17-10-2002',559,577,48);

insert into paluvo values(9,'09-11-2002',891,147,614);

insert into paluvo values(10,'28-11-2001',198,549,178);

insert into paluvo values(11,'04-10-2000',189,277,400);

insert into paluvo values(12,'02-10-2008',151,578,844);

insert into paluvo values(13,'03-10-2007',478,562,237);

Результати виконання завдання



Завдання

Виготовити запити, де відібрати дані для звітів.

Задача 3.1

Виготовити запити для перегляду вмісту таблиць зі всіма даними. Розв'язок задачі

SELECT

region.n_reg , oblast.n_obl, baza.n_b, paluvo.data, paluvo.kilk_m, paluvo.kilk_b, paluvo.kilk_d

FROM

paluvo INNER JOIN baza INNER JOIN oblast INNER JOIN region

ON region.kod_reg = oblast.kod_reg

ON oblast.kod_obl = baza.kod_obl

ON baza.kod_b = paluvo.kod_b;

Результати запиту

Задача

Побудувати запит про запаси ресурсів на базах протягом останніх 11 років. Додати назву цього родовища.

Розв'язок задачі

SELECT

baza.n_b,

paluvo.kilk_b,

paluvo.kilk_d,

paluvo.data

FROM baza INNER JOIN paluvo

ON baza.kod_b = paluvo.kod_b

WHERE extract(YEAR FROM current_date) < (extract(YEAR FROM paluvo.data) + 11)

GROUP BY n_b, kilk_b, kilk_d ,data ;

Результатизадачі 3.2



Задача

Виготовити запит з відомостями про родовища: назва регіону, назва області, назва нафтобази, кількість бензину, масла, дизпалива . Вказати, що база бідна, якщо її запаси бензину і масла менше 350 тонн, а дизпалива менше 400 тонн.

Розв'язок задачі

SELECT

region.n_reg, oblast.n_obl, baza.n_b,paluvo.kilk_m,

paluvo.kilk_b, paluvo.kilk_d,

CASE WHEN paluvo.kilk_m<550and paluvo.kilk_b<570 and paluvo.kilk_d<580 THEN 'бідна база' END AS text

FROM

paluvo,baza,oblast,region

WHERE

region.kod_reg = oblast.kod_reg AND

oblast.kod_obl=baza.kod_obl AND

baza.kod_b=paluvo.kod_b

Результати запиту:



5.3 Завдання

Задача 5

Виготовити запит про розподіл кількості нафтових областей по категоріях. Навпроти назв регіонів і областей установити значення кількості нафтових баз для кожної категорії.

Розв'язок задачі

SELECT

oblast.n_obl, region.n_reg, count(baza.kod_b)

FROM

baza INNER JOIN oblast INNER JOIN region

ON region.kod_reg = oblast.kod_reg

ON oblast.kod_obl= baza.kod_obl,

paluvo WHERE paluvo.kod_b = baza.kod_b

GROUP BY region.n_reg, oblast.n_obl;

Результати запиту



Завдання

Задача 8

Виготовити запити з відомостями про всі нафтобази. Забезпечити відповідний напис для баз про ввивіз палива в листопаді. Використовуючи курсори.

Розв'язок задачі

begin;

DECLARE

curs CURSOR FOR

SELECT *, CASE WHEN OR extract(month from paluvo.data)=11

THEN 'Вивіз палива!' END AS vuviz FROM paluvo

INNER JOIN baza ON baza.kod_b=paluvo.kod_b

INNER JOIN oblast ON oblast.kod_obl = baza.kod_obl

INNER JOIN region ON region.kod_reg=oblast.kod_reg;

FETCH 13 FROM curs;



5.4 Завдання

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

Розв'язок задачі

CREATE or REPLACE function zvit4() returns text

AS

$$

DECLARE

rec_o oblast %rowtype;

rec_b baza %rowtype;

rec_r region %rowtype ;

_text text = '';

prev_region region.n_reg %type := '';

prev_b_name baza.n_b %type := '';

_count integer := 0;

BEGIN

FOR rec_r IN SELECT * FROM region ORDER BY region.n_reg

LOOP

FOR rec_o IN SELECT * FROM oblast ORDER BY oblast.n_obl

LOOP

IF rec_r.kod_reg = rec_o.kod_reg

THEN

FOR rec_b IN SELECT * FROM baza

LOOP

IF rec_o.kod_obl = rec_b.kod_obl

THEN

IF rec_r.n_reg != prev_region

THEN

IF rec_b.n_b < prev_b_name

THEN

_text := _text || rpad(rec_o.n_obl, 70, '_') || rec_b.n_b|| E'\n';

END IF;

_count := _count + 1;

ELSE

IF _count > 0

THEN

_text := _text||repeat(' ', 30)||'Кількість баз : '||TO_CHAR(_count, '9')||E'\n';

END IF;

_text := _text||E'\n'||repeat(' ', 30)||' '||rec_r.n_reg||E'\n'||

rpad(rec_o.n_obl, 70, '_')||rec_b.n_b||E'\n';

_count:=1;

END IF;

prev_region := rec_r.n_reg;

prev_b_name := rec_b.n_b;

END IF;

END LOOP;

END IF;

END LOOP;

END LOOP;

_text := _text ||repeat(' ', 30)||'Кількість родовищ : '||TO_CHAR(_count, '9')||E'\n';

return _text;

END;

$$

LANGUAGE plpgsql;

select zvit4();

Результат задачі

Задача. Утворити запит про вартість палива яке є на нафтобазі. Використовуючи курсори.

begin;

DECLARE

curs CURSOR FOR

SELECT oblast.n_obl, baza.n_b, sum(paluvo.kilk_b*10.65+paluvo.kilk_d*6.45+paluvo.kilk_m*2.45),text('грн'),

extract (month FROM data) AS mis, extract (year FROM data) AS rik FROM

paluvo INNER JOIN baza INNER JOIN oblast

ON (oblast.kod_obl=baza.kod_obl)

ON (baza.kod_b=paluvo.kod_b)

GROUP BY oblast.n_obl,baza.n_b, mis, rik

ORDER BY oblast.n_obl;

FETCH 13 FROM curs;

Результат:

Задача. Скласти програму для видачі звіту за умовою задачі 6, використати курсор.

Розв'язок задачі

create or replace function gg() returns TEXT as

$$

DECLARE

-- локальні змінні

reg_name text;oblast_name text;baza_name text;alltxt text; kilk_b integer; kilk_d integer; kilk_m integer;

-- оголошення курсора

cur CURSOR FOR

SELECT

region.n_reg, oblast.n_obl, baza.n_b, paluvo.kilk_b, paluvo.kilk_d, paluvo.kilk_m

FROM

paluvo INNER JOIN baza ON paluvo.kod_b=baza.kod_b

INNER JOIN oblast ON baza.kod_obl=oblast.kod_obl

INNER JOIN region ON oblast.kod_reg=region.kod_reg;

BEGIN

alltxt :=' Область'||' Нафтобаза,'||' Бензину(тонн),'||' Дизпалива(тонн),'||' Машинного масла(тонн),'||'Регіон'||'\n\n';

open cur;

LOOP

FETCH cur INTO reg_name, oblast_name, baza_name, kilk_b, kilk_d, kilk_m;

exit when NOT FOUND;

alltxt := alltxt|| oblast_name||baza_name ||kilk_b ||' '||kilk_d ||' '||kilk_m ||' '||reg_name || '\n';

end loop;

return alltxt;

END;

$$

language plpgsql;

select gg() AS "ZVIT9";

Результат:

6. Висновки

реляційний база даних postgresql

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

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

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

В даному курсовому проекті було розглянуто роботу з реляційними базами даних на прикладі PostgreSQL. Було реалізовано завдання по створенню бази даних для обробки інформації в нафтогазовій області, зокрема отримання відомостей про родовища і поклади нафти на території України, для чого використовувалась як мова запитів SQL, так і мова програмування pl/pgSQL.

7. Список використаної літератури

1. http://www.postgresql.org/docs/

2. Клим Б.В. Конспект лекцій по предмету “Бази даних”

3. В.В. Кириллов Основы проектирования реляционных баз данных. Учебное пособие. - СПб.: ИТМО, 1994. - 90 с.

4. М. Мейер Теория реляционных баз данных. - М.: Мир, 1987. - 608 с.

5. PostgreSQL Reference Manual - Volume 1: SQL Language Reference Ї The PostgreSQL Global Development Group, 2007.

6. Уорсли Дж., Дрейк Дж. PostgreSQL. Для профессионалов. - СПб.: Питер, 2003. - 496с.

Размещено на Allbest.ru