Oracle SQL データの追加 : INSERT文の基礎
おすすめ書籍
- 図解入門よくわかる最新Oracleデータベースの基本と仕組み[第4版] (How‐nual Visual Guide Book)
- Oracleの基本 ~データベース入門から設計/運用の初歩まで
- [改訂第4版]SQLポケットリファレンス
INSERT...VALUES文の構文
INSERT INTO <表名> [ (<列名1>,<列名2>,<列名3>...) ]
VALUES (<値1>,<値2>,<値3>...);
INSERT文では、データを追加したい表の列とそれに対応した値を順に指定します。
追加する値は列のデータ型と同じで、データ型の大きさ（桁数等）を超えてはいけません。
<列名1>は<値1>、<列名2>は<値2>、...と順序良く対応させなければなりません。
列名リストを省略する場合は、VALUESには表の全ての列の値を指定しなければなりません。
逆に言えば、必要な列にデータを追加したい場合には列名リストの指定を行うことになります。
列名リストを指定した場合のINSERTの実行の例を示します。
SQL> SELECT * FROM TT_売上明細;
売上番号 明細番号 商品コード 売上数量 備考
--------- --------- ---------- --------- ----------------------------------------
1 1 1 2
1 2 4 1
2 1 2 1
2 2 5 2
2 3 11 1
3 1 3 3
4 1 6 1
4 2 7 2
5 1 8 3
5 2 9 2
6 1 8 1
6 2 9 1
6 3 10 1
13行が選択されました。
SQL> INSERT INTO TT_売上明細( 売上番号, 明細番号, 商品コード, 売上数量)
2 VALUES( 6, 4, 1, 1);
1行が作成されました。
SQL> SELECT * FROM TT_売上明細;
売上番号 明細番号 商品コード 売上数量 備考
--------- --------- ---------- --------- ----------------------------------------
1 1 1 2
1 2 4 1
2 1 2 1
2 2 5 2
2 3 11 1
3 1 3 3
4 1 6 1
4 2 7 2
5 1 8 3
5 2 9 2
6 1 8 1
6 2 9 1
6 3 10 1
6 4 1 1
14行が選択されました。
一番最後の行に売上番号(6)、明細番号(4)のデータが追加された様子がわかると思います。
この例のINSERT文には備考の列を指定していないので、備考には全くデータ処理が行われず、NULLの値のままです。
列名を省略してINSERT文を実行する例を示します。
SQL> INSERT INTO TT_売上明細
2 VALUES( 6, 5, 1, 1, '売上番号６で明細番号５のデータ');
1行が作成されました。
SQL> SELECT * FROM TT_売上明細;
売上番号 明細番号 商品コード 売上数量 備考
--------- --------- ---------- --------- ----------------------------------------
1 1 1 2
1 2 4 1
2 1 2 1
2 2 5 2
2 3 11 1
3 1 3 3
4 1 6 1
4 2 7 2
5 1 8 3
5 2 9 2
6 1 8 1
6 2 9 1
6 3 10 1
6 4 1 1
6 5 1 1 売上番号６で明細番号５のデータ
15行が選択されました。
列名を省略して、値の個数が足りない場合の実行例を示します。
SQL> INSERT INTO TT_売上明細
2 VALUES( 6, 6, 1, 1);
INSERT INTO TT_売上明細
*
エラー行: 1: エラーが発生しました。
ORA-00947: 値の個数が不足しています。
備考にデータを設定したくない場合にはNULLを指定すれば追加可能です。
SQL> INSERT INTO TT_売上明細
2 VALUES( 6, 6, 1, 1, NULL);
1行が作成されました。
SQL> SELECT * FROM TT_売上明細
2 WHERE 売上番号 = 6;
売上番号 明細番号 商品コード 売上数量 備考
--------- --------- ---------- --------- ----------------------------------------
6 1 8 1
6 2 9 1
6 3 10 1
6 4 1 1
6 5 1 1 売上番号６で明細番号５のデータ
6 6 1 1
6行が選択されました。
INSERT...SELECT文の構文
INSERT INTO <表名1> [ (<列名1>,<列名2>,<列名3>...) ]
SELECT <列名1>,<列名2>,<列名3>...
FROM <表名2>
[ WHERE <検索条件> ... ]
INSERT...VALUES文では、1回の処理で1件のデータしか追加できません。
一連の操作によりデータを追加したい場合に有効なのが今回のINSERT...SELECT文です。
今回の構文もINSERT...VALUES文の様に追加したい表の列名を順に指定し、SELECT句に
それに対応した列名を指定します。SELECT...以下の部分は通常のSELECT文の記述の仕方と
同じであり、列のデータ型はINSERT INTOで指定された列のものと同じでなければなりません。
尚、INSERTの<列名1>,<列名2>,<列名3>...とSELECT句の<列名1>,<列名2>,<列名3>...は同じ
列名である必要はありません。
このINSERT...SELECT文はよく使用する用途としては、ワーク表（バックアップ表）等にデータを
コピーする場合に用います。以下にその例を示します。
SQL> CREATE TABLE TW_売上明細 2 AS SELECT * FROM TT_売上明細 WHERE 1=0; 表が作成されました。 SQL> INSERT INTO TW_売上明細 2 SELECT * FROM TT_売上明細; 13行が作成されました。
最初のCREATE文はTT_売上明細を元にして、ワーク表であるTW_売上明細の作成を行っています。
その次のINSERT文でTT_売上明細から全ての行をTW_売上明細にコピーを行っています。
最初のCREATE文のなかで、"WHERE 1=0"の条件文が変に思われるかもしれませんが条件結果を偽に
するためのもので、これによりSELECTの結果は1行もデータを返さないことになります。
しかし、データはコピーされませんが表の枠組み、つまり全ての列のデータ型の情報は引き継がれます。
但し､主キーなどの索引情報は引き継がれないので注意が必要です。
COMMITについて
今までの例で、INSERT文の実行の結果、SELECT文を実行することで正しくデータが追加されている様子が
わかると思います。しかし、他のマシン上から見ると（もうひとつ別のSQLPLUSを起動）追加されている
データが検索できません。これはどうしたことでしょう、自分のSQLPLUS上ではSELECTできているのに
他からは見えません。実はこれはトランザクション処理と言われるものがOracleには存在し、INSERT文等の
表データを操作した後で実際にOracleに対してそれを有効とすることを命令してやらないと実際の表には
操作が反映されないからです。これを行う命令がCOMMITです。
COMMIT命令はその命令が指示される直前までの一連のデータ操作命令の結果を実際のデータとして表に
反映させます。データの反映を取止めたい場合も有りますが、その為の命令がROLLBACK命令です。
尚、ここではトランザクション処理の説明はこのぐらいにしておき、後日また別の項目としてUPする予定です。
INSERT...SELECT文その２
集計を行うSELECT文の結果をテーブルに追加処理するINSERT文について考えてみます。
TT_売上明細のデータを月ごとの商品の売上数量を集計するテーブルを以下の様に作成します。このテーブルには処理を簡略にする為に金額データをもっていません。また、このテーブルはINSERT...SELECTを説明する為のみのテスト用で、このテーブル自身の内容にはあまり意味はありません。
SQL> CREATE TABLE TT_売上集計(
2 年月 NUMBER( 6) ---数値でYYYYMMの形式
3 ,商品コード NUMBER( 6)
4 ,売上数量 NUMBER( 9) ---月間売上数量の集計値が入る
5 );
表が作成されました。
SQL> INSERT INTO TT_売上集計
2 SELECT 200211 , TD.商品コード , SUM(TD.売上数量)
3 FROM TT_売上 TU , TT_売上明細 TD
4 WHERE TU.売上番号 = TD.売上番号
5 AND TO_CHAR(TU.売上日,'YYYY/MM') = '2002/11'
6 GROUP BY TD.商品コード;
11行が作成されました。
SQL> SELECT * FROM TT_売上集計;
年月 商品コード 売上数量
---------- ---------- ----------
200211 1 2
200211 2 1
200211 3 3
200211 4 1
200211 5 2
200211 6 1
200211 7 2
200211 8 4
200211 9 3
200211 10 2
200211 11 1
11行が選択されました。
INSERT...SELECT文と言っても難しいものではなく、SELECT文が返す値がうまくINSERTされるテーブルのデータと合致していれば問題なく、追加されます。SELECT文のみでテストを行ってクエリーの結果が正しいことを確認してから、INSERT...SELECT文にすると良いと思います。
さて、この様な集計処理には売上数量順に並ばせることがよくあります。TT_売上集計に順位のカラムを追加し、上のSQL文に順位を加味したものを以下に示します。
以下のSQLでは2行目で副問い合わせの結果の行番号を取得するROWNUM擬似列を利用しています。4行目～9行目では、2002年11月の売上を集計し売上順位の大きいものからデータを返す副問い合わせです。10行目はこの副問い合わせの擬似テーブルにテーブルの別名を与えています。
SQL> ALTER TABLE TT_売上集計
2 ADD 順位 NUMBER(4);
表が変更されました。
SQL> INSERT INTO TT_売上集計
2 SELECT TW.年月 , TW.商品コード , TW.売上集計 , ROWNUM
3 FROM (
4 SELECT 200211 AS 年月 , TD.商品コード AS 商品コード , SUM(TD.売上数量) AS 売上集計
5 FROM TT_売上 TU , TT_売上明細 TD
6 WHERE TU.売上番号 = TD.売上番号
7 AND TO_CHAR(TU.売上日,'YYYY/MM') = '2002/11'
8 GROUP BY TD.商品コード
9 ORDER BY 売上集計 DESC , TD.商品コード
10 ) TW;
11行が作成されました。
SQL> select * from TT_売上集計;
年月 商品コード 売上数量 順位
---------- ---------- ---------- ----------
200211 8 4 1
200211 3 3 2
200211 9 3 3
200211 1 2 4
200211 5 2 5
200211 7 2 6
200211 10 2 7
200211 2 1 8
200211 4 1 9
200211 6 1 10
200211 11 1 11
11行が選択されました。
SQL> commit;
コミットが完了しました。
ROWNUMについて
上の例でROWNUMが出てきましたので、ここで少し説明します。ROWNUMはクエリーの対象となるテーブルに属するものではなく、クエリーの結果に基づいた擬似列です。以下にROWNUMを使用した例を示します。最初のSQL文の例は、単純にTT_売上の内容を一覧し、ROWNUMで行番号を表示するだけのものです。2番目のSQLはROWNUMをWHERE句の中で利用し、最初から3行目までの列を表示する様に条件付けています。
SQL> SELECT TT_売上.* , ROWNUM FROM TT_売上;
売上番号 売上日 得意先コード 担当者コード ROWNUM
---------- -------- ------------ ------------ ----------
1 02-11-01 1 1 1
2 02-11-03 2 4 2
3 02-11-04 4 5 3
4 02-11-13 3 2 4
5 02-11-15 5 7 5
6 02-11-15 1 9 6
7 02-12-05 1 1 7
8 02-12-10 1 1 8
9 02-10-01 1 1 9
9行が選択されました。
SQL> SELECT TT_売上.* , ROWNUM FROM TT_売上
2 WHERE ROWNUM <= 3;
売上番号 売上日 得意先コード 担当者コード ROWNUM
---------- -------- ------------ ------------ ----------
1 02-11-01 1 1 1
2 02-11-03 2 4 2
3 02-11-04 4 5 3
WHERE句の中で利用できることはできますが、ある行以上のものを指定すると結果は返されません。以下の例を見てください。
SQL> SELECT TT_売上.* , ROWNUM FROM TT_売上 2 WHERE ROWNUM > 3; レコードが選択されませんでした。
ROWNUMはクエリーが返す行に対して１から割り振られる擬似列の為、上の様な”＞３”の指定は不可能です。
但し、このことも副クエリーを利用すれば回避されます。以下の例では3行目に副クエリーとしてTT_売上のデータ列全てを対象としているため、データ量が非常に増えた場合には処理時間が掛かることが予想されます。その為にはもっと別の方法が必要になるかもしれません。
SQL> SELECT TW.*
2 FROM (
3 SELECT TT_売上.* , ROWNUM AS LINENO FROM TT_売上
4 ) TW
5 WHERE TW.LINENO > 3;
売上番号 売上日 得意先コード 担当者コード LINENO
---------- -------- ------------ ------------ ----------
4 02-11-13 3 2 4
5 02-11-15 5 7 5
6 02-11-15 1 9 6
7 02-12-05 1 1 7
8 02-12-10 1 1 8
9 02-10-01 1 1 9
6行が選択されました。
