Главная | Обратная связь | Поможем написать вашу работу!

Створення, видалення і оновлення уявлень

Представлення створюється командою CREATE VIEW, після якої указується його ім'я а далі слідує запит, що формує тіло представлення. Наприклад, для створення представлення OTLSTUD, яке містить інформацію про студентів, одержуючих стипендію у розмірі 25.50. можна скористатися наступною командою:

CREATE VIEW OTLSTUD AS SELECT *

FROM STUDENTS WHERE STIP=25.50;

Тепер в БД існує представлення з ім'ям OTLSTUD, яке можна використати в командах так само, як і будь-яку іншу таблицю: вона може запитати, модифікована, в неї можуть бути вставлені записи, вона може бути видалена з БД або сполучена з іншими таблицями і представленнями. При виконанні запиту

SELECT *

FROM OTLSTUD

буде отриманий наступний висновок:

SNUM SFAM SIMA SOTCH STIP

3412 Поляків Анатолій Олексійович 25.50

3416 Нагірний Євгеній Васильович 25.50

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

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

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

CREATE VIEW STIPOFF

AS SELECT SNUM, SFAM, SIMA, SOTCH FROM STUDENTS

При виконанні запиту

SELECT *

FROM STIPOFF

буде виведено весь вміст створеного представлення:

SNUM SFAM SIMA SOTCH

3412 Поляків Анатолій Олексійович

3413 Старова Любов Михайлівна

3414 Гріценко Володимир Миколайович

3415 Котенко Анатолій Миколайович

3416 Нагірний Євгеній Васильович

Інакше кажучи, представлення STIPOFF фактично та ж таблиця STUDENTS, але без поля STIP.

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

UPDATE STIPOFF

SET SIMA = 'Василий' WHERE SNUM = 3415;

аналогічно виконанню тієї ж команди для таблиці студентів STUDENTS. Проте наступна команда буде знехтувана системою: „

UPDATE STIPOFF

SET STIP = 25.50 WHERE SNUM = 3415;

Це пов'язано з тим, що поле STIP відсутнє в уявленні STIPOFF. І ще одне важливе зауваження: не всі представлення можуть бути модифіковані, про що піде розмова трохи нижче.

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

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

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

SELECT *

FROM OTLSTUD WHERE SNUM > 3412;

СУБД фактично здійснює наступне:

SELECT *

FROM STUDENTS WHERE STIP = 25.50 AND SNUM > 3412;

В даному випадку СУБД чудово справляється з своєю задачею. Проте іноді виникають ситуації, коли з'являються проблеми з представленням в результаті комбінації з двох допустимих предикатів, які не будуть працювати.

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

CREATE VIEW STIPCOUNT (STIP, COL)

AS SELECT STIP, COUNT (*)

FROM STUDENTS GROUP BY STIP;

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

SELECT *

FROM STIPCOUNT WHERE COL = 2;

Якщо скомбінувати два предикати, то, швидше за все, буде отримано

SELECT STIP, COUNT (*)

FROM STUDENTSWHERE COUNT (*) =2

GROUP BY STIP;

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

SELECT STIP, COUNT (*)

FROM STUDENTS

GROUP BY STIP HAVING COUNT (*) = 2;

але SQL може не виконати такого перетворення. Краще, що можна зробити в цьому випадку, це самому перевірити, чи справляється використовувана СУБД з аналогічними запитами і. при необхідності, дотримувати відповідні обмеження при формуванні запитів до уявлень.

В SQL існує поняття групових уявлень - тобто таких, які містять пропозицію GROUP BY або які засновані на інших групових представленнях. Групові представлення є чудовим способом обробляти складну інформацію безперервне. Попередній приклад представлення якраз відноситься до числа групових. Тепер кожного разу. коли вимагається визначити кількість студентів, одержуючих ту або іншу стипендію, достатньо просто вибрати всі записи з даного представлення замість того, щоб створювати достатньо складний запит.

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

CREATE VIEW. STUDOCEN

AS SELECT THIRD.UNUM, FIRST.SFAM SECOND.PNAME, THIRD.OCENKA

FROM STUDENTS FIRST, PREDMET SECOND, USP THIRD

WHERE FIRST.SNUM = THIRD.SNUM AND SECOND.PNUM = THIRD.PNUM;

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

SELECT *

FROM STUDOCEN

WHERE SFAM = 'Поляков';

надасть докладну інформацію про оцінки цього студента:

UNUM SEAM PNAME OCENKA

1001 Поляків Фізика 5

1004 Поляків Математика 4

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

SELECT SFAM, PNAME, OCENKA, UDATE

FROM STUDOCEN FIRST, USP SECOND

WHERE FIRST.SFAM = 'Поляков' AND FIRST.UNUM = SECOND.UNUM;

Висновок для цього запиту буде наступним:

SFAM PNAME OCENKA UDATE

Поляків Фізика 5 10/06/1999

Поляків М атематика 4 12/06/1999

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

CREATE VIEW AVGOC

AS SELECT *

FROM USP FIRST

