ヘッダーをスキップ

Oracle Database PL/SQLユーザーズ・ガイドおよびリファレンス
10g リリース2(10.2)

B19257-01
目次
目次
索引
索引

戻る 次へ

12 PL/SQLのオブジェクト型の使用

オブジェクト指向プログラミングは、再利用可能なコンポーネントおよび複雑なアプリケーションを作成する場合に特に適しています。PL/SQLのオブジェクト指向のプログラミングは、オブジェクト型をベースにしています。オブジェクト型を使用することによって、実社会のオブジェクトをモデル化し、インタフェースと実装の細部を分離して、オブジェクト指向のデータをデータベースへ永続的に格納することが可能になります。

この章の項目は、次のとおりです。

オブジェクト型の詳細は、『Oracle Databaseアプリケーション開発者ガイド-オブジェクト・リレーショナル機能』を参照してください。

PL/SQLでのオブジェクトの宣言と初期化

オブジェクト型は、任意の実社会のエンティティを表すことができます。たとえば、オブジェクト型によって、学生、銀行口座、コンピュータ・スクリーン、有理数、またはデータ構造体(待ち行列、スタック、リストなど)を表すことができます。

現在のところ、PL/SQLのブロック、サブプログラムまたはパッケージ内ではオブジェクト型を定義できません。SQL*Plusでは、SQL文CREATE TYPEを使用して対話形式で定義できます。例1-17「オブジェクト型の定義」を参照してください。

SQLのCREATE TYPE 文の詳細は、『Oracle Database SQLリファレンス』を参照してください。SQLのCREATE TYPE BODY文の詳細は、『Oracle Database SQLリファレンス』を参照してください。

オブジェクト型を宣言してスキーマにインストールすると、任意のPL/SQLブロック、サブプログラムまたはパッケージの中で、それを使用してオブジェクトを宣言できます。たとえば、そのオブジェクト型を使用して属性、列、変数、バインド変数、レコード・フィールド、表要素、仮パラメータまたはファンクション結果のデータ型を指定できます。実行時には、そのオブジェクト型のインスタンスが作成されます。つまり、その型のオブジェクトがインスタンス化されます。オブジェクトごとに異なる値を保持できます。

そのようなオブジェクトは、通常の有効範囲規則とインスタンス化のルールに従います。ブロックまたはサブプログラムでは、ローカル・オブジェクトは、ブロックまたはサブプログラムに入ったときにインスタンス化され、ブロックまたはサブプログラムが終了した時点で消滅します。パッケージでは、そのパッケージが初めて参照された時点でオブジェクトのインスタンスが生成され、データベース・セッションが終わった時点で消滅します。

例12-1に、オブジェクト型、オブジェクト型本体およびオブジェクト型表を作成する方法を示します。

例12-1    オブジェクト型の使用

CREATE TYPE address_typ AS OBJECT ( 
   street          VARCHAR2(30),
   city            VARCHAR2(20),
   state           CHAR(2),
   postal_code     VARCHAR2(6) );
/
CREATE TYPE employee_typ AS OBJECT (
  employee_id       NUMBER(6),
  first_name        VARCHAR2(20),
  last_name         VARCHAR2(25),
  email             VARCHAR2(25),
  phone_number      VARCHAR2(20),
  hire_date         DATE,
  job_id            VARCHAR2(10),
  salary            NUMBER(8,2),
  commission_pct    NUMBER(2,2),
  manager_id        NUMBER(6),
  department_id     NUMBER(4),
  address           address_typ,
  MAP MEMBER FUNCTION get_idno RETURN NUMBER,
  MEMBER PROCEDURE display_address ( SELF IN OUT NOCOPY employee_typ ) );
/
CREATE TYPE BODY employee_typ AS
  MAP MEMBER FUNCTION get_idno RETURN NUMBER IS
  BEGIN
    RETURN employee_id;
  END;
  MEMBER PROCEDURE display_address ( SELF IN OUT NOCOPY employee_typ ) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE(first_name || ' '  || last_name);
    DBMS_OUTPUT.PUT_LINE(address.street);
    DBMS_OUTPUT.PUT_LINE(address.city || ', '  || address.state || ' ' ||
                         address.postal_code);   
  END;
END;
/
CREATE TABLE employee_tab OF employee_typ;

PL/SQLブロックでのオブジェクトの宣言

CHARNUMBERなどの組込み型を使用できるところであれば、どこでもオブジェクト型を使用できます。例12-2では、employee_typ型のオブジェクトempを宣言しています。その後、オブジェクト型employee_typのコンストラクタをコールして、そのオブジェクトを初期化します。

例12-2    PL/SQLブロックでのオブジェクト型の宣言

DECLARE
  emp employee_typ; -- emp is atomically null
BEGIN
-- call the constructor for employee_typ
  emp := employee_typ(315, 'Francis', 'Logan', 'FLOGAN',
        '555.777.2222', '01-MAY-04', 'SA_MAN', 11000, .15, 101, 110, 
         address_typ('376 Mission', 'San Francisco', 'CA', '94222'));
  DBMS_OUTPUT.PUT_LINE(emp.first_name || ' ' || emp.last_name); -- display details
  emp.display_address();  -- call object method to display details
END;
/

オブジェクトは、ファンクションおよびプロシージャの仮パラメータとして宣言できます。そのようにすると、オブジェクトをストアド・サブプログラムに渡したり、あるサブプログラムから別のサブプログラムに渡すことができます。次の例では、オブジェクト型employee_typを使用して仮パラメータのデータ型を指定しています。

PROCEDURE open_acct (new_acct IN OUT employee_typ) IS ...

次の例では、オブジェクト型employee_typを使用してファンクションの戻り型を指定しています。

FUNCTION get_acct (acct_id IN NUMBER) RETURN employee_typ IS ...

PL/SQLによる未初期化オブジェクトの処理

オブジェクト型のためのコンストラクタをコールしてそのオブジェクトを初期化するまで、そのオブジェクトは基本構造的にNULLになっています。つまり、その属性のみではなくオブジェクトそのものがNULLになっています。

NULLのオブジェクトが別のオブジェクトと等しくなることは決してありません。実際のところ、NULLのオブジェクトを別のオブジェクトと比較すると、常にNULLになります。また、基本構造的にNULLになっているオブジェクトを他のオブジェクトに代入すると、そのオブジェクトも基本構造的にNULLになります(したがって再度初期化する必要があります)。同じように、値のないNULLをオブジェクトに代入すると、そのオブジェクトは基本構造的にNULLになります。

式の中で、未初期化オブジェクトの属性はNULLとして評価されます。IS NULL比較演算子を未初期化オブジェクトまたはその属性に適用すると、結果はTRUEになります。

例12-3に、NULLのオブジェクトおよび属性がNULLであるオブジェクトを示します。

例12-3    PL/SQLブロックでのNULLのオブジェクト

DECLARE
  emp employee_typ; -- emp is atomically null
BEGIN
  IF emp IS NULL THEN DBMS_OUTPUT.PUT_LINE('emp is NULL #1'); END IF;
  IF emp.employee_id IS NULL THEN 
     DBMS_OUTPUT.PUT_LINE('emp.employee_id is NULL #1');
  END IF;
  emp.employee_id := 330;
  IF emp IS NULL THEN DBMS_OUTPUT.PUT_LINE('emp is NULL #2'); END IF;
  IF emp.employee_id IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('emp.employee_id is NULL #2');
  END IF;
  emp := employee_typ(NULL, NULL, NULL, NULL,
        NULL, NULL, NULL, NULL, NULL, NULL, NULL, 
         address_typ(NULL, NULL, NULL, NULL));
  -- emp := NULL; -- this would have made the following IF statement TRUE
  IF emp IS NULL THEN DBMS_OUTPUT.PUT_LINE('emp is NULL #3'); END IF;
  IF emp.employee_id IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('emp.employee_id is NULL #3');
  END IF;
EXCEPTION
   WHEN ACCESS_INTO_NULL THEN
     DBMS_OUTPUT.PUT_LINE('Cannot assign value to NULL object');
END;
/

出力は次のとおりです。

emp is NULL #1
emp.employee_id is NULL #1
emp is NULL #2
emp.employee_id is NULL #3

未初期化オブジェクトのメソッドのコールによって、事前定義済の例外NULL_SELF_DISPATCHが呼び出されます。未初期化オブジェクトの属性をINパラメータへの引数として渡すと、それはNULLと評価されます。OUTまたはIN OUTパラメータへの引数として渡されると、書き込むときに例外が呼び出されます。

PL/SQLを使用したオブジェクトの操作

PL/SQLを使用してオブジェクト・メソッドを記述したり、オブジェクトを操作する方法について説明します。

ドット表記法を使用したオブジェクト属性へのアクセス

属性は名前で参照します。属性にアクセスしたり、その値を変更するには、ドット表記法を使用します。属性名を連鎖させて、ネストされたオブジェクト型の属性にアクセスできます。次に例を示します。

例12-4    オブジェクト属性へのアクセス

DECLARE
  emp employee_typ;
BEGIN
  emp := employee_typ(315, 'Francis', 'Logan', 'FLOGAN',
        '555.777.2222', '01-MAY-04', 'SA_MAN', 11000, .15, 101, 110, 
         address_typ('376 Mission', 'San Francisco', 'CA', '94222'));
  DBMS_OUTPUT.PUT_LINE(emp.first_name || ' '  || emp.last_name);
  DBMS_OUTPUT.PUT_LINE(emp.address.street);
  DBMS_OUTPUT.PUT_LINE(emp.address.city || ', '  ||emp. address.state || ' ' ||
                       emp.address.postal_code);   
END;
/

オブジェクトコンストラクタおよびメソッドのコール

コンストラクタは、ファンクション・コールが許可されているところでコールできます。例12-4および例12-5に示すように、すべてのファンクションと同じく、コンストラクタは式の一部としてコールされます。

例12-5    オブジェクト表での行の挿入

DECLARE
  emp employee_typ;
BEGIN
  INSERT INTO employee_tab VALUES (employee_typ(310, 'Evers', 'Boston', 'EBOSTON',
   '555.111.2222', '01-AUG-04', 'SA_REP', 9000, .15, 101, 110, 
    address_typ('123 Main', 'San Francisco', 'CA', '94111')) );
  INSERT INTO employee_tab VALUES (employee_typ(320, 'Martha', 'Dunn', 'MDUNN',
    '555.111.3333', '30-SEP-04', 'AC_MGR', 12500, 0, 101, 110,
    address_typ('123 Broadway', 'Redwood City', 'CA', '94065')) );
END;
/

パラメータをコンストラクタに渡してコンストラクタをコールすると、インスタンスを生成するオブジェクトの属性に初期値が代入されます。すべての属性に値を入れるためにデフォルト・コンストラクタをコールする場合、属性には定数や変数とは異なりデフォルト値がないため、すべての属性にパラメータを指定する必要があります。位置表記法ではなく名前表記法を使用してコンストラクタをコールすることもできます。

パッケージ化されたサブプログラムと同じく、メソッドはドット表記法を使用してコールされます。例12-6では、オブジェクトの属性を表示するためにdisplay_addressメソッドがコールされています。VALUEファンクションを使用すると、オブジェクトの値が戻されます。VALUEは相関変数を引数とします。ここでの相関変数とは、オブジェクト表の行に対応付けられている行変数または表別名のことです。

例12-6    オブジェクト・メソッドへのアクセス

DECLARE
  emp employee_typ;
BEGIN
  SELECT VALUE(e) INTO emp FROM employee_tab e WHERE e.employee_id = 310;
  emp.display_address();
END;
/

SQL文からパラメータのないメソッドをコールするには、空のパラメータ・リストが必要です。プロシージャ文では、コールを連鎖しないかぎり空のパラメータ・リストは必要ありません。連鎖する場合は、最後のコール以外のすべてのコールで空のパラメータ・リストが必要です。プロシージャは式の一部としてではなく文としてコールされため、追加のメソッド・コールをプロシージャの右側に連鎖させることはできません。さらに、2つのファンクション・コールの連鎖では、1番目のファンクションは2番目のファンクションに渡せるオブジェクトを戻す必要があります。

静的メソッドの場合、コールでは型のインスタンスを指定するかわりに、表記法type_name.method_nameを使用します。

サブタイプのインスタンスを使用してメソッドをコールする場合、実際に実行されるメソッドは型の継承での宣言によって決まります。サブタイプがスーパータイプから継承するメソッドをオーバーライドする場合、コールではサブタイプの実装が使用されます。また、サブタイプがメソッドをオーバーライドしない場合、コールではスーパータイプの実装が使用されます。この機能は動的メソッド・ディスパッチと呼ばれます。


注意:

PL/SQLを使用してメソッドを実装する場合は、superキーワードが指定されたベースまたはスーパータイプのオブジェクト・メソッド、または導出されたオブジェクト内の同等のメソッドをコールできません。スーパータイプ、サブタイプおよびオブジェクト・メソッドの詳細は、『Oracle Databaseアプリケーション開発者ガイド-オブジェクト・リレーショナル機能』を参照してください。 


オブジェクトの更新および削除

PL/SQLブロック内から、オブジェクト表の行を変更および削除できます。

例12-7    オブジェクト表での行の更新および削除

DECLARE
  emp employee_typ;
BEGIN
  INSERT INTO employee_tab VALUES (employee_typ(370, 'Robert', 'Myers', 'RMYERS',
   '555.111.2277', '07-NOV-04', 'SA_REP', 8800, .12, 101, 110, 
    address_typ('540 Fillmore', 'San Francisco', 'CA', '94011')) );
  UPDATE employee_tab e SET e.address.street = '1040 California' 
     WHERE e.employee_id = 370;
  DELETE FROM employee_tab e WHERE e.employee_id = 310;
END;
/

Ref修飾子を使用したオブジェクトの操作

ファンクションREFを使用すると、refを取り出せます。このファンクションは、相関変数を引数とします。

例12-8    REF修飾子を使用したオブジェクト表での行の更新

DECLARE
  emp         employee_typ;
  emp_ref REF employee_typ;
BEGIN
  SELECT REF(e) INTO emp_ref FROM employee_tab e WHERE e.employee_id = 370;
  UPDATE employee_tab e 
    SET e.address = address_typ('8701 College', 'Oakland', 'CA', '94321')
    WHERE REF(e) = emp_ref;
END;
/

refは、変数、パラメータ、フィールド、または属性として宣言できます。refはSQLのDML文の中で入力変数または出力変数として使用できます。

PL/SQLでは、refを通してナビゲートできません。たとえば、refを使用した例12-9の代入は誤りです。オブジェクトにアクセスするにはファンクションDEREFを使用するか、パッケージUTL_REFをコールする必要があります。REFファンクションの詳細は、『Oracle Database SQLリファレンス』を参照してください。

例12-9    SELECT INTO文でのDEREFの使用

DECLARE 
  emp           employee_typ;
  emp_ref   REF employee_typ;
  emp_name      VARCHAR2(50);
BEGIN
  SELECT REF(e) INTO emp_ref FROM employee_tab e WHERE e.employee_id = 370;
-- the following assignment raises an error, not allowed in PL/SQL
-- emp_name := emp_ref.first_name || ' ' || emp_ref.last_name;
-- emp := DEREF(emp_ref); not allowed, cannot use DEREF in procedural statements
  SELECT DEREF(emp_ref) INTO emp FROM DUAL; -- use dummy table DUAL
  emp_name := emp.first_name || ' ' || emp.last_name;
  DBMS_OUTPUT.PUT_LINE(emp_name);
END;
/

DEREFファンクションの詳細は、『Oracle Database SQLリファレンス』を参照してください。

PL/SQLコレクション型に相当するSQLの型の定義

ネストした表およびVARRAYをデータベース表の内部に格納するには、CREATE TYPE文を使用してSQLの型を宣言することも必要です。SQLの型は、列としてまたはSQLオブジェクト型の属性として使用できます。SQLのCREATE TYPE文の詳細は、『Oracle Database SQLリファレンス』を参照してください。SQLのCREATE TYPE BODY文の詳細は、『Oracle Database SQLリファレンス』を参照してください。オブジェクト型の詳細は、『Oracle Databaseアプリケーション開発者ガイド-オブジェクト・リレーショナル機能』を参照してください。

PL/SQL内で相当する型を宣言するか、PL/SQLの変数宣言でSQLの型名を使用できます。

例12-10は、SQLで宣言したネストした表をオブジェクト型の属性として使用する方法を示しています。

例12-10    SQLでのネストした表の宣言

CREATE TYPE CourseList AS TABLE OF VARCHAR2(10)  -- define type
/
CREATE TYPE student AS OBJECT (  -- create object
   id_num  INTEGER(4),
   name    VARCHAR2(25),
   address VARCHAR2(35),
   status  CHAR(2),
   courses CourseList);  -- declare nested table as attribute
/
CREATE TABLE sophomores of student
  NESTED TABLE courses STORE AS courses_nt;

識別子coursesはネストした表全体を表します。coursesの各要素には、'Math 1020'などの大学のコース名を入れます。

例12-11によって、VARRAYを格納するデータベースの列が作成されます。VARRAYの各要素にはVARCHAR2型が格納されます。

例12-11    VARRAY列を持つ表の作成

-- Each project has a 16-character code name.
-- We will store up to 50 projects at a time in a database column.
CREATE TYPE ProjectList AS VARRAY(50) OF VARCHAR2(16);
/
CREATE TABLE dept_projects (  -- create database table
   dept_id  NUMBER(2),
   name     VARCHAR2(15),
   budget   NUMBER(11,2),
-- Each department can have up to 50 projects.
   projects ProjectList);

例12-12では、行をデータベース表dept_projectsに挿入しています。VARRAYコンストラクタProjectList()によって、列projectsの値が指定されます。

例12-12    SQL文の中でのVARRAYコンストラクタ

BEGIN
  INSERT INTO dept_projects
    VALUES(60, 'Security', 750400,
      ProjectList('New Badges', 'Track Computers', 'Check Exits'));
END;
/

例12-13では、複数のスカラー値およびネストした表CourseListsophomores表に挿入しています。

例12-13    SQL文の中でのネストした表のコンストラクタ

CREATE TABLE sophomores of student
  NESTED TABLE courses STORE AS courses_nt;
BEGIN
   INSERT INTO sophomores
      VALUES (5035, 'Janet Alvarez', '122 Broad St', 'FT',
         CourseList('Econ 2010', 'Acct 3401', 'Mgmt 3100'));
END;
/

SQLでの個々のコレクション要素の操作

デフォルトのSQL操作では、個々の要素ではなく、コレクション全体が格納および取得されます。SQLで、コレクションの個々の要素を操作するには、TABLE演算子を使用します。TABLE演算子は、副問合せを使用してVARRAYまたはネストした表を抽出します。その結果、INSERT文、UPDATE文またはDELETE文が、トップレベルの表ではなく、ネストした表に適用されます。

PL/SQLのネストした表でのDML操作には、TABLE演算子とCAST演算子を使用します。この方法によって、ネストした表をデータベースに実際に格納せずに、SQL表記法を使用してネストした表で集合演算を実行できます。

CASTのオペランドは、PL/SQLコレクション変数とSQLコレクション型(CREATE TYPE文で作成)です。CASTによって、PL/SQLコレクションがSQLの型に変換されます。

例12-14    CASTを使用したPL/SQLのネストした表での操作の実行

CREATE TYPE Course AS OBJECT
           (course_no  NUMBER,
            title      VARCHAR2(64),
            credits    NUMBER);
/
CREATE TYPE CourseList AS TABLE OF course;
/

-- create department table
CREATE TABLE department (
   name     VARCHAR2(20),
   director VARCHAR2(20),
   office   VARCHAR2(20),
   courses  CourseList) 
   NESTED TABLE courses STORE AS courses_tab;

INSERT INTO department VALUES ('English', 'June Johnson', '491C',
                 CourseList(Course(1002, 'Expository Writing', 4),
                 Course(2020, 'Film and Literature', 4),
                 Course(4210, '20th-Century Poetry', 4),
                 Course(4725, 'Advanced Workshop in Poetry', 4)));

DECLARE
   revised CourseList :=
      CourseList(Course(1002, 'Expository Writing', 3),
                 Course(2020, 'Film and Literature', 4),
                 Course(4210, '20th-Century Poetry', 4),
                 Course(4725, 'Advanced Workshop in Poetry', 5));
   num_changed INTEGER;
BEGIN
   SELECT COUNT(*) INTO num_changed
      FROM TABLE(CAST(revised AS CourseList)) new,
      TABLE(SELECT courses FROM department
         WHERE name = 'English') old
      WHERE new.course_no = old.course_no AND
         (new.title != old.title OR new.credits != old.credits);
   DBMS_OUTPUT.PUT_LINE(num_changed);
END;
/

SQLのオブジェクト型でのPL/SQLコレクションの使用

コレクションを使用すると、PL/SQL内で複雑なデータ型を操作できます。添字を計算してメモリー内の特定の要素を処理し、SQLを使用してその結果をデータベース表に格納するようにプログラムを記述できます。

例12-15に示すように、SQL*Plusでは、PL/SQLのネストした表およびVARRAYに対応した定義を持つSQLのオブジェクト型を作成できます。列dept_names内の各項目は、特定の領域の部門の名前を格納するネストした表です。データベース表にネストした表の列がある場合は常に、NESTED TABLE句が必要です。この句は、ネストした表を識別し、システム生成された記憶域表に名前を指定します。Oracleはネストした表のデータをこの記憶域表に格納します。

PL/SQL内では、ネストした表の要素をループし、TRIMEXTENDなどのメソッドを使用し、要素の一部またはすべてを更新することによって、ネストした表を操作できます。その後、更新した表をデータベースに再度格納できます。ネストした表を含む表の行を挿入したり、行を更新してネストした表を置換したり、PL/SQL変数に入れるネストした表を選択することができます。ネストした表の個々の要素を、SQLを使用して直接更新または削除することはできません。表からネストした表を選択し、PL/SQLで変更してから、表を更新してその新しいネストした表を含める必要があります。

例12-15    INSERT、UPDATE、DELETEおよびSELECT文でのネストした表の使用

CREATE TYPE dnames_tab AS TABLE OF VARCHAR2(30);
/
CREATE TABLE depts (region VARCHAR2(25), dept_names dnames_tab) 
   NESTED TABLE dept_names STORE AS dnames_nt;
BEGIN
   INSERT INTO depts VALUES('Europe', dnames_tab('Shipping','Sales','Finance'));
   INSERT INTO depts VALUES('Americas', dnames_tab('Sales','Finance','Shipping'));
   INSERT INTO depts VALUES('Asia', dnames_tab('Finance','Payroll'));
   COMMIT;
END;
/
DECLARE
-- Type declaration is not needed, because PL/SQL can access the SQL object type
-- TYPE dnames_tab IS TABLE OF VARCHAR2(30); not needed
-- Declare a variable that can hold a set of department names
   v_dnames dnames_tab;
-- Declare a record that can hold a row from the table
-- One of the record fields is a set of department names
   v_depts depts%ROWTYPE;
   new_dnames dnames_tab;
BEGIN
-- Look up a region and query just the associated department names
   SELECT dept_names INTO v_dnames FROM depts WHERE region = 'Europe';
   FOR i IN v_dnames.FIRST .. v_dnames.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE('Department names: ' || v_dnames(i));
   END LOOP;
-- Look up a region and query the entire row
   SELECT * INTO v_depts FROM depts WHERE region = 'Asia';
-- Now dept_names is a field in a record, so we access it with dot notation
   FOR i IN v_depts.dept_names.FIRST .. v_depts.dept_names.LAST LOOP
-- Because we have all the table columns in the record, we can refer to region
     DBMS_OUTPUT.PUT_LINE(v_depts.region || ' dept_names = ' || 
                          v_depts.dept_names(i));
   END LOOP;
-- We can replace a set of department names with a new collection
-- in an UPDATE statement
   new_dnames := dnames_tab('Sales','Payroll','Shipping');
   UPDATE depts SET dept_names = new_dnames WHERE region = 'Europe';
-- Or we can modify the original collection and use it in the UPDATE.
-- We'll add a new final element and fill in a value
   v_depts.dept_names.EXTEND(1);
   v_depts.dept_names(v_depts.dept_names.COUNT) := 'Finance';
   UPDATE depts SET dept_names = v_depts.dept_names 
     WHERE region = v_depts.region;
-- We can even treat the nested table column like a real table and
-- insert, update, or delete elements. The TABLE operator makes the statement
-- apply to the nested table produced by the subquery.
   INSERT INTO TABLE(SELECT dept_names FROM depts WHERE region = 'Asia') 
     VALUES('Sales');
   DELETE FROM TABLE(SELECT dept_names FROM depts WHERE region = 'Asia') 
      WHERE column_value = 'Payroll';
   UPDATE TABLE(SELECT dept_names FROM depts WHERE region = 'Americas')
      SET column_value = 'Payroll' WHERE column_value = 'Finance';
   COMMIT;
END;
/

例12-16に、PL/SQL文を使用してSQLのVARRYオブジェクト型を操作する方法を示します。この例では、PL/SQL変数とSQL表の間でVARRAYを移します。VARRAYを含む表の行を挿入したり、行を更新してVARRAYを置換したり、PL/SQL変数に送信するVARRAYを選択することができます。SQLを使用して、VARRAYの個々の要素を直接更新または削除することはできません。表からVARRAYを選択し、PL/SQL内で変更してから、表を更新してその新しいVARRAYを含める必要があります。

例12-16    INSERT、UPDATE、DELETEおよびSELECT文でのVARRAYの使用

-- By using a varray, we put an upper limit on the number of elements
-- and ensure they always come back in the same order
CREATE TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30);
/
CREATE TABLE depts (region VARCHAR2(25), dept_names dnames_var);
BEGIN
   INSERT INTO depts VALUES('Europe', dnames_var('Shipping','Sales','Finance'));
   INSERT INTO depts VALUES('Americas', dnames_var('Sales','Finance','Shipping'));
   INSERT INTO depts 
     VALUES('Asia', dnames_var('Finance','Payroll','Shipping','Sales'));
   COMMIT;
END;
/
DECLARE
   new_dnames dnames_var := dnames_var('Benefits', 'Advertising', 'Contracting', 
                                       'Executive', 'Marketing');
   some_dnames dnames_var;
BEGIN
   UPDATE depts SET dept_names  = new_dnames WHERE region = 'Europe';
   COMMIT;
   SELECT dept_names INTO some_dnames FROM depts WHERE region = 'Europe';
   FOR i IN some_dnames.FIRST .. some_dnames.LAST
   LOOP
      DBMS_OUTPUT.PUT_LINE('dept_names = ' || some_dnames(i));
   END LOOP;
END;
/

例12-17では、PL/SQL BULK COLLECTは、オブジェクト型が含まれるマルチレベル・コレクションで使用されています。

例12-17    ネストした表でのBULK COLLECTの使用

CREATE TYPE dnames_var IS VARRAY(7) OF VARCHAR2(30);
/
CREATE TABLE depts (region VARCHAR2(25), dept_names dnames_var);
BEGIN
   INSERT INTO depts VALUES('Europe', dnames_var('Shipping','Sales','Finance'));
   INSERT INTO depts VALUES('Americas', dnames_var('Sales','Finance','Shipping'));
   INSERT INTO depts 
     VALUES('Asia', dnames_var('Finance','Payroll','Shipping','Sales'));
   COMMIT;
END;
/
DECLARE
   TYPE dnames_tab IS TABLE OF dnames_var;
   v_depts dnames_tab;
BEGIN
    SELECT dept_names BULK COLLECT INTO v_depts FROM depts;
    DBMS_OUTPUT.PUT_LINE(v_depts.COUNT); -- prints 3
END;
/

オブジェクトでの動的SQLの使用

例12-18は、動的SQLでのオブジェクトとコレクションの使用方法を示しています。まず、オブジェクト型person_typおよびVARRAY型のhobbies_varを定義してから、これらの型を使用するパッケージを作成します。

例12-18    オブジェクト型とコレクションの動的SQLを使用したTEAMSパッケージ

CREATE TYPE person_typ AS OBJECT (name VARCHAR2(25), age NUMBER);
/
CREATE TYPE hobbies_var AS VARRAY(10) OF VARCHAR2(25);
/
CREATE OR REPLACE PACKAGE teams 
   AUTHID CURRENT_USER AS
   PROCEDURE create_table (tab_name VARCHAR2);
   PROCEDURE insert_row (tab_name VARCHAR2, p person_typ, h hobbies_var);
   PROCEDURE print_table (tab_name VARCHAR2);
END;
/
CREATE OR REPLACE PACKAGE BODY teams AS
   PROCEDURE create_table (tab_name VARCHAR2) IS
   BEGIN
      EXECUTE IMMEDIATE 'CREATE TABLE ' || tab_name ||
                        ' (pers person_typ, hobbs hobbies_var)';
   END;
   PROCEDURE insert_row (
      tab_name VARCHAR2,
      p person_typ,
      h hobbies_var) IS
   BEGIN
      EXECUTE IMMEDIATE 'INSERT INTO ' || tab_name ||
         ' VALUES (:1, :2)' USING p, h;
   END;
   PROCEDURE print_table (tab_name VARCHAR2) IS
      TYPE  refcurtyp IS REF CURSOR;
      v_cur refcurtyp;
      p     person_typ;
      h     hobbies_var;
   BEGIN
      OPEN v_cur FOR 'SELECT pers, hobbs FROM ' || tab_name;
      LOOP
         FETCH v_cur INTO p, h;
         EXIT WHEN v_cur%NOTFOUND;
         -- print attributes of 'p' and elements of 'h'
         DBMS_OUTPUT.PUT_LINE('Name: ' || p.name || ' - Age: ' || p.age);
         FOR i IN h.FIRST..h.LAST 
         LOOP
           DBMS_OUTPUT.PUT_LINE('Hobby(' || i || '): ' || h(i));
         END LOOP;
      END LOOP;
      CLOSE v_cur;
   END;
END;
/

無名PL/SQLブロックから、パッケージteamsのプロシージャをコールできます。

例12-19    TEAMSパッケージからのプロシージャのコール

DECLARE
   team_name VARCHAR2(15);
BEGIN
   team_name := 'Notables';
   TEAMS.create_table(team_name);
   TEAMS.insert_row(team_name, person_typ('John', 31),
      hobbies_var('skiing', 'coin collecting', 'tennis'));
   TEAMS.insert_row(team_name, person_typ('Mary', 28),
      hobbies_var('golf', 'quilting', 'rock climbing', 'fencing'));
   TEAMS.print_table(team_name);
END;
/


戻る 次へ
Oracle
Copyright © 2005 Oracle Corporation.

All Rights Reserved.
目次
目次
索引
索引