申し訳ございません。検索条件に一致するものが見つかりませんでした。

お探しのものを見つけるために、以下の項目を試してみてください。

  • キーワード検索のスペルを確認してください。
  • 入力したキーワードの同義語を使用してください。たとえば、「ソフトウェア」の代わりに「アプリケーション」を試してみてください。
  • 新しい検索を開始してください。

開発者:ODP.NET

PL/SQL連想配列の使用

Mark A. Williams著

PL/SQLストアド・プロシージャの機能をODP.NETに組み込みます。

いくつかのIT構想を示す電子メールを受信したと仮定して、そのうちの2つが今後のアプリケーション開発技術に影響を与えるような電子メールであったとします。 1つは、ネットワークの利用を最小化するように設計する必要があるすべてのアプリケーションについて、もう1つは、Oracleデータベースを使用するアプリケーションがデータベース構造へのアクセスにPL/SQLを使用する必要があるということについてです。

つまり、アプリケーション・プログラミング・インタフェース(API)としてデータベースでPL/SQLを使用し、ネットワークの使用を削減する必要があります。それでは、何から開始すればいいのでしょうか。 PL/SQL連想配列のODP.NETサポートによって、両方のIT指令を満たすことができます。

ODP.NET開発者は、データベースのデータのAPIとしてPL/SQLを使用できます。また、ネットワーク・ラウンドトリップを削減するために連想配列バインディングを使用できます。 この結果、ネットワークとOracle Databaseのワークロードが削減されます。つまり、アプリケーションのパフォーマンスとスケーラビリティが向上します。

サンプル・アプリケーション

PL/SQLおよびパフォーマンスを指示する理由が、企業の急速な成長にあると仮定します。 IT部門だけで、3つの新しい職務分類(データベース管理者、マネージャ、バイス・プレジデント)があります。 このコラムでは、ネットワークの利用を最小限に抑え、.NET、PL/SQL、および連想配列を使用してこれらの職務をOracleデータベースに追加する方法を説明します。

このコラムで作成されるサンプルには、Oracle Databaseに付属するHRスキーマを使用します。 このスキーマには、職務単位の行を含むJOBS表があります。

PL/SQLコード

PL/SQLコードは、アプリケーションとデータベース間のインタフェースを提供します。 リスト1のassociative_arrayパッケージ仕様および本体のコードは、インタフェースです。データベースのHRスキーマで実行されます(Microsoft Visual Studio開発者は、Oracle Developer Tools for Visual StudioまたはOracle SQL Developerなどのツールを使用して、PL/SQLコードの作成および編集をおこなう場合があります)。

コード・リスト1: PL/SQLコード


create or replace package associative_array as
  -- define an associative array type for each column in the jobs table

  type t_job_id is table of jobs.job_id%type index by pls_integer;
  type t_job_title is table of jobs.job_title%type index by pls_integer;
  type t_min_salary is table of jobs.min_salary%type index by pls_integer;
  type t_max_salary is table of jobs.max_salary%type index by pls_integer;

  -- define the procedure that will perform the array insert

  procedure array_insert (p_job_id in t_job_id,
                                   p_job_title in t_job_title,
                                   p_min_salary in t_min_salary,
                                   p_max_salary in t_max_salary);
end associative_array;
/

create or replace package body associative_array as
  -- implement the procedure that will perform the array insert

  procedure array_insert (p_job_id in t_job_id,
                                   p_job_title in t_job_title,
                                   p_min_salary in t_min_salary,
                                   p_max_salary in t_max_salary) is
  begin
    forall i in p_job_id.first..p_job_id.last
    insert into jobs (job_id,
                           job_title,
                           min_salary,
                           max_salary)
              values (p_job_id(i),
                         p_job_title(i),
                         p_min_salary(i),
                         p_max_salary(i));
  end array_insert;
end associative_array;
/ 

       

associative_arrayパッケージには、パッケージ仕様およびパッケージ本体のコードが含まれます。 パッケージ仕様は、プロシージャのパラメータ型を定義する4つのデータ型とともにパッケージ本体で実装される単一のプロシージャを宣言します。 各型は、次の構造をもつJOBS表の列を表します。



    SQL> desc jobs

 Name            Null?     Type
 ----------      -------   --------------
 JOB_ID          NOT NULL  VARCHAR2(10)
 JOB_TITLE       NOT NULL  VARCHAR2(35)
 MIN_SALARY                NUMBER(6)
 MAX_SALARY                NUMBER(6)                               

associative_arrayパッケージの各データ型を定義して各データベース列型で構成される表を使用できるので、パッケージ・コードでJOBS表の各列の型と一致する4つの配列を効果的に作成します。

たとえば、t_job_id型を宣言して、JOBS表のjob_id列と型が一致する単一列の表(データベース表ではなくPL/SQL表)を使用します。

単一列のPL/SQL表は、基本的に配列となります。 associative_arrayパッケージ本体のarray_insertプロシージャは、4つのパラメータ(表の各列に1つ)を取得します。各パラメータは、.NETクライアント・アプリケーションが提供する値の配列です。

行をJOBS表に挿入する文は従来のINSERT文と似ていますが、FORALLキーワードが使用され、挿入された値が小文字の i で識別される点で異なります。

FORALLキーワードによって、通常のFOR LOOP文のように配列をループするのではなく、グループとして連想配列のすべての要素をPL/SQLで処理できます。

配列の上限と下限は、最初と最後のメソッドで示されます。 values句の小文字の i は、PL/SQLを取得するために配列の正しい要素を識別します。

C#コードの作成

Oracleデータベース・インスタンスのPL/SQLインタフェース・コードを使用して、リスト2の.NETクライアント・アプリケーション・コードをデプロイします。

コード・リスト2: .NET C#コード



       using System;
using System.Data;
using Oracle.DataAccess.Client;
using Oracle.DataAccess.Types;

namespace JanFeb2007 {
  class Program {
    static void Main(string[] args) {
      // connection string - make sure to adjust for your environment
      string constr = "user id="hr;" password="hr;" data source="oramag;" enlist="false;" pooling=false";

      // create and open connection object
      OracleConnection con = new OracleConnection(constr);
      con.Open();

      // insert the new jobs into the jobs table
      // create command object and set attributes
      OracleCommand cmd = con.CreateCommand();
      cmd.CommandText = "associative_array.array_insert";
      cmd.CommandType = CommandType.StoredProcedure;

      // create parameter objects for each parameter
      OracleParameter p_job_id = new OracleParameter();
      OracleParameter p_job_title = new OracleParameter();
      OracleParameter p_min_salary = new OracleParameter();
      OracleParameter p_max_salary = new OracleParameter();

      // set parameter type for each parameter
      p_job_id.OracleDbType = OracleDbType.Varchar2;
      p_job_title.OracleDbType = OracleDbType.Varchar2;
      p_min_salary.OracleDbType = OracleDbType.Decimal;
      p_max_salary.OracleDbType = OracleDbType.Decimal;

      // set the collection type for each parameter
      p_job_id.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
      p_job_title.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
      p_min_salary.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
      p_max_salary.CollectionType = OracleCollectionType.PLSQLAssociativeArray;

      // set the parameter values
      p_job_id.Value = new string[3] { "IT_DBA", "IT_MAN", "IT_VP" };
      p_job_title.Value = new string[3] { "Database Administrator", "IT Manager", "IT Vice President" };
      p_min_salary.Value = new decimal[3] { 8000, 12000, 18000 };
      p_max_salary.Value = new decimal[3] { 16000, 24000, 36000 };

      // set the size for each array
      p_job_id.Size = 3;
      p_job_title.Size = 3;
      p_min_salary.Size = 3;
      p_max_salary.Size = 3;

      // add parameters to command object collection
      cmd.Parameters.Add(p_job_id);
      cmd.Parameters.Add(p_job_title);
      cmd.Parameters.Add(p_min_salary);
      cmd.Parameters.Add(p_max_salary);

      // execute the insert
      cmd.ExecuteNonQuery();

      // display the new jobs
      cmd.CommandText = "select job_id, job_title from jobs where job_id in ('IT_DBA', 'IT_MAN', 'IT_VP') order by job_id";
      cmd.CommandType = CommandType.Text;

      OracleDataReader dr = cmd.ExecuteReader();

      Console.WriteLine("New jobs have been added to the JOBS table:\n");

      while (dr.Read()) {
        Console.WriteLine("{0,6}: {1}", dr.GetString(0), dr.GetString(1));
      }

      Console.WriteLine();

      // delete the new jobs
      cmd.CommandText = "delete from jobs where job_id in ('IT_DBA', 'IT_MAN', 'IT_VP')";
      cmd.ExecuteNonQuery();

      Console.WriteLine("New jobs have been removed from the JOBS table.");

      // clean up objects
      p_max_salary.Dispose();
      p_min_salary.Dispose();
      p_job_title.Dispose();
      p_job_id.Dispose();
      cmd.Dispose();
      con.Dispose();

      // simple prompt to keep output window from closing when executing from IDE
      Console.WriteLine("Press ENTER to continue...");
      Console.ReadLine();
    }
  }
}     

