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

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

動的SQLは、実行時にSQL情報(表名、SQL文のフルテキスト、変数の情報など)を入力できるようにするPL/SQLの機能です。

内容は次のとおりです。

3.4.1 オブジェクト型およびコレクションでの動的SQLの使用

動的SQLはオブジェクト型およびコレクションとともに使用できます。

例3-11は、動的SQLとのオブジェクトおよびコレクションの使用を示しています。この例では、最初にオブジェクト型person_typとVARRAY型hobbies_varを定義してから、これらの型を使用するパッケージteamsを定義しています。

動的パッケージ・メソッドを実行するためのAUTHID CURRENT_USERが必要です。これがない場合、例3-12 (TEAMSパッケージ内のプロシージャをコールする匿名ブロック)を実行すると、これらのメソッドでは権限不足エラーが発生します。

例3-11 オブジェクト型とコレクションに動的SQLを使用するパッケージ

CREATE OR REPLACE 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;
/

3.4.2 オブジェクト型およびコレクションを使用するパッケージ・プロシージャのコール

オブジェクト型およびコレクションを使用するパッケージ・プロシージャをコールできます。

例3-12の匿名ブロックから、例3-11に示すようにTEAMSパッケージ内のプロシージャをコールすることもできます。

例3-12 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;
/