Системные хранимые процедуры

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

• SP_ADD<имя> — добавление нового объекта (пользователя, сервера, реплики и т.п.);

• SP_DROP— удаление существующего объекта;

• SP_HELP<имя> — получение информации об объекте или объектах.
Например, процедура sp_helpuser возвращает информацию о пользователях текущей базы данных.

Хотя на расширенных диалектах SQL большинства ведущих СУБД можно писать довольно мощные хранимые процедуры, их возможности все же ограниченны. Одним из их главных ограничений является отсутствие "связи с внешним миром", т.е. Доступа к функциям операционной системы и приложений, работающих в той же системе. Кроме того, расширенные диалекты SQL — это языки очень высокого уровня, не предназначенные для низкоуровневого программирования, обычно выполняющегося на С или C++. Для преодоления этих ограничений в большинстве СУБД можно обращаться к внешним хранимым процедурам.

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

Microsoft SQL Server предоставляет программистам набор системных внешних процедур, обеспечивающих доступ к функциям операционной системы. Например процедура xp__sendmail позволяет отправлять пользователям электронные почтовые сообщения с информацией о событиях, происходящих в базе данных:

XP_SENDMAIL @RECIPIENTS = 'Joe’, 'Sam', SMESSAGE = 'Отчет готов';

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

Informix обеспечивает доступ к функциям операционной системы с помощью специальной инструкции SYSTEM. Кроме того, эта СУБД поддерживает пользовательские внешние процедуры, для объявления которых предназначена инструкция CREATE procedure. Там, где обычно начинается тело хранимой процедуры Informix, помещается предложение external, в котором задается имя, местоположение и язык внешней процедуры. Объявленную таким образом процедуру можно вызывать как обычную хранимую процедуру Informix.

Ту же возможность предоставляют и новые версии Oracle (Oracle8 и выше) — в них внешние процедуры тоже объявляются с помощью инструкции create procedure. Семейство продуктов DB2 компании IBM обеспечивает аналогичный набор возможностей.

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

1. Какие виды параметров существуют у хранимых процедур?

2. Что такое хранимые функции?

3. Какие операторы SPL(stored procedure language) организации циклов?

4. Перечислите преимущества хранимых процедур.

5. Как хранимые процедуры влияют на производительность?

Лекция 11

Примеры реализации операторов SPL в различных СУБД

Управляющие операторы, используемые в процедурах и триггерах (WATCOM SQL)

Составной оператор: BEGIN [ ATOMIC ]

statement-list

END

Оператор проверки условия: IF

IF condition THEN

statement-list

[ ELSEIF condition THEN

statement-list

ELSE statement-list ]

END IF

Выбор одного варианта среди множества альтернатив: оператор CASE

CASE value-expression

... WHEN [ constant | NULL ] THEN

statement-list ...

... [ WHEN [ constant | NULL ] THEN statement-list ] ...

... ELSE statement-list

... END CASE

Операторы циклов.

Цикл с предусловием: WHILE, LOOP

Повторяет набор операторов до тех пор, пока истинно условие.

WHILE condition LOOP

statement-list

END LOOP

Оператор цикла FOR

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

[ statement-label : ]

. . .FOR for-loop-name AS cursor-name

. . . CURSOR FOR statement

. . .[ FOR UPDATE | FOR READ ONLY ]

. . . DO statement-list

. . . END FOR [ statement-label ]

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

Оператор LEAVE

Оператор LEAVE прерывает выполнение составного оператора или оператора цикла.

Оператор CALL – выполняет вызов хранимой процедуры.

[variable = ] CALL procedure-name ( [ expression ,... ] )

[variable = ] CALL procedure-name ( [ parameter-name = expression ,... ] )

Для вызова процедуры необходимо быть владельцем процедуры или иметь право на ее выполнение (или обладать правами DBA).

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

Процедуры могут возвращать значение (например, как индикатор статуса) используя оператор RETURN. Полученное значение можно присвоить переменной для дальнейшего использования.

CREATE VARIABLE returnval INT ;

returnval = CALL proc_integer ( arg1 = val1, ... )

Управляющие операторы Transact-SQL

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

BEGIN … END

IF...ELSE

GOTO

WHILE

WAITFOR

Существуют также CASE-конструкции (наподобие SELECT CASE, DO CASE, SWITCH/BREAK в других языках), но они не предоставляют достаточных возможностей управления, как это имеет место в других языках программирования.

Оператор CASE

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

Switch - С, C++, Delphi;

