Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ

Простая процедура с вложенными операторами SELECT

Процедура выбора 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.

Вызов процедуры ORG_CHART

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

SELECT * FROM ORG_CHART;

Выбор агрегатных значений из процедур

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

SELECT COUNT (DEPARTMENT) FROM ORG_CHART;

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

SELECT

MAX(EMP_CNT),

AVG(EMP_CNT)

FROM ORG CHART;

! ! !

СОВЕТ. Если процедура получит ошибку или исключение, агрегатные функции не вернут правильных значений, поскольку процедура завершается до обработки всех строк.

. ! .

Вложенные процедуры

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

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

Приведенная далее вложенная процедура не выполняет операторов SQL[117]. Она просто берет непонятную константу из набора, используемого внутренне в Firebird для представления типов объектов, и возвращает строку, более осмысленную для человека:

SET TERM ^;

CREATE PROCEDURE SP_GET_TYPE (

IN_TYPE SMALLINT )

RETURNS (

STRING VARCHAR(7) )

AS

BEGIN

STRING = 'Unknown';

IF (IN_TYPE = 0) THEN STRING = 'Table';

IF (IN_TYPE = 1) THEN STRING = 'View';

IF (IN_TYPE = 2) THEN STRING = 'Trigger';