PowerShell Oracleデータベースを操作しろ!#1
Windows PowerShellポケットリファレンス改訂新版 3.0/2.0/1.0対応 (Pocket reference) [ 牟田口大介 ]
|
今回より、全数回にわたってPowerShellによるデータベースの操作についてやってくゼ! 扱うデータベースはOracleだ。
Oracle=神託
良い名である。俺がPowerShellにハマったのも神のお告げだったのか?!
・PowerShellでOracleを操作したいゼ!
これまでコマンドラインでのOracleデータベース操作と言えば、Oracle付属ツールであるSQL*Plusをバッチファイルで起動してこねくりまわしていた。だが、、、
もうええ加減バッチファイルは辞めたいゼ!
PowerShellは.NET Frameworkの膨大なクラスが使える。当然、Oracleデータベースへのアクセス、操作も余裕でできるはずである。
・どういう手順と方法でやればええんや?!
.NET Frameworkのクラスを使うにしても、色々と準備等は必要である。
よって、以下の手順と方法でやっていくことをまずは提示しておこう。
1.アセンブリのロード
PowerShellでOracleデータベース操作をするために、一番初めにやらないといけないこと。
それは、アセンブリのロードだ。これをやらずして、Oracleデータベースにアクセスすることも操作することもできねぇゼ!
アセンブリをロードすることによって、PowerShellセッションで .NET FrameWorkクラスの定義が可能となる。
今回ロードするアセンブリは、.NET FrameWorkのOracleClient名前空間にあるクラスだ。
Add-Typeコマンドレットを使って、以下のように書くだけで、はい終了!
楽に書けて良いもんだ。
#アセンブリのロード
Add-Type -AssemblyName System.Data.OracleClient
・.NET FrameWorkのすばらしき恩恵を受けていくことができる。
2.接続用オブジェクトの作成
Oracleデータベース接続には接続文字列を使用する。この接続文字列を使用して、接続用オブジェクトを作成する。
接続文字列とはデータベースに関する情報、データベースへの接続に必要な情報が含まれる文字列のことを言う。
もう、そのまんまだゼ! この接続文字列を使用して、接続用オブジェクトであるOracleConnectionオブジェクトを作成する。
必要となる情報や、書き方については以下の通りだ。
※接続文字列については、拙者のOracle検証用の文字列を記載した。
#////接続文字列の作成////
#Data Source:接続先
#User ID :ユーザーID
#Password :パスワード
#Integrated Security:Windowsアカウントの資格情報を認証に使うかどうか
$ConnectionString = "Data Source=orcl;User ID=system;Password=0000;Integrated Security=false;"
#OracleConnectionオブジェクトの作成
$OraCon = New-Object System.Data.OracleClient.OracleConnection($ConnectionString)
3.データセット、データテーブルの作成
さて、いよいよOracleデータベースからデータの取得に着手できそうだ。
ここで気になるのが、OracleClientのどのクラスを使って、データを取得するかとういうことだが、OracleDataAdapterクラスを使用する。
(※OracleDataAdapterクラスの使い方は、以降のセクションで説明)
なぜ、OracleDataAdapterクラスを選んだか。 それは、以下の点に尽きる。
・データセットにデータを格納することができる。
・データセットはクライアントのメモリに展開されるため、Oracleデータベースから切断しても自由に使用ができる。
・自由に使用できるということは、PowerShellで色々こねくりまわす楽しみがある!
さあ、このデータセットとはどんな構成をしているのか。
図で示すと以下の通りだ。
最上位にDataSetが鎮座し、その手下にDataTableがいる。
DataTableはさらに、レコード(行)であるDataRowをたくさん従え、フィールド(列)であるDataColumnで編成されている。
では、大ボスであるデータセットと、その手下のデータテーブルを実際に作成してみたい。
ポイントとなるのは、データセットを作成したあと、配下のデータテーブルを追加することである。
必ず使用したいテーブルの数だけ追加することが重要だ。
また、テーブルの名前はもちろん任意に決められる。
#データセットの作成
$dtSet = New-Object System.Data.DataSet
#データテーブルの作成
$dtSet.Tables.Add("部門")
$dtSet.Tables.Add("従業員")
4.SQL文の作成
OracleデータベースのサンプルであるSCOTTさんの部門テーブル、従業員テーブルに対して、それぞれSELECT実行するSQL文でテストしてみたいと思う。
書き方は以下の通りだ。
#SQL文の作成
$strSQL = @()
$strSQL += "SELECT * FROM SCOTT.DEPT"
$strSQL += @"
SELECT EMPNO,ENAME,DEPTNO,JOB FROM
SCOTT.EMP
WHERE
DEPTNO IN(10,30)
ORDER BY
DEPTNO ASC
"@
・SQL文用の空の配列を一旦作成して、要素追加していく手法にした。
特筆すべきは、PowerShellのヒア文字列(ヒアドキュメント)の恩恵であろう。
・ヒア文字列とは、「@"文字列"@」の形式で記述する文字列のことであり、長文文字列を記すのにとても便利である。
注意点であるが、@"の後、必ず改行して文字列を書くこと。書き終わったら再び、改行して"@でフィニッシュ!
・今回のような基本的なSQL文では、別に使う必要もないが、複雑なSQL文を書く場合、大変に威力を発揮する。
SQL文を作って、そのままペタっと貼れるのだからな。
5.データの取得~データの参照
さて、やっとOracleDataAdapterクラスの出番です。
此処まで、○○とは何かをたくさん説明してきたが、あとはロジックだけ。
ForEachステートメントを使って、データセットの配下にいる全テーブルに処理をぶっかまして、サクッとキメやがるゼ!
データテーブルは全部で2個あるわけだが、要素番号は0から始まるゼ!
ソースは以下の通りだ。
#データセットの全テーブルへの処理
$i = 0
ForEach($tb in $dtSet.Tables){
#OracleDataAdapterクラスを使用して、データの取得
$data = New-Object System.Data.OracleClient.OracleDataAdapter($strSQL[$i], $OraCon)
#Fillメソッドで、データセットのテーブルに取得データを展開
[void]$data.Fill($tb)
#テーブルのデータを表示
Write-Host ▼ $i 番目のテーブル -ForegroundColor Yellow
Write-Host $tb.TableName
$tb | Format-Table
$i++
}
<OracleDataAdapterクラス>
データセットとOracleデータベース間でデータを取得するブリッジ。
第1引数:作成したSQL文の配列
第2引数:OracleConnectionオブジェクト
<OracleDataAdapterクラス Fillメソッド>
引数であるデータテーブルに、OracleデータベースからSQL実行で取得したデータを格納する。
データテーブルは、$dtSet.Tables[要素番号]で指定する。(※本スクリプトでは、ForEachステートメントにおける$tbがこれに相当する)
<テーブル名称の取得>
テーブル名称は、$dtSet.Tables[要素番号].TableNameでゲットできるゼ!(※本スクリプトでは、ForEachステートメントにおける$tbがこれに相当する)
<データの参照>
$dtSet.Tables[要素番号]でようやく、データお目見えおめでとう!(※本スクリプトでは、ForEachステートメントにおける$tbがこれに相当する)
さて、以上で今回やりたかったことは達成できた。
では、これまで解説してきたソースをすべて以下に示す。
PowerShellスクリプト実行するゼ!!
#--------------------------------------
# Oracleデータベースへの接続
#--------------------------------------
#アセンブリのロード
Add-Type -AssemblyName System.Data.OracleClient
#////接続文字列の作成////
#Data Source:接続先
#User ID :ユーザーID
#Password :パスワード
#Integrated Security:Windowsアカウントの資格情報を認証に使うかどうか
$ConnectionString = "Data Source=orcl;User ID=system;Password=0000;Integrated Security=false;"
#OracleConnectionオブジェクトの作成
$OraCon = New-Object System.Data.OracleClient.OracleConnection($ConnectionString)
#データセットの作成
$dtSet = New-Object System.Data.DataSet
#データテーブルの作成
$dtSet.Tables.Add("部門")
$dtSet.Tables.Add("従業員")
#SQL文の作成
$strSQL = @()
$strSQL += "SELECT * FROM SCOTT.DEPT"
$strSQL += @"
SELECT EMPNO,ENAME,DEPTNO,JOB FROM
SCOTT.EMP
WHERE
DEPTNO IN(10,30)
ORDER BY
DEPTNO ASC
"@
#データセットの全テーブルへの処理
$i = 0
ForEach($tb in $dtSet.Tables){
#OracleDataAdapterクラスを使用して、データの取得
$data = New-Object System.Data.OracleClient.OracleDataAdapter($strSQL[$i], $OraCon)
#Fillメソッドで、データセットのテーブルに取得データを格納
[void]$data.Fill($tb)
#テーブルのデータを表示
Write-Host ▼ $i 番目のテーブル -ForegroundColor Yellow
Write-Host $tb.TableName
$tb | Format-Table
$i++
}
Read-Host "×ボタンで終了"
次回はこのソースに手を加え、データベース接続におけるエラーハンドリングについて触れる予定である。
PowerShell、イエーイ!
PowerShell Oracleデータベースを操作しろ!#2 に続く