Читать интересную книгу Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ - Хелен Борри

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, потому что в таблице хранится только идентификатор массива. Хранимая процедура может быть использована для отображения значений массива, если размерность и тип данных столбца массива заранее известны.

Таблица JOB В примере базы данных имеет столбец LANGUAGE REQ, содержащий требуемые языки. Столбец определен как массив из пяти элементов VARCHAR(15).

Следующий пример использует хранимую процедуру для просмотра содержимого этого столбца. Процедура использует цикл FOR ... SELECT для поиска каждой строки из таблицы JOB, где столбец LANGUAGE_REQ не является пустым. Затем цикл WHILE отыскивает каждый элемент массива и возвращает значение вызвавшему приложению.

SET TERM ^;

CREATE PROCEDURE VIEW_LANGS

RETURNS (

code VARCHAR(5) ,

grade SMALLINT,

cty VARCHAR(15),

lang VARCHAR(15))

AS

DECLARE VARIABLE i INTEGER;

BEGIN

FOR SELECT

JOB_CODE,

JOB_GRADE,

JOB_COUNTRY

FROM JOB

WHERE LANGUAGE_REQ IS NOT NULL

DO

BEGIN

i = 1;

WHILE (i <= 5) DO

BEGIN

SELECT LANGUAGE_REQ[:i] FROM JOB

WHERE ((JOB_CODE = :code)

AND (JOB_GRADE = :grade)

AND (JOB_COUNTRY = :cty))

INTO :lang;

i =i + 1;

SUSPEND;

END

END

END ^

SET TERM ; ^

Ее вызов:

SELECT * FROM VIEW_LANGS;

CODE GRADE CTY LANG

Eng 3 Japan Japanese

Eng 3 Japan Mandarin

Eng 3 Japan English

Eng 3 Japan

Eng 3 Japan

Eng 4 England English

Eng 4 England German

Eng 4 England French

. . .

Процедура может быть модифицирована таким образом, что будет получать входные аргументы и возвращать другую комбинацию данных в качестве выхода.

Тестирование процедур

Разработчикам не нужно напоминать о необходимости строгого тестирования модулей PSQL, прежде чем передавать их в работу, где они в один скверный день могут принести большой вред. Синтаксический анализатор предупредит вас об ошибках кодирования PSQL, однако как программы ваши модули не защищены от логических ошибок, как и любой код приложения, который вы пишете.

Например, наша процедура LOG SALES прекрасно работает, пока каждая запись продаж имеет непустое значение SALES REP. При этом данный столбец допускает пустое значение. Процедура генерирует результирующий набор, в котором каждая выходная строка зависит от значений в предшествующих строках. Если мы не учтем возможные эффекты появления значения NULL В таком ключе, наша процедура выдаст неверный результат. Позже в этой главе в разд. "Изменение хранимых процедур" мы добавим меры предосторожности при работе, когда могут появиться некоторые проблемы.