Билеты и ответы, страница 7

{CREATE | ALTER } PROC[EDURE] имя_процедуры [;номер]

[{@имя_параметра тип_данных } [VARYING ] [=default][OUTPUT] ][,...n]

[WITH { RECOMPILE | ENCRYPTION | RECOMPILE, ENCRYPTION }]

[FOR REPLICATION]

AS

sql_оператор [...n]

Удаление хранимой процедуры осуществляется командой:

DROP PROCEDURE {имя_процедуры} [,...n]

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

[[ EXEC [ UTE] имя_процедуры [;номер]

[[@имя_параметра=]{значение | @имя_переменной}

[OUTPUT ]|[DEFAULT ]][,...n]

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

EXEC my_proc1 или my_proc1

Пример Процедура без параметров. Создать процедуру для уменьшения цены товара первого сорта на 10%.

CREATE PROC my_proc2

AS

UPDATE Товар SET Цена=Цена*0.9

WHERE Сорт=’первый’

1. Т-SQL. Курсоры: назначение, описание, применение. Пример

Курсор — ссылка на контекстную область памяти. В некоторых реализациях информационно-логического языка SQL (Oracle, Microsoft SQL Server) — получаемый при выполнении запроса результирующий набор и связанный с ним указатель текущей записи.

В PL/SQL поддерживаются два типа курсоров: явные и неявные. Явный курсор объявляется разработчиком, а неявный курсор не требует объявления.

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

Курсор может быть объявлен в секциях объявлений любого блока PL/SQL, подпрограммы или пакета.

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

• Оператор CURSOR выполняет объявление явного курсора.

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

• Оператор FETCH выполняет последовательное извлечение строк из результирующего набора от начала до конца.

• Оператор CLOSE закрывает курсор и освобождает занимаемые им ресурсы

Атрибуты курсора

•  %ISOPEN — возвращает значение TRUE, если курсор открыт.

•  %FOUND — определяет, найдена ли строка, удовлетворяющая условию.

•  %NOTFOUND — возвращает TRUE, если строка не найдена.

•  %ROWCOUNT — возвращает номер текущей строки.

1. Т-SQL. Триггеры и их назначение. Типы триггеров. Операторы создания, изменения, включения/отключения, удаления триггеров. Ограничения использования триггеров. Примеры.

Триггеры являются одной из разновидностей хранимых процедур. Их исполнение происходит при выполнении для таблицы какого-либо оператора языка манипулирования данными (DML). Триггеры используются для проверки целостности данных, а также для отката транзакций.

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

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

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

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

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

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

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

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

• поддержка репликации.

Основной формат команды CREATE TRIGGER показан ниже:

CREATE TRIGGER имя_триггера

INSTEAD OF | AFTER <триггерное_событие>

ON <имя_таблицы>

[REFERENCING

<список_старых_или_новых_псевдонимов>]

[FOR EACH { ROW | STATEMENT}]

[WHEN(условие_триггера)]

<тело_триггера>

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

Типы триггеров

В SQL Server существует два параметра, определяющих поведение триггеров:

• AFTER. Триггер выполняется после успешного выполнения вызвавших его команд. Если же команды по какой-либо причине не могут быть успешно завершены, триггер не выполняется. Следует отметить, что изменения данных в результате выполнения запроса пользователя и выполнение триггера осуществляется в теле одной транзакции: если произойдет откат триггера, то будут отклонены и пользовательские изменения. Можно определить несколько AFTER-триггеров для каждой операции (INSERT, UPDATE, DELETE). Если для таблицы предусмотрено выполнение нескольких AFTER-триггеров, то с помощью системной хранимой процедуры sp_settriggerorder можно указать, какой из них будет выполняться первым, а какой последним. По умолчанию в SQL Server все триггеры являются AFTER-триггерами.

• INSTEAD OF. Триггер вызывается вместо выполнения команд. В отличие от AFTER-триггера INSTEAD OF-триггер может быть определен как для таблицы, так и для просмотра. Для каждой операции INSERT, UPDATE, DELETE можно определить только один INSTEAD OF-триггер.

Триггеры различают по типу команд, на которые они реагируют.

Существует три типа триггеров:

• INSERT TRIGGER – запускаются при попытке вставки данных с помощью команды INSERT.

• UPDATE TRIGGER – запускаются при попытке изменения данных с помощью команды UPDATE.

• DELETE TRIGGER – запускаются при попытке удаления данных с помощью команды DELETE.

Конструкции [ DELETE] [,] [ INSERT] [,] [ UPDATE] и FOR | AFTER | INSTEAD OF } { [INSERT] [,] [UPDATE] определяют, на какую команду будет реагировать триггер. При его создании должна быть указана хотя бы одна команда. Допускается создание триггера, реагирующего на две или на все три команды.

Аргумент WITH APPEND позволяет создавать несколько триггеров каждого типа.

При создании триггера с аргументом NOT FOR REPLICATION запрещается его запуск во время выполнения модификации таблиц механизмами репликации.

Конструкция AS sql_оператор[...n] определяет набор SQL- операторов и команд, которые будут выполнены при запуске триггера.

Отметим, что внутри триггера не допускается выполнение ряда операций, таких, например, как:

• создание, изменение и удаление базы данных;

• восстановление резервной копии базы данных или журнала транзакций.

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

программирование триггера

При выполнении команд добавления, изменения и удаления записей сервер создает две специальные таблицы: inserted и deleted. В них содержатся списки строк, которые будут вставлены или удалены по завершении транзакции. Структура таблиц inserted и deleted идентична структуре таблиц, для которой определяется триггер. Для каждого триггера создается свой комплект таблиц inserted и deleted, поэтому никакой другой триггер не сможет получить к ним доступ. В зависимости от типа операции, вызвавшей выполнение триггера, содержимое таблиц inserted и deleted может быть разным:

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

• команда DELETE – в таблице deleted будут содержаться все строки, которые пользователь попытается удалить; триггер может проверить каждую строку и определить, разрешено ли ее удаление; в таблице inserted не окажется ни одной строки;

• команда UPDATE – при ее выполнении в таблице deleted находятся старые значения строк, которые будут удалены при успешном завершении триггера. Новые значения строк содержатся в таблице inserted. Эти строки добавятся в исходную таблицу после успешного выполнения триггера.

Для удаления триггера используется команда

DROP TRIGGER {имя_триггера} [,...n]

1. Т-SQL. Ссылочная целостность. Правила ссылочной целостности и поддержка их с помощью триггеров. Примеры.

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

• обязательные данные;

• ограничения для доменов полей;

• целостность сущностей;

• ссылочная целостность;

• требования конкретного предприятия.

Ссылочная целостность

Внешние ключи представляют собой столбцы или наборы столбцов, предназначенные для связывания каждой из строк дочерней таблицы, содержащей этот внешний ключ, со строкой родительской таблицы, содержащей соответствующее значение потенциального ключа. Стандарт SQL предусматривает механизм определения внешних ключей с помощью предложения FOREIGN KEY, а фраза REFERENCES определяет имя родительской таблицы, т.е. таблицы, где находится соответствующий потенциальный ключ. При использовании этого предложения система отклонит выполнение любых операторов INSERT или UPDATE, с помощью которых будет предпринята попытка создать в дочерней таблице значение внешнего ключа, не соответствующее одному из уже существующих значений потенциального ключа родительской таблицы. Когда действия системы выполняются при поступлении операторов UPDATE и DELETE, содержащих попытку обновить или удалить значение потенциального ключа в родительской таблице, которому соответствует одна или более строк дочерней таблицы, то они зависят от правил поддержки ссылочной целостности, указанных во фразах ON UPDATE и ON DELETE предложения FOREIGN KEY. Если пользователь предпринимает попытку удалить из родительской таблицы строку, на которую ссылается одна или более строк дочерней таблицы, язык SQL предоставляет следующие возможности:

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

• SET NULL - выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы записывается значение NULL;

• SET DEFAULT - выполняется удаление строки из родительской таблицы, а во внешние ключи всех ссылающихся на нее строк дочерней таблицы заносится значение, принимаемое по умолчанию;

• NO ACTION - операция удаления строки из родительской таблицы отменяется. Именно это значение используется по умолчанию в тех случаях, когда в описании внешнего ключа фраза ON DELETE опущена.

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

Определитель MATCH позволяет уточнить способ обработки значения NULL во внешнем ключе.

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

В операторе CREATE TABLE используется необязательная фраза DEFAULT, которая предназначена для задания принимаемого по умолчанию значения, когда в операторе INSERT значение в данном столбце будет отсутствовать.