Использование записей (records) в PL/SQL

Records (записи) в языке программирования PL/SQLЗапись (record) представляет собой составную структуру данных; другими словами, запись состоит из нескольких полей, каждое из которых обладает собственным значением. Записи в программах PL/SQL очень похожи на строки в таблицах баз данных. Запись как целое не имеет собственного значения; однако значение имеет каждый ее компонент, или поле, а объединение их в единую запись позволяет хранить и обрабатывать все значения как одно целое. Записи сильно упрощают работу программиста, а переход от объявлений уровня полей к уровню записей повышает эффективность написания кода.



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

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

CREATE TABLE books (
   book_id         INTEGER,
   isbn            VARCHAR2(13),
   title           VARCHAR2(200),
   summary         VARCHAR2(2000),
   author          VARCHAR2(200),
   date_published  DATE,
   page_count      NUMBER
);

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

DECLARE
   my_book books%ROWTYPE;
BEGIN
   SELECT *
     INTO my_book
     FROM books
     WHERE title = 'Oracle PL/SQL Programming, 6th Edition';
   IF my_book.author LIKE '%Feuerstein%'
   THEN
     DBMS_OUTPUT.put_line ('Код ISBN: ' || my_book.isbn);
   END IF;
END;

Записи также могут определяться на базе ранее определенного типа. Допустим, в базе данных нас интересует только имя автора и название книги. Вместо того чтобы использовать %ROWTYPE для объявления записи, мы создаем тип записи: 

DECLARE
   TYPE author_title_rt IS RECORD (
     author books.author%TYPE
     ,title books.title%TYPE
   );
   l_book_info author_title_rt;
BEGIN
   SELECT author, title INTO l_book_info
   FROM books WHERE isbn = '978-1-449-32445-2';

Начнем с рассмотрения некоторых преимуществ использования записей. Далее будут более подробно представлены способы определения записей и примеры их использования в программах.

 

Преимущества использования записей

Структура данных записи представляет собой высокоуровневое средство адресации и обработки данных, определяемых в программах PL/SQL (в отличие от информации, хранящейся в таблицах баз данных). Представление данных в виде записи дает разработчикам определенные преимущества, о которых рассказывается в следующих разделах.

 

Абстракция данных

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

Чтобы создать запись, нужно выделить атрибуты или поля описываемого ею объекта, затем задать отношение между этими атрибутами и присвоить ему имя. Получившийся набор атрибутов, связанных определенным отношением, — это и есть запись.

 

Агрегатные операции

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

Предположим, что по роду своей деятельности менеджер часто взаимодействует с различными компаниями. Информация о каждой из них хранится в базе данных в виде отдельной записи. Содержимое полей этой записи может быть разным, но для менеджера это не имеет особого значения. Например, его не интересует, сколько строк занимает адрес компании, две или три. Главное, чтобы информацию о компании можно было изменять, удалять или анализировать. Таким образом, организация данных в виде записи позволяет обрабатывать сведения о конкретной компании как единое целое, скрывая отдельные реквизиты, если они не нужны, но в то же время обеспечивает доступ к каждому из этих реквизитов.

Такой подход позволяет рассматривать данные как наборы объектов, к которым применимы определенные правила.

 

Компактность и простота кода

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

Записи PL/SQL положительно влияют на качество кода как на этапе разработки, так и при дальнейшем сопровождении. Чтобы более полно использовать все возможности, предоставляемые этими замечательными структурами, старайтесь придерживаться следующих рекомендаций:

  • Создавайте записи, соответствующие курсорам. Создавая в программе курсор, тут же добавьте соответствующую запись (исключение составляет лишь курсор цикла FOR). Данные из курсора всегда извлекайте только в запись, но не в отдельные переменные. В тех немногих случаях, когда для этого потребуются дополнительные усилия, вы не пожалеете о строгом соблюдении этого принципа и оцените элегантность полученного кода. А начиная с Oracle9i Release 2, записи можно использовать даже в DML-инструкциях!
  • Создавайте записи на основе таблиц. Если в программе должны храниться данные, прочитанные из таблицы, создайте новую запись на базе таблицы (или воспользуйтесь заранее определенной записью). Такой подход позволяет объявить всего одну переменную вместо нескольких переменных. Что еще лучше, структура записи будет автоматически адаптироваться к изменениям в таблице при каждой компиляции.
  • Передавайте записи в качестве параметров. Вызываемым процедурам по возможности передавайте не отдельные переменные, а целые записи. Тем самым вы снижаете вероятность изменения синтаксиса вызова процедур, благодаря чему программный код становится более стабильным.

 

Объявление записей

В PL/SQL существует три способа объявления записей:

  • Запись на основе таблицы. Для объявления записи, каждое поле которой соответствует значению одноименного столбца таблицы, используется атрибут %ROWTYPE с именем таблицы. Пример объявления записи one_book, которая имеет ту же структуру, что и таблица books:
       DECLARE
         one_book books%ROWTYPE;
  • Запись на основе курсора. Для объявления записи на основе явно заданного курсора или курсора, представленного переменной, где каждое поле соответствует столбцу или именованному выражению в команде SELECT, предназначен атрибут %ROWTYPE. Вот пример объявления записи, которая имеет ту же структуру, что и явный курсор:
       DECLARE
         CURSOR my_books_cur IS
         SELECT * FROM books
         WHERE author LIKE '%FEUERSTEIN%';
         one_SF_book my_books_cur%ROWTYPE; 
  • Запись, определяемая программистом. Чтобы создать запись с нуля, то есть явно определить каждое поле, задавая его имя и тип данных, следует воспользоваться командой TYPE...RECORD. Значением поля записи, определяемой программистом, может быть не только скалярное значение, но и другая запись. В следующем примере определяется тип записи, содержащей информацию о писательской карьере автора книги, и объявляется экземпляр записи этого типа:
       DECLARE
          TYPE book_info_rt IS RECORD (
          author books.author%TYPE,
          category VARCHAR2(100),
          total_page_count POSITIVE);
          steven_as_author book_info_rt; 

Обратите внимание: в объявлении записи пользовательского типа атрибут %ROWTYPE не указывается. Запись в данном случае имеет тип book_info_rt.

Общий формат объявления атрибута %ROWTYPE таков: 

имя_записи [имя_схемы.]имя_объекта%ROWTYPE
[ DEFAULT|:= совместимая_запись ];

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

Пример создания записи на основе курсорной переменной: 

DECLARE
   TYPE book_rc IS REF CURSOR RETURN books%ROWTYPE;
   book_cv book_rc;
   one_book book_cv%ROWTYPE;
BEGIN
...

Запись также можно объявить и неявно — например, в цикле FOR с курсором. В следующем блоке кода раздел объявлений не содержит определения записи book_rec; PL/ SQL автоматически объявляет запись с атрибутом %ROWTYPE

BEGIN
   FOR book_rec IN (SELECT * FROM books)
   LOOP
      calculate_total_sales (book_rec);
   END LOOP;
END;

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

 

Записи, определяемые программистом

Записи, создаваемые на основе таблиц и курсоров, используются в тех случаях, когда в программе необходимо предусмотреть структуры для хранения определенных данных. Однако такие записи не охватывают всех потребностей программистов в составных типах данных. Допустим, программисту понадобится запись, структура которой не имеет ничего общего со структурой таблиц и представлений. Придется ли ему создавать фиктивный курсор только для того, чтобы получить запись нужной структуры? Конечно же, нет. В подобных ситуациях PL/SQL позволяет программисту самостоятельно определить структуру записи с помощью команды TYPE..RECORD.

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

  1. Объявить или определить тип записи, задав ее структуру с помощью команды TYPE.
  2. На основе этого типа объявляются реальные записи, которые затем можно будет использовать в программе.

Объявление типа записи

Тип записи определяется с помощью оператора TYPE..RECORD, в котором задается имя новой структуры и описания входящих в нее полей. Общий синтаксис этого оператора таков:

TYPE имя_типа IS RECORD
(имя_поля1 тип_данных1 [[NOT NULL]:=|DEFAULT значение_по_умолчанию],
имя_поля2 тип_данных2 [[NOT NULL]:=|DEFAULT значение_по_умолчанию],
...
имя_поляN тип_данныхN [[NOT NULL]:=|DEFAULT значение_по_умолчанию]
);

