第10章 データ操作とトランザクション制御

■INSERT文によるデータの追加

INSERT INTO 表名 列名1, 列名2,・・・
VALUES (値1, 値2,・・・

VALUES句のあるINSERT文では、1つの文で1つの行のみ表に追加できる。

INSERT文使用時のポイント
表名に続く列名は、省略可能である。列名を省略した場合は、VALUES句の中で表内の列の並び順に従って値をリストし、すべての列に対して値を指定する必要がある。
表名に続けて、列名をリストする場合は、列名の数と値の数を一致させる必要がある。また、列名と値の指定順序を一致させる必要がある。

■副問合せを使用したデータの追加
INSERT文では、副問合せで取り出したデータを表に追加することもできる。
副問合せを使用すると、値を既存の表から取得して、複数行のデータを一度に追加できる。

INSERT INTO 表名1 列名1, 列名2,・・・
SELECT 列名1, 列名2,・・・
FROM 表名2
WHERE 条件

INSERT文に副問合せを記述する際の注意点
★副問合せの戻り値は、複数列ありの場合は、下記の条件となる。
副問合せを囲む()丸括弧は、必須ではない。あってもなくてもよい。
VALUES句は指定しない。 副問合せの戻りが複数の場合は、VALUES句は指定できない。
INSERT句に指定する列名を省略する場合は、副問合せのSELECT句には、表に定義されている列と同数の値を、表の列構成と同じ順番で指定する。
INSERT句に列名を指定する場合は、副問合せのSELECT句には、列名のリストと同数の値を、同じ順番で指定する。

※副問合せの戻り列が一つだけの場合は、VALUES句を使うこと。
VALUES句の副問合せには、複数列は不可、単一列のみOK

■UPDATE文によるデータの更新

UPDATE句の表名に副問合せを使用することは可能。ただし、WHERE句で使用する項目がSELECT句に記載ありでなければならない。

SET句では、NULLやDEFALUTキーワードを使える。
何も行を戻さない副問合せからは、NULLが戻されます。

UPDATE句は、SET句に複数の副問合せが可能。

トランザクション修了
COMMIT
ROLLBACK
DDL 自動コミット
DCL 自動コミット
ユーザによるDveloperやSQL * Plusの修了
システム障害やシステムクラッシュの発生

トランザクション修了ではない 要注意
ROLLBACK TO SAVEPOINT セーブポイント名

A = NULL
NULL値を代入するコード。

■DELETE文使用時のポイント
同時に複数行を削除できる
WHERE句を省略すると表の全行を削除する。WHERE句を指定すると、条件に一致する行だけを削除する。
条件に一致する行がない場合は、1行も削除されず、0行削除のメッセージが表示される。
▼全行削除の例)
DELETE ENP; WHERE句を指定しないと、表から全行が削除される。
DELETE FROM EMP WHERE 1 <> 2; WHERE句で結果が常にTRUEとなる条件をつければ、全行削除となる。

DELETE * FROM EMP; *が不要のため、全行削除となはらない???

TRANCATE
表に格納されているすべてのデータを削除する場合は、TRUNCATE文を使う。

TRUNCATE TABLE 表名;

▼TRUNCATE文の特徴
表のデータは削除されるが、索引(インデックス)は削除されない。
TRUNCATE文では、削除するデータを指定できない(DELETE文では、WHERE句に条件を指定できる)
TRUNCATE文は、DDL文なので、実行時には自動コミットが実行される(DELETE文は、DML文)
自動コミットが実行されるので、処理をロールバックできない。(DELETE文は、ロールバックが可能)
TRUNCATE文では、ロールバック用のデータを生成する必要がないため、DELETE文よりも短時間で、データを削除できる。

大量のデータをすべて削除する場合は、TRUNCATE文を使用すると効果的である。

▼TRUNCATE文使用時のポイント
表からすべての行を削除する。
DDL文なのでロールバックはできないが、ロールバック情報を生成しないので、DELETE文より処理が速い
表が使用していた領域(初期割当を除く領域)は解放され、ほかのオブジェクトで再利用が可能となる。
削除トリガー(DELETEトリガー)が起動しない。そのため、DELETEトリガーで削除するデータを自動的に別の表にコピーする処理が無効となってしまう。
FOREIGN KEY制約(参照整合性制約)の親表を切り捨てることはできない。
ただし、ON DELETE CASCADEオプションを指定したOREIGN KEY制約の場合のみ、CASCADEキーワードを指定して、親表を切り捨てできる。

DOROP TABLE文および、TRUNCATE文は、空いた領域を解放する。
DELETE文は、削除により空いた領域を解放せず、領域は、その表に対する以降のINSERT文で再利用される。

CREATE SEQUENCE

セーブポイントとは、1つのトランザクションの中で、部分的ロールバックをするための機能である。
SAVEPOINT文でセーブポイントを設定し、ROLLBACK TO セーブポイント名;で特定のセーブポイントまでロールバックできる。
セーブポイントは、長時間にわたるトランザクションにおいて、エラー発生時でも、途中から再度続行できるようにするための機能である。
セーブポイントの設定、セーブポイントを使ったロールバックでは、トランザクションは終了しない。

COMMIT文を発行すれば、変更を確定できる。
ROLLBACK文を発行すれば、変更を取り消すことができる。
トランザクション実行中、変更した行データをロックしている時、他のユーザが同じ行を変更しようとするとロック解除街となる。
トランザクションが修了するとロックは解除される。
ほかのユーザは、変更中のデータを参照できない。
変更中の行データをSELECT文により検索すると、変更前のコミットされたデータが戻される。

COMMIT文で修了すれば、変更は確定される。変更前の状態は完全に失われる。
ROLLBACK文で終了すれば、変更は取り消され、データは元に戻される。
すべてのセーブポイントは解放(削除)される。
ロックは解除され、他のユーザは、その行データを更新できる。
他のユーザは、確定または取り消された結果を参照できる。
トランザクションは、COMMIT(確定)あるいは、ROLLBACK(取消)のいずれかによって、終了する。
終了後、最初のDML文が発行されると、次のトランザクションが始まる。
COMMIT文発行直後、ROLLBACK文を実行しても、新しいトランザクションは始まらない。

Oracleでは、通常のSELECT文を実行しても、ロック待ちにはならない。
SELECT文でロックを使うケースとして、明示的にロックを確保するSELECT ~FOR UPDATE文がある。
例)
SELECT EMPNO, ENAME, SAL FROM EMP WHERE DEPTNO = 30
FOR UPDATE WAIT 10 ORDER BY EMPNO;

検索対象となった行が行レベルで排他ロックされる。
検索対象の行が他のユーザによって、すでにロックされている場合は、そのロックの解放を10秒間だけ待機させる。
SELECT ~ FOR UPDATE句を指定すると、検索対象となった行は、ロックされる。

あわせて読みたい