Oracle ストアドプロシージャを使ったデータ操作

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

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

## 2. 実装と確認

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

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

```sql
CREATE TABLE Drinks (
    DrinkID NUMBER NOT NULL,
    Name VARCHAR2(255),
    Type VARCHAR2(100),
    Price NUMBER(5,2),
    Available NUMBER(1) -- 在庫状況（0=なし、1=あり）
);
```
***ステップ2: データの確認***
テーブルを作成しただけでは、当然ながら何もデータが入っていません。以下のクエリを使用して現在の状態を確認します。
```sql
-- 追加前のデータを確認
SELECT * FROM Drinks;
```

以下の画像は、上記SQLの実行した際の画面です。テーブルが作成された直後であることを示しています。この段階では、Drinksテーブルには何もデータが挿入されていません。
![001.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/322881/a4dec856-7859-62df-5366-e52e7a36831a.png)


#### 2.2 手動でのテーブルに対するインサート
次に、SQL文を手動で入力してデータをテーブルに追加する方法を説明します。

***ステップ1: データの手動追加***
以下のSQL文を実行して、Drinksテーブルにデータを追加します。
```sql
INSERT INTO Drinks (DrinkID, Name, Type, Price, Available)
VALUES (1, 'カフェラテ', 'コーヒー', 350, 1);
```

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

以下は、上記SQLでのインサート操作と、その後のデータ確認の結果を示しています。
![002.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/322881/d0791f39-3ee3-01aa-f8f7-e0c5cf53ee02.png)


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

***ステップ1: ストアドプロシージャの定義と登録***
①ストアドプロシージャを定義登録します。以下は、**AddDrink**というストアドプロシージャの作成です。
このストアドプロシージャは、引数として渡されたデータをDrinksテーブルに挿入する機能を持っています。後ほどストアドプロシージャを実行するSQLを載せているのでそれを見たらイメージつくかと思います。
```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で正常に登録された際の画面です。
これで、データの挿入操作を自動化する準備が整った状態です。
![003.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/322881/c8744ac7-7f59-1a95-7ef4-666633f9e2a9.png)


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

***ステップ1: ストアドプロシージャの実行***
以下のSQL文を使用して、AddDrinkストアドプロシージャを実行します。
Insert文を手で打つ場合より記載量が少ないです。そのため、アプリケーションやクライアントからDBへの通信量が少なくて済みます。
```sql
BEGIN
    AddDrink(2, 'アイスティー', '紅茶', 300, 1);
END;
```
このSQL文が正しく実行されると、以下のメッセージが返ります。
```
PL/SQLプロシージャが正常に完了しました。
```

***ステップ2: データの確認***
ストアドプロシージャを使用してデータが正しく挿入されたことを確認するために、以下のクエリを実行します。
```sql
SELECT * FROM Drinks;
```
以下の画像は、SQL Developerでストアドプロシージャを実行してデータが正しく挿入されたことを確認するための画面です。
このようにして、ストアドプロシージャを使用して簡単にデータを挿入し、挿入されたデータを確認することができます。

![005.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/322881/e2f01d62-c3dc-f1c4-6d50-e38f890ad5fe.png)

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

***ステップ1: ストアドプロシージャの定義と登録***
ストアドプロシージャを定義登録をします。以下は、**ManageDrink**というストアドプロシージャの作成です。
ざっくり言うと、IDが存在していたら情報の更新で、存在しないと登録です。
```sql
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;
/
```
ストアドプロシージャが正しく登録できると以下が返ります。

```sql
Procedure MANAGEDRINKがコンパイルされました
```
![006.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/322881/02dae0ee-eea9-f08b-a660-1a8e59fe83e4.png)

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

```sql
BEGIN
    ManageDrink(3, '抹茶ラテ', '抹茶', 400, 1);
END;
```
以下のメッセージが返ります。
```
PL/SQLプロシージャが正常に完了しました。
```
![007.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/322881/46bc1e5f-85ce-d6a0-b02a-cee39e89cf29.png)

そして、例のごとくselect文の実行
```sql
SELECT * FROM Drinks;
```
以下の画像は、SQL Developerでストアドプロシージャを実行してデータが正しく挿入されたことを確認するための画面です。

![008.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/322881/2f4f60f2-6da4-2867-e445-92a0691bc88a.png)


最後に登録済みの抹茶を値段が400→450に更新されたとしてストアドプロシージャを実行します。
```sql
BEGIN
    ManageDrink(3, '抹茶ラテ', '抹茶', 450, 1);
END;
```
以下のメッセージが返ります。
```
PL/SQLプロシージャが正常に完了しました。
```
![009.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/322881/7cb64c66-d9a7-c589-15b8-3714e426389d.png)

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



![010.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/322881/6170447b-e136-0bea-e5c8-704c9fa894d3.png)