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

[大田浩，日本オラクル]

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回のデータベース・ラウンドトリップで渡すことが可能です。（次ページへ続く）