PowerShell Oracleデータベースを操作しろ！#3 - 業務改善！Windows PowerShell入門

Windows　PowerShellポケットリファレンス改訂新版 3．0／2．0／1．0対応（Pocket　reference） [ 牟田口大介 ]

Oracleデータベース操作についての記事、第３弾である。

▼第１弾、第２弾の記事はこちら。

PowerShell Oracleデータベースを操作しろ！#1 - 業務改善！Windows PowerShell入門

PowerShell Oracleデータベースを操作しろ！#2 - 業務改善！Windows PowerShell入門

第１弾では、「接続、データをぶっこ抜く」ということだけをやった。
第２弾では、「例外（エラー）処理の実装」を追加した。

そして、今回はぶっこ抜いたデータに対して、以下を行いたいと思う。

・コマンドレットによる二次加工
・XMLファイル出力

まずは、参考までに第２弾後のソースである。

#アセンブリのロード
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の降順

f:id:acoustic_groove:20171227215837p:plain

▼パイプライン処理についての基礎はこちら。

PowerShell パイプラインを速習しろ！ - 業務改善！Windows PowerShell入門

XMLファイル出力

コンソールに表示するだけでは、物足りないゼ！
大量のデータだった場合はファイルに出力して色々検証したいからな。

PowerShellは、XMLの扱いがとても得意だ。
データセットのデータテーブルはXMLファイルとして、出力し保存することが可能である。データセットのメンバにはそのためのメソッドがある。

▼以下をコンソールで入力してみる。

$dtSet = New-Object System.Data.DataSet
$dtSet | gm -type Method

f:id:acoustic_groove:20171227220903p:plain

▼さらに、以下も入力する。

$dtSet.WriteXML

f:id:acoustic_groove:20171227221121p:plain

・gmは、Get-Memberコマンドレットのエイリアス
・typeは、MemberTypeパラメータのエイリアス
　主に指定するのは、MethodかPropertyが多い

以上より、WriteXmlメソッドのパラメータにファイル名フルパスを指定すれば良い。

楽でええのー！！

出力保存した後は、Invoke-Itemコマンドレット（エイリアス：ii）で、ファイルを開いて確認する。

#部門テーブルをXML出力
$fileName = "C:\hoge\DB接続\XML\" + $dtSet.Tables[0].TableName + ".xml"
$dtSet.Tables[0].WriteXml($fileName)

#XMLを開く
Invoke-Item $fileName

f:id:acoustic_groove:20171227222056p:plain

▼エクセルを使って見てみると、こんな感じだ。

f:id:acoustic_groove:20171227222151p:plain

・Invoke-Itemコマンドレット
・非常に使い勝手が良いゼ！俺は頻繁に使っているコマンドレットだ。
・パラメータにフォルダパスやファイルパスを指定すると、そいつらを開いて（実行して）くれる。
・ファイルの場合は関連付けプログラムによって、適切に開いて（実行して）くれる。

▼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、イエーイ！