ODP.NETのデータアクセス・チューニングODP.NETファーストステップ(3)(3/4 ページ)

» 2005年01月27日 00時00分 公開
[大田浩日本オラクル]

PL/SQL配列を利用する方法

 ストアドプロシージャを利用してデータベース・ラウンドトリップを減らす方法として、PL/SQL配列(以前はPL/SQL表と呼ばれていました)を利用する方法があります。例えば以下のようなデータをemployeeテーブルに3行追加したい場合には、Oracleデータベースに対してInsertを3回実行するため、データベース・ラウンドトリップも3回発生します。

employeeid employeename salary
10001 Scott 11000
10002 Billly 12000
10003 Jhon 13000
表1 employeeテーブル

 PL/SQL配列を使用すると、複数の行を1回のデータベース・ラウンドトリップで追加することが可能です。以下にPL/SQL配列の使用方法を説明します。

手順1

 テスト用のテーブルを作成します。

create table employee(
  employeeid number(5,0) not null,
  employeename varchar(100),
  salary number(7,0),
  primary key(employeeid)
)

手順2

 PL/SQL配列で1度に書き込みを行うためのパッケージを作成します。

create or replace package employee_associative as
  type t_employeeid is table of 
    employee.employeeid%type index by binary_integer;
  type t_employeename is table of 
    employee.employeename%type index by binary_integer;
  type t_salary is table of 
    employee.salary%type index by binary_integer;

  procedure bulk_insert(
    p_employeeid in t_employeeid,
    p_employeename in t_employeename,
    p_salary in t_salary
  );
end employee_associative;
/

create or replace package body employee_associative as
  procedure bulk_insert(
    p_employeeid in t_employeeid,
    p_employeename in t_employeename,
    p_salary in t_salary) is
  begin
    forall i in p_employeeid.first..p_employeeid.last
      insert into employee(employeeid,employeename,salary)
        values(p_employeeid(i),p_employeename(i),p_salary(i));
  end bulk_insert;
end employee_associative;
/

手順3

 ストアドプロシージャにPL/SQL配列を値として渡すコードは以下になります。

Dim cnn As New OracleConnection( _
  "user id=scott;password=tiger;data source=orcl")
Dim cmd As New OracleCommand( _
  "employee_associative.bulk_insert", cnn)
cmd.CommandType = CommandType.StoredProcedure

Dim p_employeeid As New OracleParameter
Dim p_employeename As New OracleParameter
Dim p_salary As New OracleParameter

p_employeeid.OracleDbType = OracleDbType.Decimal
p_employeename.OracleDbType = OracleDbType.Varchar2
p_salary.OracleDbType = OracleDbType.Decimal

p_employeeid.CollectionType = _
  OracleCollectionType.PLSQLAssociativeArray
p_employeename.CollectionType = _
  OracleCollectionType.PLSQLAssociativeArray
p_salary.CollectionType = _
  OracleCollectionType.PLSQLAssociativeArray

p_employeeid.Value = _
  New Decimal() {10001, 10002, 10003}
p_employeename.Value = _
  New String() {"Scott", "Billy", "Jhon"}
p_salary.Value = _
  New Decimal() {11000, 12000, 13000}

p_employeeid.Size = 3
p_employeename.Size = 3
p_salary.Size = 3

cmd.Parameters.Add(p_employeeid)
cmd.Parameters.Add(p_employeename)
cmd.Parameters.Add(p_salary)

cnn.Open()
cmd.ExecuteNonQuery()
リスト17 ストアドプロシージャにPL/SQL配列を値として渡すコード(VB.NET)

OracleConnection cnn = 
  new OracleConnection(
    "user id=scott;password=tiger;data source=orcl");
OracleCommand cmd = new OracleCommand(
  "employee_associative.bulk_insert", cnn);
cmd.CommandType = CommandType.StoredProcedure;

OracleParameter p_employeeid = new OracleParameter();
OracleParameter p_employeename = new OracleParameter();
OracleParameter p_salary = new OracleParameter();

p_employeeid.OracleDbType = OracleDbType.Decimal;
p_employeename.OracleDbType = OracleDbType.Varchar2;
p_salary.OracleDbType = OracleDbType.Decimal;

p_employeeid.CollectionType = 
  OracleCollectionType.PLSQLAssociativeArray;
p_employeename.CollectionType = 
  OracleCollectionType.PLSQLAssociativeArray;
p_salary.CollectionType = 
  OracleCollectionType.PLSQLAssociativeArray;

p_employeeid.Value = 
  new Decimal[] {10001, 10002, 10003};
p_employeename.Value = 
  new String[] {"Scott", "Billy", "Jhon"};
p_salary.Value = 
  new Decimal[] {11000, 12000, 13000};

p_employeeid.Size = 3;
p_employeename.Size = 3;
p_salary.Size = 3;

cmd.Parameters.Add(p_employeeid);
cmd.Parameters.Add(p_employeename);
cmd.Parameters.Add(p_salary);

cnn.Open();
cmd.ExecuteNonQuery();
リスト18 ストアドプロシージャにPL/SQL配列を値として渡すコード(C#)

 以上のように、OracleParameterオブジェクトのCollectionTypeプロパティをPLSQLAssociativeArrayと指定することにより、プログラムで確保した配列の値をデータベース・サーバのPL/SQL配列に1回のデータベース・ラウンドトリップで渡すことが可能です。(次ページへ続く)

Copyright © ITmedia, Inc. All Rights Reserved.

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。