SQL 入門 (DMLの基本形式:INSERT)
DML (Data Munipulation Language) とは、データを操作する SQL であり、INSERT, UPDATE, DELETE とされている文献は多くある。
そうすると、SELECT はデータ検索言語ということになるはずであるが、Oracle のマニュアルでは SELECT を DML の機能制限された形式として DML の位置付けをしています。という内容があった。
Oracle では、SELECT 文は DML(の一種) ということのようである。
また、UPDATE と INSERT 処理を一つの SQL で実行する、MERGE (別名 UPSERT : UPdate + inSERT ) も DML に含まれる。
レコードの新規作成 ( INSERT: データを指定した挿入 と テーブルのデータを用いた挿入 )
利用者マスタ(USER_MASTER) |
利用者ID (USER_ID) | 部門 (DEPT_NO) | 利用者名 (USER_NAME) | 作成日 (CREATED_ON) | 更新日 (MIDIFIED_ON) |
0020 | 1001 | 小泉 純一 | SYSDATE | <NULL> |
0019 | 1001 | 林 喜朗 | 〃 | 〃 |
0018 | 1002 | 少渕 恵三 | 〃 | 〃 |
0017 | 1003 | 橋本 太郎 | 〃 | 〃 |
0001 | 1003 | 鳩山 一代 | 〃 | 〃 |
9998 | <NULL> | あいう'えお | 〃 | 〃 |
9999 | <NULL> | これはゴミデータ dummy% | 〃 | 〃 |
INSERT 文のいろいろ
項目名の全てと、データの全ての双方指定
INSERT INTO USER_MASTER (
USER_ID, DEPT_NO, USER_NAME, CREATED_ON, MODIFIED_ON
) VALUES (
'0020', '1001',
'小泉 純一',
DEFAULT,
NULL
) ;
-- 行コメント
VALUES 句の DEFAULT キーワードは、項目にデフォルト値を代入にするための表記方法
テーブルの一部の項目とペアになるデータの対を設定
INSERT INTO USER_MASTER (
USER_NAME, DEPT_NO, USER_ID
) VALUES (
'林 喜朗'
'1001', '0019'
) ;
/*
これは
複数行の
コメントです。
*/
省略された項目は、通常 NULL 状態となる。(但し CREATED_ON はデフォルト値が テーブル定義 に設定されているので SYSDATE が設定される)
項目の順番は任意。
データだけを指定する場合、全カラムのデータを用意する必要がある。
INSERT INTO USER_MASTER
VALUES (
'0018', '1002',
'少渕 恵三',
DEFAULT, NULL
) ;
テーブル定義の順番に沿ってデータを設定する。
☆ガイドライン 項目名を完全に省略した形式は使用しないこと(手入力での利用時は除く)
- 入力データを他のテーブルを元に作成する (INSERT 〜 SELECT 〜)
他のテーブルのデータを元にデータを(コピー)作成する。
INSERT INTO USER_MASTER (
USER_ID, DEPT_NO, USER_NAME, CREATED_ON
)
SELECT
'0017', '1003', '橋本 太郎', SYSDATE
FROM DUAL ;
VALUES 句は記述しない。SELECT 文が複数レコードを戻す場合は、データも複数レコード作成される。
ここで使用している DUAL という表 は、すべてのユーザーが見ることができる 1 レコードのみの特別な表。
⇒ パブリックシノニム という機能を使用して全ユーザーで同じ表を共有している。
- 入力データの一部を他のテーブルから取得する。(スカラー副問い合わせ 9i〜?)
VALUE に単独の問い合わせを指定する。
INSERT INTO USER_MASTER (
USER_ID, DEPT_NO, USER_NAME, CREATED_ON
) VALUES (
'0001', '1004',
'鳩山 一代',
( SELECT SYSDATE FROM DUAL )
) ;
SELECT は括弧で囲まないとエラーとなる。
SELECT 文が複数レコードを戻す場合もエラーとなる。(対象カラムが 組み込みデータタイプ の場合)
Oracle8i だと VALUES 内でスカラー副問い合わせを使用するとエラー?
INSERT 文の基本フォーマット
INSERT INTO
{
<表名> [ エイリアス ] | <ビュー名> [ エイリアス ]
}
[ ( <列名>, … ) ]
{
VALUES
(
{ < 値 > | < 式 > | (< SELECT文/問い合わせ >) } , …
)
OR
< SELECT文/問い合わせ >
}
VALUES 句の中での問い合わせは、Oracle のバージョンにより使用できない場合がある。
ダイレクト・パス・インサートは VALUES 句では利用不可 (Oracle 11g R2 から利用できるようになった)
INSERT /*+ APPEND */ INTO 〜
-- 以下は Oracle 11g R2 から利用可能 (APPEND_VALUES ヒント)
INSERT /*+ APPEND_VALUES */ INTO 〜
更新の確定と取消し ( COMMIT / ROLLBACK )
値の更新は COMMIT 文を実行しなければ、別の接続(セッション)からみた場合、変更されたことが伝わらない。
(読み取り一貫性)
ROLLBACK 文を実行すると、行の更新は取消しされる。
更新の確定と取り消しができる、この処理単位が トランザクションである。
UPDATE 〜 ;
COMMIT; or ROLLBACK;
COMMIT、ROLLBACK は DCL (Data Control Language)
データ操作言語として認識されていることもあるが、Oracle では トランザクション 制御文である。
SQL - INSERT文 関連