Record Oracle PL/SQL

В этом учебном материале вы узнаете, как использовать спецификатор записи Record в Oracle PL/SQL с синтаксисом и примерами.

Описание

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

Определение и объявление Record в в Oracle PL/SQL

Атрибут %ROWTYPE позволяет объявить запись, представляющую строку в таблице базы данных, без перечисления всех столбцов. Ваш код продолжает работать даже после добавления столбцов в таблицу. Если вы хотите представить подмножество столбцов в таблице или столбцы из разных таблиц, вы можете определить VIEW или объявить CURSOR для выбора правильных столбцов и любых необходимых объединений, а затем применить атрибут %ROWTYPE к VIEW или CURSOR.

Синтаксис

Синтаксис определения типа Record и объявления переменной в Oracle PL/SQL:

TYPE type_rec_name IS Record (field_1 datatype,
field_2 datatype,
… field_n datatype);
var_rec type_rec_name;

Параметры или аргументы

type_rec_name – имя определенного типа Record
var_rec – имя переменной типа Record
field_1, field_2,… field_n – поля типа Record
datatype – тип данных для полей типа Record. Может быть любой из списка:

Типы данных БД Описание
collection_name Коллекция (associative array, nested table или varray), ранее объявленная в текущей области.
collection_type_name Пользовательский тип коллекции, определенный с использованием спецификатора типа данных TABLE или VARRAY.
CONSTANT Обозначает объявление константы. Вы должны инициализировать константу при объявлении. После инициализации значение константы не может быть изменено.
constant_name Программная константа.
cursor_name Явный курсор, ранее объявленный в текущей области.
cursor_variable_name Переменная курсора PL/SQL, ранее объявленная в текущей области.
db_table_name Таблица базы данных или представление, которые должны быть доступны при декларировании.
db_table_name.column_name Таблица базы данных и столбец, которые должны быть доступны при декларировании.
expression Комбинация переменных, констант, литералов, операторов и вызовов функций.Простейшее выражение состоит из одной переменной. Когда декларирование в разработке, значение выражения присваивается константе или переменной.Значение и константы или переменной должны иметь совместимые типы данных.
NOT NULL Ограничение, которое запрещает программе присваивать значение null переменной или константе. Присвоение значения null переменной, определенной как NOT NULL, вызывает предопределенное исключение VALUE_ERROR. Ограничению NOT NULL должно следовать в предложении инициализации.
object_name Экземпляр типа объекта, ранее объявленного в текущей области.
record_name Пользовательская или запись %ROWTYPE, ранее объявленная в текущей области.
record_name.field_name Поле в пользовательской или %ROWTYPE записи, ранее объявленной в текущей области.
record_type_name Пользовательский тип записи, который определяется с помощью спецификатора типа данных RECORD.
ref_cursor_type_name Пользовательский тип переменной курсора, определенный с помощью спецификатора типа данных REF CURSOR.
%ROWTYPE Представляет запись, которая может содержать строку из таблицы базы данных или курсора. Поля в записи имеют одинаковые имена и типы данных в виде столбцов в строке.
scalar_datatype_name Предопределенный скалярный тип данных, такой как BOOLEAN, NUMBER или VARCHAR2. Включает в себя любые квалификаторы для семантики размера, точности или символа в сравнении с байтом.
%TYPE Представляет тип данных ранее объявленной коллекции, переменной курсора, полем, объекту, записи, столбцу базы данных или переменной.
variable_name Программная переменная.

Примечание

  • Для создания Record вы определяете тип записи, а затем объявляете переменную этого типа.
  • Вы также можете создать или найти COLUMN, VIEW или CURSOR PL/SQL со значениями, которые вы хотите использовать, и атрибут %ROWTYPE для создания соответствующей Record.
  • Вы можете определять типы Record в декларативной части любого блока PL/SQL, подпрограммы или пакета. Когда вы определяете свой собственный тип Record, вы можете указать ограничение NOT NULL для полей или присвоить им значения по умолчанию.
  • Тип Record, определенный в спецификации пакета, несовместим с идентично определенным локальным типом Record.

Примеры

Рассмотрим несколько примеров, чтобы понять, как использовать Record в Oracle PL/SQL.

Пример Record и объявление переменной

В следующем примере Oracle PL/SQL поля инициализированы как скалярные типы данных.

В этом примере определяется тип Record с именем DeptRecTyp, указывающий начальное значение для каждого поля. Затем он объявляется переменная этого типа с именем dept_rec и печатаются её поля.

Пример Record с атрибутом поля NOT NULL

В следующем примере Oracle PL/SQL определим тип Record, укажем полям ограничение NOT NULL и присвоим начальные значения;

Пример записи в Oracle PL/SQL с помощью атрибута %ROWTYPE

Если нам запись таблицы или её некоторые столбцы, то для определения записи таблицы используем атрибут %ROWTYP. Для определения некоторых столбцов таблицы используйте курсор.
В следующем примере объявим записи с помощью атрибута %ROWTYPE;

В этом примере объявили запись rec1 с помощью конструкции teble_name%ROWTYPE и запись rec2 с помощью cursor_name%ROWTYPE.

Пример Record с помощью атрибута %TYPE

В следующем примере определим поля Record с помощью атрибута %TYPE, используя точечную нотацию table_name.column_name%TYPE и добавим пользовательское поле;

В этом примере определили запись Record с помощью конструкции table_name.column_name%TYPE и подмешали к записи пользовательское поле rating.

Пример Record в качестве возвращаемых значений функции.

В следующем примере определим тип RECORD и функцию которая этот тип будет возвращать.

В этом примере функция nth_highest_salary возвращает значение типа EmpRec.

Пример Record в качестве параметров процедуры.

Подобно скалярным переменным, пользовательские Record (записи) могут быть объявлены как формальные параметры процедур.

В этом примере процедура raise_salary принимает типа EmpRec как параметр.

Пример присвоение значений для Record

Чтобы определить все поля для Record по умолчанию, присвойте ей неинициализированную запись того же типа. Например

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

Пример Record с полем Record (вложенная запись)

Следующий пример показывает, как можно определить тип Record как вложенную в другой тип Record.

В этом примере определили тип name_rec как вложенную запись типа contact. Доступ к полям вложенного типа записи получили с помощью точечной нотации.

Пример Record с полем Varray

Следующий пример показывает, как можно VARRAY определить как поле типа Record.

В этом примере определили тип VARRAY с именем full_name и тип Record с именем contact.Теперь в типе contact доступно поле типа full_name. Доступ к данным вложенного типа VARRAY получили с помощью точечной нотации.

Пример INSERT в базу данных Oracle PL/SQL с помощью Record

Oracle PL/SQL оператора INSERT позволяет вставлять Record в строки базы данных, используя одну переменную типа RECORD или атрибут %ROWTYPE в предложении VALUES вместо списка полей. Это делает ваш код более читабельным и поддерживаемым.
Если вы выдаете INSERT через инструкцию FORALL, вы можете вставлять значения из целого набора записей.
Количество полей в записи должно быть равно числу столбцов, перечисленных в предложении INTO, а соответствующие поля и столбцы должны иметь совместимые типы данных. Чтобы убедиться, что запись совместима с таблицей, вам может быть удобнее объявить переменную как тип table_name%ROWTYPE.
Пример вставка записи в Oracle PL/SQL с использованием %ROWTYPE

В этом примере объявляется переменная dept_info с использованием %ROWTYPE. Вы можете выполнить INSERT со значением dept_info без указания списка столбцов. Объявление %ROWTYPE гарантирует, что атрибуты записи имеют точно такие же имена и типы, что и столбцы таблицы.

Пример UPDATE базы данных Oracle PL/SQL с помощью Record

Расширение только для PL/SQL оператора UPDATE позволяет обновлять строки базы данных с использованием одной переменной типа RECORD или %ROWTYPE в правой части оператора SET вместо списка полей.
Если вы запустите UPDATE с помощью оператора FORALL, вы можете обновить набор строк, используя значения из целого набора записей.
Также с помощью оператора UPDATE вы можете указать запись в предложении RETURNING для извлечения новых значений в запись. Если вы задаете UPDATE через оператор FORALL, вы можете получить новые значения из набора обновленных строк в коллекцию записей.
Количество полей в записи должно быть равно числу столбцов, перечисленных в предложении SET, а соответствующие поля и столбцы должны иметь совместимые типы данных.
Например:
Вы можете использовать ключевое слово ROW для представления целой строки:

Ключевое слово ROW разрешено только с левой стороны предложения SET.
Аргумент SET ROW должен быть реальной записью PL/SQL, а не подзапросом, который возвращает одну строку.
Запись также может содержать коллекции или объекты.

Пример использования RETURNING с Record

Операторы INSERT, UPDATE и DELETE могут включать предложение RETURNING, которое возвращает значения столбца из затронутой строки в переменную записи PL/SQL. Это устраняет необходимость выбора строки после вставки или обновления или перед удалением.
По умолчанию вы можете использовать это предложение только при работе с одной строкой. Когда вы используете коллекцию SQL, вы можете использовать форму RETURNING BULK COLLECT INTO для хранения результатов в одной или нескольких коллекциях.
Следующий пример обновляет salary (зарплату) employee (сотрудников) и возвращает last_name (имя сотрудника), и new salary (новую зарплату) в переменную emp_info:

Ограничения на INSERT / UPDATE записей

В настоящее время к INSERT/UPDATE записей применяются следующие ограничения:
Переменные Record допускаются только в следующих местах:

  • В правой части предложения SET в инструкции UPDATE
  • В предложении VALUES инструкции INSERT
  • В подпункте INTO условия RETURNING

Переменные Record не допускаются в списке SELECT, WHERE, условии GROUP BY или ORDER BY.

Ключевое слово ROW разрешено только с левой стороны предложения SET. Кроме того, вы не можете использовать ROW с подзапросом.
В UPDATE разрешено только одно предложение SET, если используется ROW.
Если в VALUES оператора INSERT содержит переменную Record, то в этом предложении не допускается никакая другая переменная или значение.
Если INTO в предложении RETURNING содержит переменную Record, то не допускается никакая другая переменная или значение.
Не поддерживаются следующие:

  • Типы вложенных записей
  • Функции, возвращающие запись
  • Запись вложений/обновлений с помощью оператора EXECUTE IMMEDIATE.

Пример запроса данных в коллекцию Record

Вы можете использовать предложение BULK COLLECT с инструкцией SELECT INTO или FETCH для извлечения набора строк в коллекцию записей.