Хелен Борри - Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ

Предложение INTO <���переменные> обязательно и должно быть последним [116] ESQL, "супермножество" DSQL, имеет небольшое отличие в синтаксисе предложения INTO. Там INTO помещается сразу после ключевого слова SELECT и квантификатора строки (если присутствует). Водворение INTO В конец оператора в PSQL позволяет использовать наборы UNION В качестве входа для курсоров PSQL. .

На рис. 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

MAX(EMP_CNT),

AVG(EMP_CNT)

FROM ORG CHART;

! ! !

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

. ! .

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

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

Приведенная далее вложенная процедура не выполняет операторов SQL [117] В Firebird 1.5 работа, выполняемая в данной вложенной процедуре, может быть реализована с помощью выражения CASE. Подробности см. в главе 21. . Она просто берет непонятную константу из набора, используемого внутренне в 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';

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 ^;

CREATE PROCEDURE SP_PRIVILEGES

RETURNS (

Q_ROLE_NAME VARCHAR (31),

ROLE_OWNER VARCHAR(31),

USER_NAME VARCHAR(31),

Q_USER_TYPE VARCHAR(7),

W_GRANT_OPTION CHAR(1),

PRIVILEGE CHAR(6),

GRANTOR VARCHAR. (31),

QUALIFIED_OBJECT VARCHAR(63),

Q_OBJECT_TYPE VARCHAR(7) )

AS

DECLARE VARIABLE RELATION_NAME VARCHAR(31);

DECLARE VARIABLE FIELD_NAME VARCHAR(31);

DECLARE VARIABLE OWNER_NAME VARCHAR(31);

DECLARE VARIABLE ROLE_NAME VARCHAR(31);