Информационное обеспечение



бет9/56
Дата05.03.2023
өлшемі256,49 Kb.
#71567
түріПрактикум
1   ...   5   6   7   8   9   10   11   12   ...   56
Байланысты:
СУБДOracle

Контрольные вопросы





    1. Какова общая структура запроса на извлечение информации?

    2. Какие предикаты могут применяться в предложении WHERE?

    3. Как можно устранить дубликаты строк в запросах?

    4. Что такое псевдонимы и для чего они используются?

    5. Поясните принцип соединения таблиц по условию равенства столбцов.

    6. Что такое внешние соединения?

    7. Когда выполняется операция декартова произведения двух таблиц?

    8. Перечислите особенности синтаксиса итоговых запросов.

    9. Для чего используется предложение HAVING?

    10. Для чего и где используются подзапросы?

    11. Какие предикаты могут использоваться с подзапросами?

    12. Как обновить данные одновременно в двух столбцах таблицы?

ЛАБОРАТОРНАЯ РАБОТА №3


СОЗДАНИЕ И ИСПОЛЬЗОВАНИЕ ПРЕДСТАВЛЕНИЙ
Цель работы – научиться создавать и работать с однотабличными, мно- готабличными, индивидуальными и агрегированными представлениями.


Теоретические сведения




Представление (views) – это хранимый в памяти SQL-запрос, который ав- томатически выполняется при работе с представлением. Другими словами, представление – это виртуальная таблица, которая сама по себе не существует, но для пользователя выглядит таким образом, как будто она существует. Пред- ставление определяется перечнем тех столбцов таблиц и признаками тех их строк, которые хотелось бы в ней увидеть. Представление не поддерживается его собственными физическими хранимыми данными. Вместо этого в каталоге таблиц хранится определение, оговаривающее, из каких столбцов и строк дру- гих таблиц оно должно быть сформировано при реализации SQL-предложения на получение данных из представления или на модификацию таких данных. В каталоге БД представление сохраняется в виде того запроса, который его опре- деляет. При трансляции запроса, содержащего обращение к представлению, SQL-машина подставляет вместо него обращение к базовой таблице/таблицам и добавляет те условия, которые определяют представление.
Одним из главных достоинств представлений является то, что они значи- тельно упрощают работу конечного пользователя с данными в БД, так как в за- пуске одного и того же запроса больше нет необходимости. Достаточно просто создать одно представление и потом уже обращаться к нему с помощью про- стых запросов. В дальнейшем представления можно объединять с другими таб- лицами или другими представлениями.
Работая с представлениями, нужно помнить о следующих их свойствах:

  • представления добавляют уровень защиты данных (например, можно создать представление для таблицы, где пользователю, выполняющему SELECT над представлением, видны только сведения о заработной плате);

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

  • представления могут скрывать настоящие имена столбцов, порой труд- ные для понимания, и показывать более простые имена.

Для создания представления в стандарте SQL используется команда CREATE VIEW, которая имеет следующий формат:

CREATE [OR REPLACE] VIEW [схема.]имя_представления[список имен столбцов]AS SELECT список_полей FROM имя_таблицы;


Как видно, в основе представления лежит SQL-выражение отбора дан- ных, т. е. запрос. Столбцы, указанные в команде SELECT, определяют столбцы (их количество и названия) в созданном представлении.
Cписок имен столбцов должен быть обязательно определен лишь в сле- дующих случаях:

    • когда хотя бы один из столбцов подзапроса не имеет имени (создается с помощью выражения, SQL-функции или константы);

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

При создании представлений инструкция SELECT не должна содержать:

  • конструкцию ORDER BY;

  • конструкцию FOR UPDATE, так как она блокирует выбранные строки и не позволяет другим пользователям их обновлять или блокировать, пока включающая эту конструкцию транзакция не будет завершена;

