Эта процедура не имеет входных параметров, следовательно, вызов 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';
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);
DECLARE VARIABLE OBJECT_TYPE SMALLINT;
DECLARE VARIABLE USF,R_TYPE SMALLINT;
DECLARE VARIABLE GRANT_OPTION SMALLINT;
DECLARE VARIABLE IS?ROLE SMALLINT;
DECLARE VARIABLE IS_VIEW SMALLINT;
BEGIN
Вначале мы создадим цикл по таблице RBD$USER_PRIVILEGES, выделяя и направляя некоторые значения прямо в выходные аргументы, а другие в локальные переменные:
FOR SELECT
RTRIM(CAST(RDB$U3ER AS VARCHAR(31))),
RDS$USER_TYPE,
RTRIM (CAST (RDB$GRANTOR AS VARCHAR (31) ) ) ,
RTRIM (CAST (RDB$RELATION_NAME AS VARCHAR (31) )) , RTRIM(CAST(RDB$FIELD_NAME AS VARCHAR (31))) ,
RDB$OBJECT_TYPE,
RTRIM(CAST(RDB$PRIVILEGE AS VARCHAR(31))),
RDB $GRANT OPTION
FROM RDB$USER_PRIVILEGES
INTO : USER_NAME, :USER_TYPE, : GRANTOR, : RELATION_NAME,
FIELD_NAME, : OBJECT_TYPE, : PRIVILEGE, : GRANT_OPTION
Взяв текущее значение выходной переменной USER_NAME, мы обращаемся к RDB$ROLES для получения владельца роли и имени роли в случае, когда "пользователь" текущей строки фактически является ролью. Если же это не роль, то эти поля будут представлены на выходе в виде пунктира:
DO BEGIN
SELECT
RTRIM (CAST (RDB$OWNER_NAME AS VARCHAR ( 31))) , RTRIM(CAST(RDB$ROLE_NAME AS VARCHAR(31)))
FROM RDB$ROLES
WHERE RDB$ROLE_NAME = : USER_NAME
INTO :ROLE_OWNER, : ROLE_NAME;
IF (ROLE_NAME IS NOT NULL) THEN
Q_ROLE_NAME = ROLE_NAME;
ELSE
BEGIN
Q_ROLE_NAME = '-';
ROLE_OWNER = '-';
END
WITH GRANT OPTION является специальной привилегией, о которой мы хотим сообщить в нашем выводе. Следовательно, мы преобразуем этот атрибут в 'Y', если атрибут присутствует (1), или в пробел, если отсутствует:
IF (GRANT_OPTION = 1) THEN
W_GRANT_OPTION = 'Y';
ELSE
W_GRANT_OPTION = '';
Теперь другой запрос к RDB$ROLES, на этот раз для поиска объекта, к которому применяется привилегия роли. Если такой найден, мы добавляем к имени этого объекта полезный префикс. Если это не роль, мы проверяем, является ли наш объект столбцом таблицы, и присваиваем его имени квалификатор.
IS_ROLE = NULL;
SELECT 1 FROM RDB$ROLES
WHERE RDB$ROLE_NAME = :RELATION_NAME
INTO :IS_ROLE;
IF (IS_ROLE = 1) THEN
QUALIFIED_OBJECT = '(Role) ' ||RELATION_NAME;
ELSE
BEGIN
IF (
(FIELD_NAME IS NULL)
OR (RTRIM(FIELD_NAME) = '')) THEN
FIELD_NAME = ' ';
ELSE
FIELD_NAME = '.'|| FIELD_NAME;
QUALIFIED_OBJECT = RELATION_NAME || FIELD_NAME;
END
В RBD$USER_PRIVILEGES и таблицы, и просмотры имеют тип объекта 0. Это не слишком хорошо для нас, значит, следующий запрос проверяет по таблице RDB$RELATIONS, является ли этот конкретный объект просмотром:
IF (OBJECT_TYPE = 0) THEN
BEGIN
IS_VIEW = 0;
SELECT 1 FROM RDB$RELATIONS
WHERE RDB$RELAT | ON_NAME = : RELATION_NAME
AND RDB$VIEW_SOURCE IS NOT NULL
INTO :IS_VIEW;
IF (IS_VIEW = 1) THEN
OBJECT_TYPE = 1;
END
В этой точке нашего цикла мы получили почти все, что хотели. Однако наш объект все еще имеет свой внутренний номер, и мы все еще не знаем тип "пользователя". Пользователями могут быть не только люди. Именно здесь мы выполняем вложенные вызовы для выполнения трансляции внутренних номеров в осмысленные строки. Когда мы это сделаем, наша запись готова к выводу в кэш строк, и мы вызываем SUSPEND для завершения цикла.
Вызовы вложенных процедур из триггеров или хранимых процедур почти идентичны вызовам, которые мы используем в DSQL. Синтаксис отличается только там, где мы обрабатываем возвращаемые значения. В DSQL сервер передает возвращаемые значения клиенту в виде структуры записи. В хранимых процедурах мы используем ключевое слово PSQL RETURNING_VALUES и предоставляем переменные для получения этих значений.
EXECUTE PROCEDURE SP_GET_TYPE(:OBJECT_TYPE)
RETURNING_VALUES (:Q_OBJECT_TYPE);
EXECUTE PROCEDURE SP_GET_TYPE (:USER_TYPE)
RETURNING_VALUES (:Q_USER_TYPE);
SUSPEND;
END
END^
Вот еще один простой вызов:
SELECT * FROM SP PRIVILEGES;
Если нам не нужны все столбцы или хотим получить их в особом порядке, мы можем сделать это. Предположим, мы просто хотим посмотреть привилегии всех пользователей-людей, отличных от SYSDBA:
117
В Firebird 1.5 работа, выполняемая в данной вложенной процедуре, может быть реализована с помощью выражения CASE. Подробности см. в главе 21.