Created
March 19, 2012 09:33
-
-
Save seraphy/2105194 to your computer and use it in GitHub Desktop.
Oracle11gR2 で、SELECT文のIN句のパラメータに配列データを渡し、その結果をカーソルとして受け取るサンプル.
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
using System; | |
using System.Configuration; | |
using System.Data; | |
using System.Collections.Generic; | |
using System.Linq; | |
using System.Text; | |
using Oracle.DataAccess.Client; | |
using Oracle.DataAccess.Types; | |
namespace odptest | |
{ | |
class Program | |
{ | |
/** | |
* Oracle11gR2 で、SELECT文のIN句のパラメータに配列データを渡し、 | |
* その結果をカーソルとして受け取るサンプル. | |
*/ | |
static void Main(string[] args) | |
{ | |
// コネクション | |
string connStr = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString; | |
using (var conn = new OracleConnection(connStr)) | |
{ | |
conn.Open(); | |
// SQLの説明: | |
// 1. バインドパラメータに文字列配列を指定し、索引つき表として無名ブロックに引き渡す | |
// 2. 索引つき表をネスト表に変換する | |
// 3. ネスト表をtable関数で展開しテーブルであるかのように扱うことでin句に使う | |
// 4. クエリをref cursorとして作成し、そのカーソルをバインド変数に設定して返す. | |
// タイプ定義: | |
// create type VARCHAR2S_NT is table of varchar2(256); | |
// ※ ネスト表をtable関数で使うにはローカルのネスト表では使えないため. | |
// テーブル定義: | |
// create table testtbl(idx varchar2(32), val varchar2(32)); | |
string sql = @"declare | |
IDS DBMS_SQL.VARCHAR2S := :IDS; | |
IDS_T VARCHAR2S_NT := VARCHAR2S_NT(); | |
CUR SYS_REFCURSOR; | |
begin | |
if IDS.COUNT > 0 then | |
IDS_T.extend(IDS.COUNT); | |
for IDX in 1 .. IDS.count | |
LOOP | |
IDS_T(IDX) := IDS(IDX); | |
end loop; | |
end if; | |
open CUR for | |
select * from TESTTBL where IDX in (select * from table(IDS_T)); | |
:CUR := CUR; | |
end; | |
"; | |
using (var cmd = new OracleCommand(sql, conn)) | |
{ | |
// IN句に渡す配列データをバインドパラメータとして定義する. | |
var ids = new OracleParameter("IDS", OracleDbType.Varchar2, ParameterDirection.Input); | |
ids.CollectionType = OracleCollectionType.PLSQLAssociativeArray; // 索引つき表指定 | |
// IN句に渡すデータを作成 | |
string[] vals = (from idx in Enumerable.Range(1, 6000) // ※ 4000個超 | |
select idx.ToString()).ToArray(); | |
ids.Size = vals.Length; // 配列のデータ件数 | |
ids.Value = vals; // データ配列 | |
// 結果のカーソルを受け取るバインドパラメータの定義 | |
var cur = new OracleParameter("CUR", OracleDbType.RefCursor, ParameterDirection.Output); | |
// バインドパラメータの設定 | |
cmd.Parameters.Add(ids); | |
cmd.Parameters.Add(cur); | |
// 実行 | |
cmd.ExecuteNonQuery(); | |
// 返されたカーソルをフェッチする. | |
using (var refCursor = (OracleRefCursor)cmd.Parameters["CUR"].Value) | |
{ | |
using (var rd = refCursor.GetDataReader()) | |
{ | |
if (rd.HasRows) | |
{ | |
// 行データが1件以上あれば、カラム名からインデックスを事前索引しておく | |
int idx_IDX = rd.GetOrdinal("IDX"); | |
int idx_VAL = rd.GetOrdinal("VAL"); | |
// null判定つき文字列カラム取得ヘルパ関数 | |
Func<int, string> GetString = (idx) => | |
{ | |
if (!rd.IsDBNull(idx)) | |
{ | |
return rd.GetString(idx); | |
} | |
return null; | |
}; | |
// フェッチループ | |
while (rd.Read()) | |
{ | |
string idx = GetString(idx_IDX); | |
string val = GetString(idx_VAL); | |
System.Console.WriteLine(idx + "=" + val); | |
} | |
} | |
rd.Close(); | |
} | |
} | |
cmd.Clone(); | |
} | |
conn.Clone(); | |
} | |
System.Console.Read(); | |
} | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment