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

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.

. ! .

В этой процедуре мы обрабатываем записи таблицы SALES базы данных EMPLOYEE. Мы получим два итога: один для каждого вида продаж, а другой - для всех продаж. В качестве входа мы просто используем начальную и конечную даты для группы интересующих нас продаж.

SET TERM ^;

CREATE PROCEDURE LOG_SALES (

.START_DATE DATE,

END_DATE DATE)

RETURNS (REP_NAME VARCHAR (37) ,

CUST VARCHAR(25),

ORDDATE TIMESTAMP,

ITEMTYP VARCHAR(12),

ORDTOTAL NUMERIC(9,2),

REPTOTAL NUMERIC(9,2),

RUNNINGTOTAL NUMERIC(9, 2))

AS

DECLARE VARIABLE CUSTNO INTEGER;

DECLARE VARIABLE REP SMALLINT;

DECLARE VARIABLE LASTREP SMALLINT DEFAULT -99;

DECLARE VARIABLE LASTCUSTNO INTEGER DEFAULT -99;

BEGIN

RUNNINGTOTAL = 0.00;

FOR SELECT

CUST_NO,

SALES_REP,

ORDER_DATE,

TOTAL_VALUE,

ITEM_TYPE

FROM SALES

WHERE ORDER_DATE BETWEEN : START_DATE AND :END_DATE + 1

ORDER BY 2, 3

INTO :CUSTNO, : REP, : ORDDATE, :ORDTOTAL, : ITEMTYP

Заметьте, что мы используем упорядоченный набор. Если вы получаете виртуальную таблицу из хранимой процедуры выбора и вам нужен упорядоченный набор, полезно сделать набор упорядоченным внутри кода процедуры. Оптимизатор может улучшить здесь производительность, если существуют полезные индексы, в то время как упорядочение, применяемое к выходному набору, не может использовать индексов по своей природе.

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

DO

BEGIN

IF(REP = LASTREP) THEN

BEGIN

REPTOTAL = REPTOTAL + ORDTOTAL;

REP_NAME = "" ;

END

ELSE

BEGIN

REPTOTAL = ORDTOTAL;

LASTREP = REP;

SELECT FULL_NAME FROM EMPLOYEE

WHERE EMP_NO = :REP

INTO :REP_NAME;

END

IF (CUSTNO <> LASTCUSTNO) THEN

BEGIN

SELECT CUSTOMER FROM CUSTOMER

WHERE CUST_NO = :CUSTNO

INTO :COST;

LASTCUSTNO = CUSTNO;

END

RUNNINGTOTAL = RUNNINGTOTAL + ORDTOTAL;

SUSPEND;

Теперь наша строка готова и отправляется в кэш строк вместе с двумя обновленными итогами.

END

END^

SET TERM ;^

Наши входные аргументы имеют тип данных DATE- начальная дата и конечная дата. Процедура отыскивает тип данных TIMESTAMP для выборки строк для курсора. Она добавляет один день к конечной дате, чтобы гарантировать получение каждой строки вплоть до конца этого дня. Это упрощает дела: когда мы вызываем процедуру, нам нужно только предоставить первую и последнюю даты при отсутствии беспокойства по поводу записей с датой более поздней, чем полночь последнего дня.

Следующий вызов возвращает целую таблицу:

SELECT * FROM LOG_SALES ('16.05.1970', CURRENT_3ATE);

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

SELECT * FROM LOG_SALES (?, ?);

Если в таблице есть столбцы, определенные как массивы, вы не сможете просмотреть данные в таком столбце в простом операторе SELECT, потому что в таблице хранится только идентификатор массива. Хранимая процедура может быть использована для отображения значений массива, если размерность и тип данных столбца массива заранее известны.