Go to Qiita Advent Calendar 2024 Top
LoginSignup
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@omizo5353

SQL内でループ処理を実装してみよう（CURSORについて）

Posted at

本記事について

業務にてストアドプロシージャを作成した際に、CURSORについて触れたので記事に起こしてみました。
SQLでループを使ってみよう！

対象者

・SQLでループ処理を実装したい人
・CURSORの使い方を知りたい人

環境

・SQLServer2022
・SQLServer Management Studio(SSMS)

実際に当記事の内容について検証する際は、汚れてもいい環境で行ってください。

1. 「CURSOR」って何？

「CURSOR」はSQLの機能の一つです。
CURSORについて一番わかりやすいと感じた記事の方より引用させていただくと、

データの「 検索条件 」と「現在位置」を保持して、複数の検索結果を1件ずつ処理するための仕組み

というものになります。

流れとして、

  1. SELECTでデータを取得する（検索条件）
  2. 取得したデータを上から1行ずつたどっていく（現在位置）

といった感じになります。

2. 実装してみよう

何かシナリオがあったほうが理解に繋がるかもしれないので、
青果店の在庫管理をしているシチュエーションを思い浮かべてください。
シナリオは以下の通りです。

シナリオ
この青果店では果物の在庫を確認して、数が少ない果物を補充予定としてリストアップしていきます。
・「在庫数」が「発注点」を下回っているものを補充対象とします。
・「補充対象の果物」と、補充する際の「発注数量」を確認できるようにします。
・各果物の「発注点」の数を倍にしたものを「発注数量」として設定します。

2-1.準備

はじめに必要なテーブルとデータを用意していきます。

◎果物在庫テーブル【FRUITS_STOCK】

名の通り果物の在庫を管理しているテーブルを用意し、
あらかじめパターン分けしたデータを入れておきます。

カラムについては以下を用意します。

物理名 論理名 説明
STOCK_ID 在庫ID 自動生成される主キー。
FRUIT_NAME 果物の名前 果物の名前が入る。
STOCK_COUNT 在庫数 果物の在庫数が入る。
ORDER_POINT 発注点 発注点となる数が入る。
在庫数 < 発注点 となっていれば入荷を行う。
FRUITS_STOCK
-- 果物在庫テーブル作成
CREATE TABLE FRUITS_STOCK (
    STOCK_ID	INT IDENTITY PRIMARY KEY,
    FRUIT_NAME	NVARCHAR(100) NOT NULL,
    STOCK_COUNT INT NOT NULL,
    ORDER_POINT INT NOT NULL
);

-- 作成したテーブルにデータを挿入
INSERT INTO FRUITS_STOCK (
	FRUIT_NAME, STOCK_COUNT, ORDER_POINT
)
VALUES 
    (N'りんご', 50, 10),   -- 在庫数が発注点を上回る
    (N'バナナ', 3, 5),     -- 在庫数が発注点を下回る
    (N'みかん', 30, 10),   -- 在庫数が発注点を上回る
    (N'ぶどう', 8, 8),     -- 在庫数が発注点と等しい
    (N'梨', 5, 7);         -- 在庫数が発注点を下回る

SELECTでこのような結果になります。
cursor_img1.png

◎補充予定テーブル【RESTOCK_SCHEDULE】

補充がな必要な果物の名前と数量を管理するテーブルです。
カラムについては以下を用意します。

物理名 論理名 説明
RESTOCK_ID 補充ID 自動生成される主キー。
FRUIT_NAME 果物の名前 補充対象の果物の名前が入る。
ORDER_QUANTITY 発注数量 補充の際に発注する数量が入る。
RESTOCK_SCHEDULE
CREATE TABLE RESTOCK_SCHEDULE (
    RESTOCK_ID INT IDENTITY PRIMARY KEY,
    FRUIT_NAME NVARCHAR(100),
    ORDER_QUANTITY INT NOT NULL,
);

2-2.実装内容

処理の流れについては以下のようになります。

  1. 果物在庫テーブルに入っているデータを取得する。
  2. データを1行ずつ確認する。
  3. 現在位置の果物が「在庫数」<「発注数量」であれば、補充予定とする。
  4. 補充予定となった果物の「発注数量」は、「発注点」x2とする。
  5. 補充予定テーブルに「果物の名前」、「発注数量」を登録する。
  6. データが確認できなくなれば、処理を終了する。
SQL
-- 変数の宣言
DECLARE @CUR_FRUIT_NAME      NVARCHAR(30)
DECLARE @CUR_STOCK_COUNT     INT
DECLARE @CUR_ORDER_POINT     INT

-- カーソルの宣言
DECLARE FRUITS_STOCK_CURSOR CURSOR FOR
SELECT
     FRUIT_NAME
    ,STOCK_COUNT
    ,ORDER_POINT
