Артём Санников

Независимо от способа определения записи (на основе таблицы или курсора или с помощью явного определения 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;

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

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

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

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

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

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

Оператор присваивания := изменяет значение в конкретном поле записи. В первом примере обнуляется поле 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;

Метки: Oracle, Записи.