Робота з реляційними базами даних на прикладі PostgreSQL

 

1. Вступ

PostgreSQL - це об'єктно-реляційна система управління базами даних (ORDBMS) заснована на POSTGRES версії 4.2 <#"justify">·успадкування

·типи даних

·функціїналежить до категорії СУБД, відомих як об'єктно-реляційні (object-relation). Зауважимо, що тут є відмінність від тих об'єктно-орієнтованих (object-oriented) СУБД, які в основному підтримують традиційні мови реляційних СУБД. Однак, PostgreSQL має деякі об'єктно-орієнтовані можливості, це важливо в світі реляційних СУБД.

2. Коротка історія PostgreSQL

Об'єктно-реляційна СУБД тепер відома як PostgreSQL (і раніше звана Postgres95) веде своє походження від пакета POSTGRES, який був написаний в департаменті Берклі, Каліфорнійського Університету. Більш ніж десятирічна розробка PostgreSQL зробила цей продукт однією з найбільш потужних СУБД з відкритим вихідним кодом у світі, пропонуючи багатоверсійність управління паралельним доступом, підтримуючи практично всі конструкції SQL (включаючи підзапити, транзакції і визначені користувачем типи і функції) і маючи широкий вибір мов, з допомогою яких можна працювати з СУБД (включаючи C, C + +, Java, Perl, Tcl і Python).

.1 Проект POSTGRES департаменту Берклі

Реалізація реляційної СУБД POSTGRES почалася в 1986. Початкові концепції для цієї системи були представлені в The design of POSTGRES <#"justify">.2 Postgres95

У 1994, Ендрю Ю (Andrew Yu) і Джоллі Чен (Jolly Chen) додали в POSTGRES інтерпретатор мови SQL. Потім Postgres95 був викладений в Інтернет, щоб знайти свій власний шлях у світі продуктів з відкритим вихідним кодом, як нащадок, заснований на оригінальному коді Берклі POSTGRES. Postgres95 був повністю приведений до стандарту ANSI C і скоротив свій розмір на 25%. Були внесено багато внутрішні зміни, які збільшили продуктивність і налагоджуваність коду.Postgres95 версій 1.0.x був швидше на 30-50% згідно Wisconsin Benchmark у порівнянні з POSTGRES, Version 4.2. За винятком виправлення помилок, були зроблені наступні серйозні розширення: Мова запитів PostQUEL була замінена на SQL (реалізований в цьому сервері). Підзапити не підтримувалися аж до виходу PostgreSQL (див. нижче), але в Postgres95 їх можна було зімітувати за допомогою функцій SQL, що визначаються користувачем. Агрегати були переписані. Також в запити була додана підтримка GROUP BY. Інтерфейс libpq залишився доступним для програм на C.

·В додаток до програми monitor, була надана нова програма (psql), яка використовувала бібліотеку GNU Readline і була призначена для інтерактивних SQL запитів.

·Створена нова front-end бібліотека, libpgtcl, що підтримує клієнтів, заснованих на Tcl. Проста оболонка pgtclsh, що надає нові команди Tcl для забезпечення взаімодействіяTcl програм і Postgres95.

·Була ретельно переглянута робота з великими об'єктами. Інверсійні великі об'єкти представляли собою тільки механізм для зберігання великих об'єктів. (Інверсійна файлова система була видалена).

·Разом з вихідним кодом став поставлятися короткий підручник з особливостей роботи з SQL вPostgres95.

·Для побудови проекту став використовуватися GNU make (замість BSD make). Також, Postgres95 був скомпільований зі стандартною версією GCC (вирівнювання даних типу double було виправлено).

2.3. PostgreSQL

У 1996 році було вирішено, що ім'я "Postgres95" не відповідає сьогоденню. Ми вибрали нове ім'я PostgreSQL щоб підкреслити відмінність від оригінального POSTGRES і вихід безлічі версій з підтримкою SQL. У той же час, ми встановили нумерацію версій починаючи з 6.0, повернувшись назад до нумерації, яку почали в проекті Берклі POSTGRES.

При розробці Postgres95 акцент ставився на виявлення і розуміння існуючих проблем в коді продукту. В PostgreSQL акцент змістився на розширення можливостей та сумісності при продовженні роботи в усіх інших областях. Головні зміни в PostgreSQL включають:

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

·Були реалізовані такі важливі можливості, як підзапити, умовчання, примуси і тригери.

·Були додані можливості для сумісності зі стандартом SQL92, включаючи первинні ключі, ідентифікатори запитів, literal string type coercion, створення типів, а також двійковий та шістнадцятковий введення цілих чисел.

·Були поліпшені вбудовані типи даних, включаючи нові широкодіапазонним типи дати / часу і додаткові геометричні типи даних.

·Швидкість роботи backend коду була збільшена приблизно на 20-40%, а час запуску backend'а було скорочено на 80% в порівнянні з версією 6.0.

3. Основні концепції роботи з PostgreSQL

3.1 Створення таблиць

PostgreSQL - це система управління реляційними базами даних (СКБД). Це означає, що це система для управління даними, які зберігаються у вигляді відносин. (В підручниках з СУБД існують і інші терміни, наприклад, кортежі - прим. Пер.). Ставлення - це математичний термін для таблиці. Поняття зберігання даних в таблицях є сьогодні таким банальним, що воно може здатися самоочевидним, однак є кілька інших способів організації баз даних. Файли і каталоги в Unix-подібних операційних системах є прикладом ієрархічної бази даних. Одне з найбільш сучасних напрямків розробки СУБД - це об'єктно-орієнтовані бази даних.

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

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

CREATE [ TEMPORARY | TEMP ] TABLE імя_таблиці (

{ імя_поля тип [ обмеження_поля [...] ] | обмеження таблиці } )

[ INHERITS ( базова_таблиця [ , ... ] ) ] ;