FROM
    FRUITS_STOCK

-- カーソルをオープン
OPEN FRUITS_STOCK_CURSOR;

-- カーソルからデータを取得
FETCH NEXT FROM 
    FRUITS_STOCK_CURSOR 
INTO 
     @CUR_FRUIT_NAME
    ,@CUR_STOCK_COUNT
    ,@CUR_ORDER_POINT

WHILE @@FETCH_STATUS = 0
BEGIN
    -- 在庫数が発注点を下回っているか
    IF @CUR_STOCK_COUNT < @CUR_ORDER_POINT
    BEGIN
        -- 補充予定テーブルに登録
        INSERT INTO RESTOCK_SCHEDULE (
             FRUIT_NAME
            ,ORDER_QUANTITY
        ) VALUES (
             @CUR_FRUIT_NAME    -- 果物在庫.果物の名前
            ,@CUR_ORDER_POINT*2 -- 果物在庫.発注点x2
        );
    END;

    -- 次のレコードを取得
    FETCH NEXT FROM 
        FRUITS_STOCK_CURSOR 
    INTO 
         @CUR_FRUIT_NAME
        ,@CUR_STOCK_COUNT
        ,@CUR_ORDER_POINT
END

-- カーソルをクローズ
CLOSE FRUITS_STOCK_CURSOR

-- カーソルを解放
DEALLOCATE FRUITS_STOCK_CURSOR

3. 実装内容の解説

変数の定義

最初に定義しているものは、ループの中で使用する変数（イテレータ変数）です。

変数の宣言
-- 変数の宣言
DECLARE @CUR_FRUIT_NAME      NVARCHAR(30)
DECLARE @CUR_STOCK_COUNT     INT
DECLARE @CUR_ORDER_POINT     INT

Pythonでいうforの隣にいるやつにあたる役割を担います。

Pythonのfor
for fruit in fruits:
    print(fruit)  #fruit←コレ

カーソルの宣言とデータ取得

次に、カーソル本体の宣言と、果物在庫テーブルよりデータの抽出を行っています。
（カーソルを宣言する際は、「@」は不要です。）

カーソルの宣言
-- カーソルの宣言
DECLARE FRUITS_STOCK_CURSOR CURSOR FOR
SELECT
     FRUIT_NAME
    ,STOCK_COUNT
    ,ORDER_POINT
FROM
    FRUITS_STOCK

DECLAREしている行の下にはSELECT文がありますね。
このSELECTで取得した結果のセットが、丸ごとFRUITS_STOCK_CURSORに入ります。

カーソルオープン

次に、カーソルをオープンして取得した結果セットにアクセスできるようにします。

カーソルオープン
-- カーソルをオープン
OPEN FRUITS_STOCK_CURSOR;

カーソル内のデータを取得し、変数に格納

次に、オープンしたカーソルからデータを取得し、一番最初に定義していた変数に格納します。

カーソルからデータを取得
-- カーソルからデータを取得
FETCH NEXT FROM 
    FRUITS_STOCK_CURSOR 
INTO 
     @CUR_FRUIT_NAME
    ,@CUR_STOCK_COUNT
    ,@CUR_ORDER_POINT

他のプログラミング言語では、ループ開始時点でイテレータ変数にはデータが格納されています。
しかし、CURSORについては、pythonのforのように現在位置が自動で移動しないため、
「現在位置を自分で移動させる」
必要があります。

Point
CURSOR定義時、現在位置はデータの外にある
初回は自力で1行目のデータを取得する

ループ開始

次に本処理を行うループに入ります。

ループ開始
WHILE @@FETCH_STATUS = 0
BEGIN

WHILE条件の@@FETCH_STATUS = 0の部分についてです。
@@FETCH_STATUSとは、カーソルからデータを取得（FETCH NEXT ~）した際のステータスを保持するグローバル変数です。
ループの条件は、「ステータスの値が0」となっているため、この状態のときはループを継続します。

Point
ループの継続 = @@FETCH_STATUSが0である = 次の行にデータがある

データを取得できない、すなわち次の行に現在位置をずらしてもデータがないとき、
@@FETCH_STATUSは0ではなくなる→ループが終了します。

@@FETCH_STATUSの戻り値については公式ドキュメントにて以下のように記載されてます。

戻り値 説明
0 FETCH ステートメントは正常に実行されました。
-1 FETCH ステートメントが失敗したか、または行が結果セットに収まりません。
-2 フェッチした行がありません。
-9 カーソルはフェッチ操作を実行しません。

本処理部分

次に、メインの処理を行う個所を見ていきます。

本処理部分
    -- 在庫数が発注点を下回っているか
    IF @CUR_STOCK_COUNT < @CUR_ORDER_POINT
    BEGIN
        -- 補充予定テーブルに登録
        INSERT INTO RESTOCK_SCHEDULE (
             FRUIT_NAME
            ,ORDER_QUANTITY
        ) VALUES (
             @CUR_FRUIT_NAME    -- 果物在庫.果物の名前
            ,@CUR_ORDER_POINT*2 -- 果物在庫.発注点x2
        );
    END;

@CUR_STOCK_COUNT、@CUR_ORDER_POINT、@CUR_FRUIT_NAMEの変数を使用しています。
各変数にはFETCH NEXTした際のデータが入っています。

ここで一度シナリオの内容を確認してみましょう。

シナリオ
この青果店では果物の在庫を確認して、数が少ない果物を補充予定としてリストアップしていきます。
・「在庫数」が「発注点」を下回っているものを補充対象とします。
・「補充対象の果物」と、補充する際の「発注数量」を確認できるようにします。
・各果物の「発注点」の数を倍にしたものを「発注数量」として設定します。

「在庫数」が「発注点」を下回っているものを補充対象としている部分は以下です。

IF @CUR_STOCK_COUNT < @CUR_ORDER_POINT

「補充対象の果物」と、補充する際の「発注数量」を確認できる、
各果物の「発注点」の数を倍にしたものを「発注数量」として設定している部分は以下となります。

    BEGIN
        -- 補充予定テーブルに登録
        INSERT INTO RESTOCK_SCHEDULE (
             FRUIT_NAME
            ,ORDER_QUANTITY
        ) VALUES (
             @CUR_FRUIT_NAME    -- 果物在庫.果物の名前
            ,@CUR_ORDER_POINT*2 -- 果物在庫.発注点x2
        );
    END

次のレコードを取得

ループのスコープの末端になります。

次のレコードを取得
    -- 次のレコードを取得
    FETCH NEXT FROM 
        FRUITS_STOCK_CURSOR 
    INTO 
         @CUR_FRUIT_NAME
        ,@CUR_STOCK_COUNT
        ,@CUR_ORDER_POINT
END

ここで気付いた方もいらっしゃると思いますが、このFETCH NEXT~INTO~部分、

カーソルオープン ~ データ取得
-- カーソルをオープン
OPEN FRUITS_STOCK_CURSOR;

-- カーソルからデータを取得
FETCH NEXT FROM 
    FRUITS_STOCK_CURSOR 
INTO 
     @CUR_FRUIT_NAME
    ,@CUR_STOCK_COUNT
    ,@CUR_ORDER_POINT

カーソルオープンしてすぐ、データを取得したときと全く同じ文を書いてます。

Point
FETCH NEXTでデータを取得する際は、ループの外側、内側共に同じものを書けばよい

2回同じものを書くのはめんどくさいですがここは楽ですね。

カーソルクローズ・解放

ループを抜け、カーソルを使用することがなくなったら、クローズと解放（破棄）を行います。

カーソルクローズ・解放
-- カーソルをクローズ
CLOSE FRUITS_STOCK_CURSOR

-- カーソルを解放
DEALLOCATE FRUITS_STOCK_CURSOR

カーソルをオープンしたままだと、メモリリークやアプリケーションのパフォーマンス低下などに繋がるため、記述を忘れないようにしましょう。

4. SQL実行結果

実装したSQLを実行し、結果を見てみましょう。

実行前の状態

cursor_img2.png
上のデータが表示されている方のテーブルが「果物在庫テーブル（FRUITS_STOCK）」、
下のデータが空のテーブルが「補充予定テーブル（RESTOCK_SCHEDULE）」です。

「在庫数（STOCK_COUNT）」が「発注点(ORDER_POINT)」を下回っている果物は
「バナナ」と「梨」の二つです。
補充予定テーブルに追加するときに、
それぞれの発注点の数 x2が発注数量として設定されているか確認してみましょう。

実行後の状態

cursor_img3.png

「バナナ」の発注数量（ORDER_QUANTITY）が、10（発注点：5 x2）
「梨」の発注数量（ORDER_QUANTITY）が、14（発注点：7 x2）
となっていることを確認できました。

（STOCK_IDのカラムは今回要らなかったですね...）

5.まとめ

実装内容のスクショに直接ポイントを書き込んでみました。
cursor_img4.png

SQLを組み立てる際は、行数が多くなりがちなので、
このようにブロックごとに分けることを意識して実装するとわかりやすいです。

最後に

この記事では、CURSORの使い方や注意点について解説しました。最後まで読んでいただき、ありがとうございます。この記事が少しでも皆様のお役に立てれば幸いです。今後のデータベース操作において、今回の内容が役立つことを願っています。

また、自分でも思い出しながら書いた部分も含まれているので、
不足している部分や誤っている個所がありましたらコメント頂けると幸いです。

参考

カーソル【DB】とは |「分かりそう」で「分からない」でも「分かった」気になれるIT用語辞典

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?