PowerShell Oracleデータベースを操作しろ!#3
|
Oracleデータベース操作についての記事、第3弾である。
▼第1弾、第2弾の記事はこちら。
PowerShell Oracleデータベースを操作しろ!#1 - 業務改善!Windows PowerShell入門
PowerShell Oracleデータベースを操作しろ!#2 - 業務改善!Windows PowerShell入門
第1弾では、「接続、データをぶっこ抜く」ということだけをやった。
第2弾では、「例外(エラー)処理の実装」を追加した。
そして、今回はぶっこ抜いたデータに対して、以下を行いたいと思う。
・コマンドレットによる二次加工
・XMLファイル出力
まずは、参考までに第2弾後のソースである。
#アセンブリのロード
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)
#Oracle接続確認
Try{
$OraCon.Open()
$OraCon.Close()
}Catch{
Write-Host "DB接続エラー!" -ForegroundColor Red
Write-Host $Error[0].Exception.Message
Break
}
#データセットの作成
$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){
#テーブル名を表示
Write-Host "▼ $i 番目のテーブル" -ForegroundColor Yellow
Write-Host $tb.TableName
#OracleDataAdapterクラスのオブジェクト作成
$data = New-Object System.Data.OracleClient.OracleDataAdapter($strSQL[$i], $OraCon)
Try{
#アダプタを通じて、Oracle接続し、SQL実行結果をデータセットのテーブルに格納
[void]$data.Fill($tb)
}Catch{
Write-Host "DB接続、またはSQLエラー!" -ForegroundColor Red
Write-Host $Error[0].Exception.Message
$i++
Continue
}
#テーブルのデータを表示
$tb | Format-Table -AutoSize
$i++
}
コマンドレットによる二次加工
今回のデータベース操作では、「非接続型のデータベース接続」で、データをぶっこ抜いている。そして、OracleDataAdapterクラスを使用して、データセットにデータを格納している。
よって、このデータはOracle切断後も、何度でも自由にこねくり回すことができるのである。
やったゼ!ここで、PowerShellの至宝、パイプライン処理を召喚したい。
嗚呼、偉大なるパイプライン処理よ!
データセットに格納した部門テーブルをこねくり回してくれ。
$dtSet.Tables[0].Rows | Where{$_.DEPTNO -gt 10} | Select DEPTNO | Sort DEPTNO -desc
・DEPTNOが10よりデカいやつ
・出力列はDEPTNOだけを選択
・DEPTNOの降順
▼パイプライン処理についての基礎はこちら。
PowerShell パイプラインを速習しろ! - 業務改善!Windows PowerShell入門
XMLファイル出力
コンソールに表示するだけでは、物足りないゼ!
大量のデータだった場合はファイルに出力して色々検証したいからな。
PowerShellは、XMLの扱いがとても得意だ。
データセットのデータテーブルはXMLファイルとして、出力し保存することが可能である。データセットのメンバにはそのためのメソッドがある。
▼以下をコンソールで入力してみる。
$dtSet = New-Object System.Data.DataSet
$dtSet | gm -type Method
▼さらに、以下も入力する。
$dtSet.WriteXML
以上より、WriteXmlメソッドのパラメータにファイル名フルパスを指定すれば良い。
楽でええのー!!
出力保存した後は、Invoke-Itemコマンドレット(エイリアス:ii)で、ファイルを開いて確認する。
#部門テーブルをXML出力
$fileName = "C:\hoge\DB接続\XML\" + $dtSet.Tables[0].TableName + ".xml"
$dtSet.Tables[0].WriteXml($fileName)
#XMLを開く
Invoke-Item $fileName
▼エクセルを使って見てみると、こんな感じだ。
・非常に使い勝手が良いゼ!俺は頻繁に使っているコマンドレットだ。
・パラメータにフォルダパスやファイルパスを指定すると、そいつらを開いて(実行して)くれる。
・ファイルの場合は関連付けプログラムによって、適切に開いて(実行して)くれる。
▼XMLについての関連記事はこちら
PowerShell XMLファイルにアクセスしろ! - 業務改善!Windows PowerShell入門
以上をもって、今回でOracleデータベース操作の記事は一旦終了する。
最後に、今回のテーマを加味した、ソースを以下に示すゼ!
Thank You,Yeah!
#アセンブリのロード
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)
#Oracle接続確認
Try{
$OraCon.Open()
$OraCon.Close()
}Catch{
Write-Host "DB接続エラー!" -ForegroundColor Red
Write-Host $Error[0].Exception.Message
Break
}
#データセットの作成
$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){
#テーブル名を表示
Write-Host "▼ $i 番目のテーブル" -ForegroundColor Yellow
Write-Host $tb.TableName
#OracleDataAdapterクラスのオブジェクト作成
$data = New-Object System.Data.OracleClient.OracleDataAdapter($strSQL[$i], $OraCon)
Try{
#アダプタを通じて、Oracle接続し、SQL実行結果をデータセットのテーブルに格納
[void]$data.Fill($tb)
}Catch{
Write-Host "DB接続、またはSQLエラー!" -ForegroundColor Red
Write-Host $Error[0].Exception.Message
$i++
Continue
}
#テーブルのデータを表示
$tb | Format-Table -AutoSize
$i++
}
#コマンドレットによる二次加工
Write-Host "▼部門テーブルをコマンドレットで二次加工" -ForegroundColor Yellow
$dtSet.Tables[0].Rows | Where{$_.DEPTNO -gt 10} | Select DEPTNO | Sort DEPTNO -desc
#部門テーブルをXML出力
$fileName = "C:\hoge\DB接続\XML\" + $dtSet.Tables[0].TableName + ".xml"
$dtSet.Tables[0].WriteXml($fileName)
#XMLを開く
Invoke-Item $fileName
PowerShell、イエーイ!
ロリポップ!なら初心者でも安心!
大人気のWordpress簡単インストールやショッピングカートなど
誰でもカンタンに使える機能が満載!
PowerShell Oracleデータベースを操作しろ!#2
Windows PowerShellポケットリファレンス改訂新版 3.0/2.0/1.0対応 (Pocket reference) [ 牟田口大介 ]
|
Oracleデータベース操作についての記事、第2弾である。
▼前回の記事はこちら。
PowerShell Oracleデータベースを操作しろ!#1 - 業務改善!Windows PowerShell入門
今回はOracleデータベース操作における例外処理について触れたい。
第1弾ではとりあえず、「接続、データをぶっこ抜く」ということが主なテーマであった。そのため、最後にまとめたソースも例外(エラー)処理を全くかましていない状態である。
よって、今回は第1弾のソースに例外処理をかましていきたい。
例外処理とはどんなんや?
例外処理とは、プログラムが異常な動作をする場合にやる処理のことである。
そのため事前にエラーが起きそうな処理を想定して、何らかの処理を仕込んでおくことをすれば良いわけである。
今回の例外処理では、Try~Catch~Finallyステートメントを用いた構造化エラー処理を使用する。
構文と流れはこうだゼ!
Try{
エラーが起きそうな処理
}Catch[エラーの型]{
エラー時にやってあげる処理
}Finally{
エラー有無によらず必ずやる処理
}
1.Tryブロックでエラー発生!
2.即座にTryブロックを抜けろ!
3.Catchブロックの処理に行け!
4.Finallyブロックの処理をやって終わり。
※エラーの型、Finallyブロックは省略可能。
どこに例外処理をかますんや?!
最低限のことだけを想定すると、例外処理かまさねばならんのは、以下の2点である。
・データベース接続
・SQL実行
これを踏まえ、第1弾のソースを以下のように改修した。
#アセンブリのロード
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)
#Oracle接続確認
Try{
$OraCon.Open()
$OraCon.Close()
}Catch{
Write-Host "DB接続エラー!" -ForegroundColor Red
$Error[0].Exception.Message
Break
}
#データセットの作成
$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){
#テーブル名を表示
Write-Host "▼ $i 番目のテーブル" -ForegroundColor Yellow
Write-Host $tb.TableName
#OracleDataAdapterクラスを使用して、データの取得
$data = New-Object System.Data.OracleClient.OracleDataAdapter($strSQL[$i], $OraCon)
Try{
#データセットのテーブルに取得データを格納
[void]$data.Fill($tb)
}Catch{
Write-Host "DB接続、またはSQLエラー!" -ForegroundColor Red
$Error[0].Exception.Message
$i++
Continue
}
#テーブルのデータを表示
$tb | Format-Table
$i++
}
「Oracle接続確認」のときにかます
ソースの前半部分で、Oracle接続確認をしている。
ここにまずは例外処理をかましている。
#Oracle接続確認
Try{
$OraCon.Open()
$OraCon.Close()
}Catch{
Write-Host "DB接続エラー!" -ForegroundColor Red
$Error[0].Exception.Message
Break
}
流れとしては、Tryブロックで接続成功したら、そのまま一旦接続を解除している。
もし、接続に失敗したら、Catchブロックに飛んでエラーの通知をした後、Breakキーワードを使って、以降の処理を実施しないようにしている。
(※成功時に何故、一旦接続解除をしているかはこの後すぐ説明する。)
さて、今回のOracelデータベース操作で大活躍しているOracleClient名前空間のOracleDataAdapterクラスだが、これは非接続型のデータベース接続方法である。
イメージは下図の通りだ。
<非接続型とは>
「データベース接続、データ抽出、データセット保持、データベース接続解除」をまとめてやってくれる。
さて、このタイミングであるが、ソースの後半にあるFillメソッド実行時である。
明示的にOpen、Closeをしなくても良いのである。
楽だ。非常にありがたい!
だが、まとめて処理する前に接続確認だけはしておきたいゼ!
これが、前半に例外処理をかました理由である。
<接続型とは>
データベースの接続を確立した後、明示的に切断するまで、接続を維持し続ける。
面倒くさいが自分でOpen、Closeをやらないといけない。
以下は、まちがった接続文字列でOracleデータベースに接続を試みた場合の例外処理である。
「データセットの全テーブルへの処理」のときにかます
ここで例外処理をかます目的は、SQL文が間違っていた場合のフォローが主目的である。 前半で接続確認は一旦とれているので。
しかし、此処に来て接続できないことも想定されるので、プロンプトに「DB接続、またはSQLエラー!」と表示をするようにした。
どっちのエラーかはメッセージを見れば一目瞭然だからだ。
以下は、Oracleデータベースに存在しないテーブルにSELECT実行しようとした場合の実行結果である。
0番目のテーブルで失敗したら、しっかり例外捕捉したあと、Continueキーワードを使って、Try~Catchブロック以降にあるテーブルデータ表示をしない。そして、1番目のテーブル取得をするため次のループ処理をしたのが分かる。
$Error[0]について
Catchブロックに仕込んである$Error[0]だが、これが何かを説明しておく。
こいつは自動変数であり、発生したエラー内容が格納されているゼ!
配列になっていて、直近のエラーは要素番号0で取得可能だ。
常に要素番号0に最新のエラーがいらっしゃいます!
自動変数$ErrorのException型にいるMessageというメンバでエラーメッセージが取得できる。
自動変数についてはこれまでの記事で何度も登場しているが、便利このうえない野郎である。
次回、第3弾は取得したデータセットのテーブルをファイルに出力する方法や、PowerShellのコマンドレットとの連携によるテーブルの二次加工について触れる予定である。
PowerShell、イエーイ!
ロリポップ!レンタルサーバー!はあなたの「やってみたい!」を応援します!
ロリポップ!なら、ホームページ、ブログ、ネットショップ…
これらが今すぐ、そして簡単にできちゃう!
マニュアルやライブチャットを完備しているので、ホームページ初心者でも安心。
これだけついてるのに月額100円(税抜)~ととってもお得。
もちろん無料のお試し期間も10日間あるので安心ですね。
▼ロリポップ!レンタルサーバーはこちら
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 に続く