• TEMPORARY | TEMP. Ознака тимчасової таблиці. Таблиця, створена з ключовим словом TEMPORARY або TEMP, автоматично знищується наприкінці поточного сеансу Всі конструкції рівня таблиці (наприклад, індекси і обмеження) знищуються наприкінці сеансу разом з таблицею. Якщо імя тимчасової таблиці співпадає з імям існуючої таблиці, то всі посилання на таблицю з цим імям впродовж сеансу відноситимуться до тимчасової таблиці. Іноді це викликає проблеми, оскільки тимчасова таблиця побічно заміщає існуючу таблицю в контексті поточного сеансу до моменту її знищення.
• таблиця. Імя створюваної таблиці.
• поле. Імя поля в новій таблиці. Імена полів перераховуються в круглих дужках і розділяються комами.
• тип. Відразу ж після імені користувача задається його тип - стандартний тип або масив одного із стандартних типів.
• обмеження_поля.
• базова_таблиця. Імя таблиці, від якої нова таблиця успадковує поля. Якщо імена успадкованих полів збігаються з іменами полів, раніше включених в структуру таблиці, PostgreSQL видає повідомлення про помилку і перериває виконання команди.

.1.1 Типи даних

Числові типиsmallintкоротке 2-х байтовое цілеinteger звичайне 4-х байтовое цілеbigintвелике 8-байтовое цілеdecimalкоротке із фіксованою точкоюnumericкоротке із фіксованою точкоюrealкоротке із рухомою точкоюdouble precisionкоротке із рухомою точкою подвійної точностіserialціле із автозбільшеннямbigserialвелике ціле із автозбільшеннямГрошові типиmoneyгроші

Символьні типиcharacter varying(n), varchar(n)рядок змінної довжиниcharacter(n), char(n)рядок фіксованої довжиниtextрядок змінної необмеженої довжини

Бінарні типыbyteaбінарний рядок змінної довжини

Дата і часtimestamp [ (p) ] [ без часового пояса ]дата і часtimestamp [ (p) ] с часовым поясомдата і час із часовим поясомinterval [ (p) ]Інтервал часуdateтільки датаtime [ (p) ] [ без часового пояса ]тільки часtime [ (p) ] с часовым поясомтільки час із часовим поясом

Логічні типиbooleanTRUE або FALSE

Геометричні типиpointТочка на площині (x,y)lineНевидима лінія (не повністю реалізовано)lsegВидимий відрізок ((x1,y1),(x2,y2))boxЧотирикутник ((x1,y1),(x2,y2))pathЗамкнений прямокутник ((x1,y1),...)pathЛамана лінія [(x1,y1),...]polygonПолігон ((x1,y1),...)circleКруг (x,y),r (центр і радіус)Типи для адрес компютерних мережcidrIPv4 або IPv6 мережаinetIPv4 або IPv6 хост і мережаmacaddrMAC адреса

Бітові рядкиbit [ (n) ]Бітовий рядок фіксованої довжиниbit varying [ (n) ]Бітовий рядок змінної довжини

Типи для пошуку текстуtsqueryЗапит на пошук текстуtsvectorСписок для пошуку тексту

UUID типuuidУніверсальний унікальний ідентифікатор

XML типыxmlданні XML(80) задає тип даних, який може зберігати символьні рядки довжиною до 80 символів. int - це звичайний цілочисельний тип. real - це тип даних, що зберігає числа з плаваючою точкою одинарної точності. Тип date говорить сам за себе. (Цілком вірно, поле з типом date так і називається дата. Зручно це чи ні - вирішувати вам.)підтримує таки корисні типи SQL як int, smallint, real, double precision, char (N), varchar (N), date, time, timestamp і interval, а також і інші загальні типи і багатий набір геометричних типів. PostgreSQL можна налаштувати так, щоб він працював з довільним числом типів даних, визначених користувачем. Отже, імена типів не є синтаксичними ключовими словами, за виключаємо тих випадків, де потрібні підтримка спеціально згідно стандартуSQL.

3.2 Внесення даних у таблицю

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

INSERT INTO имя_таблиці [(список_імен_полів)] VALUES (значення1, значення2, …, значення n);

Для завантаження великої кількості даних з простого текстового файлу, ви також можете використовувати команду COPY. Зазвичай це працює швидше, тому що команда COPY оптимізована для операції, яку вона виконує, але в той же час вона менш гнучка ніж команда INSERT. Ось приклад еЈ використання:

COPY weather FROM '/home/user/weather.txt';

де файл, вказаний як джерело даних повинен бути доступний на машині з backend сервером, а не на клієнтській машині, тому що backend сервер читає цей файл безпосередньо. Ви можете прочитати подробиці про команду COPY в PostgreSQL Reference Manual.

.3 Редагування таблиць

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

·зміна даних, тобто редагування вмісту полів;

·маніпуляція записами, їх вилучення і вставка;

·перейменування та знищення таблиці;

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

Змінити дані таблиці дозволяє команда:

UPDATE імя_таблиці SET імя_поля1=значення1, імя_поля2 = значення2, ... WHERE вираз

Приклад. Скласти команду для заміни в таблиці gazpr поля naz_g на Україна тих записів, де kod_g = 2:

UPDATE gazpr SET naz_g = Україна WHERE kod_g = 2;

Якщо редагуються всі записи, то інструкція WHERE може бути опущена. Для нашого прикладу така команда матиме такий вигляд:

UPDATE gazpr SET naz_g = Україна;

Знищення всіх або вибраних за умовою записів таблиці забезпечує команда:

DЕLETE FROM імя_таблиці WHERE вираз

Приклад. Знищити ті записи таблиці gazpr, яких naz_g = Україна.

DELETE FROM gazpr WHERE gazpr.naz_g = 'Україна';

Вставку записів забезпечує вже розглянена команда INSERT.

Модифікація таблиці командою ALTER TABLE. У більшості сучасних РСУБД передбачена можливість модифікації таблиць командою ALTER TABLE. Її реалізація в PostgreSQL забезпечує такі види модифікації:

·створення полів;

·призначення і скасування значень за умовчанням;

·перейменування таблиці;

·перейменування полів;

·додавання обмежень;

·зміна власника.

Додавання полів. Для створення нового поля в команду ALTER TABLE включається секція ADD COLUMN. Синтаксис команди ALTER TABLE з секцією ADD COLUMN:

ALTER TABLE імя_таблиці ADD [COLUMN] імя_поля тип_поля

·таблиця - імя таблиці, в якій створюється нове поле;

·імя_поля - імя нового поля;

·тип_поля - тип нового поля.

Ключове слово COLUMN не є обовязковим і включається в команду лише для наочності. Приклад включення в таблицю books нового поля publication для зберігання дати публікації:

ALTER TABLE books ADD publication date;

Призначення і скасування значень за замовчуванням

ALTER TABLE таблиця ALTER [COLUMN] імя_поля

{SET DEFAULT значення | DROP DEFAULT}

Як і в попередньому розділі, ключове слово COLUMN є необовязковим і включається в команду лише для наочності. Нижче наведено приклади встановлення та скасування послідовності значень за замовчуванням для поля id таблиці books.

ALTER TABLE books ALTER COLUMN id SET DEFAULT nextval ('books.id');

ALTER TABLE books ALTER id DROP DEFAULT;

Перейменування таблиці забезпечує команда:

ALTER TABLE таблиця RENAME ТО нове_імя

Таблицю можна перейменовувати скільки завгодно разів, це ніяк не відбивається на стані зберігаються в ній даних. Зрозуміло, що в деяких ситуаціях перейменування небажані, зокрема, якщо таблиця використовується зовнішнім додатком. Приклад зміни імені literature на books:

ALTER TABLE literature RENAME TO books;

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

ALTER TABLE таблиця RENAME [COLUMN] імя_поля ТО нове_імя_поля

Як і в інших командах ALTER TABLE, ключове слово COLUMN є необовязковим. По двох ідентифікаторах, розділеним ключовим словом ТО, PostgreSQL може визначити, що команда перейменування відноситься до одного поля, а не до таблиці. Приклад:

ALTER TABLE daily RENAME COLUMN in_stock TO is_in_stock:

Додавання обмежень. Після створення таблиці зберігаються деякі можливості додавання обмеження. У PostgreSQL команда ALTER TABLE з секцією ADD CONSTRAINT дозволяє визначати для полів існуючих таблиць тільки обмеження зовнішнього ключа. Команда створення нових обмежень має такий синтаксис:

ALTER TABLE таблиця ADD CONSTRAINT імя обмеження визначення

Синтаксис визначення залежить від типу обмеження. Нижче показано створення нового обмеження зовнішнього ключа для таблиці editions (повязаної з полем id таблиці books) і обмеження перевірки для поля type.

ALTER TABLE editions

ADD CONSTRAINT foreign_book FOREIGN KEY (bookjd) REFERENCES books (id);

