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

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

FORCE – примусове створення представлення поза залежністю

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

користувача привілеї на вибірку з базових таблиць;

NO FORCE (використовується за замовчуванням) передбачає

обов’язкове існування базових таблиць або представлень і

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

При порушенні якоїсь із цих умов представлення не створюється;

запит – будь-який синтаксично правильний запит, який не

містить ключових слів ORDER BY чи FOR UPDATE;

WITH READ ONLY – забороняє модифікацію даних базових

таблиць;

WITH CHECK OPTION – рядки базових таблиць, які змінюються і

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

добору запиту;

CONSTRAINT – визначає імя обмеження, яке використовується

для перевірки даних.

Представлення можна змінювати операторами DELETE, INSERT і

UPDATE, у тому випадку, якщо задовільняються такі умови:

– у списку вибірки не зазначене ключове слово DISTINCT;

– кожний арифметичний вираз у списку вибірки являє собою

одну специфікацію поля, і специфікація одного поля не

появляється більше, ніж один раз;

– в умові вибірки WHERE не використовуються підзапити;

– у запиті відсутні конструкції GROUP BY HAVING.

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

зміни, можна за допомогою представлення словника даних

user_updatable_columns.

Наведемо приклад створення представлення truba з обмеженням

тільки для читання за даними таблиці Gazpr. Нагадаємо, що приклад

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

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

truba відкидається системою, незважаючи на явне надання

потрібного привілею користувачу user2, хоча вставка даних

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

SQL> CREATE VIEW truba AS

2 SELECT * FROM Gazpr WITH READ ONLY;

View created.

SQL> GRANT INSERT ON Gazpr TO user2;

Grant succeeded.

111

SQL> GRANT INSERT ON truba TO user2;

Grant succeeded.

SQL> CONNECT user2/stud;

Connected.

SQL> INSERT INTO user1.truba VALUES(15,'Новий газопровід',3,4);

INSERT INTO user1.truba VALUES(15, 'Новий газопровід',3,4)

ERROR at line 1:

ORA-01732: data manipulation operation not legal on this view

SQL> INSERT INTO user1.Gazpr VALUES (15, 'Новий газопровід',3,4);

1 row created.

В іншому прикладі створимо представлення truba1, на якому

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

приналежність області допустимих значень (WHERE Kilnyt<16).

Спроба вставки даних, які не задовольняють критерій запиту,

наприклад, при Kilnyt =100 також відкидається системою. При

Kilnyt=3 вставка запису відбулася успішно.

SQL> CREATE VIEW truba1 AS SELECT * FROM Gazpr

2 WHERE Kilnyt<16 WITH CHECK OPTION;

View created.

SQL> GRANT INSERT, SELECT ON truba1 TO user2;

Grant succeeded.

SQL> CONNECT user2/stud;

Connected.

SQL> INSERT INTO userl. truba1(Kilnyt) VALUES (100);

INSERT INTO user1. truba1(Kilnyt) VALUES (100)

ERROR at line 1:

ORA-01402: view WITH CHECK OPTION where-clause violation

SQL> INSERT INTO user1. truba1(Kilnyt) VALUES(3);

1 row created.

SQL> SELECT Kilnyt FROM user1. truba1;

KILNYT

112

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

мати привілей SELECT для всіх таблиць, що беруть участь у запиті,

який формує представлення. Той привілей, яким володіє

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

Якщо користувач має будь-яку комбінацію привілеїв INSERT,

UPDATE, DELETE для базових таблиць, то ці привілеї будуть

автоматично успадковуватися представленням. У той же час,

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

таблиць, не може одержати відповідні привілеї в представленні.

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

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

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

Наприклад, нехай користувач user1 створив представлення Sumas, у

якому обчислюється сума у полі Kilnyt та середнє значення у полі

Diam таблиці Gazpr. Користувачу user2 надається право вибірки з

цього представлення, але не з базової таблиці.

SQL> CONNECT user1/stud;

Connected.

SQL> CREATE VIEW Sumas (Suma_ K, Avg_ D)

2 AS SELECT SUM(Kilnyt), AVG(Diam) FROM Gazpr;

View created.

SQL>GRANT SELECT ON Sumas TO user2;

Grant succeeded.

SQL> CONNECT user2/stud1;

Connected.

SQL> SELECT * FROM user1.Sumas;

S uma_ K A vg_ D

29 1264

SQL> SELECT * FROM user1.Gazpr

SELECT * FROM user1.Gazpr

ERROR at line 1:

ORA-01031: insufficient privileges

Для вилучення представлення необхідно бути його власником

або мати привілей DROP ANY VIEW. Застовується такий синтаксис:

DROP VIEW [ім'я_схеми.]ім'я_представлення;

113

Після вилучення представлення об’єкти, які на нього

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

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

представлення Sumas:

SQL> DROP VIEW Sumas;

View droped.

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

1. Перелічіть типи даних SQL

2. Чим відрізняється послідовність SQL від типу Лічильник MS

Access?

3. Для чого служить таблична область?

4. Чим відрізняється представлення від таблиці?

5. Чи можуть знаходитися різні таблиці бази даних у різних

файлах?

6. Як знищити таблицю, залежну (зв’язану) від іншої таблиці?

7. Чи може створити таблицю користувач, який не має власної

табличної області?

8. Як забезпечити постійне знаходження в кеші даних таблиці, яка

часто використовується?

9. Запишіть команди для створення та вилучення табличної

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

4.6 Програмні засоби доступу до даних

4.6.1 Структура програми мовою PL/SQL

Мова PL/SQL є процедурним розширенням Oracle стандартної

мови SQL. Його моделлю послужила мова програмування Ада.

За допомогою PL/SQL можна підвищити продуктивність

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

SQL Oracle вона дозволяє використовувати попередньо

скомпільовані програми, які швише виконуються. Використовуючи

PL/SQL, також можна значно зменшити обсяг робіт у клієнтській

частині додатка і навантаження на мережу. Наприклад, якщо

знадобиться виконати різні набори операторів SQL, то запит,

оператори SQL і оператори умовного керування можуть бути

включені в один блок PL/SQL і переслані серверу за одне

звертання до мережі.

114

Програма мовою PL/SQL складається з трьох блоків: описів,

виконавчого і обробки виняткових ситуацій. Виконавчий блок

обрамлюється операторними дужками BEGIN і END. Вона має таку

структуру:

DECLARE

оператори ...

BEGIN

оператори ...

EXCEPTION

оператори ...

END;

Програма такого виду називається анонімним блоком. У блоці

DECLARE описуються змінні, константи і задані користувачем типи

даних. Перший оператор BEGIN починає тіло основної програми. У

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

операторними дужками BEGIN і END. У блоці EXCEPTION

знаходяться фрагменти програмного коду для обробки виняткових

ситуацій. Тіло програми закінчується операторною дужкою END. В

анонімному блоці можуть бути відсутстніми блоки DECLARE і

EXCEPTION, але обов’язково повинен бути присутнім блок

операторів, обмежений операторними дужками BEGIN і END. У

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

оператор NULL;. Будь-яка частина програми мовою PL/SQL може

містити коментарі. Текст, що починається із символів -- (два знаки

мінус) і продовжується до кінця поточного рядка, розглядається як

коментар. Багаторядкові коментарі обрамлюються символами /* і */.

Перед блоком DECLARE можуть знаходитися команди

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

Змінні, константи і типи. В обмеженому операторами

DECLARE і BEGIN блоці програми описуються змінні, типи і

константи. Будь-яка змінна чи константа повинна мати один із

допустимих у PL/SQL типів. Константа ідентифікується ключовим

словом CONSTANT і відрізняється від змінної тим, що спроба

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

Присвоювання значень змінним здійснюється оператором :=.

Типи даних у PL/SQL практично збігаються з типами SQL, за

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

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

PLS_INTEGER, BINARY_INTEGER і т. д. Крім скалярних, PL/SQL має

складені типи, це: тип RECORD, масиви і PL/SQL-таблиці.

115

Складений тип даних RECORD призначений для збереження й

обробки записів. Кожен запис має атрибути, які можуть бути

проініціалізовані при оголошенні. При звертанні до атрибутів

запису використовується повна нотація, яка включає ім’я змінної

та ім’я атрибута, розділені крапкою. У змінні типу RECORD зручно

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

Розглянемо приклад лінійної програми мовою PL/SQL. Нехай

потрібно обчислити висоту зубців h

шарошкового долота типу В-

3Т після відпрацювання ним t одиниць часу. Зменшення висоти

зубців долота в часі обчислюється за формулою

keh





[5], де k =

4,14, а ω = -1,94. Значення змінної t ввести під час виконання

програми (t=0.8).

Текст програми має такий вигляд:

SQL> SET SERVEROUTPUT ON;

SQL> SET ECHO ON;

SQL> DECLARE --блок оголошень

2 k CONSTANT NUMBER:=4.14;

3 omega CONSTANT NUMBER:=-1.94;

4 t NUMBER;

5 ht NUMBER(6);

6 BEGIN-- Виконавчий блок

7 t:= &INPUT_DATA;

8 ht:=k*exp(omega*t*t);

9 DBMS_OUTPUT.PUT_LINE('Результ.= ' || TO_CHAR(ht,'99G999'));

10 END;

11 /

Enter value for input_data: 0.8

Результ.= 1,196

В цьому прикладі визначено режим виводу на термінал

користувача командами установки змінних оточення SET

SERVEROUTPUT і SET ECHO. Вивід даних на екран результату

забезпечує процедура DBMS_OUTPUT.PUT_LINE.

Символ / (слеш) означає завершення тексту анонімного блока і

є командою до його інтерпретації та виконання.

Оператори PL/SQL дозволяють виконувати умовні переходи,

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

при забезпеченні заданих умов. Розглянемо їх.

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

в залежності від одержаного значення TRUE чи FALSE виконуються

різні групи операторів. Альтернативна послідовність операторів

116

визначається ключовим словом ELSE. Границі дії оператора IF

визначаються операторною дужкою END IF. При вкладенні

розгалужень застосовуються операторні дужки ELSIF. Oператор

розгалуження має такий загальний вигляд:

IF умова1 THEN оператори1;

ELSIF умова2 THEN оператори2;

ELSIF …

ELSE операториn;

END IF;

Цикл оформляється декількома способами. В найпростішому

випадку використовується оператор LOOP. Вийти з циклу теж

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

забезпечує вихід з циклу за умовою. Цей оператор має такий

вигляд:

LOOP

EXIT WHEN умова завершення циклу;

тіло циклу

END LOOP;

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

WHILE. Цей цикл виконується доти, поки умова, визначена

службовим словом WHILE, має значення TRUE. Він виглядає так:

WHILE умова LOOP

тіло циклу

END LOOP;

Цикл, керований оператором FOR, використовується в тому

випадку, коли відома кількість його виконань (в даному випадку

число L). Параметр циклу (в даному випадку змінну I) описувати в

блоці DECLARE не потрібно. Він має такий вигляд:

FOR I IN 1..L LOOP

тіло циклу

END LOOP;

Оператор GOTO дозволяє здійснити перехід на мітку, яка є в тілі

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

подвійні кутові дужки, наприклад: <<mitka>>.

117

4.6.2 Підпрограми

Мова PL/SQL має два види підпрограм: процедури і функці.

Подібно до інших об’єктів БД їх створюють командою CREATE, а

знищують командою DROP. Для створення процедури, функції чи

пакета необхідно мати системний привілей CREATE PROCEDURE.

Пакет являє собою блок підпрограм, який має власне ім’я. Крім

підпрограм, він містить їх опис і ті, що описані, є глобальними,

тобто доступними зовні пакета. Неописані підпрограми є

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

складі пакета.

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

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

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

виду робіт, для обслуговування певних наборів даних тощо.

У визначені процедури чи функції необхідно задавати імена,

типи і види параметрів. Існують такі види параметрів: IN (не

змінюється підпрограмою), OUT (припускає зміну значення

параметра) чи IN OUT (параметр може бути зміненим у тілі

підпрограми).

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

CREATE [OR REPLACE] PROCEDURE [ім'я_схеми.]ім'я_процедури

[(перелік_параметрів_і_їх_типів)]

AS текст_nідпporpaми_мовою_PL/SQL;

Ключове слово OR REPLACE означає заміщення старого тексту

процедури. Якщо цього слова немає, а процедура вже існує, то її

заміщення не відбувається і повертається повідомлення про

помилку.

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

слово DECLARE не використовується, блок визначення її даних

починається відразу після ключового слова AS.

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

заносить у таблицю Oblik нашого прикладу один запис. Поле Data

таблиці містить поточну системну дату. Процедура приймає три

вхідні параметри виду IN числового типу і має такий вигляд:

SQL> CREATE OR REPLACE PROCEDURE InsRecOblik

2 (Kodpr IN INTEGER, Kodpp IN INTEGER, Kil IN NUMBER)

3 AS

4 BEGIN

118

5 INSERT INTO Таb VALUES (Kodpr, Kodpp,Kil, SYSDATE) ;

6 END;

7 /

Procedure created.

Процедура InsRecOblik може бути відправлена на виконання

командою EXEC утиліти SQL*Plus або викликом з анонімного

блоку чи з іншої програми. У наведеному нижче прикладі вона

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

Kodpr=1, Kodpp=2, Kil=3.

SQL> BEGIN

2 InsRecOblik(1,2,3);

3 END;

PL/SQL procedure successfully completed.

Часто перша спроба скомпілювати програму на PL/SQL

(процедуру, функцію, пакет чи тригер) буває невдалою, через що

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

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

скористатися командою SQL*Plus SHOW ERRORS. Ця команда

показує помилки, зроблені під час виконання команди CREATE.

Якщо команда SHOW ERRORS використовується без параметрів, то

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

функції, пакета чи тригера.

Функція PL/SQL відрізняється від процедури тим, що повертає

в програму, яка її викликає, значення. У визначенні функції

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

Повернення значення функції забезпечує оператор RETURN.

Оператор визначення функції Oracle має такий вигляд:

CREATE [OR REPLACE] FUNCTION [ім'я_схеми.]ім'я_функції

[(перелік_параметрів_і_їх_типів)]

RETURN тип_функції

AS текст_функції_мовою_PL/SQL;

Розглянемо приклад, нехай функція SumaRecOblik обчислює

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

параметрами Min і Max функції інтервал. Функція має такий вигляд:

SQL> CREATE OR REPLACE FUNCTION SumaRecOblik

2 (Min IN DATE, Max IN DATE) RETURN NUMBER AS

119

3 Suma NUMBER := 0;

4 BEGIN

5 SELECT SUM(Kil) INTO Suma FROM Oblik

6 WHERE Data BETWEEN Min AND Max;

7 RETURN Suma;

8 END;

Викликавши функцію SumRecOblik за допомогою показаного

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

тих записів таблиці Oblik, де значення поля Data відрізняються від

системної дати не більше, ніж на пів-доби.

SQL> BEGIN

2 DBMS_OUTPUT.PUT_LINE(SumRecOblik(SYSDATE-1/2, SYSDATE+1/2));

3 END;

Для знищення процедури чи функції в схемі іншого

користувача необхідна наявність привілею DROP ANY PROCEDURE.

Знищити процедуру можна за допомогою такої команди:

DROP PROCEDURE [ім'я_схеми.]ім'я_процедури;

Для знищення функції використовується така команда:

DROP FUNCTION [ім'я_схеми.]ім'я_функції;

4.6.3 Курсори

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

деяке фіксоване число рядків. Про курсор уже згадувалося в

розділі 2.2.6. В Oracle допустима кількість курсорів на сесію

встановлюється параметром OPEN_CURSOR файлу init.ora.

Існує три основних типи курсорів: неявний, явний і курсорний

цикл FOR. Неявний курсор оголошується автоматично для всіх

SQL-речень маніпулювання даними, включаючи запити, які

повертають рівно один рядок. У процесі його виконання курсор

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

закривається. Усі ці дії виконуються сервером за один крок. Якщо

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

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

120