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型 ・VARCHAR2型 ・LONG型 ・CLOB型 ・NCLOB型
数値型	列に数値データを格納できるようにするために使用するデータ型	・NUMBER型
日付型	列に日付データを格納できるようにするために使用するデータ型	・DATE型
バイナリ型	列にバイナリデータを格納できるようにするために使用するデータ型	・RAW型 ・LONG RAW型 ・BLOB型 ・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までのバイナリデータを格納できる読み取り専用のデータ型。 列に格納したデータは、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制約（一意制約）	重複値を許可しない。 複数のNULL値は許可する。 自動的に制約と同じ名前の一意牽引（重複値が許可されない牽引）が作成される。
PRIMARY KEY制約（主キー制約）	表内の各行を一意に識別できる値のみ許可する。 重複値及びNULL値は許可しない。 表に1つのみ定義でき、自動的に制約と同じ名前の一意牽引が作成される。
FOREIGN KEY （外部キー制約・参照整合性制約）	参照先の列にある値またはNULL値のみ許可する。
CHECK制約（チェック制約）	指定した条件に対してTRUEまたはNULLを戻す値のみ許可する。 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コマンドでも列名やデータ型を確認できなくなる。