Oracle Developer Days

Transcription

1 1 Oracle データベースを利用した Microsoft.NET 開発 - 応用編 - Session A-9 日本オラクル株式会社パートナービジネス本部シニアコンサルタント弥田尚子 2 1

2 はじめに Oracle Data Provider for.net(odp.net) は Oracle 固有の API を使用し あらゆる.NET アプリケーションから Oracle へのアクセスを可能にする接続ミドルウェアです 本セッションでは ADO.NET に完全に準拠した ODP.NET より Oracle DB の先進的な機能の使用方法を解説いたします 3 Agenda 1. はじめに 2. ODP.NET 機能 XML 機能 ODP.NET トランザクション管理機能 その他 3. Oracleロードマップ 4. 事例 4 2

3 はじめに ODP.NET オブジェクトモデル 非接続型レイヤー 接続レイヤー (ODP.NET) データレイヤー DataSet Oracle DataAdapter OracleCommand Builder Oracle DataReader Oracle Command Oracle Transaction Oracle Connection Oracle 5 はじめに 事前準備 1. Oracle Technology Network Japan ( 日本語 ) よりODP.NET を入手 2. ODP.NETをOracle Universal Installerよりインストールし 接続に使用するサービス名を設定 (Tnsnames.oraを編集). 3. Visual Studio.NET(VS.NET) にて 新規プロジェクトを作成 4. VS.NETの参照設定にODP.NET(%ORACLE_HOME% bin Oracle.DataAccess.dll) を追加 5. ODP.NET のクラスをインポート例 )Imports Oracle.DataAccess.Client Imports Oracle.DataAccess.Types 6 3

4 Agenda 1. はじめに 2. ODP.NET 機能 XML 機能 ODP.NET トランザクション管理機能 その他 3. Oracleロードマップ 4. 事例 7 ODP.NET XML 機能 Oracle XML DBとは Oracle Database 上に実装された ネイティブ XML データベース機能 特徴 標準仕様への準拠 XML, XSL, XML Schemaなど XML 用の API を実装 XMLType データ型 XML 文書格納用のデータ型 リポジトリ機能の実装 Oracle XML DB Repository XML パーサ スタイルシート XSLT プロセッサ XML Schema プロセッサ XML スキーマ 表 8 4

5 ODP.NET XML 機能 機能 XMLType データ型 XML フォームを DB から検索と保存 - リレーショナル表 - オブジェクト リレーショナル表 XmlReader などのMS XML APIとの同時使用可能 XSLT 及びXPathをサポート Schema-based XMLが使用可能 - W3C XML Schema のサポート 9 ODP.NET XML 機能 ODP.NET XML クラス OracleXmlType Oracle ネイティブの XMLType データ OracleXmlStream OracleXmlType に格納された XML データ型の読み取り専用の stream OracleXmlQueryProperties DB 検索に使用される XML のプロパティ OracleXmlSaveProperties Insert Update Delete に使用される XML のプロパティ 10 5

6 ODP.NET XML 機能 ODP.NET and XML DB DEMO 11 Demo 概要 スマートクライアント Client 側には DataSet のみがメモリに展開される DataSet XML Web サービスというオープンな標準技術.NET アプリケーションサーバ Oracle Data Provider for.net Oracle - インターネット

7 ODP.NET XML 機能 DEMO ソース抜粋 ~ 検索 DataSet の ReadXml を使用し XML データを DataSet に格納 Dim cmd As New OracleCommand cmd.commandtext = "Select OrderNo, OrderData From OrderData Where OrderNo=1" Dim da As New OracleDataAdapter(cmd) da.fill(datasetorder, "OrderData") 'OrderData(XmlType フィールド ) の値を StringReader へ格納 Dim sr As IO.StringReader = New IO.StringReader( _ DataSetOrder.Tables("OrderData").Rows(0).Item("OrderData")) 'StringReader の値を DataSet に格納 DataSetOrderXml.Clear() DataSetOrderXml.ReadXml(sr) 13 ODP.NET XML 機能 DEMO ソース抜粋 ~ 更新 XmlType としてパラメータを渡して更新する Dim cmd As New OracleCommand cmd.commandtext = "Update OrderData set OrderData=:OrderDate Where OrderNo=:OrderNo" ' パラメータ設定 (OrderData は XMLTYPE) Dim prmorderno = cmd.parameters.add("orderno",1) Dim prmorderdata = cmd.parameters.add("orderdata", OracleDbType.XmlType) 'DataSet を XML に変換し XMLType フィールドへ更新 DataSetOrder.WriteXml(sw) prmorderdata.value = sw.tostring 'Update の実行 cmd.executenonquery() 14 7

