Возврат значения из процедуры

В дополнение к хранимым процедурам многие диалекты SPL поддерживают хранимые функции. От процедур последние отличаются тем, что возвращают значения, тогда как процедуры этого не делают. Допустим, к примеру, что вы определили хранимую процедуру, которая получает идентификатор клиента и вычисляет общую стоимость его заказов. Если определить эту процедуру как функцию, полученная стоимость может быть возвращена в вызывающую программу.

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

Многие диалекты SPL допускают использование хранимых функций в выражениях.

Диалект Transact-SQL не поддерживает хранимые функции, хранимые процедуры Transact-SQL могут возвращать код завершения, для чего в них используется инструкция RETURN. Однако возвращаемое значение может быть только целым. Ноль указывает на успешное выполнение процедуры, а отрицательные значения определяют различные виды ошибок. Все системные хранимые процедуры Sybase Adaptive Server и Microsoft SQL Server следуют этому соглашению. Возвращенное хранимой процедурой значение можно сохранить в локальной переменой, используя следующую форму инструкции execute:

declare sts_val int

execute sts__val = add_cust 'XYZ Corporation’, 2317, 30000.00,

50000.00, 103, 'Chicago'

Возврат значений через параметры

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

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

В дополнение к входным и выходным параметрам Oracle и Sybase(Watcom-SQL) поддерживает параметры, которые одновременно являются и входными, и выходными (inout) Они передаются хранимой процедуре по тем же правилам, что и обычные выходные параметры, и, кроме того, переданные в них значения используются вызываемой процедурой как входные данные.

Условное выполнение

Одним из базовых элементов хранимых процедур является конструкция IF. . . THEN. . .ELSE, используемая для организации ветвлений внутри процедуры.

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

IF search-condition THEN statement-list

... [ ELSEIF search-condition THEN statement-list ] ...

... [ ELSE statement-list ]

... END IF

Циклы

Еще одним базовым элементом хранимых процедур является конструкция для многократного выполнения группы инструкций — проще говоря, цикл. Циклы могут быть разными: в зависимости от используемого диалекта SPL могут поддерживаться циклы FOR со счетчиком итераций (в которых значение целочисленной переменной уменьшается или увеличивается при каждом проходе цикла, пока не достигнет заданного предела) или циклы while, в которых условие продолжения цикла вычисляется в начале или конце группы составляющих его инструкций.

Второй распространенной формой цикла является выполнение последовательности инструкций до тех пор, пока остается или пока не станет истинным заданное условие. Ниже дан примеры такого цикла в Sybase SQL Anywhere. Чтобы этот цикл когда-нибудь остановился, внутри его тела должна осуществляться проверка условия окончания цикла, и если это условие истинно, должна выполняться команда выхода из цикла:

[ statement-label : ]

...[ WHILE search-condition ] LOOP

... statement-list

...END LOOP [ statement-label ]

...

SET i = 1 ;

WHILE i <= 10 LOOP

INSERT INTO Counters( number ) VALUES ( i ) ;

SET i = i + 1 ;

END LOOP ;

...

Или

SET i = 1;

lbl:

LOOP

INSERT INTO Counters( number ) VALUES ( i ) ;

IF i >= 10 THEN

LEAVE lbl ;

END IF ;

SET i = i + 1 ;

END LOOP lbl

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

Другие управляющие конструкции

Некоторые диалекты SPL включают дополнительные управляющие конструкции Например, в Informix инструкция exit прерывает нормальное выполнение цикла ц передает управление инструкции, следующей непосредственно за циклом. Инструкция continue также прерывает нормальное выполнение цикла, но вызывает переход _к следующей итерации цикла. У обеих инструкций имеется по три формы — для каждого из типов циклов, которые они могут прерывать:

• exit for;

• continue for;

• exit while;

• continue while;

• exit foreach;

• continue foreach;

В Transact-SQL единственная инструкция BREAK заменяет все три варианта инструкции exit; инструкция continue в этом диалекте тоже только одна. В Oracle инструкция exit выполняет ту же функцию, что и в Informix, а инструкция continue отсутствует.

Еще один способ изменения хода выполнения хранимых процедур — это переход по метке, выполняемый инструкцией goto. В большинстве диалектов метка представляет собой идентификатор, за которым следует двоеточие. Как правило, выход по метке за пределы цикла не допускается, как не допускается и переход внутрь цикла или условной конструкции. Следует помнить, что, как и в классических языках программирования, использование инструкции goto не поощряется, поскольку она затрудняет понимание и отладку программ.

Циклическая обработка наборов записей

Одной из самых распространенных ситуаций, в которых требуется циклическое выполнение определенных действий, является построчная обработка набора записей, возвращенного некоторым запросом. Во всех основных диалектах SPL для этого предусмотрены специальные конструкции. Концептуально они подобны встраиваемым в клиентские приложения инструкциям DECLARE CURSOR, OPEN, FETCH И CLOSE встроенного SQL или соответствующим вызовам API-функций Однако результат запроса в данном случае направляется не приложению, а хранимой процедуре, которая выполняется самой СУБД. Соответственно, результирующие данные оказываются не в переменных клиентского приложения, а в локальных переменных хранимой процедуры.

Обработка ошибок

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

В Transact-SQL информацию о произошедших ошибках можно получить из специальных системных переменных. Имеется огромное количество глобальных системных переменных, хранящих информацию о состоянии сервера и транзакции, открытых подключениях и т.п. Однако для обработки ошибок чаще всего используются только две из них: @@error — код ошибки, произошедшей при выполнении последней инструкции SQL;

@@sqlstatus — состояние последней операции FETCH.

Признаком "нормального завершения" в обеих переменных является значение 0. Другие значения указывают на ошибки или нестандартные ситуации. В хранимых процедурах Transact-SQL глобальные переменные используются точно так же, как локальные. В частности, их можно применять в условиях циклов и в инструкции if.