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

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

Основное назначение хранимых процедур - это функциональное расширение схемы базы данных. Хранимая процедура может содержать некоторый нетривиальный набор операторов, реализующий то или иное логическое действие. Например, администратор банковской системы разработал хранимую процедуру, которая реализует функцию "занести на счет номер X сумму Y". Программист, разрабатывающий приложение пользуется этой процедурой, но не знает КАК именно она это делает. В результате:

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

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

• - независимо от типа рабочего места одно и тоже действие выполняется гарантированно одними и теми же операторами - выше надежность разработанной системы;

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

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

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

Языки написания хранимых процедур в настоящий момент сильно различается у разных производителей. Для серверов Informix язык для создания хранимых процедур носит название SPL - Stored Procedure Languages. Именно его (точнее, его подмножество), мы и рассмотрим.

Хранимая процедура создается оператором

CREATE PROCEDURE <имя процедуры> (<формальные параметры>) <тело хранимой процедуры> END PROCEDURE

Если хранимая процедура будет возвращать какие-то значения, то добавляется раздел RETURNING, в котором перечислены типы возвращаемых значений:

CREATE PROCEDURE <имя процедуры> (<формальные параметры>) RETURNING <тип 1>, <тип 2> ... <тело хранимой процедуры> END PROCEDURE

Хранимая процедура может получать и возвращать произвольное число значений разных типов. Типы входных параметров и выходных значений могут быть любыми, доступными в SQL, за исключением SERIAL, BYTE и TEXT. Вместо типа SERIAL надо указывать тип INTEGER.

Примеры описаний хранимых процедур:

CREATE PROCEDURE incr_account (account_no INTEGER, quantity MONEY(20,2)) ..... END PROCEDURE

CREATE PROCEDURE add_new_user (name CHAR(20) default NULL) RETURNING INTEGER {серийный номер нового пользователя} ..... END PROCEDURE

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

Хранимая процедура, хоторая больше не нужна, может быть удалена. Для этого используется оператор:

DROP PROCEDURE <имя хранимой процедуры>

Например:

DROP PROCEDURE add_new_user

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

EXECUTE PROCEDURE <имя процедуры> (<фактические параметры>)

Например:

EXECUTE PROCEDURE incr_account (213917008, 23000000.00)

Если хранимая процедура возвращает какие-то значения (то есть в ее описании есть раздел RETURNING), то при исполнении этой процедуры в оператор EXECUTE PROCEDURE надо добавить раздел INTO с перечислением имен переменных (иногда вместо INTO используется слово RETURNING):

EXECUTE PROCEDURE <имя процедуры> (<фактические параметры>) INTO <имя переменной>, ...

Например:

EXECUTE PROCEDURE add_new_user ("Прутков") INTO user_id

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

EXECUTE PROCEDURE incr_account (123456789, 1500000.00) EXECUTE PROCEDURE incr_account (account_no=123456789, quantity=1500000.00) EXECUTE PROCEDURE incr_account (quantity=1500000.00, account_no=123456789)

Если при исполнении хранимой процедуры значения каких-либо фактических параметров не указаны, то используются значения по умолчанию. Значения по умолчанию задаются при описании хранимой процедуры с помощью ключевого слова DEFAULT. Например, в описанной выше процедуре add_new_user для параметра name задано значение по умолчанию - NULL.

Язык хранимых процедур

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

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

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

В разделе исполняемых операторов могут использоваться любые операторы из таблицы 1, а также любой из SQL-операторов, за исключеием тех, которые создают, удаляют, открывают или закрывают базу данных. При обращении к хранимой процедуре управление передается первому оператору в ее теле. Исполнение оператора RETURN завершает хранимую процедуру и возвращает исполнение в то место, откуда хранимая процедура была вызвана. Хранимая процедура завершаетя также и при достижении оператора END PROCEDURE. При этом никаких значений в вызывающую программу не возвращается.

В качестве примера рассмотрим реализацию хранимых процедур, приведенных при рассмотрении оператора CREATE PROCEDURE:

CREATE PROCEDURE incr_account (account_no INTEGER, quantity MONEY(20,2)) UPDATE accounts SET value = value + quantity WHERE account_id = account_no END PROCEDURE

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

CREATE PROCEDURE add_new_user (name CHAR(20) default NULL) RETURNING INTEGER {серийный номер нового пользователя} DEFINE user_id INTEGER; INSERT INTO users(lname) VALUES (name); LET user_id = SQLCA.SQLERR[2]; RETURN user_id END PROCEDURE

Рассмотрим основные операторы, имеющиеся в языке хранимых процедур Informix SPL (SPL - это аббревиатура отStored Procedure Language):

CALL - то же самое, что и EXECUTE PROCEDURE.

CONTINUE - продолжить выполнение цикла. После этого слова надо указать какой именно цикл надо продолжить выполнять -FOR/WHILE/FOREACH. Примеры:

CONTINUE FOR .... CONTINUE FOREACH

DEFINE - определить внутреннюю переменную. Для каждой переменной надо задать ее имя и ее тип:

DEFINE <имя переменной> <тип>

Примеры:

DEFINE user_id INTEGER DEFINE comp_name CHAR(40)

EXIT - прекратить выполнение цикла. После этого слова надо указать какой именно цикл надо прекратитьвыполнять - FOR/WHILE/FOREACH. Примеры:

EXIT WHILE .... EXIT FOR

FOR - цикл со счетчиком. Аналогичен оператору цикла со счетчиком в других языках программирования. Значения шага, начального и конечного значений вычисляются заранее, до начала выполнения операторов тела цикла:

FOR <переменная> = <целое> TO <целое> STEP <целое> <операторы> END FOR

Если шаг (STEP) не указан, то он принимается равным 1 если начальное значение меньше конечного, и -1, если нет. Пример:

FOR i = 1 TO 14 .... END FOR

IF - условный оператор. Проверяет условие и, если оно верно, выполняет операторы после слова THEN. Если не верно, и присутствует раздел ELSE - то выполняются операторы после слова ELSE:

IF <условие> THEN <операторы> END IF IF <условие> THEN <операторы> ELSE <операторы> END IF

Если после слова ELSE по логике должен следовать другой оператор IF, то такое сочетание ELSE IF ... END IF END IF можно записывать более компактно: ELIF ... END IF. Пример:

IF j > 20 THEN RETURN j ELIF j > 10 THEN RETURN 10 ELSE RETURN 0 END IF

LET - оператор присваивания. Вычисляет выражение и присваивает его значение указанной переменно:

LET <имя переменной> = <выражение>

Выражение, которое может быть здесь использовано, очень похоже на выражение, которое может использоваться в операторе SELECT. Примеры:

LET i = 0 LET str1 = "абвгдежз"

RAISE EXCEPTION - возбудить ошибку. Если на данную ошибку не стоит реакция (нет соответствующего оператора ON EXCEPTION), то исполнение хранимой процедуры завершается и в вызвавшую программу "передается" данная ошибка:

RAISE EXCEPTION <номер ошибки>

Можно в качестве номера ошибки использовать как предопределенные, стандартные номера, так и свои собственные.

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

RETURN <выражение 1>, ....

SYSTEM - позволяет выполнить внешнюю команду. Имя этой внешней команды передается операционной системе, то есть это может быть или встроенная команда ОС, или исполнимый файл:

SYSTEM <символьная строка> SYSTEM <имя символьной переменной>

Например, можно выполнить командный файл my_cmd:

SYSTEM "/usr/local/bin/my_cmd"

WHILE - цикл с завершением по условию. Аналогичен циклу WHILE в других языках программирования:

WHILE <условие> <операторы> END WHILE

Пример:

LET i = 1 WHILE i = 1 EXECUTE PROCEDURE my_proc RETURNING I END WHILE

BEGIN ... END - блок операторов. В блоке операторов можно определять и использовать локальные переменные. Допустимые любые другие операторы SPL. Пример:

BEGIN DEFINE i CHAR(20) LET i = "Ну, погоди!" IF ... THEN BEGIN DEFINE i INTEGER LET i = 0 .... END END IF END