Select Case - Visual Basic;

Do Case - Xbase;

Evaluate - COBOL.

У оператора CASE в T-SQL есть существенный недостаток: во многих отношениях он является оператором подстановки, а не управляющим оператором.

Eстъ два варианта оператора CASE - с исходным и с булевым выражением. В первом варианте исходное выражение сравнивается с выражением, указанным в каждом элементе WHEN. В документации к SQL Server такой вариант называется простой CASE (simple):

CASE <исходное_выражение>

WHEN <when_выражение> THEN <выражение_результат>

[…n]

[ELSE <выражение_результат>]

END

В рамках второго варианта условное выражение в каждом элементе WHEN оценивается как булево (TRUE или FALSE). В документации этот вариант упоминается как анализируемый CASE (searched):

CASE

WHEN <булево_выражение> THEN <выражение_результат>

[…n]

[ELSE < выражение_резулътат>]

END

Допускается использование оператора CASE внутри оператора SELECT. Эта возможность служит основой эффективного программирования.

Простой оператор CASE

В простом операторе CASE результатом проверки условия является булево значение. Давайте сразу начнем с примера:

USE Northwind

GO

SELECT TOP 10 OrderID, OrderlD % 10 AS 'Последняя цифра', Position =

CASE OrderID % 10

WHEN 1 THEN 'Единица’

WHEN 2 THEN 'Двойка'

WHEN 3 THEN 'Тройка'

WHEN 4 THEN 'Четверка'

ELSE 'Какая-то другая'

END D

FROM Orders

Создание циклов с помощью оператора WHILE

Оператор WHILE устроен точно так же, как и в других языках программирования. Условие проверяется каждый раз в начале .цикла. Цикл повторяется до тех пор, пока условие истинно.

Синтаксис:

WHILE <булево выражение>

<SQL-оператор> |

[BEGIN

<блок_операторов>

[BREAK]

<sql-oператор> | <блок_операторов>

[CONTINUE]

END]

Конечно, можно использовать простой оператор WHILE с одним исполняемым sql-оператором также как это можно делать и с оператором IF), однако на практике этот оператор почти всегда используется с блоком операторов внутри BEGIN...END.

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

Использование оператора BREAK относится к образцам плохого стиля программирования.

Оператор CONTINUE является противоположностью оператора BREAK. Он указывает, что нужно вернуться в начало цикла. Независимо от того, где вы находились в момент выполнения этого оператора, вы немедленно возвращаетесь в начало. При этом опять производится проверка условия на истинность (выход из цикла происходит после того, как условие перестает выполняться).

Оператор WAITFOR

Иногда необходимо сделать паузу и приостановить выполнение кода на некоторое время при этом хочется, чтобы пауза завершалась автоматически без какого-либо вмешательства пользователя.

Для этого используется оператор WAITFOR.

Синтаксис этого оператора очень прост:

WAITFOR

DELAY <'время'> | TIME <'время'>

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

Параметр DELAY

Параметр DELAY определяет промежуток времени, истечения которого необходимо ждать. Нельзя указывать время в днях - только часы, минуты, секунды. Максимальная задержка может составлять 24 часа. Например, можно написать:

• WAITFOR DELAY '01:00'

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

Параметр TIME

Параметр TIME определяет время суток, которого следует ждать. Опять же, нельзя указывать дату - только время суток в 24-часовом формате. Отсюда следует, что продолжительность задержки не может быть больше одних суток. Например:

WAITFOR TIME '01:00'

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

Подтверждение удачного или неудачного выполнения процедуры

с помощью возвращаемого значения

На практике ваша программа получает возвращаемое значение, независимо от того генерируете вы его или нет. При завершении процедуры SQL Server по умолчанию возвращает нулевое значение.

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

RETURN [ <целое_возвращаемое_значение>]

Триггеры

Триггер — это особая хранимая процедура, которая вызывается в ответ на модификацию содержимого базы данных. В отличие от хранимых процедур, созданных с помощью инструкции create procedure, триггер нельзя выполнить с помощью инструкции call или EXECUTE. Каждый триггер связывается с определенной таблицей базы данных, и СУБД сама выполняет его, когда данные в таблице изменяются инструкциейINSERT, DELETE ИЛИ UPDATE).

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

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

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

1. Особенности языка SPL в диалекте Watcom-SQL.

2. Особенности языка SPL в диалекте Transact-SQL.

3. Особенности оператора CASE в Transact-SQL.

4. Дайте определения триггера.

Лекция 12