Oracle тип record пример
Познакомившись, достаточно близко с курсорами, я думаю, у вас хватило терпения разобраться со всем этим! :) Далее давайте разберем один довольно интересный тип данных, который очень напоминает структуры в языке C. Обычные скалярные типы, такие как VARCHAR2, NUMBER и т.д. предварительно определены в модуле STANDART. По этому для их использования программе требуется лишь объявить переменную, данного типа, например вот так:
Но иногда удобнее использовать, так называемый составной тип. Одним из таких типов, в языке PL/SQL является запись RECORD. Как можно было догадаться, предыдущий пример, очень напоминает своей структурой, нашу учебную таблицу CUSTOMERS. Посмотрим на синтаксис объявления записи:
Вот таким образом объявляется составной тип RECORD. Как видите для полей записи, можно указывать ограничение NOT NULL, но подобно описанию, переменной вне записи исходное значение и ограничение NOT NULL не обязательны! Давайте запишем для примера вот такой блок:
После его прогона в SQL*Plus получаем:
Как видно процедура успешно выполнилась и вернула значения. Здесь хорошо видно, как мы проинициализировали переменные записи внутри объявления. Так же одно из полей объявленное как NOT NULL сразу получило значение, вследствие того, что при этих условиях это необходимо! Давайте так же рассмотрим блок, где показано, как с использованием правил обращения к полям записи, а именно через точечную нотацию (как в языке Pascal), так же можно переопределить исходные значения полей записи:
После обработки получаем:
Как видно переменная MY_SMPL.m_DtFld получила значение текущей даты, с применением функции SYSDATE, которая возвращает текущую дату и время системы, а переменная MY_SMPL.m_Fld4, сменила строковый литерал. Вот таким образом, записи как составной тип данных способны к изменению своего внутреннего содержимого. Теперь давайте рассмотрим, как производится присваивание записей. В языке PL/SQL при присвоении значения одной записи другой используется, так называемая семантика копирования. Хотя присвоить одну запись непосредственно другой не допускается, даже если поля в обоих записях одинаковы. Для PL/SQL это разные типы и по этому происходит следующее:
Что и следовало ожидать, получаем "PLS-00382: выражение неправильного типа"! Все верно, типы то разные! И такое представление не проходит! Но вот присвоение полей этих записей между собой вполне приемлимо! Вот так:
Получаем после обработки:
Вот теперь все правильно! Типы полей совпадают и присвоение проходит нормально! Присвоить значения полям, базы можно и с помощью оператора SELECT, выбрав одну запись из таблицы. Например, давайте запишем такой блок: Здесь объявлена запись на основе типов таблицы CUSTOMERS, и выбрана одна запись из этой таблицы с помощью, оператора INTO, все это отправлено в переменные записи:
Вот таким образом, работает составной тип RECORD. Да, кстати я тут нашел, кое-какие ошибочки и недочеты в прошлых уроках. Я за них извиняюсь, так как материал, очень объемный и сложный! Вы сами можете мне на них указать, я буду рад! При этом сразу оговорюсь ДЕЛАЮ КАК МОГУ И СТАРАЮСЬ ПОМОЧЬ ВСЕМ В ИЗУЧЕНИИ ORACLE! А, отпускать в мой адрес ехидные заковырки, может каждый, а вот реально что-то сделать в этой жизни способны не многие! По этому ошибки в частности в шаге 17 исправлены и ничего страшного я в этом не вижу. :) Что ж, удачи всем и жду конструктивных советов и замечаний!
Record представляет собой группу связанных элементов данных, хранящихся в полях, каждая со своим именем и типом данных. Вы можете использовать Record как переменную, которая может содержать строку таблицы или некоторые столбцы (поля) из строки таблицы.
Overview of PL/SQL Record
A PL/SQL record is a composite data structure which consists of multiple fields; each has its own value. The following picture shows an example record that includes first name, last name, email, and phone number:
PL/SQL record helps you simplify your code by shifting from field-level to record-level operations.
PL/SQL has three types of records: table-based, cursor-based, programmer-defined. Before using a record, you must declare it.
Операции над коллекциями
MULTISET UNION | Возвращает объединение двух коллекций |
MULTISET UNION DISTINCT | Возвращает объединение двух коллекций с дистинктом (убирает дубли) |
MULTISET INTERSECT | Возвращает пересечение двух коллекций |
MULTISET INTERSECT DISTINCT | Возвращает пересечение двух коллекций с дистинктом (убирает дубли) |
SET | Возвращает коллекцию с дистинктом (т.е. коллекцию без дублей) |
MULTISET EXCEPT | Возвращает разницу (усечение) двух коллекций |
MULTISET EXCEPT DISTINCT | Возвращает разницу (усечение) двух коллекций с дистинктом (убирает дубли) |
Примечание
- Для создания Record вы определяете тип записи, а затем объявляете переменную этого типа.
- Вы также можете создать или найти COLUMN, VIEW или CURSOR PL/SQL со значениями, которые вы хотите использовать, и атрибут %ROWTYPE для создания соответствующей Record.
- Вы можете определять типы Record в декларативной части любого блока PL/SQL, подпрограммы или пакета. Когда вы определяете свой собственный тип Record, вы можете указать ограничение NOT NULL для полей или присвоить им значения по умолчанию.
- Тип Record, определенный в спецификации пакета, несовместим с идентично определенным локальным типом Record.
Table-based record
To declare a table-based record, you use the %ROWTYPE attribute with a table name. A table-based record has each field corresponding to a column in a table.
The following example declares a record named r_contact with the same structure as the contacts table in the sample database:
Nested record
A record can contain a field which is another record. Nesting records is a powerful way to structure your program data and hide complexity in your code.
The following example declares a record type named address . Then in the record type customer has two fields ship_to and bill_to which are based on the address record type.
In this tutorial, you have learned about PL/SQL record and how to use it effectively to simplify your code.
Referencing a record’s field
You reference a field in a record via the dot notation as shown below:
For example, to reference the first_name field of the r_contact record, you use the following expression:
Методы коллекций
Метод коллекции - это подпрограмма PL/SQL - либо функция, которая возвращает информацию о коллекции, либо процедура, которая работает с коллекцией. Методы сбора данных упрощают использование коллекций и упрощают поддержку ваших приложений.
С нулевой коллекцией EXISTS является единственным методом сбора, который не вызывает предопределенное исключение COLLECTION_IS_NULL.
Метод коллекции | Тип | Описание |
---|---|---|
DELETE | Процедура | Удаляет элементы из коллекции. |
TRIM | Процедура | Удаляет элементы из конца varray или Nested Tables. |
EXTEND | Процедура | Добавляет элементы в конец varray или Nested Tables. |
EXISTS | Функция | Возвращает TRUE тогда и только тогда, когда существует определенный элемент varray или Nested Tables. |
FIRST и LAST | Функции | FIRST возвращает первый, LAST последний индекс в коллекции. |
COUNT | Функция | Возвращает количество элементов в коллекции. |
LIMIT | Функция | Возвращает максимальное количество элементов, которые может иметь коллекция. |
PRIOR и NEXT | Функции | PRIOR возвращает индекс, который предшествует указанному индексу, NEXT возвращает индекс, который следует за указанным индексом. |
Пример Record и объявление переменной
В следующем примере Oracle PL/SQL поля инициализированы как скалярные типы данных.
Составной тип данных хранит значения, которые имеют внутренние компоненты. Вы можете передать целые составные переменные подпрограммам в качестве параметров, а также получить доступ к внутренним компонентам составных переменных по отдельности. Внутренние компоненты могут быть скалярными или составными. Вы можете использовать скалярные компоненты везде, где вы можете использовать скалярные переменные. PL/SQL позволяет вам определять два вида составных типов данных: коллекция и запись. Вы можете использовать составные компоненты везде, где вы можете использовать составные переменные одного типа.
Синтаксис
Синтаксис определения типа Record и объявления переменной в Oracle PL/SQL:
TYPE type_rec_name IS Record (field_1 datatype,
field_2 datatype,
… field_n datatype);
var_rec type_rec_name;
Programmer-defined record
The table-based and cursor-based records are good enough when you need to create records based on existing structures.
If you want to create a record whose structure is not based on the existing ones, then you use programmer-defined record.
To declare a programmer-defined record, you use the following steps:
- Define a record type that contains the structure you want in your record.
- Declare a record based on the record type.
The following shows the syntax for defining a record type:
To declare a record based on the predefined record type, you use the following syntax:
The following example defines a record type whose name is customer_contacts and a record whose type is customer_contacts :
Коллекции
Declaring records
You define and declare records in the declaration section of a block or via package specification.
Определение и объявление Record в в Oracle PL/SQL
Атрибут %ROWTYPE позволяет объявить запись, представляющую строку в таблице базы данных, без перечисления всех столбцов. Ваш код продолжает работать даже после добавления столбцов в таблицу. Если вы хотите представить подмножество столбцов в таблице или столбцы из разных таблиц, вы можете определить VIEW или объявить CURSOR для выбора правильных столбцов и любых необходимых объединений, а затем применить атрибут %ROWTYPE к VIEW или CURSOR.
Records and INSERT / UPDATE statements
You can insert a new row into a table using a %ROWTYPE record without having to specify each field. The following statement creates a new table named persons for demonstration:
The following block inserts a new row into the persons table using a %ROWTYPE record:
To update a row from a %ROWTYPE record, you use the SET ROW keywords as shown in the following example:
Assigning records
You can assign a record to another record of the same type, for example:
But you cannot compare two records of the same type via a comparisn operator. The following example is an invalid comparison:
In this case, you need to compare individual fields of the record instead:
You can assign a value to the individual field of a record, for example:
You can use SELECT INTO a whole record (or individual fields):
You can FETCH INTO a whole record or individual fields:
Cursor-based record
A cursor-based record has each field corresponding a column or alias in the cursor SELECT statement.
To declare a cursor-based record, you use the %ROWTYPE attribute with an explicit cursor as shown below:
The following example declares a record with the same structure as an explicit cursor:
In this example:
- First, declare an explicit cursor that fetches data from the first_name , last_name , and phone columns of the contacts table.
- Second, declare a record named r_contact whose structure is the same as the c_contacts cursor.
Примеры
Рассмотрим несколько примеров, чтобы понять, как использовать Record в Oracle PL/SQL.
Параметры или аргументы
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 | Программная переменная. |
Записи
Исключительные ситуации коллекции.
В большинстве случаев, если вы ссылаетесь на несуществующий элемент коллекции, PL/SQL вызывает предопределенное исключение. Рассмотрим следующий пример:
- В первом случае вложенная таблица атомарно равна NULL.
- Во втором случае индекс равен NULL.
- В третьем случае индекс находится за пределами допустимого диапазона.
- В четвертом случае индекс превышает количество элементов в таблице.
- В пятом случае индекс обозначает удаленный элемент.
Следующий список показывает, когда возникает данное исключение:
Исключение коллекции | Когда возникает |
---|---|
COLLECTION_IS_NULL | вы пытаетесь оперировать с атомарно нулевой коллекцией. |
NO_DATA_FOUND | обозначает элемент, который был удален, или несуществующий элемент ассоциативного массива. |
SUBSCRIPT_BEYOND_COUNT | индекс превышает количество элементов в коллекции. |
SUBSCRIPT_OUTSIDE_LIMIT | индекс находится за пределами допустимого диапазона. |
VALUE_ERROR | индекс не имеет значения или не может быть преобразован в тип ключа. Это исключение может возникнуть, если ключ определен как диапазон PLS_INTEGER, а индекс находится за пределами этого диапазона. |
В некоторых случаях вы можете передавать недопустимые индексы в метод, не вызывая исключения. Например, когда вы передаете нулевой индекс процедуре DELETE, она ничего не делает. Кроме того, вы можете заменить удаленные элементы, без возникновения NO_DATA_FOUND, как показано в следующем примере:
A Record type is a complex data type which allows the programmer to create a new data type with the desired column structure.
- It groups one or more column to form a new data type
- These columns will have its own name and data type
- A Record type can accept the data
- As a single record that consists of many columns OR
- It can accept the value for one particular column of a record
Syntax for declaration at the database level:
In the first syntax, we can see the keyword ‘CREATE TYPE’ this instructs the compiler to create the record type named “type_name_db” with the specified column as a database object.
This is given as an individual statement and not inside any block.
Syntax for declaration at subprogram level:
In the syntax, we are creating the record type named “type_name” only inside the subprogram.
In both declaration method, the way of defining the column and data type is similar.
Example 1: RECORD Type as Database Object
In this program, we are going to see how to create “Record type” as a database object. We are going to create record type ’emp_det’ with four columns. The columns and their data type are as follows:
- EMP_NO (NUMBER)
- EMP_NAME (VARCHAR2 (150))
- MANAGER (NUMBER)
- SALARY (NUMBER)
Output:
Code Explanation:
- The above code will create type emp_det as a database object.
- It will have 4 column emp_no, emp_name, manager and salary as defined.
- Now ’emp_det’ is a similar to other data type (like NUMBER, VARCHAR@, etc.) And it is visible in the entire database. Hence this can be used in the entire database to declare the variable of this type.
Output:
Created the type ’emp_det’ as record type at the database level.
Example 2: Record Type at Subprogram level- Column level access
In this example, we are going to see how to create a record type at subprogram level and how to populate and fetch the values from it by column level.
We are going to create ’emp_det’ record_type at subprogram level, and we are going to use the same to populate and to display data from it.
Output:
Code Explanation:
- Code line 2-8: Record type ’emp_det’ is declared with columns emp_no, emp_name, salary and manager of data type NUMBER, VARCHAR2, NUMBER, NUMBER.
- Code line 9: guru99_emp_rec variable is declared as ’emp_det’ data type. Now this variable can hold the value that contains all the above 4 fields/columns.
- Code line 11: Populating the ’emp_no’ field of ‘guru99_emp_rec’ with value 1001.
- Code line 12: Populating the ’emp_name’ field of ‘guru99_emp_rec’ with value XXX.
- Code line 13: Populating the ‘manager’ field of ‘guru99_emp_rec’ with value 1000.
- Code line 14: Populating the ‘salary’ field of ‘guru99_emp_rec’ with value 10000.
- Code line 15-19: Displaying the value of the ‘guru99_emp_rec’ in output.
Example 3: Record Type at Subprogram level-Row level access
In this example, we are going to see how to create a record type at subprogram level and how to populate it as a row level. We are going to create ’emp_det’ record_type at subprogram level, and we are going to use the same to populate and to display data from it.
Code Explanation:
- Code line 2-8: Record type ’emp_det’ is declared with columns emp_no, emp_name, salary and manager of data type NUMBER, VARCHAR2, NUMBER, NUMBER.
- Code line 9: guru99_emp_rec variable is declared as ’emp_det’ data type. Now this variable can hold the value that contains all the above 4 fields/columns.
- Code line 11: Populating the table emp with data 1002 as emp_no, YYY as emp_name, 15000 as salary and 1000 as manager number.
- Code line 12: Committing the above insert transaction.
- Code line 13: Populating the ‘guru99_emp_rec’ variable as a row level data from the select query for employee number 1002.
- Code line 15-19: Displaying the value of the ‘guru99_emp_rec’ in output.
Output:
Note: The record type can be accessed only in column level while redirecting its value to any output mode.
Summary: in this tutorial, you will learn about the PL/SQL record and how to use records to manage your program data effectively.
Читайте также: