Иллюстрированный самоучитель по SQL для начинающих

Постоянно хранимые модули

ITERATE

Оператор ITERATE позволяет изменять последовательность выполнения команд в циклах SQL. Циклы SQL представлены операторами LOOP, WHILE, REPEAT и FOR. Если условие в операторах циклов является истинным или не задано, тут же оператор ITERATE начинает следующую итерацию цикла. Если же условие итерации является ложным или неопределенным, цикл завершается и выполняются следующие за ним команды:

AsteroidPreload4:
SET vcount = 0;
WHILE
vcount < 1000 DO
SET vcount=vcount+l;
INSERT INTO asteroid (AsteroidID)
VALUES (vcount);
ITERATE AsteroidPreload4;
SET vpreload = 'DONE';
END WHILE AsteroidPreload4

Оператор ITERATE все время передает управление в начало цикла WHILE, но до тех пор, пока переменная vcount не будет равна 9999. В следующей итерации переменная vcount увеличится до 10000, выполнится оператор INSERT, после чего оператор ITERATE прекратит итерации, переменная vpreload получит значение 'DONE', а выполнение перейдет к команде, следующей за циклом.

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

Хранимые процедуры (stored procedures) находятся на сервере баз данных, а не на компьютере пользователя, как это было до появления SQL/PSM. Хранимая процедура должна быть определена, после чего ее можно вызвать с помощью команды CALL. Хранение процедуры на сервере уменьшает сетевой обмен и повышает производительность. Команда CALL является единственным сообщением, передаваемым от пользователя к серверу. Ниже приведен пример создания процедуры.

EXEC SQL
CREATE PROCEDURE MatchScore
(IN white CHAR (20),
IN black CHAR (20),
IN result CHAR (3),
OUT winner CHAR (5))
BEGIN ATOMIC
CASE result
WHEN '1-0' THEN
SET winner='white';
WHEN '0-1 ' THEN
SET winner='black';
ELSE
SET winner='draw';
END CASE END;

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

CALL MatchScore ('Kasparov', 'Karpov', '1-0', winner);

Три первых аргумента являются входными параметрами, которые передаются процедуре MatchScore. Четвертый аргумент является выходным параметром, который процедура Match-Score использует для возврата своих результатов в вызывающую программу. В приведенном примере она возвращает значение 'white'.

Хранимые функции

Хранимые функции (stored functions) аналогичны хранимым процедурам. Вместе они называются хранимыми подпрограммами. Они имеют ряд отличий, например, они отличаются по способу их вызова. Хранимые процедуры вызываются в операторе CALL, а хранимые функции могут являться аргументом оператора SQL. Ниже представлен пример определения функции, сопровождаемый примером обращения к этой функции.

CREATE FUNCTION PurchaseHistory (CustID)
RETURNS CHAR VARYING (200)
BEGIN
DECLARE purch CHAR VARYING (200)
DEFAULT '';
FOR x AS SELECT *
FROM transaction t
WHERE t.customerID = CustID
DO
IF x.description <> ''
THEN SET purch=purch || ',';
END IF;
SET purch=purch || x.description;
END FOR
RETURN purch;
END;

Функция, заданная подобным образом, сводит воедино описания всех покупок, произведенных клиентом с определенным идентификационным номером, выбранным из таблицы TRANSACTIONS. Следующая команда, содержащая вызов функции PurchaseHistory, заносит в таблицу информацию о всех покупках, сделанных клиентом с идентификационным номером 314259:

SET customerID=3142 59;
UPDATE customer
SET history=PurchaseHistory (customerlD)
WHERE customerID=314259;