В список столбцов представления может быть включено не более 254 столбцов или выражений.
Каждый включенный в представление столбец всегда доступен для выво- да и иногда для изменения (если представление является обновляемым). Для работы с данными, отраженными в представлении, используются те же SQL- команды, что и для работы с таблицами (SELECT, INSERT, UPDATE, DELETE). Однако следует учитывать, что создание или удаление представле- ния не изменяет таблицу, но если в представлении выполняется вставка или удаление строк, то эти операции отражаются в ней.
Если какой-либо столбец не был включен в представление при его созда- нии, то при попытке вставки новой строки в представление Oracle всегда будет вставлять данные в исходные таблицы и присваивать отсутствующим столбцам значение NULL или значение по умолчанию, если такое было установлено для столбца при создании таблицы. Иногда значение NULL может привести к ошибкам (например, если этот столбец является первичным ключом или если на него было наложено ограничение NOT NULL), поэтому следует учитывать это обстоятельство при создании таблицы и представления.
Во всех выражениях выборки представление может быть использовано вме- сто таблицы без всяких ограничений, т. е. после определения представления к нему можно обращаться с помощью инструкции SELECT как к обычной таблице:
SELECT * FROM view_name;
Для удаления представления используется команда DROP со следующим форматом:
DROP VIEW имя_представления;
Сложнее обстоит дело с модификацией представления. Представление обладает свойствами таблицы: в него можно вставлять данные, удалять или мо- дифицировать их. Но тем не менее представление жестко связано с таблицами,
над которыми оно было создано. При вставке новых картежей в представление (а по сути вставки их в таблицу базы данных) анализируются ограничения этой таблицы.
Для простых представлений операции добавления, изменения и удаления можно преобразовать в эквивалентные операции по отношению к исходным таблицам.
По стандарту SQL представления будут обновляемыми (изменяемыми), если выполняются следующие условия:

  • должен присутствовать ключ и все столбцы, которые определены как NOT NULL;

  • должен отсутствовать предикат DISTINCT, т. е. повторяющиеся строки не исключаются из результата запроса;

  • в предложении FROM должна быть задана только одна таблица или представление;

  • каждое имя в списке возвращаемых столбцов должно быть ссылкой на простой столбец, не должны содержаться выражения, вычисляемые столбцы и статистические функции, спецификация одного столбца не должна появляться более одного раза;

  • в предложении WHERE не должен содержаться подчиненный запрос;

  • в запросе не должны присутствовать предложения GROUP BY и HAVING.

Все эти требования стандарта слишком жесткие и не всегда соблюдаются, в том числе и в СУБД Oracle. Так, например, Oracle дает частичную возмож- ность изменения соединенных таблиц.
Все представления можно разделить на однотабличные (основанные только на одной таблице), многотабличные, индивидуальные и агрегированные (сгруппированные).
Основное назначение однотабличных представлений – скрыть от пользо- вателя некоторые столбцы. Эта процедура получила название «маскирование столбцов». Для создания маскирующего представления необходимо в списке столбцов команды SELECT указать только те, к которым пользователю разре- шен доступ. Такие представления можно назвать также вертикальными.
Создать представление, показывающее информацию о ФИО и должности служащих:
CREATE OR REPLACE VIEW info AS
SELECT fname, lname, position FROM Staff;

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


данные через представление в первоначальную таблицу, за исключением слу- чаев, когда он заполняется с помощью последовательности.
Создать представление, показывающее информацию о служащих, рабо- тающих в отделении компании города Минска:

CREATE OR REPLACE VIEW Minsk AS


SELECT * FROM Staff
WHERE bno IN (SELECT bno FROM Branch WHERE city = 'Minsk');

Это пример создания простого горизонтального представления, т. е. пред- ставления, которое позволяет видеть в исходной таблице Staff не все строки, а только те, которые удовлетворяют условию отбора запроса, лежащего в его ос- нове. Кроме того, это представление будет обновляемым в Oracle, несмотря на то что нарушаются требования стандарта, и через него можно будет добавлять строки в исходную таблицу.


Конечно, на практике при создании представлений обычно требуется раз- делять таблицу и по вертикали, и по горизонтали – такие представления полу- чили название смешанных.
В отличие от горизонтальных, вертикальных и смешанных представлений каждой строке сгруппированного представления не соответствует какая-то одна строка исходной таблицы. Сгруппированное представление не является просто фильтром исходной таблицы, скрывающим некоторые строки и столбцы. Оно отображает исходную таблицу в виде резюме, поэтому поддержка такой вирту- альной таблицы требует от СУБД значительного объема вычислений и отсле- живания проблем с обновлением.
Создать представление с информацией о средней заработной плате со- трудников по каждому отделению:

CREATE OR REPLACE VIEW average_salary AS SELECT bno, AVG(salary) Srednya_zarplata FROM Staff


GROUP BY bno;
Полная инструкция создания представления в Oracle имеет вид CREATE [OR REPLACE] [FORCE|NOFORCE] VIEW view-name
AS sql-запрос [WITH CHECK OPTION [CONSTRAINT имя_ограничения]] [WITH READ ONLY];

Фраза OR REPLACE – пересоздает представление, если оно уже суще- ствует. Можно использовать эту опцию для изменения определения представ- ления без того, чтобы удалять его, создавать заново и вновь назначать все объ- ектные привилегии, которые были назначены по данному представлению.


