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

По числу строк, возвращаемых в качестве результата, можно выделить следующие виды хранимых процедур:

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

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

В теле процедуры выбора размещаются совместно используемые операторы выбора нескольких записей и возврат значений (FOR SELECT, DO SUSPEND), которые и обеспечивают отбор требуемых записей и построчную передачу значений их столбцов в точку вызова.

FOR <выражение SELECT>

DO

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

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

Исходные данные передаются через входные параметры, а результаты возвращаются через выходные параметры.

В зависимости от вида хранимой процедуры различаются способы ее вызова:

• Оператор EXECUTE PROCEDURE предназначен для вызова хранимой процедуры. Формат:

EXECUTE PROCEDURE <procedure_name> [:параметр1; …; параметр(n)]

[RETURNING_VALUES : параметр1; …; параметр(n)]

Лекция №6 11.03.12

Тема: продолжение

Примеры хранимых процедур (на выборку)

SET TERM^;

CREATE PROCEDURE PR_I

RETURNS (

ItemND INTEGER, Item VARCHAR(50), Qty SMALLINT, Notes VARCHAR(20)

)

AS

BEGIN

FOR

SELECT Items.ItemNO, Items.Item, Storage.Qty, Storage.Notes

FROM Items INNER JOIN Storage ON (Storage.Item=Items.ItemNO)

INTO :ItemNO, :Item, :Qty, :Notes

DO

BEGIN

Notes = ’ ’;

IF(Qty < 5) THEN Notes = ‘Срочно доставить’;

IF(Qty >= 5) AND (Qty < 10) THEN Notes = ’ Заказать’;

SUSPEND;

END

END^

SET TERM;^

В этой процедуре используется оператор SELECT, выбирающий данные из связанных между собой таблиц Items и Storages. Выбранные данные сохраняются в выходных аргументах, объявленных в конструкции RETURNS. Затем в составном операторе конструкции DO процеряется текущее значение поля Qty. Если количество <5, в поле Notes вносится значение «Срочно доставить», если кроличество товаров >5 но <10 поле Notes получает значение «Заказать», в остальных случая оно получет в значение пустую строку. Оператор SELECT получет очередную строку данных, помещая значение полей в переменные, указанные в конструкции INTO. Если над этими переменными нужно произвести некоторые действия, и необходимо, чтобы хранимая процедура возвращала значения, следует воспользоваться оператором SUSPEND. Каждый из операторов, составляющих тело хранимой процедурой должен заканчиваться «;», поэтому нужно выбрать другой символ, при помощи которого определение хранимой процедуры будет отделятся от других операторов, для этого нужно воспользоваться оператором SET TERM.

Примеры для выполнимых хранимых процедур

Выполнимые хранимые процедуры предназаначены для выполнения каких-либо действий. Эти действия не обязательно должны быть направлены на извлечение или модификацию информации таблиц БД. Кроме этого процедуры такого типа не должны возвращать какие-либо данные. Главное их назначение – производить необходимые действия. К числу этих действий можно отнести стандартные операции SQL: вставка, модицикация и удаление данных.

Пример на добавление данных в таблицу:

SET TERM^;

CREATE PROCEDURE ADD_Bludo

(

id_kategor SMALLINT, name_bluda CHAR(100), kall INTEGER

)

AS

BEGIN

INSERT INTO Bludo (id_kategor,name_bluda,kall)

VALUES (:id_kategory, :name_bluda, :kall);

SUSPEND;

END^

В таблицах локальных баз данных для первичного ключа обычно используется автоинкрементный тип данных, это гарантирует, что каждая новая запись в таблице будет получать для своего первичного ключа уникальное значение. Сервер interbase не поддерживает типы данных, аналогичные автоинкрементым. Вместо этого, чтобы получить уникальные значения для поля первичного ключа используется «генератор». Генератор – это механизм, формирующий уникальное число, после чего это число вставляется в указанное поле при операциях вставки или обновления записей. Генератор создается при помощи оператора CREATE GENERATOR:

CREATE GENERATOR generator_name;

В результате выполнения этого оператора в БД будет создан новый генератор с указанным именем и его начальное значение будет установлено в 0. Чтобы получить очередное значение генератора испольщуется функция GEN_ID(generator_name, step). Функция gen_id() выполняет следующие действия: текущее значение генератора, чье имя передано в качестве первого аргумента увеличивается на величину шага, который передается во втором аргументе. Функция возвращает новое значение генератора.

Функцию gen_id() можно вызвать прямо из приложения из тригера или хранимой процедуры, а так же использовать в операторах INSERT, UPDATE или DELETE. Пример:

INSERT INTO Category (Categ_No, Category, Description)

VALUES (gen_id(Category_gen,1),’Принтера’,’ ‘);

Изменить текущее значение генератора можно при помощи оператора SET GENERATOR:

SET GENERATOR generator_name TO начальное_значение;