8 ODP.NET XML 機能 XMLTYPE フィールドの操作以下のような発注データを挿入トリガで商品マスタの在庫数更新 CREATE OR REPLACE TRIGGER SMAN.TRG_ORDERDATA BEFORE INSERT ON SMAN.ORDERDATA REFERENCING OLD AS OLD NEW AS NEW FOR EACH ROW DECLARE v_productcode char(8); v_ordernum number; CURSOR c_orderdata is select extractvalue(value(t),'/orderdata/productcode') productcode, extractvalue(value(t),'/orderdata/ordernum') ordernum from table(xmlsequence(extract(:new.orderdata,'/newdataset/orderdata'))) t ; BEGIN -- 在庫数更新 open c_orderdata; loop fetch c_orderdata into v_productcode, v_ordernum; exit when c_orderdata%notfound; update product set stocknum=stocknum - v_ordernum where productcode = v_productcode; end loop; close c_orderdata; END; 15 Agenda 1. はじめに 2. ODP.NET 機能 XML 機能 ODP.NET トランザクション管理機能 その他 3. Oracleロードマップ 4. 事例 16 8

9 ネイティブの Oracle データタイプ LOB 型, BFILE 型, N-data 型, Date 型, TimeStamp 型, Long 型, Raw 型, etc. PL/SQL 対応データ型 REF カーソル, PL/SQL 索引付き表,etc Safe Type mapping.net データ型へ安全にマッピング 17 LOB 型とは LOB(Large Object) は 構造化されていない 大きなデータを格納するためのデータ型で Oracle8から導入 DBに格納できる最大サイズが大きく テキスト 画像 その他の特殊なデータを管理するために非常に有効なデータ型 18 9

10 ODP.NET and LOB DATA DEMO 19 DEMO ソース抜粋 ~ 検索 1.BLOB データを Dataset に格納 Dim cnn As New OracleConneciton cnn.connectionstring = "User Id=sman;Password=sman; " & _ " Data Source=orcl" Dim cmd As New OracleCommand cmd.connection = cnn cmd.commandtext = "select productcode,productimage " & _ " from product where productcode="bl000001'" 'DataAdapter に OracleCommand を設定 Dim da as New OracleDataAdapter(cmd) Dim ds As New DataSet 'DataSet にデータを格納 da.fill(ds,"productdata") 20 10

11 DEMO ソース抜粋 ~ 検索 2. 取得した BLOB データを PictureBox へ表示 'Byte 配列に画像データを格納 Dim byteimage() As Byte = _ ds.tables("productdata").rows(0).item("productimage") 'Byte 配列からストリームを生成します Dim ms As New MemoryStream(byteImage) ' ストリームから画像を生成し PictureBox へ表示します PictureBoxProduct.Image = Image.FromStream(ms) 21 DEMO ソース抜粋 ~ 挿入 1.PictureBox の画像を Byte 配列に格納 'Byte 配列の宣言 Dim byteimage(0) As Byte If Not PictureBoxProduct.Image Is Nothing Then ' 画像情報格納用のストリームを生成 Dim ms As New MemoryStream PictureBoxProduct.Image.Save(ms,ImageFormat.Jpeg) 'Byte 配列の領域確保 ReDim byteimage(ms.length) 'Byte 配列へ画像情報を確保 ms.position =0 ms.read(byteimage,0,convert.toint32(ms.length)) End If 22 11