Фраза FORCE – создает представление независимо от того, существуют ли базовые таблицы этого представления, и от того, имеет ли владелец схемы, содержащей представление, привилегии по этим таблицам. Необходимо, чтобы оба названных условия были удовлетворены, прежде чем по данному представ- лению можно будет выдавать любые предложения SELECT, INSERT, UPDATE или DELETE. Перед обращением к приложению его следует также перекомпи- лировать командой:
ALTER VIEW имя_представления COMPILE;
Фраза NOFORCE (применяется по умолчанию) – создает представление только в том случае, если существуют базовые таблицы этого представления, а владелец схемы, содержащей представление, имеет привилегии по этим таблицам.
Фраза WITH CHECK OPTION – указывает, что вставки и обновления, ко- торые будут осуществляться через этот запрос, должны давать в результате только такие строки, которые могут быть выбраны запросом этого же представ- ления. Другими словами, если представление создается посредством запроса с предложением WHERE, то в представлении будут видны только строки, удо- влетворяющие условию отбора. Остальные строки в исходной таблице присут- ствуют, но в представлении их нет.
Например, представление Minsk содержит только строки таблицы Staff с определенными значениями в столбце bno.
Это представление является обновляемым, следовательно, в него можно добавить информацию о новом служащем посредством инструкции INSERT:

INSERT INTO Minsk (sno, fname, lname, address, position, sex, dob, salary, bno) VALUES ('s129’, '…', '…', '…', 'менеджер', 'f', '01.01.81', 300, 1).


СУБД добавит новую строку в исходную таблицу Staff, она будет видна также в представлении Minsk. Также без опции WITH CHECK OPTION ничего не препятствует выполнению следующей инструкции:


INSERT INTO Minsk (sno, fname, lname, address, position, sex, dob, salary, bno) VALUES ('s129', '…', '…', '…', 'менеджер', 'f', '01.01.81', 300, 2).


После этого в запросе SELECT * FROM Minsk добавленная строка будет отсутствовать. Тот факт, что в результате выполнения инструкции INSERT или UPDATE из представления исчезают строки, в лучшем случае вызывает заме- шательство.


При указании параметра WITH CHECK OPTION пользователь не сможет вводить, удалять и обновлять информацию таблицы, из которой он не имеет возможности считать информацию через простое представление (создаваемое из данных одной таблицы). Обновляемое представление, использующее не- сколько связанных таблиц, нельзя создавать с данным параметром, так как оп-
ция WITH CHECK OPTION не может гарантировать контроль, если существует подзапрос в запросе этого представления или любого представления, на кото- ром базируется данное представление.
Фраза CONSTRAINT имя_ограничения – задает имя, которое присваива- ется ограничению CHECK OPTION. Если этот идентификатор опущен, то Ora- cle автоматически назначает этому ограничению уникальное имя.

CREATE OR REPLACE VIEW Minsk AS


SELECT * FROM Staff WHERE bno IN (SELECT bno FROM Branch
WHERE city='Minsk')
WITH CHECK OPTION CONSTRAINT check_Minsk;

Когда для представления установлен режим контроля, СУБД автоматиче- ски проверяет каждую операцию INSERT или UPDATE, выполняемую над представлением, чтобы удостовериться в том, что полученные в результате строки удовлетворяют условиям отбора в определении представления. Если до- бавляемая или обновляемая строка не удовлетворяет этим условиям, то выпол- нение инструкции INSERT или UPDATE завершается ошибкой; другими сло- вами, операция не выполняется.


Фраза WITH READ ONLY – явно запрещает операции DML над любым представлением. Если команды обновления DML (INSERT, UPDATE, DELETE) можно применить к представлению, то говорят, что представление является об- новляемым (updatable); в противном случае оно является только читаемым (read-only).
После того как представление создано, можно обратиться к таблице сло- варя данных USER_VIEWS для просмотра его определения, включающего ко- манду SELECT.
На выдачу текста о представлении могут повлиять значения следующих параметров команды SET среды SQL*Plus: Maxdata; Arraysize; Long.
Текст команды SELECT хранится в поле типа Long, и для просмотра это- го текста может понадобиться присвоить параметру ARRAYSIZE какое-либо небольшое значение (например, SET ARRAYSIZE = 1).




Достарыңызбен бөлісу:
1   ...   5   6   7   8   9   10   11   12   ...   56




©emirsaba.org 2024
әкімшілігінің қараңыз

    Басты бет