Коллекции и записи Oracle PL/SQL | Oracle PL/SQL •MySQL •SQL Server

Автор: Myp3_u_K

Авторский курс. SQL от новичка до профессионала. Бесплатное вводное занятие. Сертификат. Записывайся!
Прокачаю до уровня БОГ!

Использование множеств при работе с динамическим SQL

Работа со сложными типами данных BULK– массивами и коллекциями повышает производительность за счет минимизации количества переключений контекста между PL / SQL и SQL. То есть целые коллекции, а не только отдельные элементы, передаются туда и обратно.

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

Синтаксис
BULKFETCHstatementBULKEXECUTEIMMEDIATEstatementFORALLstatementCOLLECTINTO clause RETURNINGINTO clause %BULK_ROWCOUNT cursorattribute
Синтаксис для динамического связывания с использованием инструкции BULK

Инструкция BULK позволяет Oracle работать переменной в SQL , так , чтобы обрабатывать множества значений. Тип множества может быть любой допустимы в PL / SQL (индекс-таблицы, вложенные таблицы, и массивы переменного размера). Однако элементы должны иметь строго заданный тип данных SQL, CHAR , DATE или NUMBER . Динамические коллекции могут применяться в командах: EXECUTE IMMEDIATE , FETCH и FORALL .

EXECUTE IMMEDIATE и BULK

Эти операторы позволяет вам позволяет связывать заданные переменные или OUT аргументы и передавать их, как параметры, для динамического оператора SQL. Синтаксис имеет следующий вид:
EXECUTEIMMEDIATE dynamic_string [[BULKCOLLECT] INTO define_variable[, define_variable …]] [USING bind_argument[, bind_argument …]] [{RETURNING | RETURN} BULKCOLLECTINTO bind_argument[, bind_argument …]];
Благодаря инструкции BULK можно возвращать данные в коллекции из динамического SQL.

BULK и FETCH
BULK позволяет выбрать данные из динамического курсора так же, как выборку из статического курсора. Синтаксис имеет следующий вид:
FETCH dynamic_cursor BULKCOLLECTINTO define_variable [, define_variable …];

Если число определяемых переменных в BULK COLLECT INTO , превышает количество столбцов в запросе, Oracle выдает ошибку.

Использование FORALL

Этот синтаксис позволяет связать входные переменные в динамическом SQL. Кроме того, вы можете использовать EXECUTE IMMEDIATE совместно с FORALL .Синтаксис имеет следующий вид: FORALLindexINlowerbound..upperboundEXECUTEIMMEDIATE dynamic_string USING bind_argument | bind_argument(index) [, bind_argument | bind_argument(index)] … [{RETURNING | RETURN} BULKCOLLECTINTO bind_argument[, bind_argument … ]];

Динамическая строка может быть или INSERT , UPDATE или DELETE (но не SELECT).
Примеры динамического SQL с использованием BULK

Следующий пример демонстрирует использование BULK для вывода данных в открытые массивы с помощью FETCH
DECLARETYPE EmpCurTyp ISREFCURSOR; TYPE NumList ISTABLEOF NUMBER; TYPE NameList ISTABLEOF VARCHAR2(15); emp_cv EmpCurTyp; empnos NumList; enames NameList; sals NumList; BEGINOPEN emp_cv FOR’SELECT empno, ename FROM emp’; FETCH emp_cv BULKCOLLECTINTO empnos, enames; CLOSE emp_cv; EXECUTEIMMEDIATE’SELECT sal FROM emp’BULKCOLLECTINTO sals; END;

Использование BULK массовом обновлении записей совместно с returning результат помещается в коллекцию enames.

DECLARETYPE NameList ISTABLEOF VARCHAR2(15); enames NameList; bonus_amt NUMBER := 500; sql_stmt VARCHAR(200); BEGIN sql_stmt := ‘UPDATE emp SET bonus = :1 RETURNING ename INTO :2’; EXECUTEIMMEDIATE sql_stmt USING bonus_amt RETURNINGBULKCOLLECTINTO enames; END;

Пример показывает как использовать FORALL и USING ..
DECLARETYPE NumList ISTABLEOF NUMBER; TYPE NameList ISTABLEOF VARCHAR2(15); empnos NumList; enames NameList; BEGIN empnos := NumList(1,2,3,4,5); FORALL i IN1..5EXECUTEIMMEDIATE’UPDATE emp SET sal = sal * 1.1 WHERE empno = :1 RETURNING ename INTO :2’USING empnos(i) RETURNINGBULKCOLLECTINTO enames; … END;

Вы находитесь на страницах старой версии сайта.

«Bulk Collect Into» и «Execute Immediate» в Oracle

Переходите на новую версию Interface.Ru

Моделирование групп объектов в Oracle

© Владимир Пржиялковский,
координатор Евро-Азиатской Группы Пользователей Oracle,
преподаватель УКЦ Interface Ltd.

Объектный подход к моделированию БД, безусловно, имеет свою притягательность, хотя, как замечалось в предыдущей статье ценность его преувеличивать не стоит, так как и он не лишен собственных проблем и ограничений (к слову сказать, известных задолго до середины 90-х годов, когда ведущие поставщики СУБД взяли его на вооружение). Однако подход есть, и как-то решать задачу моделирования он обязан. В предыдущей статье говорилось о том, как в Oracle можно создавать и хранить отдельные объекты. В жизни этого недостаточно, и требуется иметь возможность моделировать группы объектов: наборы адресов, списки сотрудников и т. д.

Такая возможность предусматривалась, например, в сетевой модели данных, исторически предшествовавшей реляционной, и проектировалась в виде расширения реляционной создателем последней Э. Коддом (в силу ряда причин это расширение было проигнорировано разработчиками промышленных “реляционных” СУБД). Здесь будут рассмотрены возможности моделирования групп объектов, реализованные в Oracle последних версий (8, 9).

Таблицы хранимых и синтезированных объектов

Первая возможность моделирования групп из объектов в Oracle известна по предыдущей статье: это таблицы “исконных” объектов (object tables) и таблицы “виртуальных”, или “синтезированных” объектов (object views). Исконные объекты хранятся как самостоятельные сущности в БД, а синтезированные дают только видимость объектов (по потребительским свойствам почти не отличимую от истинных объектов) на основе данных, хранимых в обычных или объектных таблицах.

И те и другие позволяют иметь в БД неупорядоченные списки объектов. Ниже приводится пример создания двух списков сотрудников, проживающих в Москве и Ленинграде. Принадлежность сотрудников отделам задается специальной таблицей:

DROP TYPE employee_typ FORCE;
DROP TABLE e_moscow;
DROP TABLE e_leningrad;
DROP TABLE employment;

CREATE TYPE employee_typ AS OBJECT (
ename VARCHAR2(50),
job VARCHAR2(10))
/

CREATE TABLE e_moscow OF employee_typ;

CREATE TABLE e_leningrad OF employee_typ;

INSERT INTO e_moscow VALUES (
‘Scott’,
‘Manager’);

INSERT INTO e_leningrad VALUES (
‘Smith’,
‘Salesman’);

CREATE TABLE employment (
dname VARCHAR2(50),
employee REF employee_typ);

INSERT INTO employment VALUES (
‘Operations’,
(SELECT REF(m) FROM e_moscow m WHERE m.ename = ‘Scott’));

Этот способ, однако, не лишен своих ограничений. Например, по данным таблицы EMPLOYMENT нельзя понять, проживает ли сотрудник в Москве или Ленинграде. Нельзя переселить сотрудника из Москвы в Ленинград (можно только удалить его из одной таблицы и создать в другой объект с теми же атрибутами) и так далее.

Коллекции

Другую возможность по группировке объектов дают “коллекции”. Они позволяют хранить в поле “обычной” таблицы список. Элементами списка могут быть скаляры (числа, строки), объекты или ссылки на самостоятельно существующие объекты.

В Oracle они могут быть двух видов: вложенные таблицы и массивы типа VARRAY.

Вложенные таблицы

Термин, выбранный для этого вида коллекций кажется не совсем удачным. Речь на самом деле идет о моделировании не таблиц, а списков. В отличие от предыдущего примера построим таблицу DEPARTMENTS с перечнем отделов, причем в строке о каждом отделе будем хранить список сотрудников. Предварительно, однако, нужно будет создать тип для такого поля-списка:

CREATE TYPE employee_nlist_typ AS TABLE OF employee_typ
/

CREATE TABLE department (
dname VARCHAR2(20),
emps employee_nlist_typ)
NESTED TABLE emps STORE AS emps_nt_tab;

Фраза NESTED TABLE – чисто техническая. Она обязана тому, что физически Oracle будет хранить в поле EMPS не список объектов-сотрудников, а список их системных идентификаторов, присвоенных при помещении самих сотрудников в специально создаваемую для таблицы DEPARTMENT служебную таблицу.

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

Вот как можно заполнить таблицу отделов:

INSERT INTO department VALUES (
‘Operations’,
employee_nlist_typ (
employee_typ (‘Scott’, ‘Manager’),
employee_typ (‘Smith’, ‘Salesman’)
)
);

В отделе Operations теперь два сотрудника:

COLUMN emps FORMAT A60 WORD

SELECT * FROM department;

По терминологии предыдущей статьи сотрудники в таблице DEPARTMENT – “объектные атрибуты”. Другой способ смоделировать ситуацию “сотрудники-отделы” с помощью коллекции – воспользоваться списком ссылок на сотрудников, реально существующих в собственных таблицах.

Работа в PL/SQL

Вот как можно работать со вложенными таблицами в PL/SQL:

DECLARE
ee employee_nlist_typ;
BEGIN
SELECT emps INTO ee FROM department WHERE dname = ‘Operations’;
DBMS_OUTPUT.PUT_LINE(ee(1).ename);
DBMS_OUTPUT.PUT_LINE(ee(2).ename);
END;
/

В этом примере для упрощения использованы предпосылки о том, что (а) отдел с названием ‘Operations’ всего один и (в) сотрудников в нем – [по крайней мере] двое.

Массивы типа VARRAY

Массивы типа VARRAY потребительски во многом похожи на вложенные таблицы, но имеют и ряд существенных технических и внешних отличий. Например, они обязаны иметь ограничение на максимальное число элементов в конкретных массивах, наподобие типу VARCHAR2. Еще они не требуют для хранения данных служебной таблицы, наподобие вложенной таблицы. Есть и другие отличия. Фирма Oracle советует использовать вложенные таблицы, если нужно хранить неупорядоченные списки и VARRAY, если нужно хранить упорядоченные.

Пример использования для группировки сотрудников коллекции VARRAY может выглядеть так:

CREATE TYPE employee_vlist_typ AS VARRAY(20) OF employee_typ
/

CREATE TABLE department1 (
dname VARCHAR2(15),
emps employee_vlist_typ
);

Этим типом мы запретили отделам иметь более 20-и сотрудников.

Добавление нового отдела делается как и для вложенных таблиц:

INSERT INTO department1 VALUES (
‘Operations’,
employee_vlist_typ (
employee_typ (‘Scott’, ‘Manager’),
employee_typ (‘Smith’, ‘Salesman’)
)
);

Приведенный выше код на PL/SQL для массива сотрудников VARRAY проработает так же.

Преобразования коллекций

Как и следовало бы ожидать от СУБД Oracle, плотная, а не поверхностная работа с коллекциями в качестве средства моделирования групп объектов требует знания большого числа “деталей”. Здесь не место разъяснять их все, но одну важную для коллекций возможность стоит привести. Имеется в виду разворачивание коллекции в список строк, столь привычный для традиционной работы.

Для того, чтобы посмотреть список сотрудников отдела ‘Operations’ в более привычном виде, следует воспользоваться специальной функцией TABLE:

SELECT * FROM TABLE(SELECT emps FROM department);

К аргументу функции TABLE (это вложенный SELECT) есть одна настоятельная просьба: возвращать одну и только коллекцию. Наши данные это обеспечивают, а иначе вложенный SELECT нужно было бы подправить.

Аналогичный пример для массива VARRAY:

SELECT ename FROM TABLE(SELECT emps FROM department1);

Замечательно, что это преобразование решает задачу и изменения списка средствами SQL:

INSERT INTO TABLE(SELECT emps FROM department)
VALUES (‘Allen’, ‘Salesman’);

SELECT * FROM TABLE(SELECT emps FROM department);

(Эта возможность не сработает, однако, для массива VARRAY, который в БД ведет себя, по сути, как скаляр, допуская изменение поля-списка как единого, уже сформированного целого). Если бы возможность такого преобразования отсутствовала, добавить сотрудника в отдел или удалить можно было бы только программным способом, проще всего в PL/SQL.

Естественно, никто не мешает осуществить и массовую вставку:

INSERT INTO TABLE(SELECT emps FROM department)
SELECT ename, job FROM emp;

Для обратного преобразования, из таблицы в коллекцию, потребуется более сложная конструкция:

SELECT
CAST (MULTISET(SELECT ename, job FROM emp) AS employee_nlist_typ)
FROM DUAL;

Однако такое преобразование на практике менее востребовано.

Дополнительная информация

За дополнительной информацией обращайтесь в компанию Interface Ltd.

Обсудить на форуме

Рекомендовать страницу


Ассоциативный массив в PL/SQL это коллекция элементов, которые используют произвольные числа и строки в качестве индекса значения.
Объявление ассоциативного массива в декларативной части PL/SQL-элемента (пакета, процедуры и т.п.):

Ассоциативный массив можно представить как таблицу в памяти имеющую свой первичный ключ.

Как работать с BULK COLLECT INTO

Первичный ключ может быть как Integer (BINARY_INTEGER и PLS_INTEGER) так и String (VARCHAR2 или любой из его подтипов).

Пример использования ассоциативных массивов:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 DECLARETYPE dept_rty ISRECORD(deptNo NUMBER, extra_tx VARCHAR2(2000));TYPE dept_aa ISTABLEOF dept_rty INDEXBYBINARY_INTEGER; v_dept_aa dept_aa;CURSOR c_emp ISSELECT eName, deptNo FROM emp;BEGIN v_dept_aa(10).deptNo:=10; v_dept_aa(20).deptNo:=20; v_dept_aa(30).deptNo:=30;FOR r_emp IN c_emp LOOP v_dept_aa(r_emp.deptNo).extra_tx:= v_dept_aa(r_emp.deptNo).extra_tx||’ ‘||r_emp.eName;ENDLOOP;END;

Строка 6: Ассоциативные массивы начинают существовать сразу после объявления переменной.

Строки 11–13: Объявление элементов коллекции. Нет необходимости расширять коллекцию для добавления новых элементов, все что нужно для этого — присвоить любое значние любому атрибуту элемента с нужным индексом. Оракл создаст элементы коллекции таким образом что индекс 20 будет располагаться сразу после 10-го.

Строки 15–16: Так как DEPTNO используется в качестве индекса коллекции, довольно легко обновить правильную строку массива.

Формирование листа сотрудников сгруппированного по двум параметрам: департаменту и кварталу принятия на работу

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 DECLARETYPE list_aa ISTABLEOFVARCHAR2(2000)INDEXBYVARCHAR2(256); v_list_aa list_aa;   CURSOR c_dept ISSELECT deptNo FROM dept ORDERBY deptNo;CURSOR c_emp ISSELECT eName, deptNo,TO_CHAR(hireDate,’q’) q_nr FROM emp; v_subscript_tx VARCHAR2(256);BEGINFOR r_dept IN c_dept LOOP v_list_aa(r_dept.deptNo||’|1’):=’Q1 Dept#’||r_dept.deptno||’:’; v_list_aa(r_dept.deptNo||’|2’):=’Q2 Dept#’||r_dept.deptno||’:’; v_list_aa(r_dept.deptNo||’|3’):=’Q3 Dept#’||r_dept.deptno||’:’; v_list_aa(r_dept.deptNo||’|4’):=’Q4 Dept#’||r_dept.deptno||’:’;ENDLOOP;   FOR r_emp IN c_emp LOOP v_list_aa(r_emp.deptNo||’|’||r_emp.q_nr):= v_list_aa(r_emp.deptNo||’|’||r_emp.q_nr)||’ ‘||r_emp.eName;ENDLOOP;   v_subscript_tx:=v_list_aa.FIRST;   LOOPDBMS_OUTPUT.put_line(v_list_aa(v_subscript_tx)); v_subscript_tx:=v_list_aa.next(v_subscript_tx);EXITWHEN v_subscript_tx ISNULL;ENDLOOP;END;

Запись опубликована 01.12.2010 в 2:40 дп и размещена в рубрике Advanced Datatypes. Вы можете следить за обсуждением этой записи с помощью ленты RSS 2.0. Можно оставить комментарий или сделать обратную ссылку с вашего сайта.

DECLARETYPE AssocArray ISTABLEOF ElementType INDEXBYBINARY_INTEGER|pls_integer|VARCHAR2(size);

В PL/SQL существует два вида цикла FOR: с числовым счетчиком и с курсором. Цикл с числовым счетчиком — это традиционный и хорошо знакомый всем про­граммистам цикл FOR, имеющийся в большинстве языков программирования. Ко­личество итераций данного цикла известно еще до его начала — это диапазон, за­даваемый между ключевыми словами FOR и LOOP. При установке диапазона значе­ний счетчика цикла неявно объявляется сам счетчик (если он не был объявлен ранее), определяются начальное и конечное значения диапазона, а также задается направление изменена значений счетчика (от наименьшего к наибольшему или наоборот).

Приведем общий синтаксис цикла FOR:

FOR индекс_циклаIN [REVERSE]начальное_значение .. конечное_значение LOOPисполняемые_операторы END LOOP;

Между ключевыми словами LOOP и END LOOP должен стоять хотя бы один ис­полняемый оператор. Свойства цикла FOR с числовым счетчиком приведены в следующей таблице.

Условие завершения:

Если выполнено то количество итераций, которое определено диапазоном значений счетчика. (Можно завершить цикл и оператором EXIT, но это делать не рекомендуется)

Когда проверяется условие завершения цикла:

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

В каких случаях используется данный цикл:

Если тело цикла должно быть выполнено определенное количество раз

Правила для циклов FOR с числовым счетчиком

При использовании цикла FOR с числовым счетчиком необходимо следовать та­ким правилам.

  • Не объявляйте счетчик цикла.

    Oracle PL/SQL BULK COLLECT: FORALL Example

    По умолчанию он будет неявно объявлен как локальная переменная, имеющая тип данных INTEGER, Областью действия ука­занной переменной будет весь цикл; на счетчик цикла нельзя ссылаться извне

  • Выражения, используемые при определении диапазона счетчика цикла (на­чального и конечного значений) вычисляются один раз. В ходе выполнения цикла они не пересчитываются. Поэтому если внутри цикла вы измените зна­чения переменных, которые используются при определении диапазона счет­чика, его граничные значения останутся прежними.
  • Никогда ие меняйте значения счетчика цикла и границ диапазона внутри цикла. Это порочная практика. Компилятор PL/SQLлибо выдаст сообщение об ошибке, либо проигнорирует ваши действия — в любом случае у вас воз­никнут проблемы.
  • Если вы хотите, чтобы зиачения счетчика уменьшались в направлении от конечного к начальному, используйте ключевое слово REVERSE. При этом первое значение в определении диапазона (ндчальное_значение) должно быть меньше второго {конечное_значение). Но не меняйте порядок следования этих значений — ключевое слово REVERSE само сделает все, что нужно.

Примеры циклов FOR с числовым счетчиком

Далее будут рассмотрены примеры, демонстрирующие некоторые варианты син­таксиса цикла FOR с числовым счетчиком.

  • Цикл выполняется 10 раз; значение счетчика увеличивается от 1 до 10:

FOR loop_counter IN 1 .. 10 LOOP… исполняемые операторы …END LOOP;

  • Цикл выполняется 10 раз; значение счетчика уменьшается от 10 до 1:

FOR loop_comter IN REVERSE 1 .. 10 LOOP… исполняемые операторы …END LOOP:

  • Цикл не выполняется ни разу. Используется ключевое слово REVERSE, так что значение счетчика цикла, loop_counter, изменяется от наибольшего до наи­меньшего. Однако начальное и конечное значения счетчика заданы неверно:

FOR loop_counter IN REVERSE 10 … 1 LOOP/* Это тело цикла не выполнится ни разу! */END LOOP:

Даже если вы зададите направление с помощью ключевого слова REVERSE, на­чальное значение диапазона счетчика все равно должно быть указано первым. Если начальное значение больше конечного, тело цикла ни разу не будет вы­полнено. Если же граничные значения будут одинаковыми, тело цикла будет выполнено один раз.

  • Цикл выполняется для диапазона, определяемого значениями переменной и выражения:

FOR calc_index IN start_period_numberLEAST (end_period_number, current period)  LOOP… исполняемые операторы . . .   END LOOP;

В этом примере количество итераций цикла определяется во время выполнения программы. Начальное и конечное значения вычисляются один раз, перед на­чалом цикла, и затем используются в течение всего времени его выполнения.

Обработка нетривиальных приращений

PL/SQL не предоставляет специальных синтаксических способов задания шага приращения счетчика. Во всех разновидностях цикла FOR с числовым счетчиком значение счетчика на каждой итерации увеличивается на единицу.

В том случае, когда приращение должно быть нестандартным (не равным еди­нице), придется писать специальный код. Например, что нужно сделать для того, чтобы тело цикла выполнялось только для четных чисел из диапазона от 1 до 100? Во-первых, можно использовать числовую функцию MOD, как в следующем примере:

FOR loop index IN 1 .. 100 LOOP  IF MOD (loop_ndex,  Z) = 0 THEN  /* Число четное, поэтому выполним вычисления */  cal c_values( loop_index);   END IF;END LOOP;

Во-вторых, в теле цикла значение счетчика можно умножить на два и исполь­зовать вдвое меньший диапазон:

FOR even_number IN 1 .. 50 LOOP  calc_values(even_number*2);END LOOP:

В обоих случаях процедура calc_values выпотлняется только для четных чисел. В первом примере цикл FOR повторяется 100 раз, во втором — только 50. Но какой бы подход вы ни избрали, обязательно подробно его закоментируйте. Комен­тарии помогут другим программистам при сопровождении вашей программы.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *