Oracle тип данных table

Обновлено: 07.07.2024

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

Таблица - это набор данных, и на данные в таблице можно ссылаться и обрабатывать их как единое целое.

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

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

PL/SQL запись

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

Особенности записи:

• В каждой записи может быть много полей. К
• Вы можете назначать начальные значения записям, и вы можете использовать NOT NULL для ограничения записей.
• Поле без начального значения определяется как NULL • Зарезервированное слово DEFAULT также может использоваться при определении поля.
• Вы можете определить тип RECORD и объявить определяемые пользователем записи в части объявления любого блока, подпрограммы или пакета.
• Вы можете объявлять вложенные записи и ссылаться на них, одна запись может быть компонентом других записей.

грамматика:

TYPE type_name IS RECORD (field_declaration[, field_declaration]…);

field_name [[NOT NULL] expr]
TYPE emp_record_type IS RECORD (empno emp.empno%type, ename emp.ename%type, job varchar2(9)); emp_record emp_record_type ;
В присвоении можно использовать точечную нотацию для ссылки на поле в записи.

record_name.field_name

%ROWTYPE Атрибуты

• Объявленная переменная соответствует набору столбцов в таблице или представлении базы данных.
• Добавьте имя таблицы базы данных перед% ROWTYPE
• Имя и тип данных поля в записи относятся к столбцу в таблице или представлении.

DECLARE emp_record emp%rowtype; …. emp_record.ename=‘ljs’; emp_record.sal=3000; SELECT * INTO emp_record FROM emp WHERE ……;
Преимущества% ROWTYPE:

• Вам не нужно знать количество и типы столбцов в базе данных.
• Во время работы количество и типы столбцов в базе данных могут изменяться. К
• Используйте этот атрибут в инструкции SELECT для эффективного извлечения строк в таблице.

Коллекции присутствую в том или ином виде в большинстве языков программирования и везде имеют схожую суть в плане использования. А именно – позволяют хранить набор объектов одного типа и проводить над всем набором какие-либо действия, либо в цикле проводить однотипные действия со всеми элементами набора.

Таким же образом коллекции используются и в Oracle.

Содержание статьи

Общие сведения о коллекциях в pl/sql

Типы коллекций

Тип коллекции	Количество элементов	Тип индекса	Плотная или разреженная	Без инициализации	Где объявляется	Использование в SQL
Ассоциативный массив (index by table)	Не задано	String Pls_integer	Плотная и разреженная	Empty	PL/SQL block Package	Нет
Varray (variable-size array)	Задано	Integer	Только плотная	Null	PL/SQL block Package Schema level	Только определенные на уровне схемы
Nested table	Не задано	Integer	При создании плотная, может стать разреженной	Null	PL/SQL block Package Schema level	Только определенные на уровне схемы

Плотность коллекции означает, что между элементами коллекции нет пропусков, пустых мест. Некоторые коллекции, как видно из таблицы, могут быть разреженными – т.е. могут иметь разрывы между элементами. Это значит, что в коллекции, например, могут быть элементы с индексом 1 и 4, а с индексом 2 и 3 элементов нет. При этом слоты памяти под 2-й и 3-й элементы будут существовать и будут принадлежать коллекции (в случае nested table), но не содержать при этом объектов и попытка прочитать содержимое этих элементов вызовет ошибку no_data_found.
Подробности можно узнать из видео-лекции в конце статьи.

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

Набор пар ключ-значение
Данные хранятся в отсортированном по ключу порядке
Не поддерживает DML-операции (не может участвовать в селектах, не может храниться в таблицах)
При объявлении как константа должен быть сразу инициализирован функцией
Порядок элементов в ассоциативном массиве с строковым индексом зависит от параметров NLS_SORT и NLS_COMP
Нельзя объявить тип на уровне схемы, но можно в пакете
Не имеет конструктора
Индекс не может принимать значение null (но допускает пустую строку — подробности и ссылка на пример в первом комментарии)
Datatype – это любой тип данных, кроме ref cursor

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

Restrictions:
При изменении параметров NLS_SORT и NLS_COMP во время сессии после заполнения ассоциативного массива, можем получать неожиданные результаты вызовов методов first, last, next, previous. Также могут возникнуть проблемы при передаче ассоциативного массива в качестве параметра на другую БД с иными настройками NLS_SORT и NLS_COMP

Varray

Представляет собой массив последовательно хранящихся элементов

Тип описывается следующим образом (varay_type_def):

Размер задается при создании
Индексируется с 1
Инициализируется конструктором
Если параметры в конструктор не передаются, возвращается пустая коллекция
Datatype – это любой тип данных, кроме ref cursor

Знаем максимально возможное количество элементов
Доступ к элементам последовательный

Restrictions:
Максимальный размер – 2 147 483 647 элементов

Nested table

Тип описывается следующим образом (nested_table_type_def):

Размер коллекции изменяется динамически
Может быть в разряженном состоянии, как показано на картинке
<
Инициализируется конструктором
Если параметры в конструктор не передаются, возвращается пустая коллекция
Datatype – это любой тип данных, кроме ref cursor
Если содержит только одно скалярное значение, то имя колонки – Column_Value

Set operations с nested tables

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

Операция	Описание
MULTISET UNION	Возвращает объединение двух коллекций
MULTISET UNION DISTINCT	Возвращает объединение двух коллекций с дистинктом (убирает дубли)
MULTISET INTERSECT	Возвращает пересечение двух коллекций
MULTISET INTERSECT DISTINCT	Возвращает пересечение двух коллекций с дистинктом (убирает дубли)
SET	Возвращает коллекцию с дистинктом (т.е. коллекцию без дублей)
MULTISET EXCEPT	Возвращает разницу двух коллекций
MULTISET EXCEPT DISTINCT	Возвращает разницу двух коллекций с дистинктом (убирает дубли)

Небольшой пример (обратите внимание на результат операции MULTISET EXCEPT DISTINCT)

Кроме типов данных Oracle7, PL/SQL поддерживает несколько дополнительных типов данных и позволяет использовать в своих конструкциях некоторые основные типы с большим диапазоном.

Рассмотрим подробнее типы данных TABLE и RECORD, позволяющие создавать одномерные массивы и записи, широко используемые в программах PL/SQL.

Таблицы PL/SQL

Для описания типа данных TABLE используется синтаксис:

TYPE type_name IS TABLE OF < column_type | variable%TYPE |
table.column%TYPE > [NOT NULL] INDEX BY BINARY_INTEGER;

Имя (например, name_plsql_table), которое описывается табличным типом данных, называется таблицей PL/SQL. Это описание, размещаемое в разделе DECLARE, имеет вид: name_plsql_table type_name;
Ссылки на строки таблицы PL/SQL осуществляются аналогично ссылкам на элементы одномерного массива: name_plsql_table(index), где index принадлежит типу BINARY_INTEGER. Например, для ссылки на третью строку в таблице PL/SQL «ename_tab» следует написать: ename_tab(3).

Для присвоения значения конкретной строке таблицы PL/SQL используется синтаксис: name_plsql_table(index) := expr;

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

Записи PL/SQL

Если создаваемая запись (sotr) соответствует описанию столбцов какой-либо базовой таблицы (например, kadry), то ее объявление можно осуществить в разделе DECLARE с помощью атрибута %ROWTYPE: sotr kadry%ROWTYPE;

В противном случае для объявления записи необходимо сначала определить ее тип данных. Для описания типа данных RECORD используется синтаксис:

При объявлении типа записи можно присвоить ее полям некоторые значения. Если же для поля вводится ограничение NOT NULL (для предотвращения назначения пустых значений), то этому полю надо обязательно присвоить значение. Например:

TYPE SotrRecTyp IS RECORD (nomer NUMBER(4) NOT NULL := 1001,

familiy CHAR(20), dolgnost CHAR(14), otdel NUMBER(3) := 102);

Объявление создаваемой записи (например, name_plsql_record) производится в разделе DECLARE и имеет вид: name_plsql_record type_name;
Ссылки на отдельные поля записи осуществляются так: name_plsql_record.field_name;
Для присвоения значения конкретному полю записи используется синтаксис: name_plsql_record.field_name := expr;

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

Переменные, константы и выражения PL/SQL

В программах PL/SQL могут использоваться переменные и константы, описываемые в разделе DECLARE с помощью конструкции вида: variable_name [CONSTANT] type_name [NOT NULL] [ < := | DEFAULT >expr ]
Например:

Запись опубликована 08.04.2010 в 5:50 дп и размещена в рубрике Oracle7 краткий справочник. Вы можете следить за обсуждением этой записи с помощью ленты RSS 2.0. Можно оставить комментарий или сделать обратную ссылку с вашего сайта.

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

Хранение в столбцах таблицы значений в виде объектов, в смысле объектного подхода (ОП в программировании и моделировании), фирма Oracle впервые обеспечила в рамках так называемой "объектно-реляционной модели" начиная с версии Oracle 8. Некоторые существенные пробелы первой реализации (например, отсутствие наследования типов ) были устранены в версии 9. Примеры ниже не выходят за рамки возможностей версии 9.2, позже которой, впрочем, никаких существенных нововведений по объектной части не наблюдалось. Объектные возможности Oracle в общем следуют определениям SQL :1999, однако делают это непунктуально.

Программируемые типы данных и объекты в БД

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

Ниже приводится простой пример использования программируемых (объектных) типов.

Вначале требуется создать "тип", как разновидности хранимых элементов БД. Пример создания типа объекта (в SQL*Plus):

Здесь типу ADDRESS_TYPE приписаны два "свойства" (по объектной терминологии): ZIP и LOCATION . В реальной жизни для представления адреса в типе наверняка будет указано большее количество свойств, однако в ознакомительном примере их более пространный перечень излишен и не добавит понимания техники.

Определение типа напоминает определение таблицы, однако в отличие от таблицы (а также стандарта SQL и от реляционного подхода) тип объекта в Oracle не имеет права содержать ограничений целостности (которые в таком случае можно было бы назвать "ограничениями целостности типа"). Если необходимо их указать, сделать это придется только по месту употребления типа, то есть в описании таблицы.

В соответствии с традициями объектного подхода (уместно вспомнить, что "объектной теории", в отличие от реляционной, не создано) Oracle разрешает использовать тип для создания "буквальных значений" и собственно объектов. Далее приводится сначала несколько примеров первого, а затем второго.

"Буквальные значения" фактически позволяют работать со значениями, обладающими известной СУБД структурой и однозначно определяются набором значений элементов своей структуры.

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

Столбцы ADDR и HOME можно с некоторой вольностью назвать "объектными атрибутами". Они не позволяют хранить объектные значения в виде самостоятельной сущности и ссылаться на них ссылками. Локализовать такие значения можно только по обычным правилам поиска данных в таблице.

В выражениях явно указанные объектные значения формулируются с помощью конструктора. В отличие от других объектных систем, например, от Java, в Oracle конструктор умолчательно имеет список параметров, соответствующих свойствам типа. Примеры применения в операциях добавления данных:

Oracle допускает определенные синтаксические вольности в записи выражения над объектными данными. Здесь и далее используются частные случаи возможных формулировок.

Пример применения в запросе о сотрудниках, "работающих по месту жительства", за исключением конкретно указанного адреса:

Пример показывает легкость формулирования сравнения составных величин, каковыми являются адреса. Сравнение осуществляется поэлементно, путем сравнением всех свойств по очереди. Увы, но простота формулировки не дает права программисту расслабляться и забывать об особых случаях сравнения с данными типа CHAR и с NULL . Так, присутствие NULL в буквальных объектных значениях запутывает проблему сравнения еще больше, чем для случая простых типов. Сравните:

То есть получается, что x = x не дает TRUE , но притом x IS NOT NULL дает TRUE ( x имеет значение).

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

Таблицы объектов

Созданный в БД тип можно употребить и для создания "таблиц объектов":

Хотя для этой категории хранимых элементов используется термин "таблица", такая таблица всегда содержит ровно один столбец, и именно объектного типа.

Запись занесения "строк" в такую таблицу может быть, в частности, такой:

SELECT a.*, UPPER ( location ) FROM addresses1 a;

Объекты в таких таблицах хранятся как самостоятельные сущности, у которых имеется автоматически порождаемый СУБД внутренний уникальный идентификатор object ID, в соответствии с классическим объектным подходом позволяющий ссылаться на конкретные объекты из других таблиц или из программы. Сравнение элементов-"строк" в таблице объектов друг с другом происходит уже не по значениям свойств, как в случае объектного столбца в обычной таблице, а по значению object ID. Перейти на сравнение значений свойств позволяет функция VALUE, например:

Сделан запрос об отделах, расположенных по адресам из таблицы ADDRESS1 .

Не исключено, что создатели функции VALUE обсуждали другое ее название — LITERAL_VALUE . По крайней мере, оно точнее описывает совершаемое действие: создание значения со структурой из объекта. Буквальные значения сравниваются друг с другом по значениям их свойств, а объекты — по значениям object ID.

Читайте также: