検索用の結合テーブルをトリガプロシージャで作る

## 要件

【注文テーブル】、【商品テーブル】、【顧客テーブル】を結合して、情報を検索する。

- 検索するのは、注文コード、顧客名称、商品名称、商品単価、注文数、合計金額(商品単価X注文数)
- 【注文テーブル】の商品コードは、【商品テーブル】に必ず存在する
- 【注文テーブル】の顧客コードは、【顧客テーブル】に存在しない場合がある<br>
その場合は、顧客名称として「ゲストユーザ」と表示する

## 方針

- 3つのテーブル □ を用いて、検索結果 ■ を作成する。 □ + □ + □ → ■  
料理で例えると、材料を元に、料理を作ります。・・・尚、材料は使っても減りません。ここは料理と違う所ですね。
  - 材料 ： 【注文テーブル】のデータと、それに付随する2つのテーブル情報
  - 料理 ： 3つのテーブルを結合して出来た検索データ
- 【検索テーブル】 ■ を作る方法は2種類あり、どちらか片方を選ぶ  
**① 検索する時にリアルタイムに生成する**  
→ 「料理を出してください」と言われてから料理を作る形式。時間が掛かる。  
**② 検索する前に予め作っておく**   
→ 作り置きの料理を出す形式。「料理を出してください」と言われたら出すだけなので早い。


上記の ① と ② について、もう少し詳しく書きます。

#### ① 検索する時にリアルタイムに生成する

`JOIN` を用いる方法で、クエリさえ書けば良いので簡単です。料理の話でいうと、「材料があればあるだけ料理を作る」というレシピだけ書いてある状態。
料理を作るのは「料理を出して」と言われた時です。材料（注文テーブルのデータ）があるだけ、料理（検索データ）を作る訳です。これは、注文テーブルのデータ数が多いと遅くなる可能性があります。

#### ② 検索する前に予め作っておく

予め【検索データテーブル】を作っておく方法です。【注文テーブル】にデータの更新がある度に、トリガで検索データを生成して【検索データテーブル】に`INSERT`します。これが料理で言うところの「予め作り置きする」という事です。「料理を出して」と言われたら作り置きをそのまま出します。<br>
※ トリガとは、INSERTなどのイベントで実行されるストアドプロシージャの事です。トリガプロシージャとも言います。

**予め作る手間があるので面倒**ですが、**料理を出すのは早い**です。そして実際の業務では、この早さは結構大事だったりします。

## 実装してみる

① の方法は、下記のようなものになると思います。

```sql
SELECT ORDER_CODE, 
       NVL2(CUSTOMER_NAME, CUSTOMER_NAME, 'ゲストユーザ') AS CUSTOMER_NAME,
       GOODS_NAME, GOODS_PRICE, ORDER_AMT, ORDER_AMT * GOODS_PRICE
  FROM TBL_ORDER
  JOIN TBL_CUSTOMER
    ON TBL_ORDER.CUSTOMER_CODE = TBL_CUSTOMER.CUSTOMER_CODE
  JOIN TBL_GOODS
    ON TBL_ORDER.GOODS_CODE = TBL_GOODS.GOODS_CODE
;
```

しかし、① は既に提出して改善を求められたとの事なので、② の方法で実装します。

### テーブル作成

まずは、【注文テーブル】、【商品テーブル】、【顧客テーブル】の3つのテーブルと、【検索データテーブル】を作ります。

