Процедуры выбора могут легко генерировать наборы из данных, которые вообще не хранятся в базе данных. Мы все иногда находим применение этой технике. В следующем тривиальном примере список строк, разделенных запятыми, каждая из которых содержит 20 или менее символов, поступает в качестве входа. Процедура возвращает приложению каждую строку в нумерованном виде:
CREATE PROCEDURE BREAKAPART(
INPUTLIST VARCHAR(1024))
RETURNS (
NUMERO SMALLINT, ITEM VARCHAR (20)
)
AS
DECLARE CHARAC CHAR;
DECLARE ISDONE SMALLINT = 0;
BEGIN
NUMERO = 0;
ITEM = ' ' ;
WHILE (ISDONE = 0) DO
BEGIN
CHARAC = SUBSTRING (INPUTLI ST FROM 1 FOR 1);
IF (CHARAC = '') THEN
ISDONE = 1;
IF (CHARAC = OR CHARAC = '') THEN
BEGIN
NUMERO = NUMERO + 1;
SUSPEND;
/* Отправляет строку в буфер строк */
ITEM = '';
END
ELSE
ITEM = ITEM || CHARAC;
INPUTLIST = SUBSTRING(INPUTLIST FROM 2);
END
END ^
COMMIT;
/* */
SELECT * FROM BREAKAPART (' ALPHA, BETA, GAMMA, DELTA ') ;
NUMERO ITEM
1 ALPHA
2 BETA
3 GAMMA
4 DELTA
Часто сложные запросы, включающие множество соединений или подзапросов, бывают слишком медленными, чтобы удовлетворить интерактивные приложения. Некоторые запросы могут быть медленными по причине непропорциональности индексов внешних ключей. Поскольку есть возможность оперировать с наборами во внутренних циклах, хранимые процедуры способны создавать требуемые наборы гораздо быстрее и к тому же начинаются возвращать строки раньше, чем позволяют обычные последовательности SQL.
Техника
Техника извлечения и манипулирования данными выходного набора использует курсор для чтения по порядку каждой строки из оператора SELECT В предварительно объявленный набор переменных. Часто это могут быть выходные аргументы, куда помещаются значения столбцов, однако это могут быть и локальные переменные. Внутри цикла с переменными выполняются действия соответствующим образом: преобразовываются для вычислений, если необходимо, или используются как аргументы поиска для вложенных циклов, чтобы получать данные из других запросов. В конце цикла, когда все выходные аргументы получают конечные значения, оператор SUSPEND приводит к паузе в выполнении, пока этот набор передается в кэш строк. Выполнение возобновляется, когда вызывается следующая пересылка.
Как мы видели в предыдущем примере BREAKAPART, оператор SUSPEND является тем элементом, который заставляет процедуру передавать строку.
Конструкция FOR SELECT ... DO
Для поиска множества строк в процедуре мы используем конструкцию FOR SELECT ... DO. Ее синтаксис:
FOR
<выражение-выбора>
INTO <:переменная [, :переменная [, ...]] DO
<составной-оператор>;
<выражение-выбора> может быть любым запросом выбора, использующим соединения, объединения, просмотры, другие процедуры выбора, вызовы функций и т.д. в любой допустимой комбинации.
Оператор FOR SELECT отличается от стандартного оператора SELECT тем, что требует наличия переменных, в которые помещаются значения столбцов, и спецификации полей.
<составной-оператор> может быть одним оператором SUSPEND или блоком из двух или более операторов. <составной-оператор> может иметь вложенные составные операторы.
FOR SELECT ... DO является конструкцией цикла, которая отыскивает строку, заданную в <выражении-выбора>, и выполняет для каждой строки оператор или блок операторов, следующих после DO.
Предложение INTO <переменные> обязательно и должно быть последним[116].
На рис. 30.2 проиллюстрированы типичные виды деятельности, которые могут выполняться внутри циклов для генерации выхода в хранимой процедуре выбора.
Рис. 30.2. Операции в процедуре выбора
В следующих примерах мы посмотрим на то, как комбинации операций в PSQL могут представить более интересную область SQL.
Простая процедура с вложенными операторами SELECT
Процедура выбора ORG_CHART, которая присутствует в примере базы данных employee, не получает входных аргументов. Она использует цикл FOR ... SELECT, чтобы строить набор из ссылающегося на себя соединения таблицы DEPARTMENT и передавать значения столбцов по одной строке за раз набору переменных - некоторые из них локальные, некоторые объявлены как выходные аргументы.
CREATE PROCEDURE ORG_CHART
RETURNS (
HEAD_DEPT CHAR(25),
DEPARTMENT CHAR(25),
MNGR_NAME CHAR (2 0),
TITLE CHAR(5),
EMP_CNT INTEGER )
AS
DECLARE VARIABLE mngr_no INTEGER;
DECLARE VARIABLE dno CHAR(3);
BEGIN
FOR SELECT h.department, d.department, d.mngr_no, d.dept_no
FROM department d
LEFT OUTER JOIN department h ON d.head_dept = h.dept_no
ORDER BY d.dept_no
INTO :head_dept, :department, :mngr_no, :dno
DO
Каждый раз, когда цикл обрабатывает строку, он помещает значение ключа (MNGR_NO) в локальную переменную MNGR_NO. ЕСЛИ эта переменная имеет пустое значение, процедура создает значения для выходных аргументов MNGR_NAME и TITLE. ЕСЛИ же эта переменная имеет значение, она передается как аргумент поиска вложенному запросу к таблице EMPLOYEE, уникально идентифицирующему строку и выделяющему имя и код работы менеджера отдела. Эти значения передаются остальным выходным аргументам.
BEGIN
IF (:mngr_no IS NULL) THEN
BEGIN
mngr_name = '--TBH--';
title = '' ;
END
ELSE
SELECT full_name, job_code
FROM employee
WHERE emp_no = :mngr_no
INTO :mngr_name, :title;
SELECT COUNT (emp_no)
FROM employee
WHERE dept_no = :dno
INTO :emp_cnt;
Когда присвоены все выходные значения для одной строки, оператор SUSPEND передает строку в кэш. Управление передается опять на начало цикла, когда выполнен следующий запрос на пересылку.
SUSPEND;
END
END^
COMMIT^
Обратите внимание, как аккуратно вложенный запрос обходит ту проблему, которую мы имели с подзапросами в DSQL- мы могли в подзапросе вернуть одно и только одно значение. Если нам нужно много значений, а логика левого соединения не работает, то нам пришлось бы использовать множество подзапросов с множеством наборов алиасов для выделения каждого значения из его курсора.
Вызов процедуры выбора
Синтаксис вызова процедуры выбора очень похож на синтаксис обращения к таблице или к просмотру. Единственным отличием является то, что процедура может иметь входные аргументы:
SELECT <список-столбцов> FROM имя ([аргумент [, аргумент ...]])
WHERE <условия-поиска>
ORDER BY <список-упорядочения>;
Имя процедуры должно быть задано.
Правила входных аргументов идентичны правилам для выполняемых процедур - см. ранее разд. "Значения входных аргументов".
<список-столбцов>- разделенный запятыми список из одного или более выходных параметров, возвращаемых процедурой, или * для выбора всех столбцов.
Выходной набор может быть ограничен условиями поиска в предложении WHERE и упорядочен с помощью предложения ORDER BY.
Эта процедура не имеет входных параметров, следовательно, вызов SELECT выглядит как простой выбор в таблице, а именно:
SELECT * FROM ORG_CHART;
116
ESQL, "супермножество" DSQL, имеет небольшое отличие в синтаксисе предложения INTO. Там INTO помещается сразу после ключевого слова SELECT и квантификатора строки (если присутствует). Водворение INTO В конец оператора в PSQL позволяет использовать наборы UNION В качестве входа для курсоров PSQL.