テクノロジー：PL/SQL

レコードの操作

Steven Feuerstein著

PL/SQLの理解と利用に関するシリーズ記事のパート7

PL/SQL Challengeの正解

前号の"エラー管理"で出題されたPL/SQL Challengeの各質問の正解は次のとおりです。

正解1：選択肢(a)、(c)、(d)のすべてでORA-00001が発生します。このエラーは、PL/SQLコード内のDUP_VAL_ON_INDEXとしても知られています。

正解2：

plch_procプロシージャをエラーなしにコンパイルするには、例外セクションのWHEN句にある"AND"を"OR"に変更します。1つのセッション内で発生させられる例外は1つだけです。そのため、2つ（またはそれ以上）の例外を1つのハンドラでチェックできても意味がありません。

これらの正解の詳しい説明については、 plsqlchallenge.com にアクセスして登録またはログインし、Play a Quizの「Closed/Taken」タブをクリックしてください。

Oracle PL/SQL言語は、SQL言語と緊密に統合された、移植性のある高パフォーマンスのトランザクション処理言語として設計されました。確かに、データベース内の表からの読取りや表への変更を行わないPL/SQLプログラムは、ほとんど見当たりません。表はデータ行により構成され、各データ行は1つ以上の列により構成されます。そのため、PL/SQLプログラム内でそれらのデータ行をできる限り簡単に操作できることは理にかなっています。そして、PL/SQLではまさに、レコードの実装を通じてそれを実現しています。

レコードは、コンポジット・データ型の1つです。そのため、数値や文字列などのスカラ・データ型とは異なり、複数の情報を保持できます。操作するデータが単一値だけである状況はほとんどないため、レコードなどのコンポジット・データ型は、通常はPL/SQLプログラム内で頻繁に使用されます。

この記事では、レコードの宣言、表の行のレコードへの移入、レコードを使用した表内の行全体の挿入や変更に至るまで、それぞれの方法を詳しく説明します。また、ユーザー定義レコード型についても見ていきます。このレコード型を使用すれば、かならずしもリレーショナル表に関連しないレコードを操作できます。

%ROWTYPEによるレコードの宣言

PL/SQLでは、表、ビュー、またはカーソルの結果セットと同じ構造のレコードを、%ROWTYPE属性を使用して非常に簡単に宣言できます。

たとえば、アプリケーションで次のような従業員表を使用するとします。


  
SQL> DESCRIBE omag_employees
 Name         Null?      Type
 ———————————  —————————— —————————————————
 EMPLOYEE_ID  NOT NULL   NUMBER(38)
 LAST_NAME               VARCHAR2(100)
 SALARY                  NUMBER

この表の各行は3つの列により構成され、各列に独自のデータ型が定義されています。次の問合せにより、この表のすべての行のすべての列を取得します。


  
SELECT employee_id, last_name, salary
  FROM omag_employees

次に、omag_employeesからある従業員IDのデータを1行取得し、その行の列の値を操作するコード・ブロックを記述することにします。次のように各列の変数を宣言してフェッチし、それぞれの変数に格納することも可能ではあります。


  
CREATE PROCEDURE process_employee (
  employee_id_in IN 
    omag_employees.employee_id%TYPE)
IS
  l_employee_id   
     omag_employees.employee_id%TYPE;
  l_last_name     
     omag_employees.last_name%TYPE;
  l_salary        
     omag_employees.salary%TYPE;
BEGIN
  SELECT employee_id,
         last_name,
         salary
    INTO l_employee_id,
         l_last_name,
         l_salary
    FROM omag_employees
  WHERE employee_id = employee_id_in;
END;

（パラメータに、モードを示す接尾辞を使用しています。ここでの_inは、INパラメータであることを示します。）

