1. はじめに

ストアドプロシージャを利用する場合と、SQL文を手打ちで処理した場合の比較を記載しています。

ストアドプロシージャとは、データベース内に保存されるSQL群で、プログラムファイルのような役割を果たします。これにより、データベースサーバーへのアプリケーション側からのデータ送信量が減少するなどのいくつかのメリットがあります。

2. 実装と確認

2.1 事前作業

まずはじめにテーブルを作成し、その後にテーブルが空であることを確認します。以下がそれぞれのステップです。

ステップ1: テーブルの作成
事前作業として、ドリンクの情報を管理するためのテーブル「Drinks」を作成します。以下がそのSQLです。

CREATE TABLE Drinks (
    DrinkID NUMBER NOT NULL,
    Name VARCHAR2(255),
    Type VARCHAR2(100),
    Price NUMBER(5,2),
    Available NUMBER(1) -- 在庫状況（0=なし、1=あり）
);

ステップ2: データの確認
テーブルを作成しただけでは、当然ながら何もデータが入っていません。以下のクエリを使用して現在の状態を確認します。

-- 追加前のデータを確認
SELECT * FROM Drinks;

以下の画像は、上記SQLの実行した際の画面です。テーブルが作成された直後であることを示しています。この段階では、Drinksテーブルには何もデータが挿入されていません。

2.2 手動でのテーブルに対するインサート

次に、SQL文を手動で入力してデータをテーブルに追加する方法を説明します。

ステップ1: データの手動追加
以下のSQL文を実行して、Drinksテーブルにデータを追加します。

INSERT INTO Drinks (DrinkID, Name, Type, Price, Available)
VALUES (1, 'カフェラテ', 'コーヒー', 350, 1);

ステップ2: データの確認
追加した情報がテーブルに登録されていることを確認します。

SELECT * FROM Drinks;

以下は、上記SQLでのインサート操作と、その後のデータ確認の結果を示しています。

2.3 ストアドプロシージャの登録

次に、データを自動的に挿入するためのストアドプロシージャを定義して登録します。ここでは、AddDrinkという名前のストアドプロシージャを作成します。

ステップ1: ストアドプロシージャの定義と登録
①ストアドプロシージャを定義登録します。以下は、AddDrinkというストアドプロシージャの作成です。
このストアドプロシージャは、引数として渡されたデータをDrinksテーブルに挿入する機能を持っています。後ほどストアドプロシージャを実行するSQLを載せているのでそれを見たらイメージつくかと思います。

CREATE OR REPLACE PROCEDURE AddDrink (
    DrinkID IN NUMBER, 
    Name IN VARCHAR2, 
    Type IN VARCHAR2,
    Price IN NUMBER,
    Available IN NUMBER
) AS
BEGIN
    INSERT INTO Drinks (DrinkID, Name, Type, Price, Available)
    VALUES (DrinkID, Name, Type, Price, Available);
END;
/

ストアドプロシージャが正しく登録できると以下が返ります。

Procedure ADDDRINKがコンパイルされました

以下の画像は、ストアドプロシージャがSQL Developerで正常に登録された際の画面です。
これで、データの挿入操作を自動化する準備が整った状態です。

2-4 ストアドプロシージャの実行①

「2-3」で作成したストアドプロシージャを使用して、実際にデータを挿入しいます。

ステップ1: ストアドプロシージャの実行
以下のSQL文を使用して、AddDrinkストアドプロシージャを実行します。
Insert文を手で打つ場合より記載量が少ないです。そのため、アプリケーションやクライアントからDBへの通信量が少なくて済みます。

BEGIN
    AddDrink(2, 'アイスティー', '紅茶', 300, 1);
END;

このSQL文が正しく実行されると、以下のメッセージが返ります。

PL/SQLプロシージャが正常に完了しました。

ステップ2: データの確認
ストアドプロシージャを使用してデータが正しく挿入されたことを確認するために、以下のクエリを実行します。

SELECT * FROM Drinks;

以下の画像は、SQL Developerでストアドプロシージャを実行してデータが正しく挿入されたことを確認するための画面です。
このようにして、ストアドプロシージャを使用して簡単にデータを挿入し、挿入されたデータを確認することができます。

2-5 ストアドプロシージャの実行②

次に、もう少し複雑なストアドプロシージャを紹介します。このストアドプロシージャは、IDが存在する場合は情報を更新し、存在しない場合は新しいレコードを登録します。

ステップ1: ストアドプロシージャの定義と登録
ストアドプロシージャを定義登録をします。以下は、ManageDrinkというストアドプロシージャの作成です。
ざっくり言うと、IDが存在していたら情報の更新で、存在しないと登録です。

CREATE OR REPLACE PROCEDURE ManageDrink (
    DrinkID IN NUMBER, 
    Name IN VARCHAR2, 
    Type IN VARCHAR2,
    Price IN NUMBER,
    Available IN NUMBER
) AS
BEGIN
    -- レコードの存在を確認するための変数cntを宣言
    DECLARE
        cnt NUMBER;
    BEGIN
        -- DrinkIDが一致するレコードがあるかをカウントし、cntに格納
        SELECT COUNT(*) INTO cnt FROM Drinks WHERE DrinkID = ManageDrink.DrinkID;

        IF cnt > 0 THEN
            -- 既存のレコードがある場合、情報を更新
            UPDATE Drinks
            SET Name = ManageDrink.Name, Type = ManageDrink.Type, Price = ManageDrink.Price, Available = ManageDrink.Available
            WHERE DrinkID = ManageDrink.DrinkID;
        ELSE
            -- レコードが存在しない場合、新しいレコードを挿入
            INSERT INTO Drinks (DrinkID, Name, Type, Price, Available)
            VALUES (ManageDrink.DrinkID, ManageDrink.Name, ManageDrink.Type, ManageDrink.Price, ManageDrink.Available);
        END IF;
    END;
END;
/

ストアドプロシージャが正しく登録できると以下が返ります。

Procedure MANAGEDRINKがコンパイルされました

次に、登録されていない抹茶を登録します。

BEGIN
    ManageDrink(3, '抹茶ラテ', '抹茶', 400, 1);
END;

以下のメッセージが返ります。

PL/SQLプロシージャが正常に完了しました。

そして、例のごとくselect文の実行

SELECT * FROM Drinks;

以下の画像は、SQL Developerでストアドプロシージャを実行してデータが正しく挿入されたことを確認するための画面です。

最後に登録済みの抹茶を値段が400→450に更新されたとしてストアドプロシージャを実行します。

BEGIN
    ManageDrink(3, '抹茶ラテ', '抹茶', 450, 1);
END;

以下のメッセージが返ります。

PL/SQLプロシージャが正常に完了しました。

例のごとくselect文の実行

SELECT * FROM Drinks;

以下の画像は、SQL Developerでストアドプロシージャを実行してデータが正しく更新されたことを確認するための画面です。