ALTER TABLE editionsCONSTRAINT hard_or_paper_back CHECK (type = 'p1 OR type =' h ');

Установка обмеження зовнішнього ключа призводить до того, що будь-яке значення book_id у таблиці editions також має існувати і в таблиці books. Крім того, внаслідок встановленого обмеження перевірки полі type в таблиці editions може містити тільки значення р або b.

Зауважимо, що при створенні унікального індекса командою CREATE INDEX (буде розглянена у відповідному розділі) також неявно встановлюється і обмеження унікальності.

В існуючу таблицю можна додати тільки обмеження таблиць. Це робить така команда:

ALTER TABLE імя_таблиці

ADD [ CONSTRAINT імя_та_визначення_обмеження ]

Команда ALTER додає обмеження таблиці, але не поля. Нижче встановлюється нове обмеження FOREIGN KEY для поля kod_g таблиці gazpr, яке звязується з полем kod_g таблиці oblik. Обмеження FOREIGN KEY гарантує, що внаслідок вставки або оновлення даних у поле kod_g таблиці oblik не зявляться значення, відсутні в полі kod_g таблиці gazpr, тому у прикладі система видала відповідне повідомлення про помилку, тобто про те, що в таблиці gazpr не існує газопроводу з кодом 7.

ALTER TABLE gazpr ADD CONSTRAINT bbb PRIMARY KEY (kod_g);

ALTER TABLE oblik ADD CONSTRAINT aaaKEY (kod_g) REFERENCES gazpr(kod_g);INTO OBLIK VALUES(4,4,'21.12.2000',4);INTO OBLIK VALUES(7,4,'21.12.2000',4);: insert or update on table "oblik" violates foreign key constraint "aaa": Key (kod_g)=(7) is not present in table "gazpr". state: 23503

Зауважимо, що обмеження унікальності також неявно встановлюється при створенні унікального індекса командою CREATE INDEX.

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

Зміна власника. За замовчуванням творець таблиці автоматично стає її власником. Власник володіє всіма правами, повязаними з таблицею, в тому числі правами передання і відкликання прав командами GRANT і REVOKE (будуть розглянені пізніше у відповідному розділі). Зміна власника проводиться командою ALTER TABLE з секцією OWNER. Команда має такий синтаксис:

ALTER TABLE імя_таблиці OWNER ТО новий_власник

Змінювати власника таблиці може або поточний власник, або суперкористувач. Приклад зміни власника таблиці employee, де новим власником стає користувач corwin:

ALTER TABLE employees OWNER TO corwin;

Реструктуризація таблиць командами CREATE TABLE і INSERT INTO. Якщо таблиця, створена командою CREATE TABLE AS, не влаштовує (наприклад, якщо в таблиці необхідно встановити обмеження полів), то одну команду CREATE TABLE AS можна замінити двома командами SQL. Спочатку команда CREATE TABLE створює нову таблицю, а потім команда INSERT INTO з запрограмованим SELECT заповнює її даними. Видалення таблиць забезпечує команда:

DROP TABLE таблиця

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

Копіювання даних із зовнішніх файлів командою COPY. У PostgreSQL підтримується і така корисна можливість, як пряме імпортування даних в таблицю з зовнішніх файлів командою COPY. Файл, з якого беруться дані, зберігається або в стандартному текстовому форматі ASCII з обмеженням полів спеціальним символом-роздільником, або в двійковому форматі таблиць PostgreSQL. У ASCII-файлах як роздільник зазвичай використовується символ табуляції або кома. При імпортуванні данних з ASCII-файлу кожен рядок файлу інтерпретується як окремий запис даних, а кожен компонент рядка - як значення відповідного поля запису.

Команда COPY FROM працює значно швидше за звичайну команду INSERT, оскільки дані передаються прямо в приймальну таблицю за одну транзакцію. З іншого боку, до формату вихідного файлу пред'являються надзвичайно жорсткі вимоги, тому помилка всього в одному рядку спричинить до збою всієї команди COPY. Синтаксис команди COPY FROM:

COPY [BINARY] таблиця [WITH 0IDS]

FROM {імя_файлу | stdin}

[[USING] DELIMITERS роздільник]

[WITH NULL AS рядок_null]

Параметри команди:

-BINARY. Ознака імпортування вхідних даних із двійкового файлу, раніше створеного командою COPY ТО;

-таблиця. Імя таблиці, в яку імпортуються дані;

-WITH 0IDS. З першого рядка файлу завантажуються значення всіх ідентифікаторів OID імпортованої таблиці;

-FROM {імя_файлу | stdin}. Джерело, з якого PostgreSQL отримує вхідні дані, це може бути файл із заданим імям або стандартний ввід (stdin);

-[USING] DELIMITERS роздільник. Символ, який використовується в якості розділювача при розборі вхідних даних. Не використовується для файлів, виведених у двійковому форматі PostgreSQL;

-WITH NULL AS рядок null. Заданий рядок повинен інтерпретуватися як значення NULL. Не використовується для файлів, виведених у двійковому форматі PostgreSQL.

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

При роботі з вхідними файлами в форматі ASCII в секції DELIMITERS передається символ, використаний як роздільник значень полів у рядках файлу. Якщо роздільник не вказаний, PostgreSQL вважає, що значення розділюються символом табуляції. Необовязкова секція WITH NULL визначає формат, в якому передається значення NULL. Якщо секція відсутня, то PostgreSQL інтерпретує послідовність \N як NULL (наприклад, порожні поля вихідного файлу за замовчуванням інтерпретуються як порожні рядкові константи, а не як NULL).

Якщо дані вводяться вручну або передаються терміналу іншою програмою, в якості джерела в секції FROM можна вказати стандартний файл (stdin). При отриманні даних з стандартного вводу вхідний потік повинен завершуватися послідовністю \. (зворотна коса риска плюс точка), зразу за якою слідує символ нового рядка.

Нижче приведено вміст файлу, виведеного PostgreSQL в форматі ASCII. Поля (3 шт.) розділяються комами, а для відображення значення NULL використовується рядок \nul1. У файлі збережені дані з таблиці subjects.

1,Наука,Productivity Ave

2,Релігія, \null

3,Класика,Academic Rd

4,Компютери,Productivity Ave

5,Cooking,Creativity St

Двійковий формат. Команда COPY також дозволяє виконувати операції введення і виведення з даними в двійковому форматі. Якщо команда COPY FROM містить ключове слово BINARY, то вхідний файл повинен бути створений командою COPY ТО в двійковому форматі PostgreSQL. Двійкові файли завантажуються швидше за ASCII-файли, але на відміну від останніх їх не можна читати і редагувати в простих текстових редакторах.

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

Поле Описoid4-байтовий унікальний ідентифікатор записуtableoidІдентифікатор таблиці. Імя таблиці звязується з ідентифікатором у системній таблиці pg_classxminІдентифікатор транзакції вставки для кортежуcminІдентифікатор команди, асоційованої з транзакцією вставки для кортежухmахІдентифікатор транзакції видалення для кортежу. Для видимих (не видалених) кортежів дорівнює нулюсmахІдентифікатор команди, асоційованої з транзакцією видалення для кортежу. За аналогією з xmax дорівнює нулю для видимих кортежівctidІдентифікатор, що описує фізичне місцезнаходження кортежу в БД. Поле ctid містить пару чисел: номер блоку та індекс кортежу в блоці

Ідентифікатори записів дозволяють розрізнити два записи з однаковими значеннями полів. Для цього в PostgreSQL передбачений ідентифікатор OID, унікальний в межах таблиці. Приклад:*, oid FROM my_list;

Знищення таблиці виконує команда:

DROP TABLE tablename;

.4 Обмеження

PostgreSQL має декілька варіантів обмеження даних (constraint), які впливають на операції вставки і оновлення. Розглянемо один із них, який полягає в установці обмежень для таблиць і полів. Обмеженням є особливий атрибут таблиці, який встановлює критерії допустимості для вмісту її полів. Дотримання цих правил допомагає запобігти заповненню бази помилковими або невідповідними даними. Обмеження задаються в секції CONSTRAINT при створенні таблиці командою CREATE TABLE. Обмеження полів завжди стосуються лише одного поля, тоді як обмеження таблиць можуть встановлюватися як для одного, так і для декількох полів. У команді CREATE TABLE обмеження полів задаються відразу ж після визначення поля, тоді як обмеження таблиці встановлюється в спеціальному блоці, виділеному комами від усіх визначень полів.

Опис обмеження поля виглядає так:

[ CONSTRAINT обмеження]

{ NOT NULL| UNIQUE | PRIMARY KEY | DEFAULT значення CHECK (умова )| таблиця [ ( поле) ]

[ MATCH FULL | MATCH PARTIAL ]

[ ON DELETE операція]

[ ON UPDATE операція]

[ DEFERRABLE | NOT DEFERRABLE ]

[ INITIALLY DEFERRED | INITIALLY IMMEDIATE ]}

Визначення обмеження слідує в команді CREATE TABLE відразу ж за типом обмежуваного поля і передує комі, яка відокремлює його від наступного поля. Обмеження можуть встановлюватися для будь-якої кількості полів, а ключове слово CONSTRAINT і ідентифікатор обмеження не обовязкові.

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

• NOT NULL. Поле не може містити псевдозначення NULL. Обмеження NOT NULL еквівалентно обмеженню CHECK (поле NOT NULL);
• UNIQUE. Поле не може містити значення, які повторюються. Слід враховувати, що обмеження UNIQUE допускає багатократне входження псевдозначень NULL, оскільки формально NULL не збігається ні з яким іншим значенням;
• PRIMARY KEY. Автоматично встановлює обмеження UNIQUE і NOT NULL, а для заданого поля створюється індекс. У таблиці може встановлюватися тільки одне обмеження первинного ключа;
• DEFAULT значення. Пропущені значення поля замінюються заданою величиною. Тип значення за замовчуванням повинно відповідати типу поля;
• CHECK умова. Команда INSERT або UPDATE завершується успішно лише при виконанні заданої умови (виразу, що повертає логічний результат). При установці обмеження поля в секції CHECK може використовуватися тільки поле, для якого встановлюється обмеження;
• REFERENCES. Це обмеження складається з таких секцій:
• REFERENCES таблиця [(поле)]. Вхідні значення обмежуваного поля порівнюються із значеннями іншого поля в заданій таблиці. Якщо збіги відсутні, то команда INSERT або UPDATE завершується невдачею. Якщо параметр поле не вказаний, то перевірка виконується за первинним ключем. Обмеження REFERENCES подібне до обмеження таблиці FOREIGN KEY, яке описане в наступному пункті цього підрозділу. Дійсно, між цими обмеженнями є багато спільного. Приклад таблиці, створеної з обмеженням FOREIGN KEY, приведений у лістингу 7.8;
• MATCH FULL | MATCH PARTIAL. Секція MATCH указує, чи дозволяється змішувати значення NULL і звичайні значення при вставці в таблицю, у якої зовнішній ключ посилається на декілька полів. Таким чином, на практиці секція MATCH приносить користь лише в обмеженнях таблиць, хоча формально вона може використовуватися і при обмеженні полів. Конструкція MATCH FULL забороняє вставку даних, у яких частина полів зовнішнього ключа містить псевдозначення NULL (крім випадку, коли NULL міститься у всіх полях). У PostgreSQL 7.1.x конструкція MATCH PARTIAL не підтримується. Якщо секція MATCH відсутня, то вважається, що поля з псевдозначепиями NULL задовільняють обмеження. Також буде доречно нагадати, що обмеження полів стосується лише одного поля, тому секція MATCH використовується лише в обмеженнях таблиць;
• ON DELETE операція. При виконанні команди DELETE для заданої таблиці з обмежуваним полем виконується одна з таких операцій:

N0 ACTION (якщо видалення спричиняє порушення цілісності посилань, то відбувається помилка; використовується за замовчуванням, якщо операція не вказана),

RESTRICT (аналогічно N0 ACTION),

CASCADE (видалення всіх записів, що містять посилання на запис, який видаляється),

SET NULL (поля, що містять посилання на запис, який видаляється, замінюються псевдозначеннями NULL),

SET DEFAULT (полям, що містять посилання на запис, який видаляється, привласнюється значення за замовчуванням);

1. ON UPDATE операція. При виконанні команди UPDATE для заданої таблиці виконується одна з вищеописаних операцій. За замовчуванням використовується значення N0 ACTION. Якщо вибрана операція CASCADE, всі записи, які містять посилання на оновлюваний запис, оновлюються новим значенням (замість видалення, як у випадку з ON DELETE CASCADE);
2. DEFERRABLE | NOT DEFERRABLE. Значення DEFERRABLE дозволяє відкласти виконання обмеження до кінця транзакції (замість негайного виконання після завершення команди). Значення NOT DEFERRABLE означає, що обмеження завжди перевіряється відразу ж після завершення чергової команди. В цьому випадку користувач не може відкласти перевірку обмеження до кінця транзакції. За замовчуванням вибирається саме цей варіант;
3. INITIALLY DEFERRED | INITIALLY IMMEDIATE. Секція INITIALLY задається лише для обмежень, визначених з ключовим словом DEFERRED. Значення INITIALLY DEFERRED відкладає перевірку обмеження до кінця транзакції, а при установці значення INITIALLY IMMEDIATE перевірка проводиться після кожної команди. За відсутності секції INITIALLY за замовчуванням використовується значення INITIALLY IMMEDIATE.

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

Зауважимо, що деякі версії PostgreSQL забезпечують лише додавання обмежень CHECK і FOREIGN KEY.

Вибір режиму перевірки обмежень забезпечує команда:

SET CONSTRAINTS { ALL | режим [.... ] } { DEFERRED | IMMEDIATE }

Параметри:означає, що вказаний режим повинен стосуватися всіх обмежень;

режим - імя обмеження, для якого встановлюється режим перевірки;

DEFERRED - перевірка обмежень (або конкретного обмеження) вікладається до

момента фіксації транзакції, тобто до виконання команди COMMIT;

IMMEDIATE - всі або конкретне обмеження перевіряються наприкінці кожної команди.

Команда SET CONSTRAINTS задає режим перевірки для всіх обмежень або одного обмеження в поточному транзакційному блоці. Існує два режими перевірки: негайна (IMMEDIATE) і відкладена (DEFERRED) перевірка обмежень. У режимі IMMEDIATE всі обмеження перевіряються після виконання кожної команди транзакції, а в режимі DEFERRED обмеження перевіряються лише після виконання команди COMMIT.

Зауважимо, що деякі версії PostgreSQL забезпечують зміну режиму перевірки тільки для обмеження FOREIGN KEY. Команда SET CONSTRAINTS не разповсюджується на обмеження CHECK і UNIQUE

Нижченаведена команда вибирає режим негайної перевірки всіх обмежень.

SET CONSTRAINTS ALL IMMEDIATE;

3.5 Послідовності

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

Послідовність (sequence) являє собою послідовність чисел. Значення послідовності мають тип integer, тому її значення повинні лежати в межах від 2 147 483 647 до -2 147 483 647. Як правило, її використовують у обмеженні DEFAULT для надання унікальних значень полям таблиць. Ці значення автоматично змінюються шляхом виклику функції nextval(). У інших СУБД, наприклад MS Access, послідовності називають лічильниками.

Послідовність створюється командою:

CREATE SEQUENCE послідовність

[ INCREMENT приріст] [ MINVALUE мінімум ] [ MAXVALUE максимум ] [ START початок ] [ CACHE кеш ] [ CYCLE ]

Тут:

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

-INCREMENT приріст - нарощення поточного значення послідовності, це число може бути відємним або додатнім. За замовчуванням приріст дорівнює 1.

-MINVALUE мінімумум - мінімально допустиме значення. Спроба зменшити поточне значення нижче за заданий мінімум спричинить помилку або циклічний перехід до максимального значення (якщо послідовність створювалася з ключовим словом CYCLE). За замовчуванням мінімальне значення дорівнює 1;

-MAXVALUE максимум - максимально допустиме значення послідовності. Спроба перевищити заданий максимум спричинить помилку або циклічний перехід до мінімального значення (при CYCLE). За замовчуванням максимальне значення дорівнює 2 147 483 647;

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

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

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

У нижченаведеному прикладі створюється послідовність з імям ship, яка починається із значення 0 і збільшується на 1 до тих пір, поки не досягне максимального значення. Ключове слово CYCLE не вказане, тому ця послідовність набуває лише унікальних значень.

CREATE SEQUENCE ship MINVALUE 0;

До послідовності також можна звернутися командою SELECT, як до таблиці, хоча така можливість використовується відносно рідко, наприклад, під час випробування або перевірки послідовності. При складанні запиту до послідовності в списку вибірки вказуються її атрибути, перелічені в таблиці. 5.1.

Таблиця 5.1. Атрибути послідовності

АтрибутТипПриміткаsequence_namenameІмя послідовностіlast_valueintegerПоточне значенняincrement_byintegerНарощенняmax_valueintegerМаксимальне значенняmin_valueintegerМінімальне значенняcache_valueintegerОбєм кеша, байтlog_cntintegeris_cycled"char"is_called"char"

Нижче показано запит до послідовності ship. Він повертає атрибути last_value (поточне значення, оскільки послідовність щойно створена, то воно мінімально задане і дорівнює 0) та increment_by (приріст, за замовчуванням дорівнює 1).

SELECT last_value, increment_by FROM ship;

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

-nextval( послідовність) - нарощує поточне і повертає нове значення;

-currval(послідовність) - повертає поточне значення;

-setval (послідовність, n) - змінює поточне значення на число n.

Нижче в прикладі виводиться пара чергових значень послідовності ship.

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

Видалення послідовності забезпечує команда SQL DROP SEQUENCE, вона видаляє одну або декілька послідовностей одночасно. Ця команда має такий вигляд:

DROP SEQUENCE перелік_послідовностей

Приклад видалення послідовності ship: DROP SEQUENCE ship;

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

SELECT p.relname, a.adsrc FROM pg_class p JOIN pg_attrdef a ON (p.relfilenode = a.adrelid) WHERE a.adsrc ~ '"sequence_name?"';

3.6 Запити

Центральнемісце в SQL займає команда SELECT, призначена для побудови запитів та вибірки даних з таблиць. Дані, повернені в результаті запиту, називаються підсумковим набором. Дані підсумкового набору не зберігаються на диску в постійній формі. Підсумковий набір є лише тимчасовим поданням даних, отриманих в результаті запиту.

У найпростішому випадку команда SELECT вибирає з заданої таблиці всі дані. Повна вибірка даних проводиться командою SELECT * FROM імя_таблиці.

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

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

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

Загальний варіант команди SELECT:

SELECT список імен полів запиту FROM імя джерела

INNER JOIN імя звязаного джерела ON імя поля = імя звязаного поля

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

Тут службові слова SELECT і FROM - обовязкові.

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

Секція GROUP BY дозволяє групувати записи за заданим критерієм (наприклад, підсумувати кількість транспортованого газу для кожного газопроводу).

Як бачимо, запит може містити вирази. До складу виразу можуть входити:

-константи;

-оператори;

-імена полів і таблиць;

-функції;

-фігурні, квадратні і круглі дужки.

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

Оператори зазвичайно містять від одного до чотирьох символів. Слід зауважити, що деякі з них існують тільки у версії SQL для PostgreSQL і можуть не бути в інших СУБД. Обєднання двох рядкових констант у один виконується оператором конкатенації (||), наприклад, так:SELECT 'Ра' || 'кета' AS example;

Крім, власне демонстрації злиття двох рядків, у цьому прикладі показано застосування альтернативного імені example, яке тут дозволяє підвищити наглядність результату.

Строкові оператори служать для порівняння двох рядків (або чисел), вони такі:

ОператорОпис=Повертає True, якщо перший рядок точно збігається з другим! =Повертає True, якщо перший рядок не збігається з другим<>Ідентичний оператору ! =<Повертає True, якщо перший рядок передує другому<=Повертає True, якщо перший рядок передує другому або їх значення співпадають>Повертає True, якщо другий рядок передує першому>=Повертає True, якщо другий рядок передує першому або їх значення співпадають

Оператори регулярних виразів розширюють можливості строкових операторів, вони такі:

~ - перевіряє (видає true, якщо так) чи в рядку існує збіг для регулярного виразу;

!~ - перевіряє чи в рядку немає збігу для регулярного виразу;

~* - перевіряє чи в рядку існує збіг без урахування регістру символів;

!~* - перевіряє чи в рядку немає збігу без урахування регістру символів.

Регулярний вираз може містити такі метасимволи (спеціальні символи):

^ - з початку рядка;

$ - наприкінці рядка;

. - один символ;

[ ] - будь-який із символів, перелічених у квадратних дужках;

[^] - будь-який символ, крім перелічених у квадратних дужках;

[-] - будь-який символ з інтервалу, заданого в квадратних дужках;

[^-] - будь-який символ, крім символів з інтервалу, заданого в квадратних дужках;

? - один примірник попереднього символа або підвиразу (якщо, звичайно, він є) ;

* - довільна кількість примірників попереднього символа або підвиразу;

+ - один і більше примірників попереднього символа або підвиразу;

| - лівий або правий підвираз;

( ) - групування підвиразів з явним визначенням пріоритету операцій;

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

?всіх значень поля name таблиці authors, які починаються з літер А або Т:

SELECT name FROM authors WHERE name ~ '^A|^T';

?всіх значень поля title тих записів таблиці books, де зустрічається слово Кобзар:

SELECT title FROM books WHERE title ~*'Кобзар';

?всіх значень, які починаються (^) на Кобзар без врахування регістра (*), а далі через довільне число символів (.) містять слово малий або (|) повний, написане лише малими буквами (перед ними немає зірочки):

SELECT title FROM books WHERE title ~* '^ Кобзар.( малий | повний)';

?всіх значень, які починаються (^) на К без врахування регістра (*), а далі через довільне число символів (.) зустрічається якась буква з переліку ([]) о та м, наступним символом після яких зразу ж стоїть буква к, або (|) видати всі ті значення, які закінчуються ($) на ий або (|) на не:

SELECT title FROM books WHERE title ~* '(^К.*[о,м]к)|(ий$|не$)';

Математичні оператори використовуються в числових виразах, вони такі:

а + bПідсумовування чисел а і bа - bВіднімання числа b від аа * bМноження числа а на bа / bДілення числа а на bа % bЗалишок від ділення а на bа ^ bПіднесення а до степеня b|/ аКвадратний корінь з а||/ аКубічний корінь з aа!Факторіал а!!аФакторіал а (відрізняється від постфіксного оператора тільки розташуванням аргумента а)@аМодуль (абсолютне значення) а

Приклад застосування операції множення: видати коди газопроводів та кількість транспортованого газу за весь період обліку, збільшену в 1.6 разів (так начислюється премія персоналу, який обслуговує газопровід) і названу premia, взяті з таблиці oblik:

SELECT kod_g, (1.6 * kilk)::numeric(8, 2) AS premia FROM oblik;

Математичні оператори порівняння такі ж, як вищерозглянені строкові.

Ключове слово BETWEEN (також іноді називається оператором) дозволяє перевірити, чи входить значення в деякий інтервал. Наприклад, нищенаведена команда SELECT видає книги, ціна яких знаходиться в інтервалі між 10 та 17:

SELECT naz_kniga FROM kniga WHERE cina BETWEEN 10 AND 17;

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

SELECT bittoint4(b'1000' >> 2); Пріоритет операторів SQL (у порядку спадання):

ОператорОпис::Явне перетворення типу[ ]Індексація елемента масиву.Крапка , роздільник імен таблиці і поля-Унарний мінус^Піднесення до степеня* / %Множення, ділення і залишок+ -Додавання і відніманняIS значенняПеревірка наявності заданого значенняIS NULLПеревірка наявності значення NULLIS NOT NULLПеревірка розбіжності з NULLIN перелікЗадання набору значеньBETWEENПеревірка приналежності до інтервалуLIKE ILIKEВибір за зразком<> <= >= =Оператори порівнянняAND ОR NOTЛогічні оператори

3.7 Об'єднання таблиць (Join)

Ми бачимо, що наші запити використовують в тільки одну таблицю. Але запити можуть одночасно звертатися до декількох таблиць або до в один і той же час до тієї ж таблиці але з іншим способом обробки довільних записів. Запит, який звертається до кількох записів однієї таблиці або до декількох таблиць одночасно, називається join запитом (об'єднанням). Як приклад, скажімо ви хочете подивитися всі записи про погоду разом з тими містами, до яких вони належать. Щоб це зробити, нам потрібно порівняти поле city кожного запису про погоду в таблиці погоди weather з ім'ям поля всіх записів в таблиці міст cities, і вибрати пари записів, де ці значення збігаються.

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

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

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

avg(вираз) - середнє арифметичне значення;(вираз) - кількість записів зі значенням відмінним від NULL;ах(вираз) - максимальне значення;(вираз) - мінімальне значення;(вираз) - середньоквадратичне відхилення;(вираз) - сума;аnce(вираз) - дисперсія.

3.9 Курсори SQL

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

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

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

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

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

DECLARE курсор [ BINARY ] [ INSENSITIVE ] [ SCROLL ] FOR запит

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

Тут:

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

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

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

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

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

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

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

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

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

BEGIN;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;NEXT FROM gazprovid; 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;PRIOR IN gazprovid; -- на 1 назадNEXT IN gazprovid; -- на 1 вперед2 IN gazprovid; -- те ж, що NEXT 2

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

: 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. Загальний вигляд програмного блока:LANGUAGE мова_програмування;OR REPLACE FUNCTION імя_функції (перелік_типів_аргументів)тип_поверненого_значення$$ DECLARE оголошення даних; BEGIN команди; END; $$'мова_програмування';

Коментарі мови 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:INTO перелік_імен_змінних перелік_імен_полів

FROM джерело

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

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

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

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

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

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

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

IF умова THEN команда; ELSE команда; END 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 містить умову свого завершення, він виглядає так:

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

команди (тіло_циклу);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 працює так же, як у команді 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:

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

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

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

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

Завдання:

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

1. назва регіону;
2. назва області;
3. назва нафтобази;
4. дата представлення даних;
5. кількість бензину, тис. т;
6. кількість дизпалива, тис. т;
7. кількість машинного масла, тис. т.

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

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

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

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

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

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

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

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

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

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

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

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

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

5.1 Завдання

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

1. назва регіону;
2. назва області;
3. назва нафтобази;
4. дата представлення даних;
5. кількість бензину, тис. т;
6. кількість дизпалива, тис. т;
7. кількість машинного масла, тис. т.

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

(_reg integer NOT NULL,_reg character(80),region_pkey PRIMARY KEY (kod_reg),region_n_reg_key UNIQUE (n_reg)

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

(_reg integer,_obl integer NOT NULL,_obl character(80),oblast_pkey PRIMARY KEY (kod_obl),oblast_n_obl_key UNIQUE (n_obl)

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

(_obl integer,_b integer NOT NULL,_b character(80),baza_pkey PRIMARY KEY (kod_b),baza_n_b_key UNIQUE (n_b)

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

(_b integer NOT NULL,date,_b integer, -- кількість бензину_d integer, -- кількість дизпалива_m integer, -- кількість машинного маслаpaluvo_pkey PRIMARY KEY (kod_b)

)

-створення додаткових обмеженьTABLE paluvo ADD CONSTRAINT kilk_b CHECK (kilk_b> 0);TABLE paluvo ADD CONSTRAINT kilk_d CHECK (kilk_d> 0);TABLE paluvo ADD CONSTRAINT kilk_m CHECK (kilk_m> 0);

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

5.2 Завдання

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

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

- Вставляємо контрольні дані в таблицю Регіониinto region values(1, 'Передкарпатська нафтогазоносна область');into region values(2, 'Днiпровсько-Донецька нафтогазоносна область');into region values(3, 'Причорноморсько-Кримська газонафтоносна впадина');

-- Вставляємо контрольні дані в таблицю областіinto oblast values(1,1,'Івано-Франківська');into oblast values(2,2,'Дніпропетровська');into oblast values(3,3,'Одесська');into oblast values(1,4,'Львівська');into oblast values(2,5,'Донецька');into oblast values(3,6,'Миколаївська');into oblast values(1,7,'Закарпатська');into oblast values(2,8,'Харківська');into oblast values(3,9,'Херсонська');into oblast values(1,10,'Чернівецька');into oblast values(2,11,'Запорізька');

-- Вставляємо контрольні дані в таблицю базиinto baza values(1,1,'Майдан НБ');into baza values(2,7,'Чернiїв НБ');into baza values(3,12,'Яворів НБ');into baza values(4,11,'Есхар НБ');into baza values(5,13,'Берізка НБ');into baza values(6,5,'Проспект НБ');into baza values(7,3,'Паливо НБ');into baza values(8,4,' Хотин НБ');into baza values(8,10,'Міжгіря НБ');into baza values(9,6,'Запоріжжя НБ');into baza values(10,2,'Вільно НБ');into baza values(11,8,'Микантил НБ');into baza values(11,9,'Бурманськ НБ');

-- Вставляємо контрольні дані в таблицю паливоinto paluvo values(1,'01-10-2012',145,250,333);into paluvo values(2,'02-10-2010',123,321,531);into paluvo values(3,'04-10-2005',455,562,145);into paluvo values(4,'07-11-2007',754,125,645);into paluvo values(5,'18-11-2003',874,985,121);into paluvo values(6,'17-10-2008',158,247,833);into paluvo values(7,'13-10-2009',844,347,175);into paluvo values(8,'17-10-2002',559,577,48);into paluvo values(9,'09-11-2002',891,147,614);into paluvo values(10,'28-11-2001',198,549,178);into paluvo values(11,'04-10-2000',189,277,400);into paluvo values(12,'02-10-2008',151,578,844);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

FROMINNER JOIN baza INNER JOIN oblast INNER JOIN regionregion.kod_reg = oblast.kod_regoblast.kod_obl = baza.kod_oblbaza.kod_b = paluvo.kod_b;

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

Задача

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

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

SELECT

baza.n_b,

paluvo.kilk_b,

paluvo.kilk_d,.databaza INNER JOIN paluvobaza.kod_b = paluvo.kod_bextract(YEAR FROM current_date) < (extract(YEAR FROM paluvo.data) + 11)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,WHEN paluvo.kilk_m<550and paluvo.kilk_b<570 and paluvo.kilk_d<580 THEN 'бідна база' END AS text,baza,oblast,region.kod_reg = oblast.kod_reg AND.kod_obl=baza.kod_obl AND.kod_b=paluvo.kod_b

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

.3 Завдання

Задача 5

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

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

SELECT.n_obl, region.n_reg, count(baza.kod_b)INNER JOIN oblast INNER JOIN regionregion.kod_reg = oblast.kod_regoblast.kod_obl= baza.kod_obl,WHERE paluvo.kod_b = baza.kod_bBY region.n_reg, oblast.n_obl;

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

Завдання

Задача 8

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

Розвязок задачі;CURSOR FOR*, CASE WHEN OR extract(month from paluvo.data)=11'Вивіз палива!' END AS vuviz FROM paluvoJOIN baza ON baza.kod_b=paluvo.kod_bJOIN oblast ON oblast.kod_obl = baza.kod_oblJOIN region ON region.kod_reg=oblast.kod_reg;

FETCH 13 FROM curs;

5.4 Завдання

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

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

CREATE or REPLACE function zvit4() returns text

$$_o oblast %rowtype;_b baza %rowtype;_r region %rowtype ;

_text text = '';_region region.n_reg %type := '';_b_name baza.n_b %type := '';

_count integer := 0;rec_r IN SELECT * FROM region ORDER BY region.n_regrec_o IN SELECT * FROM oblast ORDER BY oblast.n_oblrec_r.kod_reg = rec_o.kod_regrec_b IN SELECT * FROM bazarec_o.kod_obl = rec_b.kod_oblrec_r.n_reg != prev_regionrec_b.n_b < prev_b_name

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

_count := _count + 1;_count > 0

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

_text := _text||E'\n'||repeat(' ', 30)||' '||rec_r.n_reg||E'\n'||(rec_o.n_obl, 70, '_')||rec_b.n_b||E'\n';

_count:=1;IF;_region := rec_r.n_reg;_b_name := rec_b.n_b;IF;LOOP;IF;LOOP;LOOP;

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

$$plpgsql;zvit4();

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

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

DECLARECURSOR FORoblast.n_obl, baza.n_b, sum(paluvo.kilk_b*10.65+paluvo.kilk_d*6.45+paluvo.kilk_m*2.45),text('грн'),(month FROM data) AS mis, extract (year FROM data) AS rik FROMINNER JOIN baza INNER JOIN oblast(oblast.kod_obl=baza.kod_obl)(baza.kod_b=paluvo.kod_b)BY oblast.n_obl,baza.n_b, mis, rikBY oblast.n_obl;13 FROM curs;

Результат:

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

Розвязок задачіor replace function gg() returns TEXT as

$$

- локальні змінні_name text;oblast_name text;baza_name text;alltxt text; kilk_b integer; kilk_d integer; kilk_m integer;

- оголошення курсораCURSOR FOR.n_reg, oblast.n_obl, baza.n_b, paluvo.kilk_b, paluvo.kilk_d, paluvo.kilk_mINNER JOIN baza ON paluvo.kod_b=baza.kod_bJOIN oblast ON baza.kod_obl=oblast.kod_oblJOIN region ON oblast.kod_reg=region.kod_reg;:=' Область'||' Нафтобаза,'||' Бензину(тонн),'||' Дизпалива(тонн),'||' Машинного масла(тонн),'||'Регіон'||'\n\n';cur;cur INTO reg_name, oblast_name, baza_name, kilk_b, kilk_d, kilk_m;when NOT FOUND;:= alltxt|| oblast_name||baza_name ||kilk_b ||' '||kilk_d ||' '||kilk_m ||' '||reg_name || '\n';loop;alltxt;;

$$plpgsql;gg() AS "ZVIT9";

Результат:

6. Висновки

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

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

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

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

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

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

1.#"justify">.Клим Б.В. Конспект лекцій по предмету Бази даних

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

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

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

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

1. Вступ PostgreSQL - це об'єктно-реляційна система управління базами даних (ORDBMS) заснована на POSTGRES версії 4.2 <#"justify">·успадкування

Больше работ по теме: