|
本連載では，第8回から第11回までの4回にわたり，SQL Server 2005をターゲットにしたVisual Basic 2005（以下，VB 2005）によるデータベース・プログラミングを解説してきました。
VB 2005では，ADO.NETを使うことで，データベースを使った複雑なプログラムを簡単に作成することが可能です（図1）。TableAdapterやDataGridViewを利用すれば，見栄えの良いアプリケーションをサクサク開発できます。Visual Studio 2005（VS 2005）によるデータベース・プログラミングは生産性が高いので，プログラミングが楽しくなりますね。
|
|
| 図1●これまでに作成したデータベース・プログラムのイメージ |
しかし，欠点もあります。ネットワーク経由で外部プログラムがデータベースにアクセスするケースでは，パフォーマンス面に不安があります。いかに高速なネットワークだとしても，多数のクライアントPCがネットワーク経由で大量のデータを取得，更新する処理を同時に実行すれば，当然パフォーマンスは落ちます。データベースをゴチャゴチャと更新する処理は，本来サーバー側で実行すればよい性質のものです。
図2は，サーバー側にプログラムを配置するアプリケーションの概念図です。サーバー側にデータベースにアクセスするプログラムBを置き，クライアント側のプログラムAがデータベースに関する処理をプログラムBに委託します。テーブルを更新する処理を，サーバー側に配置したプログラムに任せてしまえば，パフォーマンスは向上します。
|
|
| 図2●プログラムを二つにわけて，データベース・アクセス処理のプログラムは，サーバー側で実行するようにする |
しかしこの方法でも，プログラムBは，データベースに対して外部プログラムであることに変わりありません。同一サーバー上で動作しているとはいえ，データベース・サーバーとプログラムBは別々の処理として実行され，両者の間には通信が発生します。また，複数のクライアントの要求を受け，データベースにアクセスして，結果を返す処理を自前で作成することはなかなか困難です。
これらの問題を解決するために，データベースには古くから「ストアド・プロシジャ」という仕組みが用意されています（図3）。
|
|
| 図3●ストアド・プロシジャのイメージ |
ストアド・プロシジャとは，データベースに対する一連の命令群（プロシジャ）を，データベース・システム内に保存するようにした方法です。ストアド・プロシジャには，SELECTなどのSQL文だけでなく，変数や制御文を使った条件分岐や繰り返しといった制御構造を記述できます。
ストアド・プロシジャは，データベースに解釈（コンパイル）済みの状態で保存され，データベースの内部で実行されるため，高速な動作が期待できます。
ストアド・プロシジャの作成と実行
では，実際に作成してみましょう。前回，トランザクション処理の説明で作成したパソコン・レンタル会社の貸出処理をストアド・プロシジャとして作成してみます。
テーブルを二つ使います（表1）。ZAIKOテーブルには，レンタルする商品を何台所有していて，現在貸出中のものは何台かという数を持っています。RENTALテーブルには，貸出明細を記録します。
表1●ZAIKOテーブルとRENTALテーブルZAIKOテーブル
| 名前 | 列名 | データ型 | 備考 |
|---|---|---|---|
| 商品コード | SHCD | char(6) | 主キー |
| 商品名 | SHNAME | nvarchar(50) | - |
| 所有数 | SURYO | int | - |
| 貸出数 | KASI | int | - |
RENTALテーブル
| 名前 | 列名 | データ型 | 備考 |
|---|---|---|---|
| ID | RENTID | int | 主キー，IDENTITY |
| 商品コード | SHCD | char(6) | - |
| 貸出開始日 | FRDATE | datetime | - |
| 貸出終了日 | TODATE | datetime | - |
| ユーザーコード | USCODE | char(11) | - |
| 数量 | SURYO | int | - |
フローは，図4のようになります。入力フォームから貸出を希望する商品のコードと貸出希望数が入力されるものとします。ZAIKOテーブルの所有数－貸出数が貸出可能数です。貸出可能数が貸出希望数より小さいときは，貸出できません。そうでない場合は，ZAIKOテーブルの貸出数（KASI）を更新し，RENTALテーブルにレコードを追加します。
|
|
| 図4●貸し出し処理の流れ |
リスト1は，ADO.NETを使ったトランザクション処理コードの抜粋です。ExecuteReader()やExecuteNonQuery()で実際に実行するSQL文は，リスト2のように事前に定義しています。いわゆるプリペアード・クエリですね。ADO.NETを使ったクライアントからのデータベース・アクセス処理はこの例のようになります。イメージを覚えておいてください。
Dim trans As SqlTransaction = cn.BeginTransaction()
selectCmd.Transaction = trans
selectCmd.Parameters.Clear()
selectCmd.Parameters.Add(New SqlParameter("@SHCD", txtSHCD.Text))
dr = selectCmd.ExecuteReader()
dr.Read()
intZANSU = dr.Item("SURYO") - dr.Item("KASI")
intKASI = dr.Item("KASI") + CInt(numSURYO.Text)
intZANSU = intZANSU - CInt(numSURYO.Text)
dr.Close()
If intZANSU < 0 Then
trans.Commit()
Exit Sub
Else
updateCmd.Transaction = trans
updateCmd.Parameters.Clear()
updateCmd.Parameters.Add(New SqlParameter("@KASI", intKASI))
updateCmd.Parameters.Add(New SqlParameter("@SHCD", txtSHCD.Text))
recAffected = updateCmd.ExecuteNonQuery()
insertCmd.Transaction = trans
insertCmd.Parameters.Clear()
insertCmd.Parameters.Add(New SqlParameter("@SHCD", txtSHCD.Text))
insertCmd.Parameters.Add(New SqlParameter("@FRDATE", dtpFRDATE.Value))
insertCmd.Parameters.Add(New SqlParameter("@TODATE", dtpTODATE.Value))
insertCmd.Parameters.Add(New SqlParameter("@USCODE", txtUSCODE.Text))
insertCmd.Parameters.Add(New SqlParameter("@SURYO", numSURYO.Value))
recAffected = insertCmd.ExecuteNonQuery()
End If
trans.Commit()Dim selectSql As String = "select * from ZAIKO where SHCD = @SHCD"
selectCmd = New SqlCommand()
selectCmd.Connection = cn
selectCmd.CommandText = selectSql
Dim insertSql As String = "insert into RENTAL(SHCD,FRDATE,TODATE,USCODE,SURYO)"
insertSql += " values(@SHCD,@FRDATE,@TODATE,@USCODE,@SURYO)"
insertCmd = New SqlCommand()
insertCmd.Connection = cn
insertCmd.CommandText = insertSql
Dim updateSql As String = "update ZAIKO set KASI=@KASI"
updateSql += " where SHCD = @SHCD"
updateCmd = New SqlCommand()
updateCmd.Connection = cn
updateCmd.CommandText = updateSql
