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

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

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

1. Процедуры выборки в качестве результата своей работы возвращают набор данных либо сообщение об ошибке.

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

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

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

CREATE PROCEDURE name

[(param datatype [, param datatype...])]

[RETURNS (param datatype [, param datatype...])]

AS

< procedure_body>;

< procedure_body> = [< variable_declaration_list> ]

< variable_declaration_list> =

DECLARE VARIABLE var datatype;

[DECLARE VARIABLE var datatype;...]

< block> =

BEGIN

< compound_statement>

[< compound_statement...> ]

END

< compound_statement> = {< block> | statement; }

В параметре name указывается имя хранимой процедуры. Оно должно быть уникальным в рамках базы данных. В параметре param datatype содержится список входных параметров с указанием их типа.

Параметр RETURNS открывает секцию, в которой описываются параметры, возвращаемые хранимой процедурой. Параметр param datatype содержит список возвращаемых параметров с указанием их типа.

Процедура возвращает значения параметров, когда достигает в своем теле выражения SUSPEND.

Ключевое слово AS служит разделителем шапки процедуры и ее тела. Команда DECLARE VARIABLE предназначена для объявления локальной переменной определенного типа, указываемого в параметре var datatype. Далее, в блоке, обозначенном ключевыми словами BEGIN и END, помещается тело хранимой процедуры.

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

Рассмотрим несколько примеров создания хранимых процедур с использованием утилиты IBExpert при разработке информационной системы «Аренда недвижимости».

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

Ниже, приведен SQL-скрипт хранимой процедуры на выборку GET_LEASE_DATA.

CREATE PROCEDURE GET_LEASE_DATA (

TENANT_NO SMALLINT)

RETURNS (

LEASE_ID SMALLINT,

LEASE_DATE TIMESTAMP)

AS

begin

for SELECT distinct lease.id, lease.daytime

from tenant, lease

WHERE tenant.id=: tenant_no and lease.idtenant=tenant.id

INTO: lease_id,: lease_date

DO

suspend;

end^

Результаты выполнения хранимой процедуры GET_LEASE_DATA приведены на рисунках 7.- 8.

Рисунок 7. Ввод входных параметров хранимой процедуры GET_LEASE_DATA с использование утилиты IBExpert

Рисунок 8. Данные полученные в результате выполнения параметров хранимой процедуры GET_LEASE_DATA

Пример 2. Создать хранимую процедуру, которая добавляет владельцев в базу данных «Аренда недвижимости».

Ниже приведен SQL-скрипт хранимой процедуры на выполнение.

CREATE PROCEDURE ADD_OWNER (

OW VARCHAR(10),

ADOW VARCHAR(10))

AS

begin

INSERT INTO owner (owner.fio, owner.adres)

VALUES (: ow,: adow);

suspend;

end

Результаты выполнения хранимой процедуры ADD_OWNER приведены на рисунках 9.- 10.

Рисунок 9. Ввод входных параметров хранимой процедуры ADD_OWNER с использование утилиты IBExpert

Рисунок 10. Данные полученные в результате выполнения хранимой процедуры ADD_OWNER