1.1. Типы просмотров

Просмотры могут быть не обновляемыми и обновляемыми. Представление может быть обновляемым, если выполняется ряд дополнительных условий:

Представление строится на базе единственной таблицы или на базе другого обновляемого представления.

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

Выражение SELECT не должно содержать подзапросов, предиката DISTINCT, выражения HAVING, агрегатных функций, присоединенных таблиц, пользовательских функций или хранимых процедур.

При несоблюдении приведенных выше условий представление не может быть обновляемым.

Команда WITH CHECK OPTION определяет правила, на основе которых модифицируются данные при помощи представления. Представления, созданные с использованием этой команды, указывают серверу на необходимость проверки вставленных или обновленных записей на соответствие условию проверки, определенному выражением WHERE. Значения могут быть добавлены через представления только в те поля, которые определены в представлении. В остальные поля будет помещено значение NULL. В качестве примера можно привести запрос, создающий обновляемое представление на базе таблицы СОМРАСТ_ DISC по всем ее полям:

CREATE VIEW СОМРАCT_VIEW

AS

SELECT * FROM COMPACT_DISC

WHERE PROIZVODITEL = 'BASF'

WITH CHECK OPTION

После этого к представлению можно добавить новую запись:

INSERT INTO COMPACT_VIEW

VALUES ('Музыка света'.'5 ,'SOFT' ,'BASF'):

Пример 2. Создать просмотр, который позволяет вывести список цен по арендным договорам. Ниже приведен SQL-скрипт просмотра RENT_COST.

CREATE VIEW RENT_COST(

ID,

PRICE)

AS

select lease.id, lease.price from lease

;

Результат выполнения просмотра RENT_COST приведен на рисунке 4.

Рисунок 4. Данные, полученные в результате выполнения просмотра RENT_COST

Просмотр RENT_COST можно модифицировать, как показано на рисунке 5.

Результат выполнения модифицированного просмотра RENT_COST приведен на рисунке 6.

Рисунок 5. SQL-скрипт модификации просмотра RENT_COST

Рисунок 6. Данные полученные в результате выполнения модифицированного просмотра RENT_COST

2. Язык psql

Язык PSQL предназначен для написания хранимых процедур. PSQL является структурированным языком.

Блоки BEGIN … END

После объявления переменных процедурные операторы заключаются в операторные скобки BEGIN и END. В процессе разработки логики процедуры могут быть добавлены другие блоки; любой блок может включать другой блок, заключенный в begin и end.

Условные блоки

PSQL распознает два типа условных структур:

ветвление, управляемое блоками if ... then и, возможно, else;

циклическое выполнение блока, пока условие while не станет ложным.

Начиная с версии Firebird 1.5 логические контекстные переменные inserting, updating, deleting и целочисленная контекстная переменная ROW_COUNT доступны в качестве предикатов в блоках, выполняющих операции изменения состояния данных.

Конструкция IF... THEN... ELSE

Конструкция if ... then ... else осуществляет ветвление в программе, проверяя указанное условие. Синтаксис:

IF (<условие>)

THEN <составной-оператор>

[else <составной-оператор>]

<составной-оператор> = {< блок> | <оператор>;}

Предложение условие является предикатом, который должен быть истинным, чтобы выполнился оператор или блок, следующий за THEN. Необязательное предложение else задает альтернативный оператор или блок, который будет выполняться, если условие окажется ложным. Условие может быть любым правильным предикатом.

Конструкция WHILE... DO

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

. . .

while (<условие>) do

<выполнение одного иди более операторов>;

<изменение значения операнда в условии>;

END

. . .

В следующей простой процедуре while проверяет значение переменной i, которая инициализируется как входной аргумент. Блок цикла уменьшает значение i при каждой итерации, и пока i остается больше нуля, значение выходного параметра r увеличивается на значение i. Когда процедура завершается, значение с возвращается вызвавшему приложению.

PROCEDURE MOP (i INTEGER) RETURNS (r INTEGER)

AS

BEGIN

r=0;

WHILE (i > 0) DO

BEGIN

r = r + i;

i = i - 1;

END

END

Переменные

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

Локальные переменные для хранения значений используются только в триггерах и хранимых процедурах.

Контекстные переменные NEW.имястолбца и OLD.имястолбца ограничены использованием в триггерах; они хранят новые и старые значения каждого столбца таблицы, когда оператор DML ожидает завершения.

Другие контекстные переменные, специфичные в PSQL, также доступны в isql и PSQL.

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

Выходные аргументы используются для возвращения значений из хранимых процедур вызвавшим их объектам. Недоступны в триггерах.

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

Маркер двоеточия (:) для переменных

В операторах SQL задавайте для имен переменных префикс в виде двоеточия (:). когда:

переменная используется в операторе SQL;

переменная получает значение в конструкции [for] select . . . into. Опускайте двоеточие во всех других ситуациях.

Операторы присваивания

Процедура присваивает значения переменным с использованием синтаксиса:

переменная = выражение;

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

Локальные переменные

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

Необходимо инициализировать переменные насколько возможно раньше в процедуре. В Firebird 1.5 можно объявлять и инициализировать переменную в одном операторе. Например, каждый следующий оператор допустим для объявления переменной вида счетчик, и инициализировать их нулем.

Входные аргументы

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

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

CREATE PROCEDURE JOBS_COUNTRY ( COUNTRY VARCHAR(25) )

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

Выходные аргументы

Выходной аргумент (параметр) используется для задания значения, возвращаемого из процедуры вызвавшему приложению или модулю PSQL. Если задается множество возвращаемых значений, объявляйте эти аргументы в скобках, отделяя их друг от друга запятыми, следом за ключевым словом returns в заголовке процедуры. Один раз объявленные, они могут использоваться в теле процедуры везде, где могут появиться выражения.

Операторы SELECT... INTO

Используйте оператор select с предложением into для поиска значений столбцов в таблицах и сохранения их в локальных переменных или выходных аргументах.

Одиночный оператор SELECT

Обычный оператор select в PSQL должен возвращать не более одной строки из базы данных. Если оператор возвращает более одной строки, то будет выдано исключение. Предложение order by недопустимо в одиночном select кроме случая, когда используется оператор select first. Обычные правила применяются к входному списку, предложению where и предложению group by, если оно используется. Предложение into требуется и должно быть последним предложением в операторе.

Пример одиночного оператора select в параметризованном запросе SQL в приложении:

SELECT SUM(BUDGET), AVG(BUDGET)

FROM DEPARTMENT

WHERE HEAD_DEP = :head_dep;

Для использования этого оператора в процедуре объявите локальные переменные или выходные аргументы и добавьте предложение into в конец:

DECLARE VARIABLE TOT_EUDGET NUMERIC(18,2);

DECLARE VARIABLE AVG_BUDGET NUMERIC(18,2);

SELECT SUM(BODGET), WG(BODGET)

FROM DEPARTMENT

WHERE HEAD_DEPT = :head_dept

INTO :tot_budget, :avg__budget;

Операторы SELECT для множества строк

Любой модуль PSQL может оперировать с множеством входных строк, полученных с помощью оператора select, когда он содержит структуру цикла, который может "перемещаться" по набору и выполнять одинаковую обработку каждой строки. PSQL не может обрабатывать многострочные наборы другим способом, и при отсутствии контекста цикла многострочная выборка данных вызовет исключение ("Multiple rows in singleton select" — "Множество строк в одиночном операторе select").

Циклы FOR SELECT...

Основным методом реализации структуры цикла для обработки многострочных входных наборов является структура for ... select ... into ... do. Его синтаксис:

FOR SELECT < >

FROM имя-таблицы

[JOIN..]

[WHERE…]

[GROUP BY. . .]

[ORDER BY..]

INTO <сшсок-переменных>:> DO

BEGIN

< блок-обработки>

. . .

[SUSPEND];

END

SUSPEND

Оператор suspend имеет специфическое использование в конструкции for ... select ... into ... do. Если suspend включен в цикл do, то после того, как select прочтет строку в переменные строки, цикл будет ждать, когда эта строка будет выведена в кэш строк сервера перед получением следующей строки из курсора select. Такая операция позволяет создавать в Firebird хранимые процедуры выбора.

Оператор suspend недопустим в триггерах. В выполняемых хранимых процедурах он имеет тот же эффект, что и оператор exit — т, е. он немедленно завершает процедуру, а все операторы, следующие за ним, никогда не будут выполняться.

По контрасту, если процедура выбора имеет выполняемые операторы, следующие за последним оператором suspend в процедуре, то все эти операторы будут выполнены, даже если нет больше строк, возвращаемых вызвавшей программе. Процедуры такого рода завершаются финальным оператором end.

Операторы управления потоком

PSQL содержит множество операторов, которые влияют на поток управления в кодах модулей. Только что рассмотренный оператор suspend передает управление назад вызвавшей процедуре или клиентской программе, ожидая, когда только что обработанная строка будет получена из кэша строк сервера.

EXIT

В процедурах выбора и в выполняемых процедурах exit вызывает переход на финальный оператор end в процедуре. Он не имеет смысла в триггерах.

LEAVE

В Firebird 1,5 появился оператор leave для выхода из блоков. Он заменил оператор break, который был частично реализован в Firebird версии 1.0.x.

EXCEPTION

Оператор exception останавливает выполнение и передает управление первому блоку обработки исключений (блоку, начинающемуся с ключевого слова when), который может обработать исключение. Если для этого исключения не найден обработчик, управление передается финальному оператору end и процедура завершается аварийно.

Оператор exception используется в блоке if ... theN ... else для вызова пользовательских исключений, предварительно определенных как объекты базы данных. Сервep Firebird вызывает свои собственные исключения для SQL и контекста ошибок. Поток управления в этих случаях точно такой же, как и при вызове пользовательских подключений.