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 В таком ключе, наша процедура выдаст неверный результат. Позже в этой главе в разд. "Изменение хранимых процедур" мы добавим меры предосторожности при работе, когда могут появиться некоторые проблемы.
Процедуры для совместного использования
Хотя возможно написание процедуры выбора, которая будет выполнять операции по изменению данных в процессе конструирования выходного набора, этого делать не рекомендуется. Хранимая процедура выбора разрабатывается для вывода набора данных клиенту в контексте вызвавшей ее транзакции. Пока клиентское приложение не завершит использование этого выходного набора, транзакция остается неподтвержденной. Если операции DML включены в код, который генерирует выходной набор, эти запросы DML остаются неподтвержденными, пока транзакция не будет завершена клиентом.
В частности, данные могут потенциально сохраняться в несогласованном виде, если значения выхода хранимой процедуры выступают в качестве параметров для операций в других транзакциях.