Селективные процедуры

Как вы уже видели, процедуры могут возвращать данные. Если использовать execute procedure, то можно получить всегда только одно значение, или как бы "одну строку", в виде набора значений, но не "несколько строк".

Процедуры в InterBase и Firebird могут выдавать данные таким образом, что их можно вызывать через select. Отсюда и название - "селективные" процедуры.

Пример:

create procedure test

returns (n varchar(35))

as

declare variable ln varchar(20);

declare variable fn varchar(15);

begin

for select last_name, first_name

from employee

into :ln, :fn

do

begin

n=:fn ||' '|| :ln;

suspend;

end

end

Процедура перебирает все записи таблицы employee и возвращает нам "склеенные" имя и фамилию сотрудников. Размер возвращаемой переменной n выбран как сумма размеров столбцов first_name и last_name таблицы empoyee и переменных ln и fn, чтобы во время обработки не возникло переполнения.

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

Если мы вызовем эту процедуру как

EXECUTE PROCEDURE TEST

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

select * from test

SUSPEND можно использовать не только с for select, но и вообще, если вы хотите выдать данные как "запись". Возьмем процедуру с подсчетом суммы зарплат, и расширим ее

create procedure calcsalarysum

returns (fn varchar(15), ln varchar(20), sm numeric(15,2))

as

declare variable s numeric(15,2);

begin

sm=0;

for select first_name, last_name, salary

from employee

into :fn, ln, :s

do

begin

sm=:sm+:s;

suspend;

end

fn='';

ln='';

s=:sm;

suspend;

end;

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

Вот еще один вариант селективной процедуры, который вообще не содержит обращений к данным

create procedure test

returns (i int)

as

begin

i=5;

suspend;

i=3;

suspend;

i=9;

suspend;

end

Если выполнить такую процедуру через select * from test, то она вернет 3 записи со значениями 5, 3 и 9.

Если процедура не имеет выходных переменных, то suspend использовать нельзя (как минимум, в этом нет смысла).

Вызывать селективные процедуры через execute procedure можно, если требуется получить только одно, первое значение. Точно так же можно вызвать селективную процедуру через select * from myproc, и после выборки первой записи закрыть датасет