業務改善!Windows PowerShell入門

Windows PowerShellで、とにかく楽をしたい!実際に仕事で楽になった事、便利になった事を紹介しつつ進めていきます。

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の降順

 

 

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

 

ロリポップ!なら初心者でも安心!
大人気のWordpress簡単インストールやショッピングカートなど
誰でもカンタンに使える機能が満載!