Oracleのテーブル（表）の超簡易メモ。
|
|
|
|
|
テーブルの最も基本的な作成方法。
create table テーブル名 ( 項目名 データ型 〔default デフォルト値〕〔制約定義〕, 項目名 データ型 〔default デフォルト値〕〔制約定義〕, …, 〔制約定義〕 ) 〔表領域等の定義〕 ;
テーブル名や項目名は大文字でも小文字でもいいが、暗黙に大文字に変換される。
ダブルクォーテーションで囲むと変換されない。（select文などで指定する際もダブルクォーテーションで囲む必要が出てくる）
データ型の宣言の後に「default デフォルト値」（例：「default 123」「default
'abc'」）を付けることで、その項目のデフォルト値を設定することが出来る。
省略した場合のデフォルト値はNULL。
項目を列挙したinsert文で項目を指定しなかった場合や、insert文やupdate文でdefaultキーワードを指定した場合に、デフォルト値が使用される。
各項目のデフォルト値を確認するには、以下のSQLを実行する。[2010-10-23]
select column_name,data_default from user_tab_columns where table_name='テーブル名' ;
別のテーブルの定義（項目名やデータ型）とデータを引き継ぐ。
create table テーブル名 as select * from 元テーブル ;
ただし、NOT NULL制約は引き継がれるが、インデックス定義（プライマリキーも）やデフォルト値設定は引き継がれない。
プライマリキーだった項目はNOT NULLにもならない。
後からプライマリキーを作成すれば、自動的にNOT NULLも付く。
WHERE条件を付ければ、それに該当するデータだけコピーされる。[2010-06-16]
1レコードも合致しないようなWHERE条件にすれば、テーブル定義（項目名とデータ型）の複製だけが行える。
create table EMP_BK as select * from EMP where 1 = 0 ;
作成元となる項目を列挙すれば、テーブルの項目を減らすことが出来る。
また、並び順も列挙した順番になる。（Oracleでは項目追加すると末尾に追加されてしまう為、作り直すにはこのやり方が便利）
create table テーブル名 as select 項目名, 項目名, … from 元テーブル ;
項目に別名（エイリアス）を付けることで、項目名を変更することが出来る。
また、単なる値や計算式に項目名を付けることで、新しい項目を増やすことも出来る。
create table テーブル名 as select 項目名 新しい名前, 値や式 新しい名前, … from 元テーブル ;
値や式を指定した場合は、その演算結果の型でデータが作られる。
思ったのと違う型になったのなら、後から変更する。
また、新しく作成される側のテーブルで新しい項目名を指定することも可能。
（この形式の場合、データ型を指定できそうな感じがするが、出来ない）
create table テーブル名 ( 項目名 〔default デフォルト値〕〔制約定義〕, 項目名 〔default デフォルト値〕〔制約定義〕, …, 〔制約定義〕 ) as select 項目名, 項目名, … from 元テーブル ;
ただ、ここまでやるなら、普通のcreate文でテーブルを作って、insert〜select文でデータを入れた方が分かりやすい気がしないでもない＾＾；
→insert〜select文の生成を支援するExcelマクロ（insert-selectを使って新しいテーブルにデータを移送する例）
テーブル名を変更する方法。
alter table テーブル名 rename to 新テーブル名;
rename テーブル名 to 新テーブル名;
なんで2つも方法があるのか不明…たぶんどちらかが新しい文法（あるいはOracle独自と標準SQL？）なのだろう。
いずれにしても、テーブルに付けられている制約名やインデックス名は変わらないので、それらの名前も必要であれば、別途変更しなければならない。
→プライマリキー名の変更方法
あと、変更対象テーブルを使っているマテリアライズド・ビューは無効になるんだそうだ。
create tableで定義されたテーブルの項目（カラム・列）を変更する方法。
カラム名を変更する方法。
alter table テーブル名 rename column 項目名 to 新項目名;
項目名を変えても、制約は以前のまま使える。インデックスの定義も一緒に変わる。
項目名を変えると、それを使用しているビューやトリガーは無効になり、次に使われる時に妥当性チェックが再度実行されるんだそうだ。
カラムのデータ型や整合性制約を変える方法。
alter table テーブル名 modify 項目名 〔新データ型〕〔default 新デフォルト値〕〔新制約定義〕 ;
alter table テーブル名 modify ( 項目名 〔新データ型〕〔default 新デフォルト値〕〔新制約定義〕, … );
alter table テーブル名 modify 項目名 char(5); --データ型をCHAR(5)に変更する alter table テーブル名 modify 項目名 NOT NULL; --NOT NULL制約を付ける alter table テーブル名 modify 項目名 NULL; --NOT NULL制約を外す alter table テーブル名 modify 項目名 default NULL; --デフォルト値をNULLに変更する
テーブルに新しいカラムを追加する方法。
alter table テーブル名 add 項目名 データ型〔default デフォルト値〕〔制約定義〕 ;
alter table テーブル名 add ( 項目名 データ型〔default デフォルト値〕〔制約定義〕, … );
この場合、テーブルの末尾に項目が追加される。
途中に追加することは出来ないので、途中に入れたい場合は別テーブルを作って並べ替えることになる。
デフォルト値を指定すると、追加された項目の値はそのデフォルト値になる。
指定が無い場合はNULLになる。
NOT NULL制約を一緒に付けている場合はデフォルト値の指定は必須だろう。
（データが1件も入っていない場合は、デフォルト値なしでNOT NULL制約を付けても大丈夫）
alter table テーブル名 add 項目名 char(3) default 'zzz' not null;
テーブルのカラムを削除する方法。
alter table テーブル名 drop column 項目名 ;
alter table テーブル名 drop ( 項目名, … );
カラムを使用不可にする方法。
使用不可にするとselect等で使えないし、SQL*Plusのdescコマンドで見ても出てこなくなる。つまり実質的に削除されているのと同じ状態になる。
alter table テーブル名 set unused column 項目名; alter table テーブル名 set unused (項目名, …);
使用不可にしたカラムを実際に削除する方法。
alter table テーブル名 drop unused columns;
使用不可にした項目を戻すことは出来ない。
直接dropするのとunusedにするのは、unusedにする方が実行時間が短いらしい。
ただし本当にdropするまではデータ領域自体は解放されないのだとか。
テーブルを削除するにはdrop文を使う。
drop table テーブル名;
Oracle10gからリサイクルビン（フラッシュバックドロップ）という仕組みが導入され、ドロップしても別のテーブル名に変更されるだけになった。
→リサイクルビンから削除あるいは復元する方法
リサイクルビンを経由せずに直接削除するには、purge句を付ければよい。
drop table テーブル名 purge;
テーブルやカラムにはコメントを付けることが出来る。
comment on table テーブル名 is 'コメント'; comment on column テーブル名.項目名 is 'コメント';
テーブルのコメントの参照方法。
select * from USER_TAB_COMMENTS where TABLE_NAME = 'テーブル名';
※USER_TAB_COMMENTSにはTABLE_TYPEという項目があり、TABLEやVIEWといった値が入っているので、ビューでもコメントが付けられるのだろう。
カラムのコメントの参照方法。
select * from USER_COL_COMMENTS where TABLE_NAME = 'テーブル名';
テーブルを作るとデフォルトでコメントは入っている模様。（空文字列、すなわちNULLだけど）
したがって、コメントを削除するには、NULLをセットすることになる。
comment on table テーブル名 is ''; comment on column テーブル名.項目名 is '';
create table文でデフォルト値や制約と同じようにコメントを設定できて（テーブルや項目の日本語名を付ける）、
設定したコメントがSQL*Plusのdescコマンドで出てくるなら、
けっこう便利なんだけどなぁ。