Рассмотрим следующий SQL сценарий, который создает хранимую процедуру выбора с именем ListAbonent в учебной базе данных: CONNECT c:\sqllab.fdb USER SYSDBA PASSWORD masterkey;

SET TERM !! ;

CREATE PROCEDURE ListAbonent

RETURNS (LAccountCD VARCHAR(6), LFio VARCHAR(20),

LPayDate DATE, LPaySum NUMERIC(15,2), LPayMonth SMALLINT,

LPayYear SMALLINT)

AS BEGIN

FOR SELECT A.AccountCD, A.Fio, P.PayDate, P.PaySum, P.PayMonth, P.PayYear

FROM Abonent A, PaySumma P

WHERE A.AccountCD = P.AccountCD AND P.PaySum > 70 INTO :LAccountCD, :LFio, :LPayDate, :LPaySum, :LPayMonth, :LPayYear

SUSPEND; END !! SET TERM ; !!

Процедура выбора ListAbonent в выходных параметрах возвращает строку, состоящую из номера лицевого счёта абонента (LAccountCD), фамилии абонента (LFio), даты оплаты (LPayDate), суммы оплаты (LPaySum), месяца (LPayMonth) и года (LPayYear), за которые производится оплата, с суммой, большей 70.

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

Таким образом, в процедуре ListAbonent производятся следующие действия:

- запрос SELECT формирует соединение таблиц Abonent, PaySumma и возвращает таблицу результатов запроса;

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

Чтобы вывести все значения, возвращаемые процедурой ListAbonent, необходимо выполнить следующий запрос: SELECT * FROM ListAbonent;.

Результат выполнения запроса к хранимой процедуре выбора ListAbonent представлен на рис. 6.2.

Рис. 6.2. Результат выполнения запроса к процедуре ListAbonent

Результат, возвращаемый данной процедурой, может быть легко получен с помощью запроса SELECT без оформления ХП. Однако использование даже такой несложной процедуры вместо обычного запроса SELECT дает ряд преимуществ (простота доступа, сокращение объёма кода клиентского приложения и другие, описанные ранее), не говоря уже о процедурах, реализующих более сложные алгоритмы.

Рассмотрим пример SQL-сценария изменения определения процедуры ListAbonent для добавления входных параметров Mes и God. В теле следующей процедуры входные параметры Mes и God используются для отбора строк, содержащих информацию об оплатах с суммой больше 70, произведённых абонентом за услуги газоснабжения за заданный месяц (Mes) указанного года

(God):

CONNECT c:\sqllab.fdb USER SYSDBA PASSWORD masterkey; SET TERM !! ;

ALTER PROCEDURE ListAbonent (Mes SMALLINT, God SMALLINT)

RETURNS (LAccountCD VARCHAR(6), LFio VARCHAR(20),

LPayDate DATE, LPaySum NUMERIC(15,2), LPayMonth SMALLINT, LPayYear SMALLINT)

AS BEGIN

FOR SELECT A. AccountCD, A.Fio, P.PayDate, P.PaySum, P.PayMonth,

P.PayYear

FROM Abonent A, PaySumma P WHERE A. AccountCD = P. AccountCD

AND P.PaySum > 70 AND :Mes = P.PayMonth AND :God = P.PayYear INTO :LAccountCD, :LFio, : LPayDate, :LPaySum, :LPayMonth, :LPayYear

SUSPEND;

END !!

SET TERM ; !!

Если процедура выбора определена с указанием входных параметров, то их задание при вызове процедуры в запросе SELECT является обязательным. Например, чтобы вывести все сведения об оплаченных суммах, больших 70, за сентябрь 2001 года, необходимо выполнить следующий запрос: SELECT * FROM ListAbonent (9, 2001);.

Результат выполнения запроса представлен на рис. 6.3.

Рис. 6.3. Результат выполнения запроса к модифицированной процедуре

ListAbonent

Рассмотрим пример процедуры выбора, в которой используется обновляемый явный курсор для позиционированного удаления и обновления строк таблицы. Создадим процедуру ExecReq, которая путем позиционированной модификации строк таблицы Request удаляет все невыполненные ремонтные заявки, а все непогашенные ремонтные заявки преобразует в погашенные. При этом в таблицу Executor заносится дополнительная информация по исполнителям, для которых произведено удаление или погашение заявок. Соответствующий скрипт выглядит следующим образом:

CONNECT c:\sqllab.fdb USER SYSDBA PASSWORD masterkey; ALTER TABLE Executor Add Info VARCHAR(40); SET TERM !! ;

CREATE PROCEDURE Exec Req

RETURNS (ECode INTEGER, EName VARCHAR(20), Info VARCHAR(40)) AS

DECLARE EDate DATE; DECLARE Exec SMALLINT; DECLARE Req CURSOR FOR

(SELECT ExecutorCD, ExecutionDate, Executed FROM Request); BEGIN

OPEN Req;

WHILE (1=1) DO

BEGIN

FETCH Req INTO :ECode, :EDate, :Exec; IF (ROW COUNT=0) THEN LEAVE; IF (EDate IS NULL) THEN BEGIN

DELETE FROM Request WHERE CURRENT OF Req; UPDATE Executor SET Info = DEL NotExec WHERE ExecutorCD=:ECode;

END ELSE IF (Exec=0) THEN

BEGIN

UPDATE Request SET Executed = 1 WHERE CURRENT OF Req; UPDATE Executor SET Info = UPD Executed