WHERE OCENKA > (SELECT AVG (OCENKA)

FROM USP SECOND

WHERE SECOND.PNUM = FIRST.PNUM);

Витягання даних виконується простим запитом:

SELECT *

FROM AVGOC;

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

На жаль, достатньо часте представлення є об'єктами, доступними тільки для читання. Це означає, що інформацію з них можна запрошувати, але вони не можуть піддаватися діям команд модифікації. Крім того, існують також деякі команди, які не допустимі у визначеннях уявленні: представлення повинне ґрунтуватися на одиночному запиті, тому об'єднання UNION не дозволяється. Крім цього, у визначенні представлення не використовується впорядкування ORDER BY, т.к., по аналогії з базовою таблицею, в уявленні записи є неврегульованими.

Синтаксис видалення представлення з БД подібний видаленню базових таблиць:

DROP VIEW <VIEW NAME>

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

Як вже було відзначено, не всі представлення здібні до модифікації. Взагалі кажучи, команди модифікації мови DML -ІNSERT. UPDATE і DELETE - кінцевий же можуть застосовуватися для уявлень. Розглянемо ряд правил, визначальних, чи є представлення модифікується. Даний момент є найбільш важким і неоднозначним у використовуванні уявлень.

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

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

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

Критерії, по яких визначають, чи є представлення модифікується в SQL. наступні:

• представлення повинне ґрунтуватися тільки на одній базовій таблиці;
• воно повинне містити первинний ключ цієї таблиці.
• воно не повинне не мати ніяких полів, які б були агрегатними функціями;
• воно не повинне містити DISTINCT в своєму визначенні;
• представлення не повинне використати GROUP BY або HAVING в своєму визначенні.
• бажано, щоб воно не використало в своєму визначенні підзапити;
• воно може бути використане в іншому уявленні, але це представлення повинне також бути модифікується;
• воно не повинне використати константи, рядка або виразу значень серед вибраних полів висновку; • для команди INSERT воно може містити будь-які поля основної таблиці, які мають обмеження NOT NULL, якщо інше обмеження за умовчанням не визначене.

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

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

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

CREATE VIEW PRCOUNT (UDATE, COL)

AS SELECT UDATE, COUNT (*)

FROM USP

GROUP BY UDATE;

Наступне представлення є модифікується внаслідок того, що воно задовольняє перерахованим вище умовам. В якості висновку в ньому містяться дані про успішність по предмету з кодом 2003. тобто математиці:

CREATE VIEW MATEMUSP

AS SELECT *

FROM USP

WHERE PNUM = 2003;

Приведене нижче представлення - тільки для читання:

CREATE VIEW IDXSTIP (SNUM, SFAM, NEWSTIP)

AS SELECT SNUM, SFAM, STIP*2

FROM STUDENTS

WHERE STIP=25.50;

Це пов'язано з тим, то у визначенні присутня функція STIP*2. До речі кажучи, в більшості СУБД останнє представлення буде припускати видалення рядків з відповідним ключу SNUM видаленням їх в базовій таблиці.

Представлення, що виводить інформацію про студентів, що отримали оцінки в певний день, буде тільки для читання:

CREATE VIEW DATEOC

AS SELECT *

FROM STUDENTS

WHERE SNUM IN (SELECT SNUM FROM USP WHERE UDATE = 10/06/1999);

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

CREATE VIEW DATEOC2

AS SELECT *

FROM USP

WHERE UDATE IN(10/06/1999, 11/06/1999);

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

CREATE VIEW ONLY5

AS SELECT SNUM, OCENKA

FROM USP WHERE OCENKA = 5;

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

INSERT INTO ONLY5 VALUES (3415, 4);

Це - допустима команда INSERT в даному уявленні і рядок буде вставлена, за допомогою ONLY5, в таблицю успішності USP. Проте, коли ця інформація буде додана, рядок зникне з представлення, оскільки значення оцінки не рівне 5.

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

CREATE VIEW ONLY5

AS SELECT SNUM, OCENKA

FROM USP

WHERE OCENKA = 5 WITH CHECK OPTION;

Після цього згадана вставка значень відхилюватиме системою.

Фраза WITH CHECK OPTION виробляє дію в режимі "все або нічого", вона розміщується у визначенні представлення, і всі команди модифікації будуть піддаватися перевірці. Таким чином, можна регулювати процес введення значень, які користувач згодом сам не в змозі коректувати.

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

CREATE VIEW OTLSTIP

AS SELECT SNUM, SFAM

FROM STUDENTS

WHERE STIP=25.50;

Його результатом будуть номери студентських квитків і прізвища студентів, одержуючих стипендію у розмірі 25 50. Взагалі кажучи. логічно не включити в результати поле STIP - все одно список значень в уявленні для цього поля буде однаковим.

Що ж буде відбуватися кожного разу. коли користувач спробує додати сюди запис? Т. до він не в змозі ввести значення розміру стипендії, а значення за умовчанням в більшості випадків буде NULL, то введений запис буде тут же виключена з представлення. Причому, описана проблема буде відбуватися з будь-яким рядком, який спробують додати в OTLSTIP. Крім того, це ще не означає, що користувач має можливість вставляти значення в базову таблицю. Якщо, наприклад, в таблиці STUDENTS поле STIP обмежене як NOT NUL.L. те результати вставки взагалі важко передбачать користувачем. Більше того, ця проблема навряд чи буде вирішена, навіть при визначенні представлення таким чином:

CREATE VIEW OTLSTIP

AS SELECT SNUM, SFAM

FROM STUDENTS

WHERE STIP=25.50 WITH CHECK OPTION;

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

Необхідно згадати про таку властивість фрази WITH CHECK OPTION, як відсутність каскадної зміни даних Ця фраза застосовується тільки в представленнях, які засновані на базових таблицях, а не на інших представленнях. Наприклад, якщо попереднє представлення поліпшити з урахуванням виказаних рекомендацій, тобто

CREATE VIEW OTLSTIP

AS SELECT SNUM, SFAM, STIP

FROM STUDENTS

WHERE STIP=25.50

WITH CHECK OPTION

те спроба вставки

ІNSERT INTO OTLSTIP

VALUES (3417, Решітник, 17.00)/

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

CREATE VIEW NEWOTL AS SELECT *

FROM OTLSTIP/

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

CREATE VIEW NEWOTL

AS SELECT *

FROM OTLSTIP

WITH CHECK OPTION/

це не вирішить проблеми, оскільки рядок, що вставляється, задовольняє предикату представлення NEWOTL. Варто мати на увазі, що цей недолік SQL в деяких сучасних СУБД вирішений і каскадує фрази WITH CHECK OPTION буде вироблятися, проте в цьому необхідно переконатися в документації на дані системи.

Одна з сильних сторін SQL - це здатність функціонувати по всіх рядках таблиці: скільки рядків задовольнить предикату, стільки і буде виведено. Проте це може викликати ряд проблем, коли SQL взаємодіє з іншими мовами програмування. Дійсно, важко передати висновок запиту змінним, якщо наперед невідомо, наскільки великі, будуть результати висновку. В SQL розв'язання цього здійснюють з допомогою так званого курсору. Курсор - це вид зміни, яка пов'язана із запитом. Значенням цієї зміни може бути кожний рядок, який виводиться при запиті і він повинен бути оголошений перш, ніж буде використаний, що робиться командою DECLARE CURSOR таким чином:

EXEC SQL DECLARE CURSOR STIPCUR

FOR SELECT SNUM, SFAM, STIP

FROM STUDENTS

WHERE STIP=25.50;

Цей запит не виконається негайно, в даному випадку уявлено тільки його визначення. Курсор дещо нагадує представлення, в якому визначення містить запит, а змістовною частиною є будь-який висновок запиту, і це відбувається кожного разу, коли курсор стає відкритим. Проте, на відміну від базових таблиць або уявлень, рядки курсора впорядковані і є перша, друга і., а також останній рядок. Цей порядок може бути довільним з явним управлінням за допомогою фрази ORDER BY в запиті.

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

EXEC SQL OPEN CURSOR STIPCUR;

Значення в курсор передаються саме тоді, коли виконується приведена вище команда, DECLARE і FETCH на прочитування інформації в курсор впливи не надають.

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

EXEC SQL FETCH STIPCUR

INTO:STUDENTSID:STUDENTSFAM:STUDENTSSTIP

Ця конструкція привласнить значення з першого вибраного рядка в змінні пам'яті. Наступна команда FETCH виведе черговий набір значень і. Як правило, команду FETCH поміщають всередину циклу так. щоб вибравши рядок з курсору, можна було здійснити переміщення набору значень з цього рядка в змінні, повернутися назад в цикл і перемістити наступний набір значень в ті ж самі змінні. Звернете увагу на оператор CLOSE CURSOR, який повинен відповідати OPEN CURSOR. Він звільняє курсор значень, тому після нього запит потрібно буде виконати повторно з оператором OPEN CURSOR, перш ніж перейти до вибору наступних значень. Для нашого випадку цей оператор буде виглядати таким чином:

EXEC SQL CLOSE CURSOR STIPCUR;

Іншими словами, поки курсор закритий, SQL не стежить за тим, які рядки були вибрані. Якщо курсор відкривається знов, запит повторно виконується з цієї крапки, і цикл вибору значень повинен починатися спочатку. І ще одна особливість: коли в FETCH немає більше рядків, які треба витягати, він просто не міняє значення в змінних пропозиції INTO, тобто якщо дані в курсорі вичерпалися, ці змінні будуть виводитися з ідентичними значеннями до тих пір, поки не завершиться цикл.

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

ЛЕКЦІЯ№

Тема: Методи захисту інформації

ПЛАН

1. Безпека баз даних і привілею

2. Види привілей.

3..Створення, видалення і оновлення привілей

4. Системні привілеї

⇐ Предыдущая 1 2 3 456 7 8 Следующая ⇒