しかし、これでは、記述するのも読み取るのも保守するのも難しい大量のコードができてしまいます。もっと良いアプローチは、そのデータ行をフェッチしてレコードに格納することです。そのようなレコードを宣言するもっとも良い方法は次のとおりです。


  
CREATE PROCEDURE process_employee (
  employee_id_in IN 
     omag_employees.employee_id%TYPE)
IS
  l_employee   omag_employees%ROWTYPE;
BEGIN
  SELECT employee_id,
         last_name,
         salary
    INTO l_employee
    FROM omag_employees
  WHERE employee_id = employee_id_in;
END;

このプロシージャをコンパイルすると、PL/SQLによりomag_employees表の構造が検索され、表の各列に対応するフィールドを持つレコードが、同じ名前とデータ型で定義されます。%ROWTYPEを使用してレコードを宣言することで、このプロシージャがomag_employees表に依存するということもOracle Databaseに指示しています。データベース管理者が、たとえばlast_name列の最大長を200に変更した場合、このプロシージャのステータスはINVALIDに変更されます。このプロシージャを再コンパイルすると、コンパイラによってこのプロシージャのレコードの定義が更新され、表の最新の構造に一致するようになります。

さらに短縮して、次のように記述することもできます。


  
CREATE PROCEDURE process_employee (
  employee_id_in IN 
     omag_employees.employee_id%TYPE)
IS
  l_employee   omag_employees%ROWTYPE;
BEGIN
  SELECT *
    INTO l_employee
    FROM omag_employees
   WHERE employee_id = employee_id_in;
END;

このSELECT *構文は、表のすべての列をフェッチするようにOracle Databaseに指示しています。

また、%ROWTYPEを使用して、カーソル内のSELECT文と同じ構造のレコードを宣言することもできます。これは特に、1つの表または複数の表の列のサブセットをフェッチする場合に便利です。次に例を示します。


  
DECLARE
   CURSOR no_ids_cur
   IS
      SELECT last_name, salary
        FROM omag_employees;

   l_employee   no_ids_cur%ROWTYPE;

（私は通常、明示的に宣言したカーソルの名前には"_cur"接尾辞を追加するようにしてします。）

カーソルからデータをフェッチしてPL/SQL変数に格納するときには常に、そのカーソルに基づいたレコードを%ROWTYPEで宣言して、そのレコードへとフェッチすることをお勧めします。この方法により、カーソルのSELECT構文のリストが変更された場合に、レコード内のフィールドの数と型がそれに応じて変更され、同期状態が保たれます。

レコード変数の操作

ブロック内でレコードを宣言した後は、そのレコードの値を読み取ることも、値を変更することもできます。この操作はレコードレベルで実行することも、そのレコードの個別のフィールドを参照して実行することもできます。個別のフィールドを参照するには、SQLでも使用されるドット表記法により表の列を示します。

次のようなレコードを宣言したとします。


  
DECLARE
   l_employee omag_employees%ROWTYPE;

この場合、ブロックの実行可能セクションで次のように記述して、l_employeeのlast_nameフィールドの値を表示できます。


  
DBMS_OUTPUT.put_line (
   l_employee.last_name);

また、代入演算子を使用して、フィールドの値を変更できます。


  
l_employee.last_name := 'Picasso';

さらに、次のレコードレベルの操作を実行することもできます。 

  • レコードをNULLに設定します。この単純な代入文により、すべてのフィールドの値がNULLに設定されます。 

    
  
l_employee := NULL;

  • あるレコードに別のレコードを代入します。 

    
  
DECLARE
   l_employee1 omag_employees%ROWTYPE;
   l_employee2 omag_employees%ROWTYPE;
BEGIN
   l_employee1 := l_employee2;
END;

レコードへのデータの移入

ほとんどのレコード操作で、表の行をレコードに代入します。しかし、PL/SQLの代入演算子（:=）を使用すると、個々のフィールドに対して、またはレコード全体に対して、値を直接代入することもできます。レコードの移入方法について、例を見ていきましょう。

  • omag_employees表と同じ構造のレコードを宣言し、その表の特定行のデータを使用して、そのレコードにデータを入力します。 

    
  
DECLARE
  l_employee  omag_employees%ROWTYPE;
BEGIN
  SELECT *
    INTO l_employee
    FROM omag_employees
   WHERE employee_id = 100;
END;

  • すべての従業員の姓（last name）と給与（salary）をフェッチするカーソルを宣言します。次に、%ROWTYPEを使用して、l_employee.last_nameとl_employee.salaryという2つのフィールドを含むレコードを宣言します。最後に、カーソルをオープンし、1つの行をフェッチしてそのレコードに格納し、カーソルをクローズします。

    
  
DECLARE
  CURSOR no_ids_cur
  IS
     SELECT last_name, salary
       FROM omag_employees;

  l_employee   no_ids_cur%ROWTYPE;
BEGIN
  OPEN no_ids_cur;
  FETCH no_ids_cur INTO l_employee;
  CLOSE no_ids_cur;
END;
/

  • ネイティブ動的SQLを使用してレコードを移入します（注：このSELECT文は動的ではありません。この文は、EXECUTE IMMEDIATE . . . INTO文を使用してレコードを移入できることを示すために使用しています）。 

    
  
DECLARE
  l_employee  omag_employees%ROWTYPE;
BEGIN
  EXECUTE IMMEDIATE
     'SELECT * FROM omag_employees'
     INTO l_employee;
END;

  • 代入文を使用してレコードのフィールドを移入します。 

    
  
DECLARE
  l_employee  omag_employees%ROWTYPE;
BEGIN
  l_employee.last_name := 'Renoir';
  l_employee.salary := 1500;
END;

    表に基づいてレコードを定義している場合でも、レコードのフィールドの値を表から設定する必要はありません。たとえば、レコードを使用して従業員表に新規の行を挿入できます（詳しくは"レコードを使用した挿入と更新"を参照）。

  • あるレコードに別のレコードを代入します。Oracle Databaseは、レコードにNULLを代入する場合を含めて、レコードレベルの代入をサポートしています。 

    
  
DECLARE
  l_old_employee  omag_employees%ROWTYPE;
  l_new_employee  omag_employees%ROWTYPE;
BEGIN
  l_new_employee := l_old_employee;
  l_old_employee := NULL;
END;

カーソルによるFORループと暗黙的に宣言されたレコード

すべての従業員の姓を表示するプログラムを記述するとします。PL/SQLでの明快で簡単な方法は、カーソルによるFORループを利用することです（カーソルによるFORループについては、このPL/SQLの基礎に関するシリーズのパート2で説明しました）。カーソルによるFORループは、次に示す数値によるFORループの変化形です。


  
FOR index IN low_value .. high_value
LOOP
   loop_body_statements
END LOOP;

このインデックスは、Oracle Databaseによって整数値として暗黙的に宣言され、このループの本体内でのみ参照できます。

カーソルによるFORループも似たような構造ですが、数値範囲が問合せに置き換わります。


  
FOR index IN ( SELECT_statement )
LOOP
   loop_body_statements
END LOOP;

Oracle Databaseにより、このループのインデックスも暗黙的に宣言されます。しかし、カーソルによるFORループの場合は、ループのヘッダー部で問合せに対して%ROWTYPEが使用され、インデックスがレコードとして宣言されます。

次のブロックでは、カーソルによるFORループを使用して各従業員の姓のみをフェッチし、その名前をレコードに格納して、そのレコードのlast_nameフィールドの値を表示します。


  
BEGIN
   FOR employee_rec
      IN (SELECT last_name
            FROM omag_employees
           ORDER BY last_name)
   LOOP
      DBMS_OUTPUT.put_line (
         employee_rec.last_name);
   END LOOP;
END;
/

レコードをパラメータとして渡す

