Особенности разработки триггеров и хранимых процедур в СУБД

Федеральное агентство по образованию 

Государственное образовательное учреждение

высшего профессионального образования

САРАТОВСКИЙ ГОСУДАРСТВЕННЫЙ УНИВЕРСИТЕТ

ИМЕНИ Н.Г.ЧЕРНЫШЕВСКОГО 

 

Кафедра прикладная информатика

 

Особенности разработки триггеров  и хранимых процедур в СУБД

КУРСОВАЯ  РАБОТА

Студентки 4 курса механико-математического факультета  431 группы

заочного отделения

Поликарповой  Анны Павловны

 

Научный руководитель

____________________    ____________________      _____________________

Зав. кафедрой

к. ф.-м. н., доцент_____    ____________________     __С.П.Шевырев________

 

Саратов 2012 г.

 

Содержание

 

 ВВЕДЕНИЕ

В данной работе рассмотрим такие объекты Базы данных, как хранимые процедуры. Их виды, свойства, способы реализации, назначения и преимущества. И остановим свое внимание на типе хранимых процедур Триггер. Рассмотрим особенности строения и преимущества данного вида хранимых процедур.

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

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

Хранимая процедура — объект базы данных, представляющий собой набор SQL-инструкций, который компилируется один раз и хранится на сервере. Хранимые процедуры очень похожи на обыкновенные процедуры языков высокого уровня, у них могут быть входные и выходные параметры и локальные переменные, в них могут производиться числовые вычисления и операции над символьными данными, результаты которых могут присваиваться переменным и параметрам. В хранимых процедурах могут выполняться стандартные операции с базами данных (как DDL, так и DML). Кроме того, в хранимых процедурах возможны циклы и ветвления, то есть в них могут использоваться инструкции управления процессом исполнения.

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

CALL процедура(…)

или

EXECUTE процедура(…)

Хранимые процедуры могут возвращать множества результатов, то есть результаты запроса SELECT. Такие множества результатов могут обрабатываться, используя курсоры, другими сохраненными процедурами, возвращая указатель результирующего множества, либо же приложениями. Сохраняемые процедуры могут также содержать объявленные переменные для обработки данных и курсоров, которые позволяют организовать цикл по нескольким строкам в таблице. Стандарт SQL предоставляет для работы выражения IF, LOOP, REPEAT, CASE и многие другие. Сохраняемые процедуры могут принимать переменные, возвращать результаты или изменять переменные и возвращать их, в зависимости от того, где переменная объявлена.

Реализация сохраняемых процедур варьируется от одной СУБД к другой. Большинство крупных поставщиков баз данных поддерживают их в той или иной форме. В зависимости от СУБД, хранимые процедуры могут быть реализованы на различных языках программирования, таких, как SQL, Java, C или C++. Хранимые процедуры, написанные не на SQL, могут самостоятельно выполнять SQL-запросы, а могут и не выполнять. Все более широкое использование хранимые процедур привело к появлению процедурных элементов в языке SQL стандарта SQL:1999 и SQL: 2003 в части SQL/PSM. Это сделало SQL императивным языком программирования. Большинство СУБД предлагают собственные расширения производителя, сверх SQL/PSM.

 Реализация хранимых процедур

Хранимые процедуры обычно создаются с помощью языка SQL или конкретной его реализации в выбранной СУБД. Например, для этих целей в СУБД Microsoft SQL Server существует язык Transact-SQL, в Oracle — PL/SQL, в InterBase и Firebird — PSQL, в PostgreSQL — PL/pgSQL, PL/Tcl, PL/Perl, PL/Python, в IBM DB2 — SQL/PL (англ.), в Informix — SPL. MySQL достаточно близко следует стандарту SQL:2003, её язык похож на SQL/PL.

В некоторых СУБД возможно использование хранимых процедур, написанных на любом языке программирования, способном создавать независимые исполняемые файлы, например, на C++ или Delphi. В терминологии Microsoft SQL Server такие процедуры называются расширенными хранимыми процедурами и являются просто функциями, содержащимися в Win32-DLL. А, например, в Interbase и Firebird для функций, вызываемых из DLL/SO, определено другое название — UDF (User Defined Function). В MS SQL 2005 появилась возможность написания хранимых процедур на любом языке .NET, а от расширенных хранимых процедур в будущем планируется отказаться. СУБД Oracle, в свою очередь, допускает написание хранимых процедур на языке Java.[1] В IBM DB2 написание хранимых процедур и функций на обычных языках программирования является традиционным способом, поддерживаемым с самого начала, а процедурное расширение SQL было добавлено в эту СУБД только в достаточно поздних версиях, после его включения в стандарт ANSI. Также процедуры на Java и С поддерживает Informix.

В СУБД Oracle хранимые процедуры могут объединяться в так называемые пакеты (англ. packages). Пакет состоит из двух частей — спецификации (англ. package specification), в которой указывается определение хранимой процедуры, и тела (англ. package body), где находится её реализация. Таким образом Oracle позволяет отделить интерфейс программного кода от его реализации. В СУБД IBM DB2 хранимые процедуры можно объединять в модули.

 

