Извлечение данных с помощью курсоров. Коллекции. Oracle. Перечень основных команд

• индексирования содержимого массива можно использовать VARCHAR2 или PLS_INTEGER. *Associative array (or index-by table)
• Вложенные таблицы – одномерные, несвязанные коллекции, состоящие из однородных элементов. Их можно определять и в PL/SQL и в базе данных, например в качестве столбцов. Из за удаления некоторых элементов могут стать разреженными. *Nested table
• Массив типа VARRAY – Это одномерные массивы переменной длины состоящие из однородных элементов. Могут использоваться и в PL/SQL и в базах данных. Эти массивы (в отличии от предыдущих) не могут быть разреженными. *Variable-size array (varray)

Пример создания коллекции

--Вложенная таблица

DECLARE TYPE list_of_dates_t IS TABLE OF DATE; TYPE list_of_names_t IS TABLE OF VARCHAR2 (100) INDEX BY BINARY_INTEGER; birthdays list_of_dates_t:=list_of_dates_t(); happyfamily list_of_names_t; BEGIN birthdays.EXTEND; birthdays(1):=’23.09.1985’; birthdays.EXTEND; birthdays(2):=’09.04.1978’; happyfamily(-15070):=‘Иван’; happyfamily(88):=‘Ева’; happyfamily(909):=‘Женя’; happyfamily(2020202020):=‘Таня’; DBMS_OUTPUT.put_line (birthdays.COUNT); DBMS_OUTPUT.put_line (happyfamily.FIRST); END; 2 -15070

--Ассоциативный массив

Встроенные методы коллекций

Пример перебора элементов коллекции

DECLARE TYPE population IS TABLE OF NUMBER INDEX BY VARCHAR2(64); city_population population; i VARCHAR2(64); BEGIN city_population(‘Бердск') := 100259; city_population(‘Омск') := 1160670; city_population(‘Новосибирск') := 1473754; city_population(‘Бердск') := 100260; i := city_population.FIRST; WHILE i IS NOT NULL LOOP DBMS_Output.PUT_LINE (‘Население в ' || i || ‘е составляет ' ||TO_CHAR(city_population(i))); i := city_population.NEXT(i); END LOOP; END; Население в Бердске 100260 Население в Новосибирске 1473754 Население в Омске 1473754

Объявление вложенной таблицы или типа VARRAY

Перед объявлением вложенной таблицы или VARRAY, также как и при объявлении ассоциативного массива, нужно объявить соответствующий тип. Его можно задать либо в базе данных, либо в блоке PL/SQL.

Создание типа вложенной таблицы в базе данных:

Create [or replace] TYPE имя_типа AS|IS TABLE OF тип_данных_элемента [NOT NULL];

Создание типа VARRAY в базе данных:

Create [or replace] TYPE имя_типа AS|IS VARRAY(максимальный индекс) OF тип_данных_элемента [NOT NULL];

Для удаления любого типа данных, необходимо выполнить команду:

DROP TYPE имя_типа [FORCE];

Объявление вложенных таблиц или VARRAY в PL/SQL

Создание типа вложенной таблицы в PL/SQL:

TYPE имя_типа AS|IS TABLE OF тип_данных_элемента [NOT NULL];

Создание типа VARRAY в PL/SQL:

TYPE имя_типа AS|IS VARRAY(максимальный индекс) OF тип_данных_элемента [NOT NULL];

Пример использования вложенной таблицы

1. Создаём тип typ_color в базе данных:

CREATE OR REPLACE TYPE typ_color AS TABLE OF VARCHAR2(30);

2. Создаём анонимный блок в котором мы будем использовать тип typ_color:

DECLARE v_font_colors typ_color:=typ_color('Зелёный','Синий','Голубой'); BEGIN for i in 1..v_font_colors.count loop DBMS_Output.PUT_LINE(v_font_colors(i)); end loop; END;

Зелёный Синий Голубой

Мультимножество

MULTISET EXCEPT

MULTISET EXCEPT

MULTISET INTERSECT

MULTISET UNION

MULTISET UNION DISTINCT

Уникальное множество

Оператор MULTISET UNION

CREATE OR REPLACE TYPE typ_color AS TABLE OF VARCHAR2(30); DECLARE v_font_colors typ_color:=typ_color('Зелёный','Синий','Голубой'); v_font_colors2 typ_color:=typ_color('Красный','Синий','Голубой'); v_font_colors3 typ_color:=typ_color(); BEGIN v_font_colors3:=v_font_colors MULTISET UNION v_font_colors2 ; for i in 1..v_font_colors3.count loop DBMS_Output.PUT_LINE(v_font_colors3(i)); end loop; END;

Зелёный Синий Голубой Красный Синий Голубой

Оператор MULTISET UNION DISTINCT

CREATE OR REPLACE TYPE typ_color AS TABLE OF VARCHAR2(30); DECLARE v_font_colors typ_color:=typ_color('Зелёный','Синий','Голубой'); v_font_colors2 typ_color:=typ_color('Красный','Синий','Голубой'); v_font_colors3 typ_color:=typ_color(); BEGIN v_font_colors3:=v_font_colors MULTISET UNION DISTINCT v_font_colors2 ; for i in 1..v_font_colors3.count loop DBMS_Output.PUT_LINE(v_font_colors3(i)); end loop; END;

Зелёный Синий Голубой Красный

Оператор MULTISET INTERSECT

DECLARE v_font_colors typ_color:=typ_color('Зелёный','Синий','Голубой'); v_font_colors2 typ_color:=typ_color('Красный','Синий','Голубой'); v_font_colors3 typ_color:=typ_color(); BEGIN v_font_colors3:=v_font_colors MULTISET INTERSECT v_font_colors2 ; for i in 1..v_font_colors3.count loop DBMS_Output.PUT_LINE(v_font_colors3(i)); end loop; END;

Синий Голубой

Оператор MULTISET EXCEPT

CREATE OR REPLACE TYPE typ_color AS TABLE OF VARCHAR2(30); DECLARE v_font_colors typ_color:=typ_color('Зелёный','Синий','Голубой'); v_font_colors2 typ_color:=typ_color('Красный','Синий','Голубой'); v_font_colors3 typ_color:=typ_color(); BEGIN v_font_colors3:=v_font_colors MULTISET EXCEPT v_font_colors2 ; for i in 1..v_font_colors3.count loop DBMS_Output.PUT_LINE(v_font_colors3(i)); end loop; END;

Зелёный

Использование коллекций

• Коллекции можно использовать как компоненты записи.

CREATE OR REPLACE TYPE typ_color AS TABLE OF VARCHAR2(30);

DECLARE( TYPE toys is RECORD (form NUMBER, weight NUMBER, color typ_color );

• Коллекцию можно передавать в качестве параметров в программу

CREATE OR REPLACE TYPE typ_color AS TABLE OF VARCHAR2(30); CREATE OR REPLACE PROCEDURE make_colors_superset(first_colors IN typ_color, Second_colors IN typ_color, superset OUT typ_color,type_multiset IN NUMBER) as Begin IF type_multiset=1 then superset :=first_colors MULTISET UNION second_colors ; ELSIF type_multiset=2 then superset :=first_colors MULTISET INTERSECT Second_colors ; ELSIF type_multiset=3 then superset :=first_colors MULTISET EXCEPT Second_colors ; ELSE superset :=first_colors MULTISET UNION DISTINCT Second_colors; END IF; End;

DECLARE v_font_colors typ_color:=typ_color('Зелёный','Синий','Голубой'); v_font_colors2 typ_color:=typ_color('Красный','Синий','Голубой'); v_font_colors3 typ_color:=typ_color(); BEGIN make_colors_superset(v_font_colors ,v_font_colors2,v_font_colors3,1) ; for i in 1..v_font_colors3.count loop DBMS_Output.PUT_LINE(v_font_colors3(i)); end loop; END;

Зелёный Синий Голубой Красный Синий Голубой

Выборка данных

• Одной из важнейших характеристик PL/SQL является тесная интеграция с базой данных ORACLE как в отношении изменения данных в таблицах, так и в отношении выборки информации из таблиц.
• Когда SQL инструкция выполняется из PL/SQL, СУБД ORACLE назначает ей приватную рабочую область, а некоторые данные записывает в системную глобальную область.
• PL/SQL предоставляет программистам несколько механизмов доступа к этой рабочей области и содержащейся в ней информации.

Механизмы доступа к данных из PL/SQL

• Неявные курсоры. Инструкция SELECT..INTO считывает одну строку данных и присваивает её в качестве переменной программы.
• Явные курсоры. Запрос можно явно объявить как курсор в разделе объявления локального блока или пакета. После этого такой курсор можно открывать и выбирать из него данные