パラメータは、レコードの型に基づいて定義できます。そのため、レコードをサブプログラムに引数として渡すことができます。たとえば、従業員を表示するプロシージャを記述する必要があるとします。このプロシージャは次のように実装できます。


  
CREATE PROCEDURE show_employee (
  employee_id_in   IN 
    omag_employees.employee_id%TYPE,
  last_name_in     IN 
    omag_employees.last_name%TYPE,
  salary_in        IN 
    omag_employees.salary%TYPE)
IS
BEGIN
  DBMS_OUTPUT.put_line (
        employee_id_in
     || '-'
     || last_name_in
     || '-'
     || salary_in);
END;

しかし、個々のパラメータをそれぞれ宣言することを避けるため（100列の表を扱うことを想像してみてください）、次のようにレコードを渡します。


  
CREATE PROCEDURE show_employee (
   employee_in IN 
      omag_employees%ROWTYPE)
IS
BEGIN
   DBMS_OUTPUT.put_line (
         employee_in.employee_id
      || '-'
      || employee_in.last_name
      || '-'
      || employee_in.salary);
END;
/

言うまでもなく、新しい列が表に追加されたときに、このプロシージャでは新しい列のデータは自動的に表示されません。そのため、%ROWTYPEを使用して引数をサブプログラムに渡している場合は、表の変更後にサブプログラムのロジックをかならず見直してください。

レコードを使用した挿入と更新

これまで見てきたように、PL/SQLでは表の行からレコードへ非常に簡単に移入できます。しかし、レコードを使用して表の行のデータを変更する場合はどうでしょうか。PL/SQLには、INSERT文とUPDATE文の両方で、レコードを簡単に使用してこれらのデータ操作言語（DML）も実行できる特別な構文があります。

INSERT文で非常によく使用される形式は次のとおりです。


  
INSERT INTO table_name (culumn_list)   
		VALUES (expression_list)

culumn_listは、挿入時に移入される列のリストを示します。また、expression_listは、それぞれの列に代入する式のリストを示します。

たとえば、500列ある表のそれぞれの列に値を設定するとすれば、そのようなコードの記述や管理は非常に面倒です。レコードを使用した挿入は、そのようなシナリオで非常に役に立ちます。

コード・リスト1：各列を指定した単一行の挿入


  
DECLARE
   l_employee_id   omag_employees.employee_id%TYPE
      := 500;
   l_last_name     omag_employees.last_name%TYPE
      := 'Mondrian';
   l_salary        omag_employees.salary%TYPE
      := 2000;
BEGIN
   INSERT
     INTO omag_employees (employee_id,
                          last_name,
                          salary)
   VALUES (
             l_employee_id,
             l_last_name,
             l_salary);
END;

レコードレベルの挿入を実行するには、単純にVALUES句に丸括弧なしのレコードを記述します。リスト1は、それぞれの列を個々に指定した単一行をomag_employees表に挿入する操作の例を示しています。次のコードは、レコードを使用してこれと同じ挿入操作を実行します。


  
DECLARE
   l_employee   omag_employees%ROWTYPE;
BEGIN
   l_employee.employee_id := 500;
   l_employee.last_name := ‘Mondrian’;
   l_employee.salary := 2000;

   INSERT
     INTO omag_employees 
   VALUES l_employee;
END;
/

無限のように感じられる変数リストをINSERT文のVALUES句に記述している場合には、代わりにレコードを使用してみてください。

次に更新についてです。SET ROWを使用して、レコードの特定の行にあるすべての列を更新します。


  
DECLARE
  l_employee  omag_employees%ROWTYPE;
BEGIN
  l_employee.employee_id := 500;
  l_employee.last_name := 'Mondrian';
  l_employee.salary := 2000;

  UPDATE omag_employees
     SET ROW = l_employee
   WHERE employee_id = 100;
END;

このUPDATEでは、主キーを含む表のすべての列の値が設定されます。そのため、SET ROW構文は慎重に使用してください。

ユーザー定義レコード型

前項までに、%ROWTYPE属性を使用した、表またはカーソルに基づくレコード変数の宣言方法を見てきました。一方、TYPE. . . RECORD文を使用すれば、独自のユーザー定義レコード型を宣言できます。

ユーザー定義レコード型は、次のように個々の変数の"セット"を宣言するような場合に便利です。


  
DECLARE
   l_name1           VARCHAR2 (100);
   l_total_sales1    NUMBER;
   l_deliver_pref1   VARCHAR2 (10);
   --
   l_name2           VARCHAR2 (100);
   l_total_sales2    NUMBER;
   l_deliver_pref2   VARCHAR2 (10);

この代わりに、独自のレコード型を作成して2つのレコードを宣言してみましょう。


  
DECLARE
   TYPE customer_info_rt IS RECORD
   (
      name           VARCHAR2 (100),
      total_sales    NUMBER,
      deliver_pref   VARCHAR2 (10)
   );

   l_customer1   customer_info_rt;
   l_customer2   customer_info_rt;

（私は通常、型を宣言するときに、ルートの"t"接尾辞を使用し、さらに"型の型"を追加します。ここでは、レコード型（record type）の意味で"_rt"を追加しました。）

このアプローチには、文の記述の繰り返しを避ける以上の利点があります。これらの3つの情報のすべてが顧客（customer）に関連するということも記述しているのです。さらに、"一段上に立って"レコードを使用すると、そのレコードを引数として渡すことや、レコードレベルの操作を実行することが可能になります。そうすることで、要件の実装に必要となるコード量がさらに削減されます。

次のステップ 

 ダウンロード Oracle Database 11g

 テスト  PL/SQLの知識

 その他の記事 PL/SQLの基礎、パート1～6  

TYPE . . .

RECORD文を使用して独自のレコード型を作成すべき状況は他にもあります。それは、レコード内に、BOulEANなどのPL/SQL固有の型を使用する必要のあるフィールドがある場合です。%ROWTYPEを使用する場合は、すべてのフィールドのデータ型がSQLの型に制限されます。

2つのBOulEANフィールドを含むレコード型の例を次に示します。


  
DECLARE
  TYPE user_preferences_rt IS RECORD
  (
     show_full_name   BOOLEAN,
     autologin        BOOLEAN
  );

  l_user   user_preferences_rt;

レコードはそれ自体、PL/SQL固有のデータ型です。そのため、ユーザー定義レコード型のもう1つの優れた機能として、レコード型を別のレコード型のフィールドとして定義できます。次の宣言セクションでは、電話番号を構成する異なる数値要素を保持する1つのレコード型を作成しています。その後、ある営業担当者の複数の電話番号を保持する別のレコードを作成しています。


  
DECLARE
   TYPE phone_rt IS RECORD
   (
      area_code    PLS_INTEGER,
      exchange     PLS_INTEGER,
      phn_number   PLS_INTEGER,
      extension    PLS_INTEGER
   );

   TYPE contact_rt IS RECORD
   (
      day_phone#    phone_rt,
      eve_phone#    phone_rt,
      cell_phone#   phone_rt
   );

   l_sales_rep contact_rt;

問題を解決するコンポジット・データ型

PL/SQLは、複数あるコンポジット・データ型の1つであるレコードをサポートしているため、単純かつ明快で保守しやすいコードを記述できます。大量の変数やパラメータを操作する代わりに、それらの情報のすべてが格納された1つのレコードを操作できます。ユーザー定義レコードは、独自のコンポジット・データ型を構成する柔軟性を実現し、リレーショナル表では表現できないようなプログラム固有の要件を反映させることができます。

このPL/SQLの基礎に関するシリーズの次回の記事では、もう1つの重要なコンポジット・データ型であるコレクションについて説明します。コレクションは、PL/SQLにおける配列に似た構造の実装であり、パフォーマンス関連のもっとも重要なPL/SQL機能（FORALL、BULK CulLECTなど）の一部で使用されます。

データベース・トリガーでの疑似レコード

表に定義された行レベル・トリガーでは、NEWおよびulDという擬似レコードを参照できます（これらのデフォルトの名前は、トリガーのREFERENCING句を使用してオーバーライドできます）。擬似レコードと呼ばれる理由は、構造的には、%ROWTYPEを使用する表に定義されたレコードに似ていることです。しかし、その使用法が制限されています。

NEWとulDの両方の擬似レコードに、トリガーが定義された表のすべての列に対応するフィールドが含まれます。INSERT文またはUPDATE文を実行すると、NEW擬似レコードのフィールドには列の"ポスト"値（INSERTまたはUPDATEの実行後の値）が含まれます。

DELETE文またはUPDATE文を実行すると、ulD擬似レコードのフィールドには列の"プレ"値（文の実行前の行の状態）が含まれます。

擬似レコードは、ビジネス・ルールの検証、列の値が変更されたかどうかの判定などに使用できます。次のトリガーでは、昇給を凍結します。この不況時には誰にも昇給を許可しません。


  
CREATE OR REPLACE TRIGGER 
  omag_employees_freeze_trg
  BEFORE INSERT
  ON omag_employees
  FOR EACH ROW
DECLARE
BEGIN
  IF :NEW.salary > :OLD.salary
  THEN
     RAISE_APPLICATION_ERROR (
       -20000,
       'Salary freeze in effect:'||
       ' no increases allowed!');
   END IF;
END omag_employees_freeze_trg;

ただし、擬似レコードに適用されないレコード機能があります。たとえば、サブプログラムのパラメータがtablename%ROWTYPE（tablenameはトリガーを起動する表の名前）と定義されている場合でも、擬似レコードをそのサブプログラムに引数として渡すことはできません。

クイズにチャレンジ

PL/SQLの基礎に関するそれぞれの記事では、記事の中で説明した情報の知識をテストするクイズを毎回出題しています。このクイズは以下の他、PL/SQL Challenge（ plsqlchallenge.com ）にも掲載されます。PL/SQL Challengeは、PL/SQL言語に関するオンライン・クイズを提供するWebサイトです。このOracle Magazineの記事でクイズを読んで回答した場合、正解について次のパートで確認できます。一方、PL/SQL Challengeでクイズに挑戦した場合は、O’Reilly Media（oreilly.com）の電子書籍が当たるチャンスに応募できます。

次の表を作成して移入します。


  
CREATE TABLE plch_parts
(
  partnum    INTEGER PRIMARY KEY,
  partname   VARCHAR2 (100) UNIQUE
)
/

BEGIN
  INSERT INTO plch_parts
      VALUES (100, 'Keyboard');

  COMMIT;
END;
/

質問

実行後に"Keyboard"と表示されるように次のブロックの/*DECLARE*/コメント部で使用できるコードを含む選択肢はどれですか。


  

DECLARE
   /*DECLARE*/
BEGIN
   SELECT *
     INTO l_part
     FROM plch_parts
    WHERE partnum = 100;

   DBMS_OUTPUT.put_line 
   (l_part.partname);
END;
/


 l_part   plch_parts%TYPE; 
                     _part   plch_parts;         
                     l_part   plch_parts%ROWTYPE;                
                   >CURSOR parts_cur  IS    
				   SELECT * FROM plch_parts;  
				   l_part   parts_cur%ROWTYPE;

Steven Feuerstein（steven.feuerstein@quest.com）は、Quest SoftwareのPL/SQLエヴァンジェリストです。これまで、Oracle PL/SQLに関する著書（O’Reilly Media）を10冊発行しており、Oracle ACE Directorでもあります。詳細は、stevenfeuerstein.comをご覧ください。

ご意見ご感想をお寄せください。