🗊Презентация Работа с составными типами данных в PL/SQL

Работа с составными типами данных

В PL/SQL существует 2 составных типа данных: В PL/SQL существует 2 составных типа данных: Record (запись) - Запись как целое не имеет собственного значения; однако значение имеет каждый ее компонент или поле, а объединение их в единую запись позволяет хранить и обрабатывать все значения как одно целое. Записи сильно упрощают работу программиста, а переход от объявлений уровня полей к уровню записей повышает эффективность написания кода. Collection (коллекция) 3 типа Ассоциативный массив Вложенная таблица VARRAY

где field_declaration описывается как: где field_declaration описывается как: field_name {field_type | variable%TYPE | table.column%TYPE | table%ROWTYPE} [[NOT NULL] {:= | DEFAULT} expr]

Объявление записей: Объявление записей: Запись, определяемая программистом DECLARE TYPE book_info IS RECORD ( author books.author%TYPE, category VARCHAR2(100), total_page_count POSITIVE); v_book book_info; Запись на основе таблицы DECLARE V_CNTY COUNTRIES%ROWTYPE; Record это НЕ запись таблицы БД

... ... TYPE emp_record_type IS RECORD (ename VARCHAR2(25), job VARCHAR2(10), sal NUMBER(8,2)); emp_record emp_record_type; ...

... ... TYPE emp_record_type IS RECORD (empno NUMBER(4) NOT NULL:=100, ename emp.ename%TYPE, job emp.job%TYPE ); emp_record emp_record_type; ...

Переменная составного типа коллекции столбцов в таблице или представлении. Переменная составного типа коллекции столбцов в таблице или представлении. Префикс %ROWTYPE используется для обращения к коллекции базирующейся на столбцах таблицы Поля в Record будут именованы также и того же типа что и поля таблицы БД

Тип данных и количество полей таблицы базы данных может быть неизвестно. Тип данных и количество полей таблицы базы данных может быть неизвестно. Тип данных и количество полей таблицы базы данных может быть в любое время изменено. Атрибут может быть использован после того как данные выбраны Select *

Пример объявления переменной, департамент: Пример объявления переменной, департамент: dept_record departments%ROWTYPE; Пример объявления переменной, сотрудники: emp_record employee%ROWTYPE;

DECLARE DECLARE e_rec emp%ROWTYPE; BEGIN SELECT * INTO e_rec FROM employees WHERE empno = &employee_number; INSERT INTO retired_emps (empno, ename, job, mgr, hiredate, sal, comm,deptno) VALUES (e_rec.empno, e_rec.ename,e_rec.job, e_rec.mgr, e_rec.hiredate, e_rec.sal, e_rec.comm, e_rec.deptno); COMMIT; END;

DECLARE DECLARE my_book books%ROWTYPE; BEGIN SELECT * INTO my_book FROM books WHERE title = 'Oracle PL/SQL'; DBMS_OUTPUT.put_line ('Код ISBN: ' || my_book.isbn); END;

PL/SQL поддерживает следующие операции над записями: PL/SQL поддерживает следующие операции над записями: копирование содержимого одной записи в другую (если они имеют совместимую структуру, то есть одинаковое количество полей одного или взаимопреобразуемых типов); присваивание записи значения NULL (простым оператором присваивания); передача записи в качестве аргумента и возврат записи функцией (RETURN) Операции на уровне записей не поддерживаются: проверить, содержат ли все поля записи значение NULL, использовать синтаксис IS NULL нельзя. Осуществить такую проверку можно лишь путем приме нения оператора IS NULL по отношению к каждому полю Невозможно сравнить две записи в одной операции. Нельзя узнать, равны или нет две записи (то есть значения всех их полей), или же узнать, какая из записей больше. чтобы ответить на эти вопросы, нужно сравнить каждую пару полей. * Записи в PL/SQL. Глава 11. Oracle_PL-SQL_dlya_professionalov_6-e_izdanie.pdf

В PL/SQL существует 2 составных типа данных: В PL/SQL существует 2 составных типа данных: Record (запись) Collection (коллекция) – аналог традиционных массивов. 3 типа коллекций : Ассоциативный массив, Вложенная таблица, VARRAY. Ситуации для применения : Ведение списков данных. Ускорение многострочных операций SQL. Использование коллекций в сочетании с конструкциями FORALL и BULK COLLECT повышает производительность многострочных операций SQL. Кэширование информации базы данных. Коллекции хорошо подходят для кэширования статической информации, которая часто запрашивается в ходе одного сеанса.

Концепции и терминология Концепции и терминология Элементы и индексы. Коллекция состоит из множества элементов, каждый элемент обладает своим индексом. Иногда элементы называются «строками», а индексы — «номерами строк». Целочисленное / строковое индексирование. Индекс представляет собой целочисленное значение. С Oracle9, в качестве индекса ассоциативных массивов можно использовать строки (до 32 Кбайт длиной) – эта возможность не поддерживается для вложенных таблиц, и VARRAY. Тип коллекции. Каждая переменная, представляющая коллекцию, должна быть объявлена на основании заранее определенного типа. Разреженные и плотные коллекции. Коллекция (или массив, или список) называется плотной, если все ее элементы, от первого до последнего, определены и каждому из них присвоено некоторое значение (таковым может быть и NULL). Коллекция считается разреженной, если отдельные ее элементы отсутствуют. Ограниченная и неограниченная коллекция. Коллекция называется ограниченной, если заранее определены границы возможных значений индексов. Двумерные структуры в настоящее время напрямую не поддерживаются, можно создать многомерный массив, объявляя коллекцию коллекцией

Ассоциативные массивы Ассоциативные массивы Это одномерные неограниченные разреженные коллекции, состоящие из однородных элементов, доступные только в PL/SQL. Ранее в Oracle7) они назывались таблицами PL/SQL, а в Oracle8 индексируемыми таблицами. С Oracle9 назваются ассоциативными массивами. INDEX BY используется для индексирования посредством значений типа VARCHAR2 или PLS_INTEGER. Вложенные таблицы одномерные коллекции, состоящие из однородных элементов. Первоначально заполняются полностью, позднее из-за удаления элементов могут стать разреженными. Вложенные таблицы могут определяться и в PL/SQL, и в базах данных (например, в качестве столбцов таблиц). Вложенные таблицы представляют собой мультимножества, то есть элементы вложенной таблицы не упорядочены. Массив типа VARRAY Размер всегда ограничен, не бывают разреженными. Как и вложенные таблицы, массивы VARRAY используются и в PL/SQL, и в базах данных. Однако порядок их элементов при сохранении и выборке, в отличие от вложенных таблиц, сохраняется.

Метод – встроенная функция, оперирующая с коллекциями. Метод – встроенная функция, оперирующая с коллекциями.

DECLARE DECLARE TYPE list_of_names_t IS TABLE OF EMPLOYEES.first_name%TYPE INDEX BY PLS_INTEGER; l_row PLS_INTEGER; happyfamily list_of_names_t; BEGIN happyfamily (2020202020) := 'Eli'; happyfamily (-15070) := 'Steven'; happyfamily (-90900) := 'Chris'; happyfamily (88) := 'Veva'; l_row := happyfamily.FIRST; WHILE (l_row IS NOT NULL) LOOP DBMS_OUTPUT.put_line(happyfamily(l_row)); l_row := happyfamily.NEXT (l_row); END LOOP; END;

TYPE type_name IS TABLE OF TYPE type_name IS TABLE OF {column_type | variable%TYPE | table.column%TYPE} [NOT NULL] | table.%ROWTYPE [INDEX BY BINARY_INTEGER]; Identifier_name type_name;

DECLARE DECLARE TYPE ename_t_type IS TABLE OF employees.ename%TYPE INDEX BY BINARY_INTEGER; TYPE hiredate_t_type IS TABLE OF DATE INDEX BY BINARY_INTEGER; ename_table ename_t_type; hiredate_table hiredate_t_type; BEGIN ename_table(1) := ’CAMERON’; hiredate_table(8) := SYSDATE + 7; IF ename_table.EXISTS(1) THEN INSERT INTO ... ... END;

DECLARE DECLARE TYPE dept_table_type IS TABLE OF dept%ROWTYPE INDEX BY BINARY_INTEGER; dept_table dept_table_type; * dept%ROWTYPE – строка таблицы DEPT.

DECLARE DECLARE TYPE emp_table_type is table of emp.ename%TYPE INDEX BY BINARY_INTEGER; my_emp_table emp_table_type; BEGIN my_emp_table(1) : = ‘SMITH’; UPDATE emp SET sal = 1.1 * sal WHERE ename = my_emp_table(1); COMMIT; END;

--CREATE TYPE list_of_names_t IS TABLE OF VARCHAR2 (100); --CREATE TYPE list_of_names_t IS TABLE OF VARCHAR2 (100); DECLARE TYPE list_of_names_t IS TABLE OF VARCHAR2 (100); happyfamily list_of_names_t := list_of_names_t (); children list_of_names_t := list_of_names_t (); parents list_of_names_t := list_of_names_t (); BEGIN happyfamily.EXTEND (4); happyfamily (1) := 'Eli'; happyfamily (2) := 'Steven'; happyfamily (3) := 'Chris'; happyfamily (4) := 'Veva'; children.EXTEND; children (1) := 'Chris'; children.EXTEND; children (2) := 'Eli'; parents := happyfamily MULTISET EXCEPT children; FOR I IN parents.FIRST .. parents.LAST LOOP DBMS_OUTPUT.put_line (parents (i)); END LOOP; END;

CREATE TYPE first_names_t IS VARRAY (2) OF VARCHAR2 (100); CREATE TYPE first_names_t IS VARRAY (2) OF VARCHAR2 (100); CREATE TYPE child_names_t IS VARRAY (1) OF VARCHAR2 (100); CREATE TABLE family ( surname VARCHAR2(1000), parent_names first_names_t, children_names child_names_t); DECLARE parents first_names_t := first_names_t (); children child_names_t := child_names_t (); BEGIN parents.EXTEND (2); parents (1) := 'Samuel'; parents (2) := 'Charina'; children.EXTEND; children (1) := 'Feather'; INSERT INTO family ( surname, parent_names, children_names ) VALUES ( 'Assurty', parents, children ); END; SELECT * FROM family SURNAME PARENT_NAMES CHILDREN_NAMES -------------------------------------------- Assurty FIRST_NAMES_T('Samuel', 'Charina') CHILD_NAMES_T('Feather')

Выбор типа коллекции

Массовая обработка Массовая обработка - команда FORALL и секция BULK COLLECT - конструкции массовой обработки (bulk processing). PL/SQL выполняет процедурные команды самостоятельно, а команды SQL передает ядру SQL. Каждое переключение контекста приводит к дополнительным затратам ресурсов.

FORALL for cnt in 1..100000 loop insert into temp_bulk values(numb(cnt),name(cnt)); end loop; FORALL i in 1..100000 insert into temp_bulk values(numb(i),name(i)); Execution Time (secs) --------------------- FOR loop: 4,9 FORALL: ,14

BULK COLLECT DECLARE TYPE first_name_t IS TABLE OF EMPLOYEES.first_name%TYPE; TYPE salary_t IS TABLE OF EMPLOYEES.salary%TYPE; v_first_name first_name_t; v_salary salary_t; BEGIN SELECT first_name, salary BULK COLLECT INTO v_first_name, v_salary FROM employees WHERE job_id = 'IT_PROG' ; FOR i IN 1 .. v_first_name.COUNT LOOP dbms_output.put_line(v_first_name(i)||' earns - ' ||v_salary(i) ); END LOOP; END;

Итоги Изучены составные типы данных: Записи Коллекции 2 типа FORALL и BULK COLLECT

Практика №5! 40 минут