Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ
Шрифт:
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) в локальную
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
MAX(EMP_CNT),
AVG(EMP_CNT)
FROM ORG CHART;
! ! !
СОВЕТ. Если процедура получит ошибку или исключение, агрегатные функции не вернут правильных значений, поскольку процедура завершается до обработки всех строк.
. ! .
Вложенные процедуры
Хранимая процедура сама может вызывать хранимую процедуру. Каждый раз, когда хранимая процедура вызывает другую хранимую процедуру, такой вызов называется вложенным, потому что он появляется в контексте предыдущего и все еще активного вызова первой процедуры. Хранимая процедура, вызываемая другой хранимой процедурой, называется вложенной процедурой.
Следующая процедура возвращает список пользователей, ролей и привилегированных объектов базы данных с их привилегиями SQL. Внутри процедуры два вложенных вызова другой процедуры. Необходимо начать с определения и подтверждения вложенной процедуры - иначе внешняя процедура выдаст ошибку при подтверждении. Вы всегда должны начинать с нижней части "цепочки" при создании процедур, использующих вложенные процедуры.
Приведенная далее вложенная процедура не выполняет операторов SQL [117] . Она просто берет непонятную константу из набора, используемого внутренне в Firebird для представления типов объектов, и возвращает строку, более осмысленную для человека:
117
В Firebird 1.5 работа, выполняемая в данной вложенной процедуре, может быть реализована с помощью выражения CASE. Подробности см. в главе 21.
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';
IF (IN_TYPE = 5) THEN STRING = 'Proc';
IF (IN_TYPE = 8) THEN STRING = 'User';
IF (IN_TYPE = 9) THEN STRING = 'Field';
IF (IN_TYPE = 13) THEN STRING = 'Role';
END^
COMMIT ^
Теперь о внешней процедуре. Запрашиваемая в ней таблица является системной таблицей RBD$USER_PRIVILEGES. Она использует множество техник манипулирования, включая вызовы внутренней SQL-функции CAST и внешней функции RTRIMO из стандартной библиотеки внешних функций ib_udf для преобразования элементов CHAR(31) в VARCHAR(31). Мы это делаем, потому что собираемся выполнять конкатенацию некоторых из этих строк и нам не нужны конечные пробелы.
SET TERM ^;