業務改善！Windows PowerShell入門

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

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

2.接続用オブジェクトの作成

3.データセット、データテーブルの作成

4.SQL文の作成

5.データの取得～データの参照 

 

1.アセンブリのロード

PowerShellでOracleデータベース操作をするために、一番初めにやらないといけないこと。

それは、アセンブリのロードだ。これをやらずして、Oracleデータベースにアクセスすることも操作することもできねぇゼ！

 

アセンブリをロードすることによって、PowerShellセッションで .NET FrameWorkクラスの定義が可能となる。

 

今回ロードするアセンブリは、.NET FrameWorkのOracleClient名前空間にあるクラスだ。

 

Add-Typeコマンドレットを使って、以下のように書くだけで、はい終了！

楽に書けて良いもんだ。

 

#アセンブリのロード
Add-Type -AssemblyName System.Data.OracleClient

 

・このアセンブリのロードの後、New-Objectコマンドレットを使って、以降の手順で使用するオブジェクトをいくつかインスタンス化する。
・.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で色々こねくりまわす楽しみがある！

 

さあ、このデータセットとはどんな構成をしているのか。

図で示すと以下の通りだ。

 

f:id:acoustic_groove:20171212221838p:plain

 

最上位に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ステートメントを使って、データセットの配下にいる全テーブルに処理をぶっかまして、サクッとキメやがるゼ！

データテーブルは全部で２個あるわけだが、要素番号は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データベース間でデータを取得するブリッジ。
第１引数：作成したSQL文の配列
第２引数：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 "×ボタンで終了"

 

f:id:acoustic_groove:20171212224656p:plain

 

次回はこのソースに手を加え、データベース接続におけるエラーハンドリングについて触れる予定である。

 

PowerShell、イエーイ！

 

PowerShell Oracleデータベースを操作しろ！#2 に続く