現在勉強しているオラクルマスターブロンズ（SQL）についてまとめたものです。
自分のアウトプット用なので見やすさなどは期待しないでください。
DML文によるデータの追加・更新・削除
INSERT文によるデータの追加
INSERT文を使用すると、表にデータを追加できる。
列名と値は1対1の関係になり、追加する値は列に定義されているデータ型と一致させる必要がある。
また、文字値や日付値を追加する場合は値を一重引用符で囲む必要がある。
INSERT句に指定する列名を省略する場合は、VALUES句には、表に定義されている列と同数の値を、表の列構成と同じ順番で指定する必要がある。
INSERT句に列名を指定する場合は、VALUES句には、列名のリストと同数の値を、同じ順番で指定する必要がある。
NULL値を含む行の追加
NULL値を含む行を追加する方法は、以下の2種類がある。
| 手法 | 説明 |
|---|---|
| 暗黙的手法 | 列名のリストから対象の列名を省略する。省略された列にはNULL値が指定される。 |
| 明示的手法 | 1.NULLキーワードを指定する。 2.文字値や日付値を格納する列に対して、「''」（空の文字列）を指定する。 |
DESCコマンドで表の定義情報を確認した際に、NULL?の列にNOT NULLが表示されている列には、NULL値を設定することはできない。
副問い合わせを使用したデータの追加
INSERT文では、副問い合わせで取り出したデータを表に追加することもでき、値を既存の表から取得して複数行のデータを一度に追加できる。
INSERT文に副問い合わせを記述する場合、以下の注意点がある。
- 副問い合わせを囲む（）は、必須ではない
- VALUES句は指定しない。
- INSERT句に指定する列名を省略する場合は、副問い合わせのSELECT句には表に定義されている列と同数の値を、表の列構成と同じ順番で指定する。
- INSERT句に列名を指定する場合は、副問い合わせのSELECT句には、列名のリストと同数の値を、同じ順番で指定する。
INSERT文では、「INSERT INTO 表名 SELECT・・・」という構文の副問い合わせ以外にも、表名の代わりに副問い合わせを使用する「INSERT INTO (SELECT・・・) VALUES・・・」や、列の値の代わりに副問い合わせを使用する「INSERT INTO 表名 VALUES ((SELECT 列名 FROM・・・), (SELECT 列名 FROM・・・)・・・)」など、いろいろな副問い合わせを指定できる。
列名の代わりに使用する場合、副問い合わせは単一行副問い合わせ、かつ単一列副問い合わせになる。
UPDATE文によるデータの更新
UPDATE文を使用すると、既に表に格納されているデータを更新でき、条件を省略するとすべての行が更新される。
また、SET句に「列名=値」の組み合わせをカンマで区切って複数指定すれば、1つのUPDATE文で複数の列を更新することができる。
複数の副問い合わせを含むUPDATE文は、複数列副問い合わせを使用したUPDATE文に書き換えることができる。
DELETE文によるデータの削除
DELETE文を使用すると、表に格納されているデータを行単位で削除できる。
なお、条件を省略するとすべての行が削除され、指定した条件を満たす行が複数ある場合は、そのすべての行が同時に削除される。
DELETE文のWHERE句に副問い合わせを使用した条件を指定すると、副問い合わせで取り出したデータを元にして削除する行を指定できる。
トランザクションの制御
トランザクションとは
トランザクションとは、1つまたは複数の操作によって完結する、一連のデータ操作をまとめた論理的な処理単位で、Oracleサーバーはトランザクションとしてまとめられた一連のデータ操作を必ず以下のいずれかとして処理する。
- すべての操作を確定する（コミット）
- すべての操作を取り消す（ロールバック）
トランザクションの構成要素
| 種類 | 構成要素 |
|---|---|
| DML（データ操作言語） | SELECT文、INSERT文、UPDATE文、DELETE文など。 トランザクションは、論理的な最小作業単位としてあつかわれる1つまたは複数のDML文で構成される。 |
| DDL（データ定義言語） | CREATE文、ALTER文、DROP文など。 トランザクションは1つのDDL文のみで構成される。 |
| DCL（データ制御言語） | GRANT文、REVOKE文など。 トランザクションは1つのDCL文のみで構成される。 |
DMLを含むトランザクションは、Oracleサーバーに接続後または直前のトランザクション終了後、最初のDML文が実行されたときに自動的に開始され、以下のいずれかのイベントが発生した際に終了する。
- COMMIT文またはROLLBACK文の実行
- DDL文の実行（自動コミット）
- DCL文の実行（自動コミット）
- ユーザーによるSQL DeveloperやSQL *Plusの終了（自動コミット/自動ロールバック）
- マシン障害やシステムクラッシュの発生（自動ロールバック）
明示的なトランザクション制御
トランザクションは、以下のトランザクション制御文を使用することで、明示的に制御できる。
| トランザクション制御文 | 説明 |
|---|---|
| COMMIT | 一連の処理（データの追加・更新・削除）をすべて確定し、トランザクションを終了する。 その後でROLLBACK文を実行しても、確定した処理を取り消すことはできない。 |
| SAVEPOINT | セーブポイントを作成する。トランザクションは継続する。 |
| ROLLBACK | 一連の処理（データの追加・更新・削除）をすべて取り消し、トランザクションを終了する。 |
| ROLLBACK TO | 指定したセーブポイントまでの処理を取り消す。トランザクションは継続する。 なお、指定されたセーブポイントよりも後に制作したセーブポイントは破棄される。 |
トランザクション内の1つのDML文でエラーが発生した場合は、そのDML文だけが自動的に取り消され（文レベルのロールバック）、トランザクション全体はロールバックされず、終了もしない。
暗黙的なトランザクション処理
トランザクション制御文を実行していない場合でも、トランザクション処理が実行される場合があり、これを暗黙的なトランザクション処理という。
暗黙的なトランザクション処理には以下の2種類がある。
| 種類 | トランザクション処理が実行されるタイミング |
|---|---|
| 自動コミット | ・DDL文の実行時 ・DCL文の実行時 ・SQL DeveloperまたはSQL Plusの正常終了時 |
| 自動ロールバック | ・SQL DeveloperまたはSQL Plusの異常終了時 ・システム障害発生時 |
TRUNCATE文
表に格納されているすべてのデータを削除する場合は、TRUNCATE文を使用することもできる。
TURNCATE文には以下の特徴がある。
- 削除するデータを指定できない
- DDL文なので実行時には自動コミットが実行される
- 自動コミットが実行されるので処理をロールバックできない
- ロールバック用のデータを生成する必要がないため、DELETE文よりも短時間でデータを削除できる
このため、大量のデータが格納されている表の全てのデータを削除する場合などにTRUNCATE文を使用すると効果的。
TRUNCATE文を実行した場合、操作対象の表にDELETEトリガー（表に対してDELETE文が実行された場合に自動的に起動されるプログラム）が定義されていても、実行されない。
同時実行制御
Oracleサーバーでは、複数のユーザーが同時にSQL文を実行できるが、このとき複数のユーザーが同時に同じデータに対して操作を行うと矛盾が発生する可能性がある。
そこで、Oracleサーバーはこのような場合でもデータに矛盾が発生しないように制御している。
ユーザーが表に対して行う操作は以下の2種類に分類できる。
- 読み取り操作（SELECT文）
- 書き込み操作（INSERT文、UPDATE文、DELETE文）
読み取り一貫性
あるユーザーがデータを読んでいるとき、他のユーザーが読んでいる途中のデータを変更するような問題に対応するために、Oracleサーバーには読み取り一貫性の機能がある。
この目的は、DML操作開始時点での最新のコミット済みデータが、常に各セッションに戻されるように保証することである。
読み取り一貫性は、以下のような仕組みで実現されている。
- Aさんが読み取り操作（DML文）を開始する。
- Bさんがデータの変更操作を実行して、コミットする。
- データの変更操作を受け付けたOracleサーバーは、変更前のデータをUNGOセグメントという特別な領域にコピーしてから、データを変更する。
- Aさんには、UNGOセグメント内のデータを戻す。
これはあるセッションが行った変更処理の内容は、その処理がコミットされるまで別のセッションからは参照できない（ロールバックされる可能性のある未確定のデータは別のセッションからは参照できない）ということでもある。
なお、同じユーザーでも、セッションが異なればほかのユーザーと同じように変更途中の未コミットデータは参照できない。
ロック
複数のユーザーが同時に書き込み処理を行う場合は矛盾が発生する可能性があり、書き込むタイミングが一瞬でも遅いほうの結果で上書きされ、どちらかの更新処理の結果が失われてしまうことになる。
Oracleサーバーでは、このような矛盾が発生しないように、書き込み処理に対してロックのメカニズムを使用している。
ロックはOracleサーバーによって自動的に管理され、ユーザーがデータの変更処理を実行すると、変更対象の行ごとに排他ロック（行レベルの排他的なロック）をかけてからデータを変更する。
そのため、既にロックがかかっている行に対してロックが必要な処理を行った場合、その処理はすでにかかっていたロックが解除されるまで待機する。
なお、ロックはトランザクション終了時まで保持される。
FOR UPDATE句による排他ロック
Oracleサーバーのデフォルトの動作では、SELECT文の実行時にはロックはかからないが、場合によってはロックをかけることが必要なこともある。
このような場合は、SELECT文のFOR UPDATE句を使用し、SELECT文の実行時に対象の行に行レベルの排他ロックをかけることができる。
FOR UPDATE句の構成要素
| 要素 | 説明 |
|---|---|
| NOWAIT | 待機せずにすぐエラーを戻す。 |
| WAIT 秒数 | 指定した秒数だけ待機し、その間にロックが解除されない場合はエラーを戻す。 |
| OF 表名.列名 | 指定した列を含む表の行のみにロックを限定する。 |