Контроль над обновлением представлений (предложение WITH CHECK OPTION)

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

Это представление является обновляемым как по стандарту ANSI/ISO, так и в большинстве коммерческих СУБД. В него можно добавить информацию посредством инструкции INSERT,

СУБД добавит новую строку в исходную таблицу; она будет видна также и в представлении. Но возможна ситуация в которой вновь добавляемая запись не будет удовлетворять условию WHERE запроса, лежащего в основе представления.

В СУБД имеются механизмы обнаружения таких инструкций и предотвращения их выполнение. SQL позволяет организовать этот вид контроля целостности представления путем создания представлений с режимом контроля. Данный режим задается в инструкции create view с помощью предложения with CHECK OPTION:

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

Стандарт SQL2 позволяет также указывать параметр контроля: CASCADE или LOCAL. Этот параметр применим только к тем представлениям, в основе которых лежит не таблица базы данных, а другое представление. В основе этого, исходного, представления может лежать другое представление и т.д. Для каждого из представлений в такой цепочке может быть задан (или не задан) режим контроля. Если представление верхнего уровня создано с предложением with CASCADE CHECK OPTION, то любая попытка обновить такое представление вынудит СУБД просмотреть всю цепочку представлений нижнего уровня и проверить те из них, для которых задан режим контроля. Если же представление верхнего уровня создано с предложением WITH LOCAL check OPTION, то СУБД ограничится проверкой только этого представления. Считается, что параметр CASCADE установлен по умолчанию; его можно не указывать.

Из вышесказанного должно быть понятно, что введение режима контроля приводит к значительным затратам на выполнение инструкций INSERT и update по отношению к "многоуровневым" представлениям. Тем не менее, он играет важную роль в обеспечении целостности базы данных. Если обновляемое представление создается с целью повысить безопасность базы данных, то следует всегда задавать режим контроля. Тогда пользователь не сможет путем модификации представления воздействовать на данные, доступ к которым ему запрещен.

Удаление представления

Во всех основных СУБД существует возможность удаления представлений. Поскольку представления подобны таблицам и не могут иметь совпадающие с ними имена, во многих СУБД для удаления представлений используется инструкция drop table. В других СУБД этой же цели служит отдельная инструкция drop view.

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

Согласно стандарту SQL2, инструкция drop view удалит из базы данных оба представления:

DROP VIEW EASTREPS CASCADE

Параметр CASCADE означает, что СУБД должна удалить не только указанное в инструкции представление, но и все представления, созданные на его основе. В противоположность этому, следующая инструкция DROP view:

DROP VIEW EASTREPS RESTRICT

выполнится с ошибкой, так как параметр restrict означает, что СУБД должна удалить представление только в том случае, если нет других представлений, созданных на его основе. Это служит дополнительной защитой от случайных побочных эффектов при применении инструкции drop view. Стандарт SQL2 требует, чтобы в инструкции drop view обязательно присутствовал или параметр restrict, или cascade, но в большинстве коммерческих СУБД используется инструкция drop view без каких-либо явно заданных параметров. Это сделано для поддержания обратной совместимости с теми продуктами, которые были выпущены до публикации стандарта SQL2. Работа инструкции drop view в таком случае зависит от СУБД.

СНИМКИ (SNAPSHOT)

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

Определение снимка во многом подобно выполнению запроса, за исключением следующего.

1)- Результат выполнения этого запроса хранится в базе данных под указанным именем как отношение, доступ к которому разрешен только для чтения (не считая операции периодического обновления; см. пункт 2).

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

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

Аналогичным образом может потребоваться зафиксировать состояние большого объема данных, которые используются для выполнения сложного запроса или приложения, не требующего модификации исходных данных, опять же, чтобы избегать блокирования обновления данных на время их выполнения или изменения этих данных

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

В общем случае определение снимка имеет следующий синтаксис.

VAR <relvar name> SNAPSHOT <relation exp> <candidate key def list> REFRESH EVERY <now and then> ;

В этом определении для указания периода обновления снимка используется параметр <now and then>, который может принимать, например, следующие значения: MONTH (Месяц), WEEK (Неделя), HOUR (Час), n MINUTES (n минут), MONDAY (Понедельник), WEEKDAY (День недели) и т.п. Следует особо отметить, что для поддержки постоянной синхронизации снимка с одной или несколькими переменными отношения, на основании которых он был создан, может использоваться спецификация в форме REFRESH

[ON] EVERY UPDATE.

Ниже приведен синтаксис оператора DROP, применяемого для удаления определения снимка.

DROP VAR <relvar name> ;

Здесь параметр <relvar name> задает имя удаляемого снимка.

Примечание. Подразумевается, что операция удаления снимка завершится неудачно, если какая-либо переменная отношения в данный момент ссылается на удаляемый снимок. Альтернативным решением может быть расширение приведенного выше определения снимка за счет включения опций RESTRICT и CASCADE. Здесь мы не будем обсуждать эту возможность.

