Привязка массивами: конструкция BULK COLLECT INTO

Заменяет конструкцию INTO, используемую для помещения результата в скалярную переменную программы. Синтаксис BULK COLLECT INTO:

 

BULK COLLECT INTO { список_коллекций_из_скаляров | коллекция_из_записей };

 

список_коллекций_из_скаляров – перечисление через запятую имен коллекций, по каждой на столбец, представленный в конструкции SELECT. 

коллекция_из_записей может использоваться начиная с версии 9.2.

 

Фразу BULK COLLECT INTO можно использовать в предложениях SELECT INTO, FETCH INTO и RETURNING INTO, например:

 

DECLARE

           TYPE vendor_name_tab IS TABLE OF vendor_name%TYPE;

           TYPE vendor_term_tab IS TABLE OF vendor_term%TYPE;

           v_names vendor_name_tab;

           v_terms vendor_term_tab;

BEGIN

           SELECT name, terms

           BULK COLLECT INTO v_names, v_terms

           FROM vendors

           WHERE terms < 30;

           …

END;

 

(Сравните с SELECT … INTO … для обычной скалярной программной переменной).

 

В следующем примере будут удалены сотрудники, перечисленные во входном списке, а выражение RETURNING вернет список удаленных:

 

DECLARE

           TYPE dlist_t IS TABLE OF dept.deptno%TYPE;

           TYPE enolist_t IS TABLE OF emp.empno%TYPE;

           deptlist dlist_t:= dlist_t (10, 30);

 

FUNCTION whack_emps_by_dept (deptlist dlist_t)

RETURN enolist_t

IS

enolist enolist_t;

BEGIN

FORALL dept# IN deptlist.FIRST.. deptlist.LAST

DELETE FROM emp WHERE deptno = deptlist(dept#)

RETURNING empno BULK COLLECT INTO enolist;

 

RETURN enolist;

END;

BEGIN

           SAVEPOINT altogether;

           DBMS_OUTPUT.PUT_LINE(whack_emps_by_dept(deptlist).COUNT || ‘ employees deleted’);

           ROLLBACK TO SAVEPOINT altogether;

END;

/

 

 

Ограничение числа одновременно подчитываемых записей

Следующий пример показывает, как с помощью ключевого слова LIMIT можно ограничить порции подчитываемых в программу данных в случае неизвестного заранее размера результата (и предупредить переполнение памяти):

 

…

OPEN curs;

LOOP

           FETCH curs BULK COLLECT INTO list1, list2 LIMIT 1000;

           EXIT WHEN curs%NOTFOUND;

           FOR i IN 1.. list1.COUNT

           LOOP

                           -- обрабатываем list1(i) и list2(i)

                           END LOOP;

END LOOP;

CLOSE curs;

…

 

 

BULK COLLECT INTO во встроенном динамическим SQL

Начиная с версии 9.2 конструкцию BULK COLLECT INTO можно использовать и во встроенном динамическом SQL:

 

DECLARE
TYPE num_tab IS TABLE OF emp.ename%TYPE;
saltab num_tab;
BEGIN
EXECUTE IMMEDIATE

'UPDATE emp SET sal = sal + 10 WHERE deptno = 10 RETURNING ename INTO:1'
RETURNING BULK COLLECT INTO saltab;

 

FOR i IN saltab.FIRST.. saltab.LAST LOOP
DBMS_OUTPUT.PUT_LINE(saltab(i));

END LOOP;
END;
/

 

ROLLBACK;

 

 

Пример для схемы SCOTT

Пример связывания массивами для демонстрационной схемы БД SCOTT:

 

SAVEPOINT old_salaries;

 

DECLARE

TYPE up_ttype IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;

TYPE newsal_ttype IS TABLE OF emp.sal%TYPE INDEX BY BINARY_INTEGER;

TYPE names_ttype IS TABLE OF emp.ename%TYPE INDEX BY BINARY_INTEGER;

toupgrade up_ttype;

newsal newsal_ttype;

upgraded_name names_ttype;

BEGIN

toupgrade(1):= 10;

toupgrade(2):= 20;

 

FORALL i IN toupgrade.FIRST.. toupgrade.LAST

UPDATE emp SET sal = sal * 10 WHERE deptno = toupgrade(i)

RETURNING ename, sal BULK COLLECT INTO upgraded_name, newsal;

 

FOR i IN upgraded_name.FIRST.. upgraded_name.LAST LOOP

DBMS_OUTPUT.PUT_LINE(RPAD(upgraded_name(i),10)||newsal(i));

END LOOP;

EXCEPTION

WHEN OTHERS THEN DBMS_OUTPUT.PUT_LINE('Can''t upgrade -> ' || SQLERRM);

END;

/

 

/

 

ROLLBACK TO SAVEPOINT old_salaries;

 

Упражнение. Конструкция FORALL не терпит пропусков элементов в коллекции (см. замечание выше). Предложите для примера выше более надежный вариант коллекции для списка отделов. Добавьте в код выше выдачу числа сотрудников с увеличенной зарплатой в каждом отделе и общего числа таких сотрудников.

 

 

Использование коллекций в табличных функциях (потоковой реализации)

Табличными в PL/SQL называются функции, принимающие в качестве аргумента коллекции и/или возвращающие коллекции. Их определение и способ обращения к ним из SQL-предложения имеют свои особенности. Так, для таких функций можно использовать потоковую выдачу результата по мере его выработки телом функции. Это позволяет

 

- сократить требования к памяти, особенно при больших таблицах и

- ускорить получение результата, особенно при наличии параллельной платформы.

 

 

16.11.1. Простой пример

Пример:

 

CREATE TYPE ename_type IS TABLE OF VARCHAR2 (256)

/

 

CREATE OR REPLACE FUNCTION emps_in_dept (deptname IN VARCHAR2)

RETURN ename_type

PIPELINED

IS

BEGIN

FOR employee IN

(SELECT emp.* FROM emp, dept WHERE emp.deptno = dept.deptno AND dept.dname = deptname)

LOOP

       PIPE ROW (employee.ename);

END LOOP;

RETURN;

END;

/

 

Обращение к такой функции из SQL:

 

SELECT emps_in_dept ('SALES') FROM dual;

 

SELECT * FROM TABLE (emps_in_dept ('SALES'));

 

(Второй SELECT более реалистичен).

 

Упражнение. Выдайте SELECT * FROM TABLE (emps_in_dept (' sales ')).

 

Упражнение. Создайте табличную функцию PIVOT, выдающую указанное ее параметром число строк с последовательными целыми значениями («опорная таблица», полезная при составлении отчетов).

 

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

 

 

16.11.2. Использование для преобразования данных

Более сложный пример поточного преобразования данных, характерный, в частности, для приложений типа Data Warehouse. Он потребует создания специального служебного пакета, поскольку для удобства использования мы хотим употребить ссылку на курсор (строгую!), а также воспользоваться вложенной таблицей из записей; в SQL такие типы отсутствуют: