DDL文を使用した表の作成と管理

現在勉強しているオラクルマスターブロンズ（SQL）についてまとめたものです。
自分のアウトプット用なので見やすさなどは期待しないでください。

# 表の作成と削除

Oracleデータベースには、表、ビュー、牽引といった様々なデータ構造を格納することができ、これらを総称して**データベースオブジェクト**と呼ぶ。

## スキーマとは

スキーマとは、データベースオブジェクトを管理するために使用される**論理的な概念**で、Oracleサーバーの各ユーザーは、ユーザー名と同じ名前のスキーマを1つ所有しており、各ユーザーが作成したオブジェクトは、そのユーザーが所有するスキーマに格納される。
このことから、スキーマはユーザーが所有するオブジェクトのリストと考えることもできる。
ただし、スキーマはあくまでも論理的な概念であり、実際に領域が割り当てられるわけではない。

あるユーザーが、自身が所有するスキーマ以外のスキーマ内のオブジェクトを参照する場合は、オブジェクト名に接頭辞（スキーマ名）を付けて**スキーマ名.オブジェクト名**の形式で指定する必要がある。

Oracleデータベースにはいろいろな種類のオブジェクトがあり、各ユーザーが所有するスキーマに含まれるオブジェクトは**スキーマオブジェクト**と呼ばれる。
最も代表的なスキーマオブジェクトは**表**だが、他にも**ビュー、順序、牽引、シノニム**などがある。

ビューは、1つまたは複数の表やビューを元に制作された**論理的な表**で、**SELECT文に名前を付けてデータベースに保存したもの**といえる。
そのため、ビューには実データは含まれず、ビューに定義あれているSELECT文を用いて、表からデータを取り出す。
このように、ビューはあたかも表であるかのように使用できるため**仮想表**とも呼ばれる。

ビューを使用すると**複雑なSELECT文を簡略化**でき、すべてのユーザーには見せたくない列については、その列を除いたビューを予め作成しておき、一般ユーザーにはそのビューを参照させることで**セキュリティの機能**を実装できる。

順序は、指定した規則に従って**一意な番号（順序値）を自動的に生成する**スキーマオブジェクトで、表の主キー列には重複した値を登録できないため、順序を使用して一意な番号を生成すると便利。
順序を作成する際は、START WITHやINCREMENT BYなどのオプションを指定できる（省略した場合は、いずれも規定値の1が設定される）。

新しい番号を参照するには**順序名.NEXTVAL**を指定し、使用している**セッションで最後に参照した順序番号**を確認するには**順序名.CURRVAL**を指定する。
ここで指定しているNEXTVALやCURRVALは**疑似列**で、実際に表に登録されている列ではないが、列を参照したサイト登用にデータを返す項目を示すキーワード。

牽引は、**検索のパフォーマンスを向上させるために使用する**スキーマオブジェクトで、**データの一意性（重複がないこと）をチェックする**際にも使用されます。
Oracleデータベースでは、各表の列または列の組み合わせで牽引を作成でき、1つの表に複数個を作成できる。

SQL文の実行時に、どの牽引を使用するか、あるいはいずれの牽引も使用しないかはOracleサーバーの**オプティマイザー**という機能が**自動的に判断**する。

シノニムは、**オブジェクトの別名**を表すスキーマオブジェクトで、名前が長いオブジェクトに短い名前のシノニムを設定しておくと便利。
また、他のユーザーの所有するスキーマ内のオブジェクトを参照する際は、スキーマ名.オブジェクト名という形式でオブジェクトを指定することが必要だが、シノニムを作成しておけば、より簡単に参照できる。

シノニムには、作成したユーザーだけだ参照できるプライベートシノニムと、すべてのユーザーが参照できるパブリックシノニムの2種類がある。

## オブジェクトの命名規則

オブジェクトを作成する際は、オブジェクトの名前は以下の命名規則に従う必要がある。

- 長さは30バイト以下
- **先頭の文字は数字以外の文字**
- 使用できる文字は、英数字および数字、カタカナ、ひらがな
- 使用できる記号は、「**_, $, #**」の3種類のみ
- 同一のスキーマ内で重複する名前は指定できない
- Oracleサーバーの予約語は使用できない
- アルファベットの**大文字・小文字は区別されない**

## 表の作成

データベースに表を作成するには、CREATE TABLE文を使用するが、作成するには**CREATE TABLE権限**が必要。
また、自分が所有しているスキーマ以外に表を作成するには、CREATE ANY TABLE権限が必要。

CREATE TABLE文はDDLである。
DDLを実行すると**データディクショナリ**と呼ばれるOracleの管理情報を格納するために使用される特別な表に、**オブジェクトの定義情報**が登録されたり、登録されている定義情報が変更・削除されたりする。

表の作成時に、列の定義に**DEFAULTオブジェクト**を指定すると、その列にデフォルト値（データの追加時にその列に対する値の指定を省略した場合に、その列に格納される値）を設定できる。

デフォルト値には、**リテラル値、式またはSQL関数**を指定でき、Oracle 12cからは、順序オブジェクトを参照する**NEXTVAL疑似列やCURRVAL疑似列**も指定できるようになった。
一方、**別の列の名前は指定できない**。

## 表の削除

表を削除するには、DROP TABLE文を使用するが、削除できるのは、表の所有者または**DROP ANY TABLE権限**を持つユーザーのみ。

表を削除すると表内の全てのデータに加えて、表に定義されている**制約や牽引も削除される**一方、**表を参照するビューやシノニムは削除されない**（無効になる）。
なお、DROP TABLE文を実行すると表は**ごみ箱に移動され、**完全に削除されるわけではない。

表を完全に削除するにはPURGE句を指定し、削除された表をごみ箱から復元するにはFLASH BACK TABLEを使用する。

# データ型

## データ型とは

データ型とは、**Oracleサーバーで扱うデータの形式**で、表の作成時に列ごとに定義する。
列には定義されたデータ型のデータしか格納できないので、適切に定義することで意図しないデータの格納を防止することができる。
主なデータ型は、以下の5つに分類できる。

| 分類 | 説明 | 主なデータ型 |
|:-:|:--|:--|
| 文字型 | 列に文字データを格納できるようにするために使用するデータ型 | ・CHAR型<br>・VARCHAR2型<br>・LONG型<br>・CLOB型<br>・NCLOB型 |
| 数値型 | 列に数値データを格納できるようにするために使用するデータ型 | ・**NUMBER型** |
| 日付型 | 列に日付データを格納できるようにするために使用するデータ型 | ・**DATE型** |
| バイナリ型 | 列にバイナリデータを格納できるようにするために使用するデータ型 | ・RAW型<br>・LONG RAW型<br>・BLOB型<br>・BFILE型 |
| ROWID | 列にROWIDを格納できるようにするために使用するデータ型 | ・ROWID型 |

文字型には次の5種類がある。

| データ型 | 説明 |
|:-:|:--|
| **VARCHAR2型** | 最大4000バイトまでの文字データを格納できる**可変長**のデータ型で**最大サイズの指定が必要** |
| **CHAR型** | 最大2000バイトまでの文字データを格納できる**固定長**のデータ型で最大サイズの指定を省略した場合、**デフォルトの1が使用される** |
| LONG型 | 最大2GBまでの文字データを格納できる可変長のデータ型 |
| CLOB型 | 最大4GBまでの文字データを格納できるデータ型 |
| NCLOB型 | 最大4GBまでのUnicode文字データを格納できるデータ型 |

LONG型が定義された列には以下の制限がある。

- 副問い合わせを使用した表の制作時に、**LONG列はコピーできない**（エラーが発生する）
- **GROUP BY句とORDER BY句に指定できない**
- **1つの表に1つだけ**定義できる（LONG列またはLONG RAW列のどちらか1つだけ）
- **制約を定義できない**

NUMBER型には以下の2つのオプションがある。

- 最大精度：格納する数値データの最大精度を指定する（最大38桁）。
- 位置取り：格納する数値データの小数点以下の桁数を指定する。

最大精度と位置取りの両方を省略すると、最大38桁の浮動小数点（指定した値によって小数点以下の桁数が変わる）を格納でき、最大精度を指定して位置取りを省略した場合は、最大精度に指定した桁数の整数値を格納できる。

DATE型に格納されるデータのサイズは**固定長、7バイトで、世紀、年、月、日、時、分、秒が内部的な数値書式**の形式で格納される。

バイナリデータ型には次の4種類がある。

| データ型 | 説明 |
|:-:|:--|
| RAW型 | 最大2000バイトまでのバイナリデータを格納でき、可変長のデータ型、最大サイズの指定は省略できない。 |
| LONG RAW型 | 最大2GBまでのバイナリデータを格納でき、可変長のデータ型、LONG型と同様の制約がある。 |
| BLOG型 | 最大4GBまでのバイナリデータを格納できるデータ型。 |
| BFILE型 | 最大4GBまでのバイナリデータを格納できる読み取り専用のデータ型。<br>列に格納したデータは、Oracleサーバーのデータファイル上ではなく、OS上のファイルに保存される。 |

CLOB、BLOB、BFILEは**LOB型（Large OBject型）**と呼ばれ、大きなオブジェクトを格納できる。
LOB型は、表とは異なる場所に実際のデータを格納し、表の中にはデータを収めた場所へのポインタ情報のみを格納して使うことができる。

LONG列やLONG RAW列と異なり、**1つの表に複数のLOB型の列を定義できる**。

ROWID型はROWID疑似列（実際には列としては定義されていないが、表に定義された列のように使用できる、疑似的な列）から戻される値を列に保存する場合に使用され、表の各行に割り当てられている一意なアドレス。

TIMESTAMP型は、**DATE型を拡張したデータ型**で、DATE型の列が格納できる値に加えて、**秒の小数点以下の値も格納**できる。
TIMESTAMP型には、以下の2種類のバリエーションがある。

| バリエーション | 説明 |
|:-:|:--|
| TIMESTAMP WITH TIME ZONE | タイムゾーンの時差を含むことができ、列の一部として格納され表示される |
| TIMESTAMP WITH LOCAL TIMEZONE | タイムゾーンの時差を含むことができ、列の一部として格納されず、データ取得時にローカルセッションのタイムゾーンの値で表示される |

INTERVAL YEAR TO MONTH型とINTERVAL DAY TO SECOND型は、それぞれ指定した**2つの日付・時刻の間隔を格納**できるデータ型で、これらの違いは格納する値の単位にある。

| データ型 | 説明 |
|:-:|:--|
| INTERVAL YEAR TO MONTH | 2つの日付・時刻の間隔を**年及び月の単位**で格納する |
| INTERVAL DAY TO SECOND | 2つの日付・時刻の間隔を**日、時、分、秒の単位**で格納する |

# 制約の種類と指定方法

## 制約とは

制約とは、表に格納するデータに関するルールで、定義したルールに反するデータの追加や、ルールを満たさなくなるようなデータの更新・削除が行えなくなる。
制約のチェックは、**データの変更時（追加、更新、削除）に自動的**に行われるため、適切に制約を定義することで、ビジネスルールに反する無効なデータが表に格納されることを未然に防止したり、操作ミスによるデータの削除を防止したりすることができる。
Oracleデータベースでは、以下の5種類の制約を使用できる。

| 種類 | 説明 |
|:-:|:--|
| NOT NULL制約 | **NULL値を許可しない**。 |
| UNIQUE制約（一意制約） | **重複値を許可しない**。<br>**複数のNULL値は許可する**。<br>自動的に**制約と同じ名前の一意牽引**（重複値が許可されない牽引）が作成される。 |
| PRIMARY KEY制約（主キー制約） | 表内の各行を**一意に識別できる値のみ許可する**。<br>**重複値及びNULL値は許可しない**。<br>**表に1つのみ**定義でき、自動的に**制約と同じ名前の一意牽引**が作成される。 |
| FOREIGN KEY<br>（外部キー制約・参照整合性制約） | **参照先の列にある値またはNULL値のみ許可する**。 |
| CHECK制約（チェック制約） | 指定した**条件に対してTRUEまたはNULLを戻す値のみ許可する**。<br>**1つの列に複数定義できる**。 |

制約は**列レベル（列レベル制約）または表レベル（表レベル制約）**で定義でき、この2つの違いは主に構文による違いだけで、**作成された制約の機能は同じ**。
制約を定義する際には以下の点にも注意が必要。

- CONSTRAINT制約名は省略可能（省略時にはOracleサーバーが「**SYS_Cｎ**」の形式で名前を作成する）
- 表レベル制約の構文では、**（）内に制約を定義する列**を1つ以上指定する
- 1つのCREATE TABLE文の中に列レベル制約と表レベル制約を**同時に指定できる**
- **NOT NULL制約は列レベルでのみ定義できる**
- 複数の列の組み合わせから成る複合の制約は**表レベルでのみ**定義できる
- 1つの列に**複数の列レベル制約**を定義する場合は、**改行またはスペース**で区切る
- 1つの表に**複数の表レベル制約**を定義する場合は、**カンマ**で区切る

なお、制約は表作成後に定義することもでき、既存の表にデータが格納されている場合でも、**格納されているデータが、追加する制約のルールに従っている場合は、制約を追加できる**。

また、制作した制約はデフォルトで有効な状態になるので、制約のルールに従っていることが分かっている大量のデータを追加する場合は、一時的に制約を無効化し、データの追加後に再度有効化する。

## FOREIGN KEY制約（外部キー制約）

FOREIGN KEY制約を定義する場合は、以下の値を指定する必要がある。

| 指定値 | 説明 |
|:-:|:--|
| 現表名 | 参照先の表の名前で、参照する表を**親表**、制約が定義されている表を**子表**と呼ぶ。なお、親表には別の表だけでなく、同じ表を指定することもできる。 |
| 参照する列名 | 親表に定義されている、参照先の列の名前。ただし、**UNIQUE制約またはPRIMARY KEY制約が定義されている列しか参照できない。** |

子表内に親表を参照する行が存在する場合は、親表の参照されている行を削除することはできない。
また、FOREIGN KEY制約を定義すると依存する行が無くても**親表は削除できなくなる**（親表を削除したい場合は、**先に子表を削除**する必要がある）。
この削除時の動作は制限ルールと呼ばれる、**デフォルトの動作**である。

FOREIGN KEY制約と**ON DELETE CASCADE**キーワードや**ON DELETE SET NULL**キーワードを**組み合わせて定義する**と、親表のデータを削除した場合のデフォルトの動作を次のように変更できる。

| キーワード | 説明 |
|:-:|:--|
| 設定なし | 子表に親表を参照する行が存在する場合は、親表の参照されている行は削除できない。 |
| **ON DELETE CASCADE** | 子表に親表を参照する行が存在する場合に、親表の参照されている行を削除すると、**子表の参照している行も削除される**。列レベル構文は使用できない。 |
| **ON DELETE SET NULL** | 子表に親表を参照する行が存在する場合に、親表の参照されている行を削除すると、**子表の参照している行にNULL値が設定される**。列レベル構文は使用できない。 |

## CHECK制約

CHECK制約の条件には、WHERE句に指定できる条件と同様の指定を行えるが、以下の指定は**許可されていない**。

- **CURRVAL、NEXTVAL**、LEVEL、ROWNUM疑似列の参照
- **SYSDATE、USER**、UID、USERENV関数の呼び出し
- **ほかの行の値を参照する**問い合わせ

# 副問い合わせを使用した表の作成と表構造の変更

## 副問い合わせを使用した表の作成

CREATE TABLE文と副問い合わせを組み合わせると、**既存の表を基にして新しい表を作成でき**、同時に、副問い合わせで取り出したデータを**表にコピー**できる。

列名の指定を省略した場合は、副問い合わせのSELECT句で指定した列名または列別名と同じ名前の列が定義されるが、**計算式や関数**を使用している場合は、**CREATE TABLE文で列名**を指定するか、**副問い合わせのSELECT句で列別名**を指定して新しい列名を指定する必要がある。

一方、列名を指定した場合はその列名が表に定義されるが、この場合は副問い合わせのSELECT句のリストと同じ個数の列名を指定する必要がある。
また、列の定義には列名に加えて、**デフォルト値や制約を指定することもできるが、データ型は指定できない**。

各列のデータ型と明示的に定義されたNOT NULL制約は新しい表にもコピーされるが、**NOT NULL制約以外の制約はコピーされない**（PRIMARY KEY制約を定義した際に暗黙的に設定されるNOT NULL制約もコピーされない）。

## 表構造の変更

Oracleサーバーでは作成した表の構造を後から変更することもできる。
**ALTER TABLE文**を使用すると以下の操作を実行できる。

- **表に新しい列を追加する**
- 既存の列のデータ型を変更する
- 既存の列にデフォルト値を設定する
- **既存の列を削除する**
- 既存の列の名前を変更する
- 表を**読み取り・書き込みモード**にする
- 表を**読み取り専用モード**にする（**表の削除は実行できる**）

列の追加時にDEFAULTオプションを指定すると、今後登録される行だけでなく、既存の行に新しく追加した列にもデフォルト値が設定される。
また、既存のデータが**制約のルールに従っている場合**、新しい列を追加する際に、列レベル制約の構文を使用して同時に**制約を追加**できる。
つまり、列の追加とともにNOT NULL制約を追加できるのは、**表が空の場合**、または**DEFAULTオプションを指定してデフォルト値にNULL以外を設定した場合**となる。

表の仕様変更などがある場合には、既存の列の**データ型、サイズ、およびデフォルト値**を変更できる。
列の定義を変更する際には以下の注意点がある。

- サイズまたは精度の**増加はいつでもできる**
- 列のサイズは、列に**NULL値のみが含まれている**、表に**行がない**場合のみ**減少**でき、**既存の列の値未満には減少できない**
- 列に**NULL値のみ**が含まれている場合は、**データ型を変更できる**
- 列にNULL値以外のデータが含まれていても、**サイズを変更しない場合は、CHAR型からVARCHAR2型**、または**VARCHAR2型からCHAR型**へはデータ型を変更できる
- 列の**デフォルト値の変更は、以後の表への挿入のみ**に適用される

基本的に、サイズを大きくする変更はいつでも可能であり、サイズを小さくする変更については、**既存のデータが入らなくなるようなサイズへの変更はできない**。

列を削除する場合は、以下の点に注意する必要がある。

- 削除の対象の列にはデータが存在する場合も、存在しない場合もある
- 列を削除した後も、表には**1つ以上の列を残す**必要がある
- 列の削除は**元に戻せない**
- 別の列から参照される主キーは、**CASCADEオプション**を指定しない限り削除できない
- 列に多くの値が含まれている場合は、削除に時間がかかる
- 1度に1つの列のみを削除する場合は、**ALTER TABLE DROP COLUMN文**も使用できる

大勢のユーザーがデータベースを利用している時間帯に、大きな表から列を削除するような負荷の高い処理を行わせたくない場合は、列を削除するのではなく、列に**UNUSEDマーク**を設定する。
UNSUEDマークが設定されている列は、**削除された列と同等の扱い**になるため、**同じ名前の列を作成できる**。
なお、UNUSEDマークが設定されるとDESCコマンドでも列名やデータ型を確認できなくなる。