第3章で作った部署テーブルと社員テーブルに、INSERT でサンプルデータを登録します。部署4件、社員10人です。登録したデータを確定する COMMIT と、取り消す ROLLBACK を学び、制約に反するデータを入れようとしたときにどんなエラーになるかも確かめます。ここで登録したデータを、第5章以降のすべての SQL で使います。
この章で学ぶこと
-
INSERTの書き方と、シーケンスのNEXTVAL - 日付の値の書き方（
DATE '2000-06-22'） -
COMMIT（確定）とROLLBACK（取り消し）、トランザクション - 制約に反したときのエラーの読み方
4.1 この章で登録するデータ
部署（4件）
| DEPT_ID | DEPT_NAME |
|---|---|
| 1 | 総務部 |
| 2 | 営業部 |
| 3 | 開発部 |
| 4 | 人事部 |
人事部には社員がいません。第10章で、社員のいない部署をどう取り出すかを学ぶために入れています。
社員（10人）
| EMP_ID | NAME | GENDER | AGE | BIRTH_DATE | DEPT_ID | ADDRESS |
|---|---|---|---|---|---|---|
| 1 | 雷禅 海皇 | 1 | 26 | 2000-06-22 | 2 | 大阪府 |
| 2 | 院照 心愛 | 2 | 27 | 1999-07-11 | 1 | 京都府 |
| 3 | 林檎 星飛 | 1 | 24 | 2001-12-24 | 3 | 東京都 |
| 4 | 田中 美咲 | 2 | 24 | 2002-03-05 | 3 | 愛知県 |
| 5 | 佐藤 健太 | 1 | 31 | 1995-04-12 | 2 | 東京都 |
| 6 | 鈴木 花子 | 2 | 29 | 1997-01-30 | 1 | 大阪府 |
| 7 | 高橋 翔 | 1 | 35 | 1991-08-15 | 3 | 東京都 |
| 8 | 伊藤 さくら | 2 | 22 | 2004-05-20 | 2 | 兵庫県 |
| 9 | 渡辺 大輔 | 1 | 42 | 1984-02-10 | 2 | 神奈川県 |
| 10 | 中村 結衣 | 2 | 26 | 2000-09-03 | 3 | 京都府 |
GENDER は 1 が男、2 が女です。DEPT_ID は部署テーブルの部署IDで、たとえば雷禅さんの 2 は営業部を表します。
4.2 サンプルデータを登録する
社員は部署を参照するので、ここでも 部署を先に 登録します。XE_DBAPP のワークシートの内容をすべて消してから、次の SQL を入力し、F5（スクリプトの実行）を押します。
-- 部署（DEPT_ID は SEQ_DEPT から 1, 2, 3, 4 と振られる）
INSERT INTO DEPARTMENT (DEPT_ID, DEPT_NAME) VALUES (SEQ_DEPT.NEXTVAL, '総務部');
INSERT INTO DEPARTMENT (DEPT_ID, DEPT_NAME) VALUES (SEQ_DEPT.NEXTVAL, '営業部');
INSERT INTO DEPARTMENT (DEPT_ID, DEPT_NAME) VALUES (SEQ_DEPT.NEXTVAL, '開発部');
INSERT INTO DEPARTMENT (DEPT_ID, DEPT_NAME) VALUES (SEQ_DEPT.NEXTVAL, '人事部');
-- 社員（EMP_ID は SEQ_EMP から 1〜10 と振られる）
INSERT INTO EMPLOYEE (EMP_ID, NAME, GENDER, AGE, BIRTH_DATE, DEPT_ID, ADDRESS)
VALUES (SEQ_EMP.NEXTVAL, '雷禅 海皇', 1, 26, DATE '2000-06-22', 2, '大阪府');
INSERT INTO EMPLOYEE (EMP_ID, NAME, GENDER, AGE, BIRTH_DATE, DEPT_ID, ADDRESS)
VALUES (SEQ_EMP.NEXTVAL, '院照 心愛', 2, 27, DATE '1999-07-11', 1, '京都府');
INSERT INTO EMPLOYEE (EMP_ID, NAME, GENDER, AGE, BIRTH_DATE, DEPT_ID, ADDRESS)
VALUES (SEQ_EMP.NEXTVAL, '林檎 星飛', 1, 24, DATE '2001-12-24', 3, '東京都');
INSERT INTO EMPLOYEE (EMP_ID, NAME, GENDER, AGE, BIRTH_DATE, DEPT_ID, ADDRESS)
VALUES (SEQ_EMP.NEXTVAL, '田中 美咲', 2, 24, DATE '2002-03-05', 3, '愛知県');
INSERT INTO EMPLOYEE (EMP_ID, NAME, GENDER, AGE, BIRTH_DATE, DEPT_ID, ADDRESS)
VALUES (SEQ_EMP.NEXTVAL, '佐藤 健太', 1, 31, DATE '1995-04-12', 2, '東京都');
INSERT INTO EMPLOYEE (EMP_ID, NAME, GENDER, AGE, BIRTH_DATE, DEPT_ID, ADDRESS)
VALUES (SEQ_EMP.NEXTVAL, '鈴木 花子', 2, 29, DATE '1997-01-30', 1, '大阪府');
INSERT INTO EMPLOYEE (EMP_ID, NAME, GENDER, AGE, BIRTH_DATE, DEPT_ID, ADDRESS)
VALUES (SEQ_EMP.NEXTVAL, '高橋 翔', 1, 35, DATE '1991-08-15', 3, '東京都');
INSERT INTO EMPLOYEE (EMP_ID, NAME, GENDER, AGE, BIRTH_DATE, DEPT_ID, ADDRESS)
VALUES (SEQ_EMP.NEXTVAL, '伊藤 さくら', 2, 22, DATE '2004-05-20', 2, '兵庫県');
INSERT INTO EMPLOYEE (EMP_ID, NAME, GENDER, AGE, BIRTH_DATE, DEPT_ID, ADDRESS)
VALUES (SEQ_EMP.NEXTVAL, '渡辺 大輔', 1, 42, DATE '1984-02-10', 2, '神奈川県');
INSERT INTO EMPLOYEE (EMP_ID, NAME, GENDER, AGE, BIRTH_DATE, DEPT_ID, ADDRESS)
VALUES (SEQ_EMP.NEXTVAL, '中村 結衣', 2, 26, DATE '2000-09-03', 3, '京都府');
-- 登録を確定する
COMMIT;
「スクリプト出力」に「1行挿入しました。」が14回、最後に「コミットが完了しました。」と表示されれば成功です。
図4-1 データ登録の SQL を実行したあと（最後に「コミットが完了しました。」）
登録したデータを確認する
次の SQL の行にカーソルを置いて Ctrl + Enter（文の実行）を押し、「問合せ結果」に 4.1 の表と同じ内容が表示されることを確かめます。SELECT の詳しい書き方は第5章で学びます。ここでは、「SELECT * FROM テーブル名 で、テーブルの中身をすべて表示できる」とだけ覚えてください。
SELECT * FROM DEPARTMENT ORDER BY DEPT_ID;
| DEPT_ID | DEPT_NAME |
|---|---|
| 1 | 総務部 |
| 2 | 営業部 |
| 3 | 開発部 |
| 4 | 人事部 |
SELECT * FROM EMPLOYEE ORDER BY EMP_ID;
| EMP_ID | NAME | GENDER | AGE | BIRTH_DATE | DEPT_ID | ADDRESS |
|---|---|---|---|---|---|---|
| 1 | 雷禅 海皇 | 1 | 26 | 2000-06-22 00:00:00 | 2 | 大阪府 |
| 2 | 院照 心愛 | 2 | 27 | 1999-07-11 00:00:00 | 1 | 京都府 |
| 3 | 林檎 星飛 | 1 | 24 | 2001-12-24 00:00:00 | 3 | 東京都 |
| 4 | 田中 美咲 | 2 | 24 | 2002-03-05 00:00:00 | 3 | 愛知県 |
| 5 | 佐藤 健太 | 1 | 31 | 1995-04-12 00:00:00 | 2 | 東京都 |
| 6 | 鈴木 花子 | 2 | 29 | 1997-01-30 00:00:00 | 1 | 大阪府 |
| 7 | 高橋 翔 | 1 | 35 | 1991-08-15 00:00:00 | 3 | 東京都 |
| 8 | 伊藤 さくら | 2 | 22 | 2004-05-20 00:00:00 | 2 | 兵庫県 |
| 9 | 渡辺 大輔 | 1 | 42 | 1984-02-10 00:00:00 | 2 | 神奈川県 |
| 10 | 中村 結衣 | 2 | 26 | 2000-09-03 00:00:00 | 3 | 京都府 |
図4-2 社員テーブルの中身（「問合せ結果」タブ）
【知識メモ】日付の表示について
BIRTH_DATE が 2000-06-22 00:00:00 のように時刻付きで表示されるのは、「開発環境の準備」で日付書式を YYYY-MM-DD HH24:MI:SS にしたためです。DATE 型は時刻も持つので、日付だけを入れると時刻は 00:00:00 になります。本文の結果の表では、以降この形で載せます。設定を変えていない場合は 00-06-22 のように年が2桁で表示されますが、データベースには正しく 2000年6月22日 として保存されています。
4.3 解説：INSERT 文
INSERT は、テーブルに 1行を追加する SQL 文です。テーブルの中のデータを扱う DML の1つで、第1章で学んだ CRUD の C（Create：登録） にあたります。次の形で書きます。
INSERT INTO テーブル名 (列名1, 列名2, ...) VALUES (値1, 値2, ...);
- 列名と値は、同じ順番で、同じ数だけ 書きます。1つ目の列名に1つ目の値、2つ目の列名に2つ目の値、…と対応します
-
数の値はそのまま 書きます（
26）。文字の値は'で囲みます（'大阪府'）。囲み忘れると「ORA-00917: カンマがありません。」などのエラーになります -
日付の値は
DATE '年-月-日'で書きます（DATE '2000-06-22'）。年は4桁、月と日は2桁で、-で区切ります。この書き方なら、環境の設定に左右されず、確実に日付として扱われます -
主キーには
シーケンス名.NEXTVALを書きます。SEQ_EMP.NEXTVALと書くと、シーケンス SEQ_EMP が次の番号（1、2、3、…）を払い出し、その値が EMP_ID に入ります -
外部キーには参照先の ID を書きます。雷禅さんは営業部なので、営業部の部署ID
2を書いています
【知識メモ】列名を省略した書き方
INSERT INTO DEPARTMENT VALUES (SEQ_DEPT.NEXTVAL, '総務部'); のように、列名を省略して書くこともできます。この場合、値はテーブルを作ったときの列の順にすべて書かなければなりません。後からテーブルに列が増えると動かなくなるので、業務のシステムでは 列名を明示して書く のが原則です。この教材でも、常に列名を書きます。
【知識メモ】NULL を入れる
値を入れない列があるときは、その列名と値を書かなければ NULL（空）になります。VALUES の中に NULL と書いても同じです。ただし、この教材のテーブルはすべての列が NOT NULL なので、どの列も省略できません。省略すると 4.5 で見るエラーになります。
4.4 解説：COMMIT と ROLLBACK
INSERT・UPDATE・DELETE でデータを変更しても、その変更はまだ 確定していません。変更は、COMMIT を実行して初めて確定します。確定する前なら、ROLLBACK で変更を すべて取り消す ことができます。
図4-3 COMMIT と ROLLBACK
| SQL | 意味 |
|---|---|
COMMIT; |
前回の COMMIT（または ROLLBACK）以降の変更を すべて確定 する。確定した変更は、ほかの接続からも見えるようになり、ROLLBACK では戻せない |
ROLLBACK; |
前回の COMMIT（または ROLLBACK）以降の変更を すべて取り消し、変更前の状態に戻す |
前回の COMMIT から次の COMMIT（または ROLLBACK）までの、ひとまとまりの変更を トランザクション といいます。この仕組みがあるので、たとえば「社員を登録し、同時に別のテーブルも更新する」という一連の処理の途中で失敗しても、ROLLBACK すれば中途半端な状態が残りません。
【再確認】COMMIT を忘れない
COMMIT する前の変更は、変更した本人の接続からしか見えません。ほかの接続（別の人の SQL Developer や、Spring入門で作るアプリなど）からは、まだ変更前のデータが見えています。「自分の画面ではデータが見えるのに、ほかの人には見えない」というトラブルの多くは、COMMIT のし忘れが原因です。データを変更したら、確定してよいことを確かめたうえで、必ず COMMIT しましょう。
【知識メモ】自動でコミットされる場合
CREATE TABLE や DROP TABLE などの DDL（第1章）は、実行すると自動で COMMIT されます。それまでに行った INSERT などの変更も、一緒に確定します。また、SQL Developer を接続したまま正しく終了したときも、未確定の変更は確定されます（終了時に確認の画面が表示されます）。
ボタンでコミット・ロールバックする
SQL Developer では、COMMIT; と入力しなくても、ツールバーのボタンで変更を確定（コミット）できます。確定する前なら、変更を取り消す（ロールバック）こともできます。練習として、部署を1件登録して取り消してみましょう。
-
XE_DBAPP のワークシートに次の SQL を入力し、その行にカーソルを置いて Ctrl + Enter で実行します。 「スクリプト出力」に「1行挿入しました。」と表示されます。ただし、この時点ではまだ 確定していません。
ワークシート（XE_DBAPP）INSERT INTO DEPARTMENT (DEPT_ID, DEPT_NAME) VALUES (SEQ_DEPT.NEXTVAL, '経理部');
図4-4 INSERT を実行したあと（赤枠の左が「コミット」、右が「ロールバック」のボタン） -
SELECT * FROM DEPARTMENT ORDER BY DEPT_ID;を実行すると、経理部の行が表示されます。自分の接続からは、未確定の変更も見えます。 -
変更を取り消すときは、次のどちらかを行います。 ここでは ロールバック します。もう一度
SELECTを実行すると、経理部の行が消えて4件に戻っています。- ワークシートで
ROLLBACK;を実行する - ツールバーの「ロールバック」ボタン（赤枠の右。マウスを重ねると「ロールバック (F12)」と表示される）をクリックする、または F12 を押す
- ワークシートで
-
変更を確定するときは、ワークシートで
COMMIT;を実行するか、「コミット」ボタン（赤枠の左。「コミット (F11)」）をクリックします。この練習では確定しません。
【ご注意】この練習は、ロールバックして終える
この後の章は、部署4件・社員10人のデータで進みます。練習で登録した経理部は、コミットせずにロールバック して消しておきましょう。うっかりコミットしてしまった場合は、DELETE FROM DEPARTMENT WHERE DEPT_NAME = '経理部'; を実行してからコミットすれば消せます（DELETE は第7章で学びます）。
4.5 制約に反するデータを入れてみる
第3章で付けた制約が、本当に正しくないデータを防いでくれるのかを確かめます。次の SQL を1つずつ、Ctrl + Enter で実行してください。どれもエラーになり、行は登録されません。エラーメッセージを読んで、どの制約に反したのかを確かめましょう。
主キーの重複
-- 部署ID 1 はすでに総務部が使っている
INSERT INTO DEPARTMENT (DEPT_ID, DEPT_NAME) VALUES (1, '経理部');
ORA-00001: 一意制約(DBAPP.PK_DEPARTMENT)に反しています
主キー PK_DEPARTMENT に反した、と表示されます。同じ部署IDは2つ入れられません。
NOT NULL の列に値を入れない
-- 部署名を入れていない
INSERT INTO DEPARTMENT (DEPT_ID) VALUES (SEQ_DEPT.NEXTVAL);
ORA-01400: ("DBAPP"."DEPARTMENT"."DEPT_NAME")にはNULLは挿入できません。
どの列が NULL になったか（DEPT_NAME）が表示されます。NOT NULL には名前を付けなくても、列名で分かります。
CHECK 制約に反する値
-- 性別に 3 を入れている
INSERT INTO EMPLOYEE (EMP_ID, NAME, GENDER, AGE, BIRTH_DATE, DEPT_ID, ADDRESS)
VALUES (SEQ_EMP.NEXTVAL, '山田 太郎', 3, 30, DATE '1996-01-01', 1, '東京都');
ORA-02290: チェック制約(DBAPP.CK_EMPLOYEE_GENDER)に違反しました
外部キーの参照先が無い
-- 部署ID 9 の部署は無い
INSERT INTO EMPLOYEE (EMP_ID, NAME, GENDER, AGE, BIRTH_DATE, DEPT_ID, ADDRESS)
VALUES (SEQ_EMP.NEXTVAL, '山田 太郎', 1, 30, DATE '1996-01-01', 9, '東京都');
ORA-02291: 整合性制約(DBAPP.FK_EMPLOYEE_DEPARTMENT)に違反しました - 親キーがありません
「親キーがありません」は、参照先（部署テーブル）に該当する部署IDが無い、という意味です。
列の長さを超える値
-- 部署名は20文字まで
INSERT INTO DEPARTMENT (DEPT_ID, DEPT_NAME) VALUES (SEQ_DEPT.NEXTVAL, 'とても長い部署の名前はこの列には入りきりません');
ORA-12899: 列"DBAPP"."DEPARTMENT"."DEPT_NAME"の値が大きすぎます(実際: 23、最大: 20)
これは制約ではなくデータ型（VARCHAR2(20 CHAR)）による制限ですが、同じように登録が拒否されます。
エラーメッセージの読み方
Oracle のエラーメッセージは、ORA- と5桁の番号で始まります。番号で原因を調べられるので、エラーが出たらまずこの番号を見ます。制約に反したときは、制約の名前（PK_DEPARTMENT など）や列名が表示されるので、第3章で付けた名前が役に立ちます。この教材でよく出るエラーは、付録B にまとめています。メッセージを読んでも原因が分からないときは、付録B「メッセージで分からないときは、番号で検索する」 を参考にしてください。
【ご注意】失敗した INSERT でもシーケンスの番号は進む
上の練習で SEQ_DEPT.NEXTVAL・SEQ_EMP.NEXTVAL を書いた INSERT は、エラーになりましたが、シーケンスの番号は払い出された（消費された）ままになります。一度払い出された番号は、ROLLBACK しても戻りません。このため、4.4 の練習（ロールバックした経理部）とこの節の2回の失敗で部署IDの 5〜7 が消費され、次に部署を登録すると部署IDは 8 になります。同じように、社員IDも 11・12 が消費され、次に登録する社員の ID は 13 になります。番号が飛ぶこと自体は問題ありませんが、第7章で社員を1人追加する練習では、ID を決め打ちせずに氏名で指定します。この教材と同じ ID にそろえたい場合は、4.6 の手順で作り直してください。
最後に、練習の後始末として ROLLBACK; を実行しておきます（エラーになった INSERT は登録されていないので、実際には何も変わりません）。
4.6 やり直したいとき
データを登録し直したい、ID を 1 から振り直したい、というときは、テーブルとシーケンスを削除して作り直すのが確実です。
-- 社員テーブルを先に削除する（部署テーブルを参照しているため）
DROP TABLE EMPLOYEE;
DROP TABLE DEPARTMENT;
-- シーケンスも削除する（番号を 1 から振り直すため）
DROP SEQUENCE SEQ_EMP;
DROP SEQUENCE SEQ_DEPT;
削除したら、第3章 3.2 のシーケンスとテーブルの作成と、4.2 のデータ登録をもう一度行います。付録A には、この2つをつなげた SQL を置いてあるので、まとめて実行することもできます。
4.7 SQL をファイルに保存する
第3章と第4章で書いた SQL は、ファイルに保存しておくと、やり直すときにすぐ使えて便利です。SQL Developer のワークシートの内容は、そのままファイルに保存できます。
- エクスプローラーで、SQL を保存するフォルダーを作ります。例：
ドキュメント\db-sql - XE_DBAPP のワークシートに、第3章 3.2 の SQL と 4.2 の SQL を続けて貼り付けます（コードの右上の「コピー」ボタンでコピーできます）。
- メニューの ファイル → 保存 を選び（または Ctrl + S）、保存先に作成したフォルダーを選んで、ファイル名を
setup.sqlとして保存します。
やり直すときは、メニューの ファイル → 開く で setup.sql を開き、ワークシートの右上の接続の選択で XE_DBAPP を選んでから、F5（スクリプトの実行）を押します。ファイルから開いたワークシートは、最初はどの接続にもつながっていないので、接続の選択を忘れないでください。
4.8 うまくいかないときは
| 症状 | 考えられる原因と対処 |
|---|---|
| 「ORA-00947: 値の個数が不足しています。」 | 列名の数と値の数が合っていません。VALUES の中の値を数えてください。 |
| 「ORA-00913: 値の個数が多すぎます。」 | 値の数が列名の数より多くなっています。 |
| 「ORA-00917: カンマがありません。」 | 文字の値を ' で囲み忘れています（'大阪府'）。値と値の間の , が抜けているときにも出ます。 |
| 「ORA-01722: 数値が無効です。」 | 数の列（AGE など）に文字を入れようとしています。列名と値の順番がずれていないか確認してください。 |
| 「ORA-01438: この列に許容される指定精度より大きな値です」 | 数の列の桁数を超えています（NUMBER(3) の AGE に 1234 など）。 |
| 「ORA-01756: 引用符付き文字列が正しく終了していません」 |
' が片方しかありません。文字の値は ' と ' で囲みます。 |
| 「ORA-00942: 表またはビューが存在しません。」 | テーブル名の打ち間違いか、第3章のテーブル作成が済んでいません。XE_SYSTEM のワークシートで実行していないかも確認してください。 |
| SELECT の結果が、本文の表と違う（件数が多い・少ない、ID が飛んでいる） | INSERT を2回実行した、一部がエラーになった、などが考えられます。4.6 の手順で作り直してください。 |
ほかのエラーは、付録B「よくあるエラーと対処法」 も参考にしてください。
4.9 まとめと確認問題
まとめ
-
INSERT INTO テーブル名 (列名, …) VALUES (値, …);で1行を登録する。列名と値は同じ順番・同じ数で書く - 文字の値は
'で囲む。日付はDATE '2000-06-22'と書く。主キーにはシーケンス名.NEXTVALを書く - 変更は COMMIT で確定し、確定前なら ROLLBACK で取り消せる。確定前の変更は、ほかの接続からは見えない
- 制約に反する INSERT はエラーになり、登録されない。エラーメッセージの制約名・列名で原因が分かる
- 失敗した INSERT でも、シーケンスの番号は消費される
- 作り直すときは、社員 → 部署の順にテーブルを消し、シーケンスも消してから、第3章と第4章の SQL を実行し直す
この章で使った SQL は、付録C「SQL 早見表」 にまとめています。
確認問題
問1 社員テーブルにデータを INSERT するとき、EMP_ID に SEQ_EMP.NEXTVAL と書くのはなぜですか。
解答を見る
シーケンス SEQ_EMP から次の番号を取り出して、社員IDにするためです。番号はシーケンスが 1 から順に払い出すので、自分で番号を考える必要がなく、ほかの社員と重なることもありません。
問2 次の INSERT 文の間違いを2つ見つけてください。INSERT INTO DEPARTMENT (DEPT_ID, DEPT_NAME) VALUES (SEQ_DEPT.NEXTVAL, 経理部, 5);
解答を見る
文字の値 経理部 が ' で囲まれていないことと、列名が2つなのに値が3つあることです。正しくは VALUES (SEQ_DEPT.NEXTVAL, '経理部') です。
問3 INSERT を実行した後、別のパソコンの SQL Developer から同じテーブルを SELECT しても、登録した行が見えませんでした。何が足りませんか。
解答を見る
COMMIT です。COMMIT するまで変更は確定せず、ほかの接続からは見えません。
問4 INSERT INTO EMPLOYEE (…) VALUES (SEQ_EMP.NEXTVAL, '山田 太郎', 1, 30, DATE '1996-01-01', 9, '東京都'); がエラーになるのはなぜですか。
解答を見る
部署ID 9 の部署が部署テーブルに無いためです。外部キー制約 FK_EMPLOYEE_DEPARTMENT に反し、「ORA-02291: … 親キーがありません」というエラーになります。
問5 INSERT を実行して COMMIT した後に、間違いに気付きました。ROLLBACK で取り消せますか。
解答を見る
取り消せません。COMMIT した変更は確定しているので、ROLLBACK では戻せません。間違えた行は、第7章で学ぶ UPDATE や DELETE で直します。