Здесь имя_поляN — имя N-го поля записи, а тип_данныхN — тип данных указанного поля. Поля записи могут относиться к любому из следующих типов:

  • жестко запрограммированный скалярный тип данных (VARCHAR2, NUMBER и т. д.);
  • подтип, определяемый программистом;
  • тип, устанавливаемый на основе типа уже определенной структуры данных, например объявление с привязкой посредством атрибута %TYPE или %ROWTYPE (в последнем случае мы создаем вложенную запись);
  • тип коллекции PL/SQL — поле записи может быть списком и даже коллекцией;
  • тип REF CURSOR (то есть поле содержит курсорную переменную).

Пример команды TYPE...RECORD:

TYPE company_rectype IS RECORD (
comp# company.company_id%TYPE
, list_of_names DBMS_SQL.VARCHAR2S
, dataset SYS_REFCURSOR
);

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

 

Объявление записи

Создавая собственные пользовательские типы записей, вы можете использовать их в объявлении конкретных записей:

имя_записи тип_записи; 

Здесь имя_записи — имя объявляемой записи, а тип_записи — имя типа, определенное в команде TYPE...RECORD. Например, чтобы создать запись с информацией о покупках клиента, сначала нужно определить ее тип:

PACKAGE customer_sales_pkg
IS
TYPE customer_sales_rectype IS RECORD
(customer_id customer.customer_id%TYPE,
customer_name customer.name%TYPE,
total_sales NUMBER (15,2)
);

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

DECLARE
prev_customer_sales_rec customer_sales_pkg.customer_sales_rectype;
top_customer_rec customer_sales_pkg.customer_sales_rectype;

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

Записи, созданные на основе этих типов, могут передаваться в аргументах процедур; для этого достаточно указать тип записи в качестве типа формального параметра: 

PROCEDURE analyze_cust_sales (
sales_rec_in IN customer_sales_pkg.customer_sales_rectype)

В определении типа записи для каждого поля наряду с его именем и типом можно задать значение по умолчанию. Для этой цели используется синтаксис DEFAULT или :=. Здесь имена полей внутри записи должны быть уникальными.

 

Примеры определений типов записей

Предположим, в программе объявлены подтип, курсор и ассоциативный массив:

SUBTYPE long_line_type IS VARCHAR2(2000);
CURSOR company_sales_cur IS
SELECT name, SUM (order_amount) total_sales
FROM company c, orders o
WHERE c.company_id = o.company_id;
TYPE employee_ids_tabletype IS
TABLE OF employees.employee_id%TYPE
INDEX BY BINARY_INTEGER; 

Затем в том же разделе объявлений определяются две записи.

  • Задаваемая программистом запись представляет подмножество столбцов таблицы company и таблицу PL/SQL. Атрибут %TYPE используется для привязки полей записи к столбцам таблицы, третье поле записи является ассоциативным массивом с идентификационными кодами сотрудников):
       TYPE company_rectype IS RECORD
           (company_id company.company_id%TYPE,
            company_name company.name%TYPE,
            new_hires_tab employee_ids_tabletype); 
  • Смешанная запись демонстрирует разные типы объявлений полей, в том числе ограничение NOT NULL, использование подтипа и атрибута %TYPE, значение по умолчанию, использование ассоциативного массива и вложенную запись:
       TYPE mishmash_rectype IS RECORD
         (emp_number NUMBER(10) NOT NULL := 0,
          paragraph_text long_line_type,
          company_nm company.name%TYPE,
          total_sales company_sales.total_sales%TYPE := 0,
          new_hires_tab employee_ids_tabletype,
          prefers_nonsmoking_fl BOOLEAN := FALSE,
          new_company_rec company_rectype
         ); 

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

 

Обработка записей

Независимо от способа определения записи (на основе таблицы или курсора или с помощью явного определения TYPE...RECORD), приемы работы с нею всегда одинаковы. С записью можно работать либо как с «единым целым», либо с каждым из ее полей по отдельности.

 

Операции над записями

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

  • копирование содержимого одной записи в другую (если они имеют совместимую структуру, то есть одинаковое количество полей одного или взаимопреобразуемых типов);
  • присваивание записи значения NULL (простым оператором присваивания);
  • передача записи в качестве аргумента;
  • возврат записи функцией (команда RETURN).

