金宏 和實(かねひろ かずみ)

 富山県高岡市 株式会社イーザー副社長。昨年(2007),久しぶりに単行本を書きました。「ベテランが丁寧に教えてくれるデータベースの知識と実務」(翔泳社 発行)です。実務編では,自分の経験をもとに実務で必要な知恵をIT企業の新入社員とその上司である主任を通してケース・スタディ的に説明してみました。どうぞ,読んでみてください。

 本連載では,第8回から第11回までの4回にわたり,SQL Server 2005をターゲットにしたVisual Basic 2005(以下,VB 2005)によるデータベース・プログラミングを解説してきました。

 VB 2005では,ADO.NETを使うことで,データベースを使った複雑なプログラムを簡単に作成することが可能です(図1)。TableAdapterやDataGridViewを利用すれば,見栄えの良いアプリケーションをサクサク開発できます。Visual Studio 2005(VS 2005)によるデータベース・プログラミングは生産性が高いので,プログラミングが楽しくなりますね。

図1●これまでに作成したデータベース・プログラムのイメージ
図1●これまでに作成したデータベース・プログラムのイメージ

 しかし,欠点もあります。ネットワーク経由で外部プログラムがデータベースにアクセスするケースでは,パフォーマンス面に不安があります。いかに高速なネットワークだとしても,多数のクライアントPCがネットワーク経由で大量のデータを取得,更新する処理を同時に実行すれば,当然パフォーマンスは落ちます。データベースをゴチャゴチャと更新する処理は,本来サーバー側で実行すればよい性質のものです。

 図2は,サーバー側にプログラムを配置するアプリケーションの概念図です。サーバー側にデータベースにアクセスするプログラムBを置き,クライアント側のプログラムAがデータベースに関する処理をプログラムBに委託します。テーブルを更新する処理を,サーバー側に配置したプログラムに任せてしまえば,パフォーマンスは向上します。

図2●プログラムを二つにわけて,データベース・アクセス処理のプログラムは,サーバー側で実行するようにする
図2●プログラムを二つにわけて,データベース・アクセス処理のプログラムは,サーバー側で実行するようにする

 しかしこの方法でも,プログラムBは,データベースに対して外部プログラムであることに変わりありません。同一サーバー上で動作しているとはいえ,データベース・サーバーとプログラムBは別々の処理として実行され,両者の間には通信が発生します。また,複数のクライアントの要求を受け,データベースにアクセスして,結果を返す処理を自前で作成することはなかなか困難です。

 これらの問題を解決するために,データベースには古くから「ストアド・プロシジャ」という仕組みが用意されています(図3)。

図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●貸し出し処理の流れ
図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()
リスト1●ADO.NETを使ったトランザクション処理コード(抜粋)

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
リスト2●SQL文は事前に定義する