データ処理ロジックがデータベースにあるので、クライアント・コードは、データベースのパラメータの作成、値の設定、およびコードの起動におもに関連づけられます。 PL/SQLインタフェースとデータベースのデータが同じ場所に配置されているため、データ挿入コードにSQL文が含まれないので注意してください。

新しいPL/SQLインタフェースをテストするには、Visual StudioのC#コンソール・アプリケーションを実行します。

最初に、ODP.NETが連想配列を渡すことができるように接続文字列をOracleデータベース・インスタンスの適切な値に変更します。次に、Visual Studioのコードをコンパイルし、Visual Studioメニューの「 デバッグ 」→「 ステップ イン 」を選択して動作を確認します。

コードをステップ・スルーする場合、PL/SQLパッケージ名およびプロシージャに設定されているCommandTextプロパティ値(associative_array.array_insert)でOracleCommandオブジェクトが作成されるので注意してください。

次に、PL/SQLストアド・プロシージャの各パラメータのOracleParameterオブジェクトが作成されます。 このあと、各パラメータのターゲット列と一致するように、各パラメータ型がOracleDbTypeに設定されます。

コードをステップ・スルーする際の重要な注意事項は、連想配列を正しく使用するために各パラメータのコレクション・タイプをOracleCollectionType.PLSQLAssociativeArrayに正しく設定する必要があることです。 これによって、ODP.NETアプリケーションがPL/SQL連想配列としてOracleParameterオブジェクトをPL/SQLストアド・プロシージャにバインドできます(図1を参照)。

PL/SQL連想配列の詳細について

『Oracle Database PL/SQLユーザーズ・ガイドおよびリファレンス』

『Oracle Data Provider for .NET開発者ガイド』

.NET Developer Center へのアクセス

ダウンロード

このコラムの サンプル・コード

ODP.NET

図1:サンプルのステップ・スルー

コレクション・タイプの設定後、JOBS表に挿入される(3つの新しい職務の)値が割り当てられます。 次に、各配列のサイズの設定、OracleCommandオブジェクトのパラメータ・コレクションへのパラメータの追加、およびデータを挿入するためのストアド・プロシージャの起動が実行されます。

最後に、表の問合せが実行され、3つの新しい職務(IT_DBA、IT_MAN、IT_VP)がJOBS表に正しく追加されたことが示されます。

詳細情報

PL/SQL連想配列は、INSERT操作に制限されません。 PL/SQL連想配列を使用して、SELECT、UPDATE、およびDELETE操作も実行できます。 Oracleデータベースおよび.NET中間層のバルク・データ共有メカニズムやPL/SQL APIを使用して一元管理されたデータ・アクセスによるアプリケーションのメリットを発見するために、PL/SQL連想配列をさらに試してみることをお奨めします。

Mark A. Williams ( mawilliams@cheshamdbs.com )氏は、2004年にApressから発行された『 Pro .NET Oracle Programming 』の著者で、Oracle ACEおよびOracle Certified Professional DBAの資格をもっています。また、Oracle Technology NetworkのOracle Data Provider for .NETに関するフォーラムに貢献しています。