Примечание, касающееся терминологии. Первоначально снимки были известны не под их современным названием, а именовались (фактически почти исключительно) материализованными представлениями. Однако этот термин является крайне неудачным, и, до сих пор некоторые авторы (но не все) применяют термин "материализованное представление" исключительно для обозначения снимков, в отношении которых можно гарантировать, что они всегда будут оставаться актуальными (т.е. для создания которых применяется оператор REFRESH ОN EVERY UPDATE).

Встроенные процедуры и триггеры

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

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

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

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

Концепции хранимых процедур

В своей исходной форме SQL не был полноценным языком программирования. Он задумывался и создавался как язык, предназначенный для выполнения операций над базами данных — создания их структуры, ввода и обновления данных — и особенно для выполнения запросов, SQL может использоваться как интерактивный командный язык: пользователь по очереди вводит инструкции SQL с клавиатуры, а СУБД их выполняет. В этом случае последовательность операций над базой данных определяется ее пользователем. Инструкции SQL могут встраиваться в программы, написанные на других языках программирования, например на С , и тогда последовательность операций над базой данных определяется приложением

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

Конкретные детали зависят от реализации языка, но в целом эти возможности можно описать так:

• Условное выполнение. Конструкция if. . .then. . .else позволяет SQL-процедуре проверить условие и в зависимости от результата выполнить различные действия.

• Циклы. Цикл while или for либо другая подобная структура позволяет многократно выполнять последовательность инструкций SQL до тех пор, пока не выполнится заданное условие окончания цикла. В некоторые реализации языка SQL включены специальные циклы для прохода по всем строкам в таблице результатов запроса.

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

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

• Именованные процедуры. Последовательность инструкций SQL можно объединить в группу, дать ей имя и назначить формальные входные и выходные параметры, так что получится обычная подпрограмма или функция, какие используются в традиционных языках программирования. Созданную таким образом процедуру можно вызывать по имени, передавая ей нужные значения в качестве входных параметров. Если она является функцией, возвращающей значение, то его можно использовать в выражениях.

Набор элементов, реализующих все эти возможности, составляет язык хранимых процедур (SPL — Stored Procedure Language). Впервые механизм хранимых процедур был предложен компанией Sybase в ее популярном продукте Sybase SQL Server. С тех пор их поддержка была встроена во многие СУБД. Одни компании смоделировали в своих продуктах конструкции языков С или Pascal, тогда как другие предпочли сохранить исходный стиль языка SQL, чтобы все его инструкции, как языков DDL и DML, так и языка хранимых процедур, были единообразны.

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

Создание хранимой процедуры

Во многих распространенных диалектах для создания хранимой процедуры применяется инструкция create procedure. Эта инструкция назначает новой процедуре имя, по которому в дальнейшем процедуру можно будет вызывать. Имя процедуры должно соответствовать общим правилам для идентификаторов SQL. Хранимая процедура может принимать ноль или более параметров. Обычно значения параметров указываются в виде разделенного запятыми списка, заключенного в скобки и следующего за именем вызываемой процедуры. Заголовок хранимой процедуры содержит имена параметров и типы их данных. Для параметров хранимых процедур могут использоваться те же типы данных, которые поддерживаются в СУБД для столбцов таблиц.

Параметры процедуры могут быть входными, при использовании ключевого слова IN, следующего за их именами в заголовке процедуры. Когда процедура вызывается, переданные ей аргументы присваиваются ее параметрам и начинается выполнение тела процедуры. Имена параметров могут использоваться в теле процедуры (и, в частности, в составляющих ее стандартных инструкциях SQL) везде, где допускается наличие констант. Встретив имя параметра, СУБД подставляет на его место текущее значение этого параметра.

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

Параметрам процедуры могут быть назначены значения по умолчанию в операторе CREATE PROCEDURE. Значения по умолчанию должны быть константами (константы могут принимать значения NULL). В качестве примера показана процедура использующая по умолчанию значение NULL для параметра IN, для того чтобы не выполнять запрос, не имеющий смысла (без указания параметра).

CREATE PROCEDURE

CustomerProducts( IN customer_id

INTEGER DEFAULT NULL )

RESULT ( product_id INTEGER,

quantity_ordered INTEGER )

BEGIN

IF customer_id IS NULL THEN

RETURN;

ELSE

SELECT product.id,

sum( sales_order_items.quantity )

FROM product,

sales_order_items,

sales_order

WHERE sales_order.cust_id = customer_id

AND sales_order.id = sales_order_items.id

AND sales_order_items.prod_id=product.id

GROUP BY product.id;

END IF;

END

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

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

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

В диалекте Watcom-SQL процедуры вызываются с помощью оператора CALL и используют параметры для приема значений и передачи результатов. Процедуры могут вызывать другие процедуры и вызывать срабатывание триггеров. Для вызова процедуры необходимо быть владельцем процедуры или иметь право на ее выполнение (или обладать правами DBA).

Список аргументов может быть либо в позиционном формате или в формате с указанием имен параметров.

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

1. Какой вид контроля целостности представления задается с помощью предложения with CHECK OPTION:

2. Команда удаления представлений и ее особенности.

3. Определение снимка (Snapshot), его сравнение с представлением.

4. Дайте определение хранимой процедуры.

Лекция 10