Книга: Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ

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

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

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

Следующая процедура возвращает список пользователей, ролей и привилегированных объектов базы данных с их привилегиями 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:

SELECT

USER_NAME,

QUALIFIED_OBJECT,

PRIVILEGE

FROM SP_PRIVILEGES

WHERE Q_USER_TYPE = 'User'

AND USER_NAME <> 'SYSDBA'

ORDER BY USER_NAME, QUALIFIED_OBJECT;

Могут быть использованы заменяемые параметры поиска:

SELECT

USER_NAME,

QUALIFIED_OBJECT,

PRIVILEGE

FROM SP_PR1VILEGES

WHERE Q_USER_TYPE = ?

ORDER BY USER_NAME, QUALIFIED_OBJECT;

! ! !

СОВЕТ. Вы можете найти эту процедуру полезной для проверки привилегий SQL в вашей базе данных. Информацию об установке привилегий см. в главе 35.

. ! .

Оглавление книги