開発を行っていると、仕様変更等でカラムの追加を行いたいケースが出てくると思いますが、手作業で行うとなると中々面倒です。
- 仕様変更 -
そこでSI Object Browser(以下、OB)の出番です。
OBを使用すると、GUIでのカラム追加だけでなく以下のメリットがあります。
・カラムの追加位置を判別してDDLを自動変更してくれる
・DDL実行時に、必要に応じてデータ退避テーブルを作成できる(オプション)
既存テーブルにカラムを追加する方法
シナリオ
今回は、「T3」テーブルのデータを一覧表示/データ削除を行うことのできるアプリケーションにおいて、
変更前) DELETE文による物理削除
変更後) 削除ステータスによる論理削除
という仕様変更が発生したことを想定してテーブルにカラム追加を行います。
－ 「T3」テーブル定義 －
テーブルの末尾にカラムを追加
まずは、テーブルの末尾に削除ステータス管理用の「DEL_FLG」カラムを追加して[作成]ボタンを押下してみます。
- 末尾へのカラム追加 -
末尾にカラムを追加する場合はALTER TABLE文での変更が可能であり、OBが発行するDDLもALTER TABLE文が採用されます。
また、この場合は既存データが「T3」テーブルにそのまま残ります。
既存カラムの間にカラムを追加
カラムの持つデータの意味合いから、末尾ではなく既存カラムの間に新規カラムを追加したくなることもあると思います。
この場合、ALTER TABLE文で変更することはできないためOBが発行するDDLはDROP TABLE/CREATE TABLE文による再作成が採用されます。
先ほどと同様に「DEL_FLG」カラムを追加し、「▲」ボタンで既存カラムの間に移動したのち [作成]ボタンを押下してみます。
- 既存カラム間へのカラム追加 -
発行されるDDLがALTER TABLE文ではなくDROP TABLE/CREATE TABLE文に変わることを確認できました。
なお、実際のOBの動作としては
①既存カラムの間にカラム追加を行い[作成]ボタン押下
②データが消失する(DROP TABLE/CREATE TABLE文のみ実行される)場合は確認ダイアログを表示
③DDL実行
という流れになります。
- 確認ダイアログ -
このダイアログで「はい」を選択するとDDLの実行によりテーブルの定義が変更されますが、DROP TABLE文により既存のデータは消失することとなります。
データ退避テーブル作成オプション
・既存カラムの間にカラム追加を行いたい
・かつ、テストデータが既に作成済みであるためデータを消したくない
というケースでは、データを退避しつつ再作成を行うオプションが利用できます
メイン画面上部の[ツール]から[オプション]を開き、[定義情報]タブにある[テーブル再作成時に、データ退避テーブルを作成する]をONに変更します。
- オプション – 定義情報タブ -
この設定を行っておくと、
①DROP TABLE文の前に、CREATE TABLE AS SELECT文でデータ退避テーブルを作成する
②CREATE TABLE文の後に、①のデータ退避テーブルからINSERT INTO SELECT文で投入する
動作となり、既存データを残しつつカラムの追加を行うことができます。
([作成]ボタン押下後の確認ダイアログは表示されなくなります)
ただし、以下の注意事項もありますので留意ください。
・データ退避テーブルが作成されるため一時的に該当テーブルのデータ量の2倍、ストレージが使用される
・エラーが発生せず処理が完了した場合は、データ退避テーブルは削除される
(エラーが発生した場合は、データ退避テーブルは削除されない)
・必須列を追加する場合、省略時値を指定する必要がある
(省略時値を指定しない場合、データ退避テーブルからの投入でORA-01400エラーが発生する) [RELATED_POSTS]
データ退避テーブル作成オプション
いかがでしたか？
カラム追加が発生するような仕様変更が発生しないよう、しっかりと設計を行うことはもちろん重要です。
しかし、システム開発の現場ではよくある事例かとも思います。
今回ご紹介した内容を簡単にまとめると以下のようになります。
ご参考の上、機会があればぜひお試しください！
・テーブルの末尾にカラムを追加
⇒ALTER TABLE文による変更が行われ、既存カラムのデータはそのまま残る
・既存カラムの間にカラムを追加
⇒DROP TABLE/CREATE TABLE文による再作成が行われ、データは消える
・既存カラムの間にカラムを追加 + データ退避テーブル作成オプション
⇒DROP TABLE/CREATE TABLE文による再作成に加え、事前に作成されたデータ退避テーブルから
データの投入が行われるため、既存カラムのデータはそのまま残る
あ、テーブル定義を変更した後はSI Object Browser ERのデータベース同期機能を使ってER図への反映も忘れないでくださいね。(笑)
![いまさら聞けない Oracleの基本 [初級編]](https://products.sint.co.jp/hubfs/images/siob/thumb/ora_b.png)
![いまさら聞けない Oracleの基本 [中級編]](https://products.sint.co.jp/hubfs/images/siob/thumb/ora_b2.png)