Назначение и преимущества хранимых процедур

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

 

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

Кроме собственно выполнения запроса, хранимые процедуры позволяют также производить вычисления и манипуляцию данными — изменение, удаление, выполнять DDL-операторы (не во всех СУБД!) и вызывать другие хранимые процедуры, выполнять сложную транзакционную логику. Один-единственный оператор позволяет вызвать сложный сценарий, который содержится в хранимой процедуре, что позволяет избежать пересылки через сеть сотен команд и, в особенности, необходимости передачи больших объёмов данных с клиента на сервер.

В большинстве СУБД при первом запуске хранимой процедуры она компилируется (выполняется синтаксический анализ и генерируется план доступа к данным). В дальнейшем её обработка осуществляется быстрее. В СУБД Oracle выполняется интерпретация хранимого процедурного кода, сохраняемого в словаре данных. Начиная с версии Oracle 10g поддерживается так называемая естественная компиляция (native compilation) хранимого процедурного кода в С и затем в машинный код целевой машины, после чего при вызове хранимой процедуры происходит прямое выполнение её скомпилированного объектного кода.

Возможности программирования

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

Безопасность

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

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

Снижается вероятность таких действий как «внедрение SQL-кода», поскольку хорошо написанные хранимые процедуры дополнительно проверяют входные параметры перед тем, как передать запрос СУБД.

 

Триггеры

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

Триггер (англ. trigger) — это хранимая процедура особого типа, которую пользователь не вызывает непосредственно, а исполнение которой обусловлено действием по модификации данных: добавлением INSERT, удалением DELETE строки в заданной таблице, или изменением UPDATE данных в определенном столбце заданной таблицы реляционной базы данных. Триггеры применяются для обеспечения целостности данных и реализации сложной бизнес-логики. Триггер запускается сервером автоматически при попытке изменения данных в таблице, с которой он связан. Все производимые им модификации данных рассматриваются как выполняемые в транзакции, в которой выполнено действие, вызвавшее срабатывание триггера. Соответственно, в случае обнаружения ошибки или нарушения целостности данных может произойти откат этой транзакции.

Момент запуска триггера определяется с помощью ключевых слов BEFORE (триггер запускается до выполнения связанного с ним события; например, до добавления записи) или AFTER (после события). В случае, если триггер вызывается до события, он может внести изменения в модифицируемую событием запись (конечно, при условии, что событие — не удаление записи). Некоторые СУБД накладывают ограничения на операторы, которые могут быть использованы в триггере (например, может быть запрещено вносить изменения в таблицу, на которой «висит» триггер, и т. п.)

Кроме того, триггеры могут быть привязаны не к таблице, а к представлению (VIEW). В этом случае с их помощью реализуется механизм «обновляемого представления». В этом случае ключевые слова BEFORE и AFTER влияют лишь на последовательность вызова триггеров, так как собственно событие (удаление, вставка или обновление) не происходит.

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

Проектирование триггеров

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

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

Создание триггеров

Триггеры создаются с помощью команды  REATE TRIGGER. Эту команду можно использовать в любом интерактивном инструменте (таком как SQL*Plus или SQL*DBA); при использовании в таких инструментах, одиночная наклонная черта ("/"), вводимая как последняя строка, обозначает конец предложения CREATE TRIGGER. Следующее предложение создает триггер, ассоциированный с таблицей EMP:

CREATE TRIGGER dummy

BEFORE DELETE OR INSERT OR UPDATE ON emp

FOR EACH ROW

WHEN (new.empno > 0)

DECLARE

/* переменные, константы, курсоры и т.п. */

BEGIN

/* блок PL/SQL */

END;

Предложение CREATE собьется, если в блоке PL/SQL будут обнаружены ошибки.

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

Предварительные условия

Прежде чем создавать любые триггеры, необходимо выполнить скрипт CATPROC.SQL под учетным именем SYS. Этот скрипт автоматически запускает все скрипты, которые необходимы для процедурной опции или используются этой  опцией. Местоположение этого файла зависит от операционной системы.

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

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

Опции BEFORE/AFTER

Либо опция BEFORE, либо опция AFTER должна быть указана в предложении CREATE TRIGGER, чтобы точно специфицировать, когда должно исполняться  тело  триггера по отношению к исполнению предложения триггера. В предложении CREATE TRIGGER опция BEFORE или AFTER задается непосредственно перед ключевым словом, обозначающим предложение триггера. Например, триггер DUMMY, который был определен в предыдущем примере, является триггером BEFORE.

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

Предложение триггера

Предложение триггера специфицирует:

• Тип предложения  SQL, которое возбуждает тело триггера. Допустимыми возможностями являются DELETE, INSERT и UPDATE. В спецификацию предложения триггера могут быть включены одна, две или все три этих опции.
• Таблицу, ассоциированную с триггером. Заметим, что в предложении триггера может быть специфицирована ровно одна таблица (но не обзор).

Например, предложениями, возбуждающими триггер DUMMY, являются любые предложения DELETE, INSERT или UPDATE по таблице EMP. Любое из следующих предложений возбудит триггер DUMMY, показанный в предыдущем примере: