Лекции и практики (1). Курс лекций и материалы для практических занятий
 Скачать 1.01 Mb.
|
я форма:CASE <проверяемое выражение> WHEN <сравниваемое выражение 1> THEN <возвращаемое значение 1> [… WHEN <сравниваемое выражение N> THEN <возвращаемое значение N>] [ELSE <возвращаемое значение>] END я форма:CASE WHEN <предикат 1> THEN <возвращаемое значение 1> [… WHEN <предикат N> THEN <возвращаемое значение N>] [ELSE <возвращаемое значение>] END Все предложения WHENдолжны иметь одинаковую синтаксическую форму, то есть нельзя смешивать первую и вторую формы. При использовании первой синтаксической формы условие WHENудовлетворяется, как только значение проверяемого выражения станет равным значению выражения, ука- занного в предложении WHEN. При использовании второй синтаксической формы условие WHEN удовлетворяется, как только предикат принимает значе- ние TRUE. При удовлетворении условия оператор CASE возвращает значение, указанное в соответствующем предложении THEN. Если ни одно из условий WHEN не выполнилось, то будет использовано значение, указанное в предло- жении ELSE. При отсутствии ELSE, будет возвращено NULL-значение. Если удовлетворены несколько условий, то будет возвращено значение предложения THEN первого из них, так как остальные просто не будут проверяться. Примерыиспользования операцииCASE: Вывести список сотрудников с указанием количества сыновей и дочерей: SELECT e.name, count(case when sex='м' then 1 else null end) sons, count(case when sex='ж' then 1 else null end) daughters FROM emp e, children c WHERE e.tabno=c.tabno GROUP BY e.name;
Обратите внимание: бездетные сотрудники в список не вошли. Вывести список сотрудников с указанием тех, у кого самая высокая и самая низкая зарплата на предприятии: SELECT e.name, salary, (case salary when (select max(salary) from emp) then 'самая высокая' when (select min(salary) from emp) then 'самая низкая' else 'средняя' end) as note FROM emp e;
Работа с представлениями Представление (view, обзор) – это хранимый запрос, создаваемый на ос- нове команды SELECT. Представление реально не содержит данных. Запрос, определяющий представление, выполняется тогда, когда к представлению про- исходит обращение с другим запросом, например, SELECT,UPDATEи т.д. Назначение представлений: Хранение сложных запросов. Представление данных в виде, удобном пользователю. Сокрытие конфиденциальной информации. Предоставление дифференцированного доступа к данным. Создание представления выполняется командой CREATEVIEW: CREATE [ OR REPLACE ] VIEW <имя представления> [ (<список имён столбцов>) ] AS <запрос> [ WITH CHECK OPTION ]; Запрос (команда SELECT), на основании которого создаётся представле- ние, называется определяющим запросом, а таблицы, к которым происходит обращение в определяющем запросе – базовыми таблицами. Определяющий запрос по стандарту SQL не может включать фразу ORDER BY, хотя большин- ство СУБД позволяют включать эту фразу в определяющий запрос. Если не указывать имена столбцов, то они получат названия по именам, перечисленным в списке выбора определяющего запроса. Указывать имена столбцов представления обязательно, если список выбора содержит вычисляе- мые поля, константы, функции (в том числе, агрегирующие) или столбцы с одинаковыми именами из разных таблиц. Имена можно указать в списке имён после имени представления (как в примере ниже), а можно указать алиасы для отдельных столбцов результата (например, select name, salary*0.87 AS sal,…). Преимущество имеет список имён, указанный после имени представления. Примеры: Создать представление "Сотрудники с детьми" (для удобного представления данных о детях сотрудников): CREATE VIEW emp_child(depno, name, child, sex, born) AS SELECT e.depno, e.name, c.name, c.sex, c.born FROM emp e, children c WHERE e.tabno = c.tabno; SELECT * FROM emp_child;
Создать представление "Сотрудники 2-го отдела" (для предоставления пол- ного доступа к данным о сотрудниках 2-го отдела начальнику этого отдела): CREATE VIEW emp2 AS SELECT * FROM emp WHERE depno = 2; SELECT * FROM emp2;
Создать представление "Сотрудники" (без данных о зарплате, для сокрытия конфиденциальной информации): CREATE VIEW employees AS SELECT tabno, depno, name, post, born, phone FROM emp; Представление может быть обновляемым и не обновляемым. Обновляе- мым является представление, при обращении к которому можно обновить базо- вую таблицу. Примеробновлениябазовойтаблицыempчерезпредставлениеemp2: UPDATE emp2 SET salary = 48000 WHERE tabno = '100'; Изменения будут произведены в базовой таблице и отразятся в представлении. SELECT * FROM emp2;
Вносимые изменения могут выйти за рамки определяющего запроса и по- этому не будут видны через представление. Если необходимо защитить данные от такого вмешательства, то нужно в команде создания представления указать ключевые слова WITH CHECK OPTION: тогда система отвергнет изменения, выходящие за рамки определяющего запроса. По стандарту SQL-2 представление не является обновляемым, если опре- деляющий запрос: содержит ключевое слово DISTINCT; содержит множественные операции (UNIONи др.); содержит предложение GROUPBY; ссылается на другое необновляемое представление; содержит вычисляемые выражения в списке выбора; выбирает данные более чем из одной таблицы. Многие СУБД поддерживают т.н. частично обновляемые представления, т.е. такие представления, над которыми допускаются отдельные операции мо- дификации данных. Например, если представление содержит вычисляемое вы- ражение в списке выбора, то СУБД позволяет менять данные во всех полях, кроме вычисляемого поля. Общее правило определения частичной обновляемо- сти представления такое: если СУБД может однозначно определить, к какой строке и к какому полю таблицы применить изменения, она их применит. |