要件
【注文テーブル】、【商品テーブル】、【顧客テーブル】を結合して、情報を検索する。
- 検索するのは、注文コード、顧客名称、商品名称、商品単価、注文数、合計金額(商品単価X注文数)
- 【注文テーブル】の商品コードは、【商品テーブル】に必ず存在する
- 【注文テーブル】の顧客コードは、【顧客テーブル】に存在しない場合がある
その場合は、顧客名称として「ゲストユーザ」と表示する
方針
- 3つのテーブル □ を用いて、検索結果 ■ を作成する。 □ + □ + □ → ■
料理で例えると、材料を元に、料理を作ります。・・・尚、材料は使っても減りません。ここは料理と違う所ですね。- 材料 ： 【注文テーブル】のデータと、それに付随する2つのテーブル情報
- 料理 ： 3つのテーブルを結合して出来た検索データ
- 【検索テーブル】 ■ を作る方法は2種類あり、どちらか片方を選ぶ
① 検索する時にリアルタイムに生成する
→ 「料理を出してください」と言われてから料理を作る形式。時間が掛かる。
② 検索する前に予め作っておく
→ 作り置きの料理を出す形式。「料理を出してください」と言われたら出すだけなので早い。
上記の ① と ② について、もう少し詳しく書きます。
① 検索する時にリアルタイムに生成する
JOIN を用いる方法で、クエリさえ書けば良いので簡単です。料理の話でいうと、「材料があればあるだけ料理を作る」というレシピだけ書いてある状態。
料理を作るのは「料理を出して」と言われた時です。材料（注文テーブルのデータ）があるだけ、料理（検索データ）を作る訳です。これは、注文テーブルのデータ数が多いと遅くなる可能性があります。
② 検索する前に予め作っておく
予め【検索データテーブル】を作っておく方法です。【注文テーブル】にデータの更新がある度に、トリガで検索データを生成して【検索データテーブル】にINSERTします。これが料理で言うところの「予め作り置きする」という事です。「料理を出して」と言われたら作り置きをそのまま出します。
※ トリガとは、INSERTなどのイベントで実行されるストアドプロシージャの事です。トリガプロシージャとも言います。
予め作る手間があるので面倒ですが、料理を出すのは早いです。そして実際の業務では、この早さは結構大事だったりします。
実装してみる
① の方法は、下記のようなものになると思います。
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 で作ってみました。（アカウント登録が必要です）
左側メニューの Schema → 右上の + Create Database Object を押して、Table を選択します。後は適当に情報を入れると下記のSQLが出来ました。（少し書き換えてます。今回の要件で指定されているchar型およびnumber型の長さが選べなかったので）
-- 商品テーブル
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)
);
/
テストデータも入れておきます。注文データはまだ入れません。
-- 商品テーブル
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', '冨岡義勇');
同様に、検索データテーブルも作成します。
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」などでググれば出てきます。例えばこれなど。
INSERTで起動するトリガー雛形は下記のような形になります。これに少しずつ付け加えていきます。
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（アカウント登録が必要） で試したほうが楽かもしれません。
試行錯誤の過程
経験10年以上のプログラマが思考錯誤しているので、学習中の人にとっては「なんでそうなるの」という事が多々出てくると思います...。分からない部分はなるべく一つ一つ調べるか聞くなりして、理解していってください。
（本当に分からないと、何が分からないか分からない状態になりますが、そういう時はお茶でも飲んで脳みそを休めて、少しずつ学習を進めて下さい）
この記事を読みながら課題を解く場合は、各ステップを手元で実行すると理解の助けになると思います。
実際には、ここに書いた以上に色々試しています。が、解答への直進ルートから外れる部分はノイズになるので省きました。
🐳
まずは雛形を、最低限の状態でエラーが出ないようにします。変数はまだ無いので、宣言部（DECLARE）は外しました。変数が必要になった時に追加します。
-- 注文テーブルに 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.列名』を用いた値ベース監査」
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;
では、データを入れてテストしてみましょう。動くかな？
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 した結果をいったん変数に入れるようにします。
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;
またテストしてみます。
INSERT INTO TBL_ORDER VALUES ('ORDER_0001', 'CUSTOMER_1', 'GOODS_0001', 1);
1 row(s) inserted.
お、行けましたね。ちゃんと検索データテーブル TBL_ORDER_SEARCH に登録されたか見てみましょう。
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つ作っておくようになりました。
さて、顧客テーブルに存在しない顧客コードを入れた場合はどうでしょうか。
-- 以前のデータはいったんクリア
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のパフォーマンス問題～存在チェックが遅い～
これを元に、またコードを改造します。
※ この後のテストで意図通りに動作するため、これが完成コードになります
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;
テストデータ投入
-- 以前のデータはいったんクリア
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 を確認します。
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
出来ました！
-- 注文テーブルに続けてデータを投入します
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);
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つの記事を作成されていますが、古い記事から順番に全て読むのが良いと思います。
情報-データベース カテゴリーの記事一覧 - 工業大学生ももやまのうさぎ塾
データベースの正規化について
【初級編⑧】テーブル正規化の概要とその手順 | SQL Server 虎の巻
また、SQLの操作については下記が良いです。
【新人教育 資料】SQLへの道 〜DB編〜 - Qiita
SQLの記事には色々なデータベースが出てきますが、基本的にSQLであれば殆ど同じで方言程度の違いなので学習に際しては気にせずに進めて下さい。
実際にSQLを書く時でも、「あれ、動かないな」となった時に、自分が使ってるデータベースに合わせて少し調べて書き直す程度です。（例外はあるけれど、基本的には）