データベース千夜一夜第30回

ストアドプロシージャ(4)~繰り返し処理 長谷川裕行
有限会社 手國堂

繰り返し処理(1)~Whileの基本機能

制御構造には、前回紹介した条件判断と分岐のIf~Elseの他、繰り返し処理を行うWhileがあります。まずは基本を知っておきましょう。


- 条件が「真」の間繰り返す -

Whileの書式は以下のようになります。

  While <論理式>
  Begin
   <処理>
  End

<論理式>とは真(True)または偽(False)を返す式で、一般には表1に示す比較演算子を用いて記述します。また、比較演算子を用いた式同士を表2に示す論理演算子で結ぶこともできます。比較演算子も論理演算子も、式を評価した結果が正しければTrue、正しくなければFalseを返します。

Whileは、<論理式>がTrueである間、Begin~End間の<処理>を繰り返します。<処理>を1回実行するたびに<論理式>を評価し、その結果が偽(False)になった時点で繰り返しをやめて、Endの次の処理に移ります。これを(ループを)「抜ける」あるいは「脱出する」と表現します。

表1:比較演算子
演算子 意味
= 記号の左右の値が等しい
> 記号の左が右より大きい
< 記号の左が右より小さい
>= 記号の左が右以上
<= 記号の左が右以下
<> 記号の左右の値が等しくない
!= 記号の左右の値が等しくない★
!< 記号の左が右より小さくない(記号の左が右以上)★
!> 記号の左が右より大きくない(記号の左が右以下)★
【注意】★マークを付けた「!」を使った演算子はSQL-92に準拠していません

表2:論理演算子
演算子 意味
AND 双方の条件式がTrueの場合にTrueを返す
OR どちらかまたは双方の条件式がTrueの場合にTrueを返す
NOT 論理値を反転(TrueをFalseに、FalseをTrueに)する


- 単価を段階的に割り引く -

Whileを使った単純な例を紹介しておきましょう。

販売単価が8,000円を超える高額商品が増えてきたため、商品の価格を見直すことになりました。これらの高額商品を値下げしようと思うのですが、どの程度値下げすればよいか一律には決められません。

まず、以下のSQLを実行すれば、販売単価が8,000円以上の商品が11件あることが分かります。

  SELECT COUNT(商品ID) FROM 商品_mr WHERE 販売単価 >=8000

そこで、これら高額商品の販売単価を5%ずつ減額していき、8,000円以上の高額商品が5件以下になるよう調整するとどうなるか──シミュレーションすることになりました。

上記のようなシミュレーションを実行するには、以下のような処理が必要になります。

まず、テーブル「商品_mr」を直接書き換えてはいけないので、ダミーのテーブル「商品_dmy」を使うことにします。

(1)繰り返し処理開始

(2)販売単価が8,000円を超える商品が5件になったら処理をやめる -- 脱出条件

(3)販売単価が8,000円を超える商品の販売単価を5%引きにする

(4)繰り返し処理終わり


- SELECT文を条件にする -

上のような処理を実現するには、以下のようなソースを記述します。プロシージャ名を“PriceDown1”としています(ex04.sql)。

Create Procedure PriceDown1

As

While (SELECT COUNT(商品ID) FROM 商品_dmy WHERE 販売単価 >=8000) > 5

Begin

UPDATE 商品_dmy SET 販売単価 = 販売単価 * 0.95 WHERE 販売単価 >=8000

End

Whileに続く以下の部分が繰り返しの条件です。

  (SELECT COUNT(商品ID) FROM 商品_dmy WHERE 販売単価 >=8000) > 5

SELECT命令にCOUNT集計関数を使って、販売単価が8000以上のレコード件数(「商品ID」に値の入っている件数)を取得し、それが5より多いときだけ処理を繰り返します。アンダーラインの箇所の「> 5」が比較演算子を使った条件式で、この評価結果がTrueである間だけ続く処理が繰り返されます。

その直前にある「WHERE 販売単価 >=8000」という条件式は、SELECT命令でレコード件数を取得するためのものです。

なお、While命令にSELECT命令によるSQL文を与える場合は、SELECT文全体(上の例では“>=8000”まで)を( )で囲まなければなりません。

【注意】
冒頭にも書いたように、この処理はテーブルの値(「販売単価」フィールドの値)を書き換えるため、必ず「商品_dmy」テーブルを使ってください。


- 更新処理を繰り返す -

繰り返される処理は、Begin~Endで囲んだ以下のSQL文です。

  UPDATE 商品_dmy SET 販売単価 = 販売単価 * 0.95 WHERE 販売単価 >=8000

処理が1行で済む場合には特にBegin~Endで囲む必要はありませんが、ここでは分かりやすくするために囲んでいます。

このSQL文は、単純に

  販売単価が8000円以上のレコードに対して
  一律に販売単価を5%引き(0.95倍)する

という処理を実行します。

この処理を、最初の条件に設定した「販売単価が8,000円を超える商品が5件より大きい間」繰り返すことになります。


- プロシージャの実行 -

実行には、EXECUTE命令を使います(ex05.sql)。

  EXECUTE PriceDown1

とすれば、クエリアナライザの「メッセージ」ペインには画面2のように延々と処理件数が表示され、やがて処理は終わります。UPDATE命令で5%の値下げ処理を繰り返しているため、処理件数が次第に減っているのが分かります。レコードが5件以下になれば処理は終わるため、(6件処理されました)のメッセージが何度か表示されて処理は終了します。

このようにWhile命令は、SQL文による処理を何度も繰り返したいときに用います。レコードを1件ずつ処理するのではなく、レコード全体(データセット)に対する処理を繰り返す――という点に注意しましょう。

テーブルからレコードを1件ずつ取り出して処理を行うなら、アプリケーションのレベルで繰り返し構造(プログラミング言語のForやWhile)を使うことになります。ストアドプロシージャの繰り返し処理は、UPDATEやINSERTなどテーブルの内容を一括して書き換えるような処理に対して用います。




トップページ
処理構造を整理する
繰り返し処理(1)~Whileの基本機能
条件が「真」の間繰り返す
単価を段階的に割り引く
SELECT文を条件にする
更新処理を繰り返す
プロシージャの実行
繰り返し処理(2)~脱出と継続
あとがき
Copyright © GrapeCity inc. All rights reserved.