Читать интересную книгу Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ - Хелен Борри

Следующий оператор создает естественно изменяемый просмотр:

CREATE VIEW EMP_MNGRS (FIRST, LAST, SALARY) AS

SELECT FIRST_NAME, LAST_NAME, SALARY

FROM EMPLOYEE

WHERE JOB_CODE = 'Mngr'

WITH CHECK OPTION;

Поскольку предложение WITH CHECK OPTION включено в эту спецификацию, приложения не смогут изменять значение столбца JOB_CODE, даже если не было нарушения ограничения внешнего ключа для этого столбца в базовой таблице.

Изменение поведения изменяемых просмотров

Альтернативное поведение естественно изменяемых просмотров может быть задано с использованием триггеров. Для конкретной фазы операции (BEFORE/AFTER) триггеры просмотра вызываются до триггеров базовой таблицы. Следовательно, можно бережно и умело использовать просмотры для замены поведения обычного триггера базовой таблицы на планируемое.

Однако также можно создать разрушение при наличии плохо спланированных триггеров просмотров. Проверяйте, проверяйте, проверяйте!

Изменить определение просмотра?

Термины изменяемый и только для чтения относятся к тому, как может осуществляться доступ к данным из базовых таблиц, а не к тому, может ли изменяться определение просмотра. Firebird не предоставляет синтаксис ALTER VIEW.

Для изменения определения просмотра вы должны удалить просмотр, а затем заново его создать.

Удаление просмотра

Оператор DROP VIEW позволяет владельцу просмотра удалять его описание из базы данных. Он не влияет на базовые таблицы, связанные с просмотром.

Синтаксис:

DROP VIEW имя-просмотра;

Операция DROP VIEW не будет выполнена, если вы не соединены как владелец просмотра, или если этот просмотр используется в другом объекте, таком как просмотр, хранимая процедура, триггер для другого просмотра, определение таблицы или ограничение CHECK.

Следующий оператор удаляет определение просмотра:

DROP VIEW SOB_DEPT;

Использование просмотров в SQL

В SQL просмотр ведет себя во многих отношениях как обычная таблица. Вы можете осуществлять выборку из него, используя или нет предложения ORDER BY, GROUP BY или WHERE.

Если просмотр является естественно изменяемым, или он был сделан изменяемым с помощью триггера и существуют соответствующие привилегии SQL, вы можете выполнять для него позиционированные и поисковые операции изменения, добавления и удаления, которые будут оперировать с базовой таблицей. Вы можете также делать следующее:

* создавать просмотры просмотров;

* выполнять выбор из просмотра в модулях PSQL;

* выполнять соединение (JOIN) просмотра с другими просмотрами и таблицами. В некоторых случаях вы можете соединять просмотры с хранимыми процедурами выбора.

Для простой иллюстрации создадим просмотр и хранимую процедуру для таблицы EMPLOYEE и выполним их соединение. Вот просмотр:

CREATE VIEW V_EMP_NAMES

AS

SELECT EMP_NO, LAST_NAME, FIRST_NAME

FROM EMPLOYEE ^

COMMIT ^

А вот хранимая процедура:

CREATE PROCEDURE P_EMP_NAMES

RETURNS (

EMP_NO SMALLINT;

EMP_NAME VARCHAR (35))

AS

BEGIN

FOR SELECT EMP_NO, FIRST_NAME || ' ' || LAST_NAME FROM EMPLOYEE

INTO :EMP_NO, :EMP_NAME

DO

SUSPEND;

END ^

COMMIT ^

Запрос, который их соединяет:

SELECT

V.EMP_NO,

V.LAST_NAME,

V.FI RST_NAME,

P.EMP_NAME

FROM V_EMP_NAMES V

JOIN P_EMPNAMES P

ON V.EMP_NO = P.EMPNO ^

Использование планов запросов для просмотров

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

Оптимизатор трактует ссылку на просмотр так, как если бы базовые таблицы, используемые при создании просмотра, были добавлены в список FROM запроса.

Предположим, просмотр был создан следующим образом:

CREATE VIEW V_PROJ_LEADERS (

PROJ_ID,

PROJ_TITLE,

LEADER_ID,

LEADER_NAME)

AS

SELECT

P.PROJ_ID,

P. PROJ_NAME,

P. TEAM_LEADER,

E.FULL_NAME,

FROM PROJECT P

JOIN EMPLOYEE E

ON P.TEAM_LEADER = E.EMPNO;

Простой запрос к просмотру

SELECT * FROM V_PROJ_LEADERS;

выводит следующий план:

PLAN JOIN (V_PROJ_LEADERS P NATURAL, V_PROJ_LEADERS E INDEX (RDB$PRIMARY7) )

Обратите внимание, что оптимизатор обращается к индексам базовых таблиц (через алиасы р и Е) для лучшего способа поиска в просмотре. Спецификация SELECT в объявлении CREATE VIEW определяет логику выполнения соединения.

Следующий запрос является чуть более сложным. В этот раз просмотр соединяется с таблицей EMPLOYEE PROJECT с помощью первичных ключей таблиц EMPLOYEE и PROJECT. Затем он опять соединяется с таблицей EMPLOYEE для получения ненормализованного списка, который включает имена участников всех проектов, управляемых просмотром:

SELECT

PL.*,

EMP.LAST_NAME

FROM V_PROJ_LEADERS PL

JOIN EKPLOYEE_PROJECT EP

ON PL.PROJ_ID = EP.PROJ_ID

JOIN EMPLOYEE EMP

ON EP.EMP_NO = EMP.EMP_NO;

PLAN JOIN (EMP NATURAL, EP INDEX (RDB$FOREIGN15) , PL P INDEX (RDB$PRIMARY12) ,

PL E INDEX (RDB$PRIMARY?) )

В этот раз индекс внешнего ключа для столбца EMP_NO таблицы EMPLOYEE_PROJECT (с алиасом EP) используется для выбора имен участников проекта из второго "элемента" - EMPLOYEE. Как и в предыдущем случае, соединение внутри просмотра использует первичный ключ таблицы EMPLOYEE для поиска соответствия в TEAM_LEADER.

Если вы решите написать свой план для запроса, который работает с просмотром, вам нужно хорошо знать определение просмотра, понимать индексы и методы доступа.

Известная ошибка в просмотрах в Firebird 1.0.x

Если вы определяете просмотр, который является объединением (union) двух или более наборов, просмотр будет вести себя неправильно при использовании в подзапросе в Firebird 1.0.x. Например, следующий запрос приведет к краху сервера:

SELECT 1 FROM Table1 WHERE EXISTS (

SELECT FIELD1 FROM UNION_VIEW WHERE <условия-поиска> )

Эта ошибка была исправлена до релиза версии 1.5.

Другие наследуемые таблицы

В настоящий момент Firebird поддерживает две другие формы наследуемых таблиц: хранимые процедуры выбора и внешние виртуальные таблицы (External Virtual Table, EVT).

Хранимые процедуры выбора

Расширения Firebird PSQL предоставляют синтаксис для определения хранимой процедуры, которая выводит наследуемый набор данных из чего угодно виртуального: из базы данных, контекстных переменных (даже только из входных переменных), из внешних таблиц или из любой их комбинации. Синтаксис SELECT В PSQL и DSQL предоставляет возможность поиска в этих виртуальных таблицах, как если бы они были реальными таблицами.

Выходной набор хранимой процедуры выбора определяется как набор выходных переменных с использованием предложения RETURNS из оператора CREATE PROCEDURE. Выходные данные создаются в результате цикла по набору курсора, определенному в операторе SELECT, В процессе чтения значений указанных столбцов в эти выходные переменные или в объявленные локальные переменные. Внутри цикла возможно выполнение практически любых операций манипулирования данными, включая использование внутренних циклов. Хранимая процедура выбора может быть вызвана из другой хранимой процедуры. Все, что может быть выбрано, вычислено или наследовано, может преобразовываться в выход.

В качестве простой иллюстрации следующее объявление хранимой процедуры содержит цикл, в котором последовательно передаются строки (по одной за раз) в выходной буфер:

CREATE PROCEDURE SHOW_JOBS_FOR_COUNTRY (

COUNTRY VARCHAR(15))

RETURNS ( /* виртуальная таблица */

CODE VARCHAR (11) ,

TITLE VARCHAR(25),

GRADE SMALLINT)

AS

BEGIN

FOR SELECT job_code, job_title, job_grade FROM job

WHERE JOB_CODNTRY = :COUNTRY

INTO :CODE, :TITLE, :GRADE

DO

BEGIN /* начало цикла */

CODE = 'CODE: ' || CODE; /* немного похулиганить с этим значением */

SUSPEND; /* выводит одну строку цикла */

END

END

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

SELECT * FROM SHOW_JOBS_FOR_COUNTRY ('England');

CODE TITLE GRADE

CODE: Admin Administrative Assistant 5

CODE: Eng Engineer 4

CODE: Sales Sales Co-ordinator 3

CODE: SRep Sales Representative 4

Подробнее о создании и использовании хранимых процедур см. в части VII. Хранимые процедуры выбора детально обсуждаются в главе 30.

Внешние виртуальные таблицы

Внешняя виртуальная таблица (EVT) является таблицей, которая получает данные от некоторого внешнего источника данных, а не из базы данных. Результаты запроса к EVT трактуются точно тем же образом, что и результаты любого другого запроса, они выглядят точно так же, как если бы они были получены из таблицы базы данных. Это позволяет интегрировать такие внешние данные, как источники данных реального времени, форматированные данные файлов операционной системы, другие базы данных (включая даже не реляционные базы данных), а также любые другие источники табулированных данных.

Firebird реализует внешние виртуальные таблицы с помощью предложения EXTERNAL FILE оператора CREATE TABLE. Внешние данные читаются из текстовых записей фиксированного формата в обычные столбцы данных Firebird.