Предложение INTO <переменные> обязательно и должно быть последним [116] .

На рис. 30.2 проиллюстрированы типичные виды деятельности, которые могут выполняться внутри циклов для генерации выхода в хранимой процедуре выбора.

Рис. 30.2. Операции в процедуре выбора

В следующих примерах мы посмотрим на то, как комбинации операций в PSQL могут представить более интересную область SQL.

Процедура выбора ORG_CHART, которая присутствует в примере базы данных employee, не получает входных аргументов. Она использует цикл FOR ... SELECT, чтобы строить набор из ссылающегося на себя соединения таблицы DEPARTMENT и передавать значения столбцов по одной строке за раз набору переменных - некоторые из них локальные, некоторые объявлены как выходные аргументы.

CREATE PROCEDURE ORG_CHART

RETURNS (

HEAD_DEPT CHAR(25),

DEPARTMENT CHAR(25),

MNGR_NAME CHAR (2 0),

TITLE CHAR(5),

EMP_CNT INTEGER )

AS

DECLARE VARIABLE mngr_no INTEGER;

DECLARE VARIABLE dno CHAR(3);

BEGIN

FOR SELECT h.department, d.department, d.mngr_no, d.dept_no

FROM department d

LEFT OUTER JOIN department h ON d.head_dept = h.dept_no

ORDER BY d.dept_no

INTO :head_dept, :department, :mngr_no, :dno

DO

Каждый раз, когда цикл обрабатывает строку, он помещает значение ключа (MNGR_NO) в локальную переменную MNGR_NO. ЕСЛИ эта переменная имеет пустое значение, процедура создает значения для выходных аргументов MNGR_NAME и TITLE. ЕСЛИ же эта переменная имеет значение, она передается как аргумент поиска вложенному запросу к таблице EMPLOYEE, уникально идентифицирующему строку и выделяющему имя и код работы менеджера отдела. Эти значения передаются остальным выходным аргументам.

BEGIN

IF (:mngr_no IS NULL) THEN

BEGIN

mngr_name = '--TBH--';

title = '' ;

END

ELSE

SELECT full_name, job_code

FROM employee

WHERE emp_no = :mngr_no

INTO :mngr_name, :title;

SELECT COUNT (emp_no)

FROM employee

WHERE dept_no = :dno

INTO :emp_cnt;

Когда присвоены все выходные значения для одной строки, оператор SUSPEND передает строку в кэш. Управление передается опять на начало цикла, когда выполнен следующий запрос на пересылку.

SUSPEND;

END

END^

COMMIT^

Обратите внимание, как аккуратно вложенный запрос обходит ту проблему, которую мы имели с подзапросами в DSQL- мы могли в подзапросе вернуть одно и только одно значение. Если нам нужно много значений, а логика левого соединения не работает, то нам пришлось бы использовать множество подзапросов с множеством наборов алиасов для выделения каждого значения из его курсора.

Синтаксис вызова процедуры выбора очень похож на синтаксис обращения к таблице или к просмотру. Единственным отличием является то, что процедура может иметь входные аргументы:

SELECT <список-столбцов> FROM имя ([аргумент [, аргумент ...]])

WHERE <условия-поиска>

ORDER BY <список-упорядочения>;

Имя процедуры должно быть задано.

Правила входных аргументов идентичны правилам для выполняемых процедур - см. ранее разд. "Значения входных аргументов".

<список-столбцов>- разделенный запятыми список из одного или более выходных параметров, возвращаемых процедурой, или * для выбора всех столбцов.

Выходной набор может быть ограничен условиями поиска в предложении WHERE и упорядочен с помощью предложения ORDER BY.

Эта процедура не имеет входных параметров, следовательно, вызов SELECT выглядит как простой выбор в таблице, а именно:

SELECT * FROM ORG_CHART;

В дополнение к получению значений из процедуры вы можете использовать агрегатные функции. Например, для использования нашей процедуры с целью отображения количества отделов применяйте следующий оператор:

SELECT COUNT (DEPARTMENT) FROM ORG_CHART;

Аналогично, для отображения с помощью ORG_CHART максимального и среднего количества служащих в каждом отделе используйте следующий оператор:

SELECT