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でストアドプロシージャを実行してデータが正しく更新されたことを確認するための画面です。