12 DEMO ソース抜粋 ~ 挿入 2. Byte 配列の情報を Xml Web Services へ送信 XML Web Services のプロキシインスタンスを作成 Dim ws_smanager As New we_sman.servicestockmanagement XML Web Services の Web メソッドを呼び出し ws_smanager.updateproductdata(strproductcode,byteimage) XML Web Services 側の Web メソッド <WebMethod()> Public Function UpdateProductData(ByVal strproductcode As String, _ ByVal byteimage() As Byte) As String 23 DEMO ソース抜粋 ~ 挿入 3. Byte 配列から BLOB フィールドへの格納 Dim prm(2) As OracleParameter cmd.commandtext = "update product set productcode = :productcode and" & _ "productimage = :productimage" prm(0) = cmd.parameters.add("productcode",strproductcode) prm(1) = cmd.paramters.add("productimage",byteimage) 24 12

13 DEMO ソース抜粋 ~ 検索 ( 接続型 ) 1.BLOB データを接続型で取得 Dim cmd As New OracleCommand cmd.connection = cnn ここでは 格納されている BLOB データがあらかじめ最大 10K 以下であることがわかっているため InitialLOBFetchSizes プロパティを設定しています この設定により 1 回のラウンドトリップで BLOB データを取得することが可能です cmd.initiallobfetchsize = cmd.commandtext = "select productcode,productimage " & _ " from product where productcode="bl000001'" cmd の ExecuteReader メソッドにより 結果セットである OracleDataReader オブジェクトを取得 し 結果をリードします Dim rdr As OracleDataReader rdr = cmd.executereader rdr.read() 25 DEMO ソース抜粋 ~ 検索 ( 接続型 ) 2. 取得した BLOB データを PictureBox へ表示 Byte 配列として BLOB データを取得 Dim img As New Byte Dim byteimage(10000) As Byte rdr.getbytes(2, 0, byteimage, 0, 10000) Dim prodimage() As Byte prodimage = byteimage Byte 配列からストリームを生成します Dim img_stream As New MemoryStream img_stream.write(prodimage, 0, 10000) ' ストリームから画像を生成し PictureBox へ表示します pbprodimage.image = Image.FromStream(img_stream) 26 13

14 PL/SQL 対応データ型 27 REF カーソル - プロシージャ内での SELECT 処理 28 14

15 REFカーソル ODP.NET 型のOracleRefCursorにて REF CURSOR データを使用 CREATE OR REPLACE PACKAGE MyPackage AS -- REF カーソル型を宣言します TYPE empcur IS REF CURSOR; PROCEDURE GetEmpRecords( p_cursor OUT empcur, indeptno IN NUMBER, p_errorcode OUT NUMBER); END MyPackage; / このサンプルは EMP 表を検索し 結果を REF カーソル変数として返します CREATE OR REPLACE PACKAGE BODY MyPackage AS PROCEDURE GetEmpRecords( -- REF カーソルの変数を宣言します p_cursor OUT empcur, indeptno IN NUMBER, p_errorcode OUT NUMBER) IS BEGIN p_errorcode := 0; -- カーソル変数を OPEN します OPEN p_cursor FOR SELECT * FROM emp WHERE deptno = indeptno; EXCEPTION WHEN OTHERS THEN p_errorcode := SQLCODE; END GetEmpRecords; END MyPackage; / 29 REF カーソル 例 )PL/SQL ストアドプロシージャの実行コマンドを作成 各パラメータを設定 コマンドを実行し REF カーソルの結果を取得 Dim rdr As OracleDataReader Dim cmd As New OracleCommand("Mypackage.GetEmpRecords", con) cmd.commandtype = CommandType.StoredProcedure 'REF CURSOR パラメータのバインド cmd.parameters.add("p_cursor", OracleDbType.RefCursor, ParameterDirection.Output) cmd.parameters.add("indeptno", OracleDbType.Decimal, 10, ParameterDirection.Input) cmd.parameters.add("p_errorcode", OracleDbType.Int32, ParameterDirection.Output) 'SQL 文の実行と Ref Cursor の使用 rdr = cmd.executereader() Console.WriteLine("Empno Ename") Do While (rdr.read()) Console.WriteLine("{0} {1}", rdr.getvalue(0).tostring(), rdr.getvalue(1).tostring()) Loop 30 15

16 REF カーソル 31 PL/SQL Associative Array data type (PL/SQL 索引付き表 ) - Oracle7 からのオブジェクト機能 - 要素数の制限無し - 索引付きであるため要素へのアクセスが容易 32 16

17 PL/SQL Associative Array data type( PL/SQL 索引付き表 ) CREATE TABLE T1 (COL1 NUMBER,COL2 VARCHAR2(20)); CREATE or replace PACKAGE MYPACK AS TYPE AssocArrayVarchar2_t is table of VARCHAR2(20) index by BINARY_INTEGER; PROCEDURE TestVarchar2( Param1 IN AssocArrayVarchar2_t, Param2 IN OUT AssocArrayVarchar2_t, Param3 OUT AssocArrayVarchar2_t); END MYPACK; / このサンプルは PL/SQL 索引付き表パラメータを IN パラメータとして受け渡し OUT パラメータとして戻り値を取得します CREATE or REPLACE package body MYPACK as PROCEDURE TestVarchar2( Param1 IN AssocArrayVarchar2_t, Param2 IN OUT AssocArrayVarchar2_t, Param3 OUT AssocArrayVarchar2_t) IS i integer; BEGIN Param3(1) := Param2(1); Param3(2) := NULL; Param3(3) := Param2(3); Param2(1) := Param1(1); Param2(2) := Param1(2); Param2(3) := Param1(3); FOR i IN 1..3 LOOP insert into T1 values(i,param2(i)); END LOOP; END TestVarchar2; END MYPACK; / 33 PL/SQL Associative Array data type( PL/SQL 索引付き表 ) 例 )PL/SQL ストアドプロシージャの実行コマンドにパラメータを設定 実行後 OUT パラメータを取得 Dim mycmd As New OracleCommand Dim p1, p2, p3 As OracleParameter mycmd.connection = con ' 実行するストアド プロシージャを text として指定する mycmd.commandtype = CommandType.Text mycmd.commandtext = "begin MyPack.TestVarchar2(:1, :2, :3);end;" 各パラメータの要素のデータ型 Directioin を設定します p1 = mycmd.parameters.add("p1", OracleDbType.Varchar2, ParameterDirection.Input) p2 = mycmd.parameters.add("p2", OracleDbType.Varchar2, ParameterDirection.InputOutput) p3 = mycmd.parameters.add("p3", OracleDbType.Varchar2, ParameterDirection.Output) 34 17

18 PL/SQL Associative Array data type( PL/SQL 索引付き表 ) PL/SQL 索引付表としてバインドすることを宣言 p1.collectiontype = OracleCollectionType.PLSQLAssociativeArray p2.collectiontype = OracleCollectionType.PLSQLAssociativeArray p3.collectiontype = OracleCollectionType.PLSQLAssociativeArray 'PL/SQL 索引付表内の要素数を設定 p1.size = 3 p2.size = 3 p3.size = 3 ' パラメータへ文字列データを設定 Dim dataarray(2) As String dataarray(0) = "Input1" dataarray(1) = "Input2" dataarray(2) = "Input3" p1.value = dataarray dataarray(0) = "Inout1" dataarray(1) = "Inout2" dataarray(2) = "Inout3" p2.value = dataarray p3.value() = "" 35 PL/SQL Associative Array data type( PL/SQL 索引付き表 ) ' パラメータに配列データサイズをセットします Dim parambindsizearray(2) As Int32 parambindsizearray(0) = 20 parambindsizearray(1) = 20 parambindsizearray(2) = 20 p1.arraybindsize = parambindsizearray p2.arraybindsize = parambindsizearray p3.arraybindsize = parambindsizearray mycmd.executenonquery() 'OUT 引数で取得したデータを表示 Dim st(2) As String Dim i As Integer = 0 Do While i < 3 st(i) = p3.value(i).tostring Loop Console.WriteLine(st(i)) i = i

19 PL/SQL Associative Array data type( PL/SQL 索引付き表 ) 37 Safe type mapping 以下のような Oracle データ型において.NET 型に変換する際に発生するデータ損失の可能性を OracleDataAdapter の Safe type mapping を使用する事でなくす NUMBER DATE TIMESTAMP INTERVAL DAY TO SECOND 次のいずれかの型として DataSet 内で表すことが可能 Oracle 書式での.NET Byte[].NET の String 38 19

20 Safe type mapping 例 )NUMBER 型の場合 OracleのNumber 型は最大 38 精度.NETのDecimal 型は最大 28 精度しか保持できないため 28 精度を超えるNUMBERデータ型が.NETの Decimal 型内に取得されると 精度が失われる Oracle の NUMBER 型と.NET の Decimal の最大値と最小値をリストします Oracle のNumber.NETのDecimal 最大値 e125 79,228,162,514,264,337,593,543,950,335 最小値 e125-79,228,162,514,264,337,593,543,950, Safe type mapping 例 )NUMBER 型の場合データ損失を防ぐためStringとして表示 =================================== Visual Basic.NET Coding Sample =================================== OracleDataAdapter のインスタンス化 Dim adapt As OracleDataAdapter = New OracleDataAdapter( select empno from emp, conn) SafeMappingの設定 empnoを.netのstring 型へマッピング adpt.safemapping.add( empno, GetType(System.String)) DataSet へのデータ充填 Dim dataset As DataSet = New DataSet() adapt.fill(dataset, emp ) 40 20

21 Agenda 1. はじめに 2. ODP.NET 機能 XML 機能 ODP.NET トランザクション管理機能 その他 3. Oracleロードマップ 4. 事例 41 ODP.NET トランザクション管理機能 トランザクション MTSとの連携 (System.Enterprise.Services) Oracle Services for MTS 経由 ローカルトランザクション 接続のコンテキストで開始 Savepoints 42 21

22 ODP.NET トランザクション管理機能 ローカルトランザクション接続インスタンスでBeginTransactionメソッドを実行することでトランザクションインスタンスを返す OracleCommandオブジェクトのTransactionプロパティは 暗黙的に設定 =================================== Visual Basic.NET Coding Sample =================================== con = New OracleConnection(sConn) con.open() Dim cmd As OracleCommand = New OracleCommand デフォルトの自動コミットを解除し トランザクションを開始します Dim txn As OracleTransaction = con.begintransaction() 43 ODP.NET トランザクション管理機能 Savepoints //Savepoint 'a' を作成 // 従業員 No.1 は 'a' の中に保存される Dim strsql1 As String = "INSERT INTO emp (empno, ename) VALUES (1,'Employee1')" Dim mycmd As New OracleCommand(strSQL1, con) Dim res As Integer = mycmd.executenonquery() txn.save("a") // Savepoint 'b' を作成 // 従業員 No.1 および 2 は 'b'2 の中に保存される Dim strsql2 As String = "INSERT INTO emp (empno, ename) VALUES (2,'Employee2')" Dim mycmd2 As New OracleCommand(strSQL2, con) Dim res2 As Integer = mycmd2.executenonquery() txn.save("b") //'a' までの情報を DB へ格納することを決定し Commit にて DB へ反映 txn.rollback("a") txn.commit() 44 22

23 ODP.NET トランザクション管理機能 Savepoints Employee1のデータのみDBに格納される SQL> select empno,ename from emp where empno<10; EMPNO ENAME Employee1 SQL> 45 Agenda 1. はじめに 2. ODP.NET 機能 XML 機能 ODP.NET トランザクション管理機能 その他 3. Oracleロードマップ 4. 事例 46 23

24 ODP.NET その他の機能 PL/SQL 完全サポート -パッケージ 非パッケージ 無名 SQL, etc. プロキシ ユーザー認証 グローバリゼーション 完全なUnicode サポート 環境に応じて Oracle のデータを表示するためにクライアントの設定を行う 透過的アプリケーションフェールオーバー (TAF) コールバック通知 DataAdapter を使用した再クエリー 47 ODP.NET その他の機能 コネクションプーリング 最小値, 最大値, タイムアウト, ライフタイム, 増加 減少 < 接続プーリングの使用 不使用による処理速度の相違 > OracleConnection の Open Close を 10 回繰り返して行った場合の処理時間で Pooling=True を 1 とした場合の比較 Pooling=True Pooling=False ソースコード例 cnn.connectionstring = User Id=scott;Password=tiger;Data Source=ora10g;Pooling=false Do While i < 10 cnn.open() cnn.close() I = I+1 Loop 48 24

25 ODP.NET その他の機能 パラメーター 配列バインド 名前指定パラメータ 位置パラメータ例 ) <PL/SQL> CREATE OR REPLACE PROCEDURE REGIST_PUPIL(pid in number,pname in char) IS BEGIN insert into PUPIL(ID, NAME) VALUES(pid, pname); END; / <VB.NET sample coding> ' 名前指定パラメータ BindByName の値を設定 OracleParameter オブジェクトの追加順は引数順にする必要はない cmd.bindbyname = True cmd.parameters.add(new OracleParameter("PNAME", OracleDbType.Char, 20, "TEST",ParameterDirection.Input)) cmd.parameters.add(new OracleParameter("PID", OracleDbType.Decimal, 4, 1,ParameterDirection.Input)) ' 位置パラメータ ( デフォルト ) OracleParameter オブジェクトを引数順に追加する 'cmd.bindbyname = False cmd.parameters.add(new OracleParameter("0", OracleDbType.Decimal, 4, 1,ParameterDirection.Input)) cmd.parameters.add(new OracleParameter("1", OracleDbType.Char, 20, "TEST",ParameterDirection.Input)) 49 Agenda 1. はじめに 2. ODP.NET 機能 XML 機能 ODP.NET トランザクション管理機能 その他 3. Oracleロードマップ 4. 事例 50 25

26 Oracle ロードマップ A N N O U N C E M E N T 2004/5/20 Oracle は Visual Studio Industry Partner (VSIP) プログラムに Premier パートナーとして参加を発表 51 Oracle ロードマップ Visual Studio との更なる統合 User Interface Experience( 使い心地 ) を含めた Visual Studioとの統合 機能例 Server Explorer との統合 Oracle Database リソース ( 表 ビューなど ) の作成と変更 ストアードプロシージャの作成 修正 デバッグ コードの生成 カスタムデザイナー コードエディター 52 26

27 Agenda 1. はじめに 2. ODP.NET 機能 XML 機能 ODP.NET トランザクション管理機能 その他 3. Oracleロードマップ 4. 事例 53 事例 Israel Ministry of Defense 大規模ミッションクリティカルなソフトウェアグループ 軍隊におけるアプリケーションを作成している.NETとOracle 以外のDBにてプロジェクト発足 6 週間でOracleとODP.NETの環境へ移行 同じハードウェア アプリケーション 最小限の変更にて ODP.NET へ変更 Oracle を使用することで 2 から 6 倍の優れたパフォーマンスを確保 54 27

28 事例 DELL DellOrderManagement で Oracle を採用 ODP.NETを組み込んだ理由 接続プーリングを通してコントロールしたい パフォーマンスの良さ REF カーソルのネイティブサポート LOB データ型のネイティブサポート パラメータバインディング XML DB と ODP.NET を XML 文書管理のために使用 55 事例 DataTrak 薬品業界でのリーディング EDC(Electronic Data Capture) ベンダー Central Admin and DATAUnifyer が ODP.NET を採用した理由 XML データを格納および検索できること 最適なデータアクセスパフォーマンス 簡単に使えること 56 28

29 まとめ Oracle Data Provider for.net は ADO.NET に完全に準拠しているため.NET の機能が全て使用可能 加えて Oracle DB がもつ優れた機能優位性を容易な開発手法で取得可能 57 参照情報 Oracle Technology Network Japan ( 日本語 ) - Oracle on Windows 日本語サイト ODP.NET (US OTN ) Oracle on Windows (US OTN ) Visual Studio Magazine interviews

30 参照情報 ODP.NET 付属サンプル - %ORACLE_HOME% ODP.NET samples KROWN( クラウン ) サポート契約によりご覧いただける FAQ 59 Learn Oracle From Oracle Instructor led training Self-Study Online learning Oracle Certification Oracle ilearning Oracle Tutor oracle.com/education 60 30

31 61 31