Однако некоторые операции на уровне записей пока не поддерживаются.

  • Если вы хотите проверить, содержат ли все поля записи значение NULL, использовать синтаксис IS NULL нельзя. Осуществить такую проверку можно лишь путем применения оператора IS NULL по отношению к каждому полю.
  • Невозможно сравнить две записи в одной операции. Например, нельзя узнать, равны или нет две записи (то есть значения всех их полей), или же узнать, какая из записей больше. К сожалению, для того чтобы ответить на эти вопросы, нужно сравнить каждую пару полей. Далее в этом блоге, в разделе «Сравнение записей» описывается утилита, автоматически генерирующая код для сравнения двух записей.
  • Только в Oracle9i Release 2 появилась возможность добавления новых записей в таблицу базы данных. В предыдущих версиях системы значение каждого поля приходилось записывать в соответствующий столбец таблицы отдельно.

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

CREATE TABLE cust_sales_roundup (
customer_id NUMBER (5),
customer_name VARCHAR2 (100),
total_sales NUMBER (15,2)
)

Три объявленные ниже записи имеют совместимую структуру и их можно «смешивать» в разных операциях:

DECLARE
cust_sales_roundup_rec cust_sales_roundup%ROWTYPE;
CURSOR cust_sales_cur IS SELECT * FROM cust_sales_roundup;
cust_sales_rec cust_sales_cur%ROWTYPE;TYPE customer_sales_rectype IS RECORD
(customer_id NUMBER(5),
customer_name customer.name%TYPE,
total_sales NUMBER(15,2)
);
preferred_cust_rec customer_sales_rectype;
BEGIN
-- Присвоить содержимое одной записи другой.
cust_sales_roundup_rec := cust_sales_rec;
preferred_cust_rec := cust_sales_rec;
END; 

Рассмотрим еще несколько примеров выполнения операций на уровне записей.

  • Запись можно инициализировать при объявлении, присвоив содержимое другой, совместимой с ней записи. В следующем фрагменте локальной переменной присваивается запись, переданная процедуре в аргументе IN. Теперь значения отдельных полей записи можно изменять:
        PROCEDURE compare_companies
           (prev_company_rec IN company%ROWTYPE)
        IS
           curr_company_rec company%ROWTYPE := prev_company_rec;
        BEGIN
           ...
        END; 
  • В следующем примере сначала создается новый тип записи и объявляется запись этого типа. Затем создается второй тип записи, для которого в качестве типа единственного столбца устанавливается первый тип. В определении данного типа столбец-запись инициализируется переменной-записью:
        DECLARE
           TYPE first_rectype IS RECORD (var1 VARCHAR2(100) := 'WHY NOT');
           first_rec first_rectype;
           TYPE second_rectype IS RECORD (nested_rec first_rectype := first_rec);
        BEGIN
            ...
        END;         

Разумеется, операцию присваивания можно осуществить и в разделе выполнения. Ниже объявляются две разные записи типа rain_forest_history, и информация из первой из них присваивается второй записи: 

DECLARE
prev_rain_forest_rec rain_forest_history%ROWTYPE;
curr_rain_forest_rec rain_forest_history%ROWTYPE;
BEGIN
... Инициализация записи prev_rain_forest_rec ...
-- Копирование данных первой записи во вторую
curr_rain_forest_rec := prev_rain_forest_rec;
  • В результате выполнения такой операции значение каждого поля первой записи присваивается соответствующему полю второй записи. Значения полям можно было бы присваивать и по отдельности, но согласитесь: так гораздо удобнее. Поэтому старайтесь обрабатывать записи как одно целое — это позволит сэкономить время, упростить программный код и облегчить его сопровождение.
  • Для перемещения данных строки таблицы в запись достаточно одной операции выборки. Примеры:
DECLARE
/*
|| Объявляем курсор, а затем при помощи атрибута %ROWTYPE
|| определяем запись на его основе
*/
CURSOR cust_sales_cur IS
SELECT customer_id, customer_name, SUM (total_sales) tot_sales
FROM cust_sales_roundup
WHERE sold_on < ADD_MONTHS (SYSDATE, -3)
GROUP BY customer_id, customer_name;
cust_sales_rec cust_sales_cur%ROWTYPE;
BEGIN
/* Перемещаем значения из курсора непосредственно в запись */
OPEN cust_sales_cur;
FETCH cust_sales_cur INTO cust_sales_rec;
CLOSE cust_sales_cur; 
  • В следующем блоке сначала задается тип записи, который соответствует данным, возвращаемым неявным курсором, а затем производится извлечение данных непосредственно в запись: 
DECLARE
   TYPE customer_sales_rectype IS RECORD
   (customer_id customer.customer_id%TYPE,
   customer_name customer.name%TYPE,
   total_sales NUMBER (15,2)
   );
   top_customer_rec customer_sales_rectype;
BEGIN
   /* Загрузка значений непосредственно в запись: */
   SELECT customer_id, customer_name, SUM (total_sales)
   INTO top_customer_rec
   FROM cust_sales_roundup
   WHERE sold_on < ADD_MONTHS (SYSDATE, -3)
   GROUP BY customer_id, customer_name;
  • В следующем примере одна операция присваивает всем полям записи значение NULL:
/* Файл в Сети: record_assign_null.sql */
FUNCTION dept_for_name (
   department_name_in IN departments.department_name%TYPE
   )
RETURN departments%ROWTYPE
IS
   l_return departments%ROWTYPE;
FUNCTION is_secret_department (
department_name_in IN departments.department_name%TYPE
)
RETURN BOOLEAN
IS
BEGIN
   RETURN CASE department_name_in
   WHEN 'VICE PRESIDENT' THEN TRUE
   ELSE FALSE
END;
END is_secret_department;
BEGIN
   SELECT *
   INTO l_return
   FROM departments
   WHERE department_name = department_name_in;
   IF is_secret_department (department_name_in)
   THEN
      l_return := NULL;
   END IF;
RETURN l_return;
END dept_for_name; 

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

 

Операции над отдельными полями

Чтобы получить доступ к отдельному полю записи (для выполнения операции чтения или изменения), используйте точечный синтаксис, как при идентификации столбца таблицы базы данных: 

[[имя_схемы.]имя_пакета.]имя_записи.имя_поля

Имя пакета необходимо указывать только в том случае, если запись определена в спецификации другого пакета (не того, с которым вы работаете в настоящее время). А имя схемы потребуется определить лишь при условии, что пакет принадлежит не той схеме, где компилируется данный код.

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

Оператор присваивания := изменяет значение в конкретном поле записи. В первом примере обнуляется поле total_sales. Во втором примере вызывается функция, которая возвращает значение флага output_generated (ему присваивается TRUE, FALSE или NULL): 

BEGIN
top_customer_rec.total_sales := 0;
report_rec.output_generated := check_report_status (report_rec.report_id);
END;

Код следующего примера создает запись на основе таблицы rain_forest_history, заполняет ее поля значениями и возвращает запись в ту же таблицу:

DECLARE
   rain_forest_rec rain_forest_history%ROWTYPE;
BEGIN
   /* Установка значений полей записи */
   rain_forest_rec.country_code := 1005;
   rain_forest_rec.analysis_date := ADD_MONTHS (TRUNC (SYSDATE), -3);
   rain_forest_rec.size_in_acres := 32;
   rain_forest_rec.species_lost := 425;
   /* Вставка в таблицу строки значений из записи */
   INSERT INTO rain_forest_history
      (country_code, analysis_date, size_in_acres, species_lost)
   VALUES
     (rain_forest_rec.country_code,
     rain_forest_rec.analysis_date,
     rain_forest_rec.size_in_acres,
     rain_forest_rec.species_lost);
...
END; 

Обратите внимание, что полю analysis_date можно присвоить любое допустимое выражение типа DATE. Сказанное относится и к другим полям, а также более сложным структурам.

Начиная с Oracle9i Release 2, появилась возможность выполнения вставки уровня записей, в результате чего приведенная инструкция INSERT упрощается до следующего вида:

INSERT INTO rain_forest_history
   (country_code, analysis_date, size_in_acres, species_lost)
VALUES rain_forest_rec;

 

Операции уровня полей с вложенными записями

Предположим, у нас имеется вложенная структура записей, то есть одно из полей «внешней» записи в действительности является другой записью. В следующем примере сначала определяется тип записи для хранения всех элементов телефонного номера (phone_rectype), а затем — тип записи, в которой объединяются в единую структуру contact_set_rectype несколько телефонных номеров одного человека: 

DECLARE
   TYPE phone_rectype IS RECORD
     (intl_prefix VARCHAR2(2),
     area_code VARCHAR2(3),
     exchange VARCHAR2(3),
     phn_number VARCHAR2(4),
     extension VARCHAR2(4)
   );
   -- Каждое поле представляет собой вложенную запись
   TYPE contact_set_rectype IS RECORD
      (day_phone# phone_rectype,
      eve_phone# phone_rectype,
      fax_phone# phone_rectype,
      home_phone# phone_rectype,
      cell_phone# phone_rectype
   );
   auth_rep_info_rec contact_set_rectype;
BEGIN

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

auth_rep_info_rec.fax_phone#.area_code :=
auth_rep_info_rec.home_phone#.area_code;

 

Операции уровня полей с записями на базе пакетов

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

CREATE OR REPLACE PACKAGE summer
IS
   TYPE reading_list_rt IS RECORD (
   favorite_author VARCHAR2 (100),
   title VARCHAR2 (100),
   finish_by DATE);
   must_read reading_list_rt;
   wifes_favorite reading_list_rt;
END summer;
CREATE OR REPLACE PACKAGE BODY summer
IS
BEGIN -- Раздел инициализации пакета
   must_read.favorite_author := 'Tepper, Sheri S.';
   must_read.title := 'Gate to Women''s Country';
END summer;

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

DECLARE
   first_book summer.reading_list_rt;
   second_book summer.reading_list_rt;
BEGIN
   summer.must_read.finish_by := TO_DATE ('01-AUG-2009', 'DD-MON-YYYY');
   first_book := summer.must_read;
   second_book.favorite_author := 'Hobb, Robin';
   second_book.title := 'Assassin''s Apprentice';
   second_book.finish_by := TO_DATE ('01-SEP-2009', 'DD-MON-YYYY');
END; 

Мы объявляем две записи для представления информации о книге. Сначала устанавливается значение поля finish_by объявленной в пакете summer записи must_read (обратите внимание на синтаксис пакет.запись.поле), затем запись присваивается переменной, представляющей первую книгу из числа запланированных для чтения. После этого значения присваиваются отдельным полям записи, относящейся ко второй книге.

При работе со встроенным пакетом UTL_FILE, предназначенным для выполнения файлового ввода/вывода в PL/SQL, необходимо следовать тем же правилам обработки записей. Объявление типа данных UTL_FILE.FILE_TYPE фактически является определением типа записи. Таким образом, при объявлении дескриптора файла вы на самом деле объявляете запись типа, определяемого в пакете: 

DECLARE
   my_file_id UTL_FILE.FILE_TYPE;

 

Сравнение записей

Как узнать, равны ли две записи, то есть совпадают ли их соответствующие поля? Было бы неплохо, если бы PL/SQL позволял выполнять непосредственное сравнение. Например: 

DECLARE
   first_book summer.reading_list_rt := summer.must_read;
   second_book summer.reading_list_rt := summer.wifes_favorite;
BEGIN
   IF first_book = second_book /* НЕ ПОДДЕРЖИВАЕТСЯ! */
   THEN
      lots_to_talk_about;
   END IF;
END;

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

DECLARE
   first_book summer.reading_list_rt := summer.must_read;
   second_book summer.reading_list_rt := summer.wifes_favorite;
BEGIN
   IF first_book.favorite_author = second_book.favorite_author
   AND first_book.title = second_book.title
   AND first_book.finish_by = second_book.finish_by
   THEN
      lots_to_talk_about;
   END IF;
END;

Однако здесь возникает одна проблема. Если вы хотите указать, что две записи со значениями NULL должны считаться равными, оператор сравнения необходимо изменить следующим образом:

(first_book.favorite_author = second_book.favorite_author
OR( first_book.favorite_author IS NULL AND
second_book.favorite_author IS NULL)

Как видите, код получается довольно громоздким. Правда, хорошо было бы генерировать его автоматически? Оказывается, это и в самом деле возможно — по крайней мере для тех записей, которые определены с атрибутом %ROWTYPE на основе таблиц или представлений. В таком случае имена полей можно получить из представления ALL_TAB_COLUMNS в словаре данных, а затем вывести сгенерированный код на экран или в файл.

К счастью, вам не придется самостоятельно разбираться во всех тонкостях. Готовый генератор кода, разработанный Дэном Спенсером (Dan Spencer), можно найти на сайте в файле gen_record_comparison.pkg.

 

Триггерные псевдозаписи

При программировании триггеров для конкретной таблицы Oracle предоставляет в ваше распоряжение две структуры, OLD и NEW, которые являются псевдозаписями. По формату эти структуры сходны с записями на базе таблиц, объявленными с атрибутом %ROWTYPE: они также содержат поле для каждого столбца таблицы:

  • OLD — псевдозапись содержит значения всех столбцов таблицы до начала текущей транзакции.
  • NEW — псевдозапись содержит новые значения всех столбцов, которые будут помещены в таблицу при завершении текущей транзакции.

При использовании ссылок на OLD и NEW в теле триггера перед идентификаторами ставится двоеточие, а в условии WHEN оно не используется. Пример: 

TRIGGER check_raise
   AFTER UPDATE OF salary
   ON employee
   FOR EACH ROW
   WHEN (OLD.salary != NEW.salary) OR
     (OLD.salary IS NULL AND NEW.salary IS NOT NULL) OR
     (OLD.salary IS NOT NULL AND NEW.salary IS NULL)
BEGIN
   IF :NEW.salary > 100000 THEN ...
%ROWTYPE и невидимые столбцы (Oracle Database 12c)

В версии 12.1 появилась возможность определения невидимых (INVISIBLE) столбцов в реляционных таблицах. Если вы захотите вывести или присвоить значение невидимого столбца, его имя необходимо задать явно. Пример определения невидимого столбца в таблице: 

CREATE TABLE my_table (i INTEGER, d DATE, t TIMESTAMP INVISIBLE)

Чтобы сделать невидимый столбец видимым, выполните соответствующую команду ALTER TABLE:

ALTER TABLE my_table MODIFY t VISIBLE 

Синтаксис SELECT * не отображает столбцы INVISIBLE. Но если включить столбец INVISIBLE в список выборки команды SELECT, столбец будет выводиться. Значение столбца INVISIBLE нельзя явно задать в условии VALUES команды INSERT; такие столбцы должны явно задаваться в атрибутах %ROWTYPE.

Например, при попытке откомпилировать следующий блок выдается ошибка PLS-00302: component‘T’ must be declared:

DECLARE
   /* Запись с двумя полями: i и d */
   l_data my_table%ROWTYPE;
BEGIN
   SELECT * INTO l_data FROM my_table;
      DBMS_OUTPUT.PUT_LINE ('t = ' || l_data.t);
END;
/

Так как столбец T является невидимым, запись, объявленная с %ROWTYPE, содержит только два поля с именами I и D. Мы не можем обратиться к полю с именем T. Но если сделать столбец видимым, Oracle создаст для него поле в записи, объявленной с использованием %ROWTYPE. Это также означает, что после того, как невидимый столбец станет видимым, Oracle изменит статус всех программных модулей, объявляющих записи с использованием синтаксиса %ROWTYPE для этой таблицы, на недействительный (INVALID).

 

Вас заинтересует / Intresting for you:

Встроенные методы коллекций PL...
Встроенные методы коллекций PL... 6234 просмотров sepia Tue, 29 Oct 2019, 09:54:01
Управление приложениями PL/SQL...
Управление приложениями PL/SQL... 2839 просмотров Rasen Fasenger Thu, 16 Jul 2020, 06:20:48
Тип данных RAW в PL/SQL
Тип данных RAW в PL/SQL 5130 просмотров Doctor Thu, 12 Jul 2018, 08:41:33
Символьные функции и аргументы...
Символьные функции и аргументы... 9900 просмотров Анатолий Wed, 23 May 2018, 18:54:01

Войдите чтобы комментировать