Firebird РУКОВОДСТВО РАЗРАБОТЧИКА БАЗ ДАННЫХ
Шрифт:
СОВЕТ. Вы можете найти эту процедуру полезной для проверки привилегий 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
. . .
Процедура может быть модифицирована таким образом, что будет получать входные аргументы и возвращать другую комбинацию данных в качестве выхода.