SQLは手書きしても良いですが、今回は [Oracle Live SQL](https://livesql.oracle.com/apex/) で作ってみました。（アカウント登録が必要です）
![スクリーンショット 2020-11-20 17.15.36.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/389476/3d34eaa5-b8b5-c6eb-a098-4a734f46ce05.png)
左側メニューの Schema → 右上の + Create Database Object を押して、Table を選択します。後は適当に情報を入れると下記のSQLが出来ました。（少し書き換えてます。今回の要件で指定されている`char`型および`number`型の長さが選べなかったので）

```sql
-- 商品テーブル
create table TBL_GOODS (
    GOODS_CODE char(10) not null constraint tbl_goods_pk primary key,
    GOODS_NAME varchar2(20) not null,
    GOODS_PRICE number(5) not null
);
/
-- 顧客テーブル
create table TBL_CUSTOMER (
    CUSTOMER_CODE char(10) not null constraint tbl_customer_pk primary key,
    CUSTOMER_NAME varchar2(20) not null
);
/
-- 注文テーブル
create table TBL_ORDER (
    ORDER_CODE varchar2(10) not null constraint tbl_order_pk primary key,
    CUSTOMER_CODE char(10),
    GOODS_CODE char(10),
    ORDER_AMT number(5)
);
/
```

テストデータも入れておきます。注文データはまだ入れません。

```sql
-- 商品テーブル
INSERT INTO TBL_GOODS VALUES ('GOODS_0001', '商品名その1', 1980);
INSERT INTO TBL_GOODS VALUES ('GOODS_0002', '商品名その2', 500);
INSERT INTO TBL_GOODS VALUES ('GOODS_0003', '商品名その3', 10000);
INSERT INTO TBL_GOODS VALUES ('GOODS_0004', '商品名その4', 99999);
-- 顧客テーブル
INSERT INTO TBL_CUSTOMER VALUES ('CUSTOMER_1', '竈門炭治郎');
INSERT INTO TBL_CUSTOMER VALUES ('CUSTOMER_2', '竈門禰豆子');
INSERT INTO TBL_CUSTOMER VALUES ('CUSTOMER_3', '我妻善逸');
INSERT INTO TBL_CUSTOMER VALUES ('CUSTOMER_4', '嘴平伊之助');
INSERT INTO TBL_CUSTOMER VALUES ('CUSTOMER_5', '冨岡義勇');
```

同様に、検索データテーブルも作成します。

```sql
create table TBL_ORDER_SEARCH (
    ORDER_CODE varchar2(10) not null constraint tbl_order_search_pk primary key,
    CUSTOMER_NAME varchar2(20),
    GOODS_NAME varchar2(20),
    GOODS_PRICE number,
    ORDER_AMT number,
    ORDER_PRICE number
);
```

5つのカラムは、他のテーブルのカラム定義そのまま持ってきています。また、新しく定義した合計金額カラムについての説明を下に書きます。細かい部分なので、頭に入れなくて大丈夫です。

> 合計金額のカラム名は、注文ごとの金額という意味で `ORDER_PRICE` にしました。`TOTAL_PRICE` などでも良いと思います。
> 商品単価と注文数はそれぞれ仕様に `NUMBER(5)` と書いてありますが、これは「5桁の数字、つまり 99,999 まで入りますよ」という意味です。商品単価 × 注文数である合計金額は、最大で 99,999 × 99,999 = 9,999,800,001 になります。最大でも10桁に収まるので、合計金額の方は `NUMBER(10)` となりました。

検索データテーブルのデータは、注文データがINSERTされた時に、トリガプロシージャで作られる予定です。

ひとまず、これでテーブルは出来ました！

### トリガプロシージャ作成

さて、次に料理を作り置きする仕組みを作ります。

**【注文テーブル】にデータが`INSERT`されたら、検索データを生成して、【検索データテーブル】に`INSERT`するトリガプロシージャを作れば、この課題は完成です**。

「トリガプロシージャ is 何」状態だと思うので、調べてみてください。SQLイベントで起動するストアドプロシージャみたいなものです。

前回と同じく、使えそうな雛形を探してきましょう。「PL/SQL トリガー INSERT」などでググれば出てきます。[例えばこれなど。](https://itsakura.com/sql-trigger)  

INSERTで起動するトリガー雛形は下記のような形になります。これに少しずつ付け加えていきます。

```sql
CREATE OR REPLACE TRIGGER OrderInsertTrigger
    AFTER INSERT
    ON TBL_ORDER FOR EACH ROW
DECLARE
    -- ここで変数を定義する
BEGIN
    IF INSERTING THEN
        -- 注文コード、顧客名称、商品名称、商品単価、注文数のデータを持ってくる
        -- 顧客名称は、データがなかったらゲストユーザにする
        -- 合計金額(商品単価X注文数)を計算する
        -- TBL_ORDER_SEARCH に INSERT する
    END IF;
END;
```

雛形は、あんまり深く考えずに「こういう形なんだな」と思えばOKです。

---

---

---

---

---



ここから、解答編のようなものに入るので、どうしても駄目だったら進んでください。
最初に解答を見ても良いですが、あまり自分の力にならないかもしれません。

また、いわゆる「黒い画面」（コンソール）は、メモ帳などから複数行をコピペ出来ます。SQLは多少フォーマットが崩れても動きます。毎回手打ちしていると大変だと思うので、楽な方法を見つけて下さい。

どうしてもコピペなど出来ない場合、試行錯誤の段階では [Oracle Live SQL](https://livesql.oracle.com/apex/)（アカウント登録が必要） で試したほうが楽かもしれません。

---

---

---

---

---


### 試行錯誤の過程

経験10年以上のプログラマが思考錯誤しているので、学習中の人にとっては「なんでそうなるの」という事が多々出てくると思います...。分からない部分はなるべく一つ一つ調べるか聞くなりして、理解していってください。
（本当に分からないと、何が分からないか分からない状態になりますが、そういう時はお茶でも飲んで脳みそを休めて、少しずつ学習を進めて下さい）

この記事を読みながら課題を解く場合は、各ステップを手元で実行すると理解の助けになると思います。
実際には、ここに書いた以上に色々試しています。が、解答への直進ルートから外れる部分はノイズになるので省きました。

🐳

まずは雛形を、最低限の状態でエラーが出ないようにします。変数はまだ無いので、宣言部（`DECLARE`）は外しました。変数が必要になった時に追加します。

```sql
-- 注文テーブルに INSERT されると起動するトリガ
CREATE OR REPLACE TRIGGER OrderInsertTrigger
  AFTER INSERT
  ON TBL_ORDER FOR EACH ROW
BEGIN
  IF INSERTING THEN
    NULL; -- 何もしない
  END IF;
END;
```

これを実行すると、注文テーブル`TBL_ORDER`にデータがINSERTされた時に起動するトリガが設定されます。この状態ではまだトリガは何もせずに終了します。

ここから肉付けしていきます。

ここで作ろうとしているのは、注文テーブルに1行データが`INSERT`されると、検索データを作成して検索データテーブルに1行`INSERT`する、そういうトリガです。

まずは、シンプルに①で動いたJOINを少し修正してトリガの中に入れてみました。
ここに出てくる `:NEW` は、注文テーブルにINSERTされた行データが入っています。
参考： [第39回 「行トリガーで『:OLD.列名』と『:NEW.列名』を用いた値ベース監査」](https://www.kushim.co.jp/blog/plsql/039)

```sql
CREATE OR REPLACE TRIGGER OrderInsertTrigger
  AFTER INSERT
  ON TBL_ORDER FOR EACH ROW
BEGIN
  IF INSERTING THEN
    -- ① の方法で作った JOIN の SQL を使って、SELECT - INSERT をする。
    INSERT INTO TBL_ORDER_SEARCH (
      ORDER_CODE, CUSTOMER_NAME, GOODS_NAME, 
      GOODS_PRICE, ORDER_AMT, ORDER_PRICE
    )
    SELECT ORDER_CODE, NVL2(CUSTOMER_NAME, CUSTOMER_NAME, 'ゲストユーザ'),
           GOODS_NAME, GOODS_PRICE, ORDER_AMT, ORDER_AMT * GOODS_PRICE
      FROM TBL_ORDER
      JOIN TBL_CUSTOMER
        ON TBL_ORDER.CUSTOMER_CODE = TBL_CUSTOMER.CUSTOMER_CODE
      JOIN TBL_GOODS
        ON TBL_ORDER.GOODS_CODE = TBL_GOODS.GOODS_CODE
     WHERE ORDER_CODE = :NEW.ORDER_CODE
    ;
  END IF;
END;
```


では、データを入れてテストしてみましょう。動くかな？


```sql
INSERT INTO TBL_ORDER VALUES ('ORDER_0001', 'CUSTOMER_1', 'GOODS_0001', 1);
```

何やらエラーが出てきました。

```
ORA-04091: table SQL_DSERLNHVYJPHTAYSQRUBWNWMP.TBL_ORDER is mutating, trigger/function may not see it ORA-06512: at "SQL_DSERLNHVYJPHTAYSQRUBWNWMP.ORDERINSERTTRIGGER", line 3
ORA-06512: at "SYS.DBMS_SQL", line 1721
```

**エラーが出てきたら、エラーに出てきた特徴的な単語を拾って検索**します。
今回なら例えば、「table is mutating, trigger/function may not see it ORA-06512」ですね。

どうやら、`TBL_ORDER` に設定したトリガの中で、`TBL_ORDER` へのクエリを発行することは出来ないようです。

`TBL_ORDER` に `INSERT` したデータは `:NEW` を使えば取れます。欲しいのは、そのデータの顧客コードと商品コードに紐づく `TBL_CUSTOMER` と `TBL_GOODS` の情報です。やり方を変えて、この2つのテーブルに `SELECT` した結果をいったん変数に入れるようにします。


```sql
CREATE OR REPLACE TRIGGER OrderInsertTrigger
    AFTER INSERT
    ON TBL_ORDER FOR EACH ROW
DECLARE
    -- TBL_ORDER のデータは :NEW に入っているので、
    -- TBL_ORDER 以外のテーブルから取得する顧客名、商品名、商品単価を変数にする
    V_CUSTOMER_NAME TBL_CUSTOMER.CUSTOMER_NAME%TYPE;
    V_GOODS_NAME TBL_GOODS.GOODS_NAME%TYPE;
    V_GOODS_PRICE TBL_GOODS.GOODS_PRICE%TYPE;
BEGIN
    IF INSERTING THEN
        -- TBL_CUSTOMER の CUSTOMER_NAME を取得して、
        -- 変数 V_CUSTOMER_NAME に入れる
        SELECT CUSTOMER_NAME
          INTO V_CUSTOMER_NAME 
          FROM TBL_CUSTOMER
         WHERE CUSTOMER_CODE = :NEW.CUSTOMER_CODE;

        -- TBL_GOODS の GOODS＿NAME と GOODS_PRICE を取得して、
        -- 変数 V_GOODS_NAME と、変数 V_GOODS_PRICE に入れる
        SELECT GOODS_NAME, GOODS_PRICE
          INTO V_GOODS_NAME, V_GOODS_PRICE
          FROM TBL_GOODS
         WHERE GOODS_CODE = :NEW.GOODS_CODE;

        -- TBL_ORDER_SEARCH に INSERT 実行
        INSERT INTO TBL_ORDER_SEARCH (
            ORDER_CODE, CUSTOMER_NAME, GOODS_NAME, 
            GOODS_PRICE, ORDER_AMT, ORDER_PRICE
        ) VALUES (
            :NEW.ORDER_CODE,
            NVL2(V_CUSTOMER_NAME, V_CUSTOMER_NAME, 'ゲストユーザ'),
            V_GOODS_NAME, V_GOODS_PRICE, :NEW.ORDER_AMT,
            :NEW.ORDER_AMT * V_GOODS_PRICE
        );
    END IF;
END;
```

またテストしてみます。


```sql
INSERT INTO TBL_ORDER VALUES ('ORDER_0001', 'CUSTOMER_1', 'GOODS_0001', 1);
```

```
1 row(s) inserted.
```

お、行けましたね。ちゃんと検索データテーブル `TBL_ORDER_SEARCH` に登録されたか見てみましょう。

```sql
SELECT * FROM TBL_ORDER_SEARCH;
```

```
ORDER_CODE	CUSTOMER_NAME	GOODS_NAME	GOODS_PRICE	ORDER_AMT	ORDER_PRICE
ORDER_0001	竈門炭治郎	商品名その1	1980	1	1980
```

出来ているようです！
この検索データテーブル（`TBL_ORDER_SEARCH`）に入ったデータが、料理で言う所の「作り置き」です。材料（注文データ）が1つ入ったら、料理（検索データ）を1つ作っておくようになりました。

さて、顧客テーブルに存在しない顧客コードを入れた場合はどうでしょうか。


```sql
-- 以前のデータはいったんクリア
DELETE FROM TBL_ORDER;
DELETE FROM TBL_ORDER_SEARCH;

-- 1行目は顧客テーブル TBL_CUSTOMER に存在する顧客コード
INSERT INTO TBL_ORDER VALUES ('ORDER_0001', 'CUSTOMER_1', 'GOODS_0001', 1);
-- 2行目は顧客テーブル TBL_CUSTOMER に存在しない顧客コード
INSERT INTO TBL_ORDER VALUES ('ORDER_0002', 'NULL_CODE', 'GOODS_0003', 10);
```


```
ORA-01403: no data found ORA-06512: at "SQL_DSERLNHVYJPHTAYSQRUBWNWMP.ORDERINSERTTRIGGER", line 7
ORA-06512: at "SYS.DBMS_SQL", line 1721
```

エラーになってしまいました。存在しない顧客コードをINSERTしようとすると、トリガ内でデータが見つからなくてエラーになるようです。  
でも、あと少しですね。

検索すると、下記の情報が出てきました。  
[Tips1. Oracleのパフォーマンス問題～存在チェックが遅い～](https://oracle.tf17.net/plsql/?p=132)

これを元に、またコードを改造します。  
※ この後のテストで意図通りに動作するため、これが完成コードになります


```sql
CREATE OR REPLACE TRIGGER OrderInsertTrigger
    AFTER INSERT
    ON TBL_ORDER FOR EACH ROW
DECLARE
    -- 存在チェック用のカーソルを宣言
    CURSOR cur_customer IS SELECT CUSTOMER_NAME FROM TBL_CUSTOMER WHERE CUSTOMER_CODE = :NEW.CUSTOMER_CODE;
    row_customer cur_customer%ROWTYPE;
    -- TBL_ORDER のデータは :NEW に入っているので、
    -- TBL_ORDER 以外のテーブルから取得する顧客名、商品名、商品単価を変数にする
    V_CUSTOMER_NAME TBL_CUSTOMER.CUSTOMER_NAME%TYPE;
    V_GOODS_NAME TBL_GOODS.GOODS_NAME%TYPE;
    V_GOODS_PRICE TBL_GOODS.GOODS_PRICE%TYPE;
BEGIN
    IF INSERTING THEN
        -- 顧客データの存在チェック
        OPEN cur_customer;
        FETCH cur_customer INTO row_customer;
        IF cur_customer%NOTFOUND THEN
            -- 存在しない時は、ゲストユーザとする
            V_CUSTOMER_NAME := 'ゲストユーザ';
        ELSE
            -- 存在する時
            -- 変数 V_CUSTOMER_NAME に入れる
            V_CUSTOMER_NAME := row_customer.CUSTOMER_NAME;
        END IF;

        -- TBL_GOODS の GOODS＿NAME と GOODS_PRICE を取得して、
        -- 変数 V_GOODS_NAME と、変数 V_GOODS_PRICE に入れる
        SELECT GOODS_NAME, GOODS_PRICE
          INTO V_GOODS_NAME, V_GOODS_PRICE
          FROM TBL_GOODS
         WHERE GOODS_CODE = :NEW.GOODS_CODE;

        -- TBL_ORDER_SEARCH に INSERT 実行
        -- :NEW には TBL_ORDER の行データが入っている
        INSERT INTO TBL_ORDER_SEARCH (
            ORDER_CODE, CUSTOMER_NAME, GOODS_NAME, 
            GOODS_PRICE, ORDER_AMT, ORDER_PRICE
        ) VALUES (
            :NEW.ORDER_CODE, V_CUSTOMER_NAME, V_GOODS_NAME,
            V_GOODS_PRICE, :NEW.ORDER_AMT,
            :NEW.ORDER_AMT * V_GOODS_PRICE
        );
    END IF;
END;
```

テストデータ投入

```sql
-- 以前のデータはいったんクリア
DELETE FROM TBL_ORDER;
DELETE FROM TBL_ORDER_SEARCH;

-- 1行目は顧客テーブル TBL_CUSTOMER に存在する顧客コード
INSERT INTO TBL_ORDER VALUES ('ORDER_0001', 'CUSTOMER_1', 'GOODS_0001', 1);
-- 2行目は顧客テーブル TBL_CUSTOMER に存在しない顧客コード
INSERT INTO TBL_ORDER VALUES ('ORDER_0002', 'NULL_CODE', 'GOODS_0003', 10);
```

今度はエラーが出ずに行けました！
無事に検索データが作成できたか、 `TBL_ORDER_SEARCH` を確認します。

```sql
SELECT * FROM TBL_ORDER_SEARCH;
```

```
ORDER_CODE	CUSTOMER_NAME	GOODS_NAME	GOODS_PRICE	ORDER_AMT	ORDER_PRICE
ORDER_0001	竈門炭治郎	商品名その1	1980	1	1980
ORDER_0002	ゲストユーザ	商品名その3	10000	10	100000
```

出来ました！

```sql
-- 注文テーブルに続けてデータを投入します
INSERT INTO TBL_ORDER VALUES ('ORDER_0003', 'CUSTOMER_3', 'GOODS_0003', 3);
INSERT INTO TBL_ORDER VALUES ('ORDER_0004', 'CUSTOMER_4', 'GOODS_0004', 4);
INSERT INTO TBL_ORDER VALUES ('ORDER_0005', 'CUSTOMER_5', 'GOODS_0002', 5);
INSERT INTO TBL_ORDER VALUES ('ORDER_0006', 'CUSTOMER_1', 'GOODS_0004', 99999);
```

```sql
SELECT * FROM TBL_ORDER_SEARCH;
```

```
ORDER_CODE	CUSTOMER_NAME	GOODS_NAME	GOODS_PRICE	ORDER_AMT	ORDER_PRICE
ORDER_0001	竈門炭治郎	商品名その1	1980	1	1980
ORDER_0002	ゲストユーザ	商品名その3	10000	10	100000
ORDER_0003	我妻善逸	商品名その3	10000	3	30000
ORDER_0004	嘴平伊之助	商品名その4	99999	4	399996
ORDER_0005	冨岡義勇	商品名その2	500	5	2500
ORDER_0006	竈門炭治郎	商品名その4	99999	99999	9999800001
```


OKですね。「【注文テーブル】にデータがINSERTされたら、検索データを生成して、【検索データテーブル】にINSERTするトリガプロシージャ」が出来ました。

検索データテーブルには要件通り「注文コード、顧客名称、商品名称、商品単価、注文数、合計金額(商品単価X注文数)」が入っています。

これで課題達成です！

----

## 前提知識など

データベースの基礎はここが図が豊富で分かりやすいです。2020/11/25時点でカテゴリ内に6つの記事を作成されていますが、古い記事から順番に全て読むのが良いと思います。
[情報-データベース カテゴリーの記事一覧 - 工業大学生ももやまのうさぎ塾](https://www.momoyama-usagi.com/archive/category/%E6%83%85%E5%A0%B1-%E3%83%87%E3%83%BC%E3%82%BF%E3%83%99%E3%83%BC%E3%82%B9)

データベースの正規化について
[【初級編⑧】テーブル正規化の概要とその手順 | SQL Server 虎の巻](https://kaya-soft.com/sqlserver2008-toranomaki/beginner/normalize/)

また、SQLの操作については下記が良いです。
[【新人教育 資料】SQLへの道 〜DB編〜 - Qiita](https://qiita.com/devopsCoordinator/items/9b70e506150888e190be)

SQLの記事には色々なデータベースが出てきますが、基本的にSQLであれば殆ど同じで方言程度の違いなので学習に際しては気にせずに進めて下さい。
実際にSQLを書く時でも、「あれ、動かないな」となった時に、自分が使ってるデータベースに合わせて少し調べて書き直す程度です。（例外はあるけれど、基本的には）