読者です 読者をやめる 読者になる 読者になる

潜伏バグからのロングフリーズ

Javaっぽいエンジニアの徒然草

地味に便利なOracle置換変数

PrimaryKeyが同じ複数のテーブルがあるときに、データを書き換えながら導通確認したい場面がありました。

 

例えばテーブル構成が以下のようなときに、

「TABLE_01.COL1がAの時の動作、Bの時の動作、Cの時の動作がみたいなぁ〜」

「TABLE_02.COL4がDの時の動作、Eの時の動作もみたいなぁ〜」

「確認したいレコードは1件だけじゃなくてたくさんあるなぁ〜」

という場面です。

 

TABLE_01

  KEY1

  KEY2

  COL1    (COLUMNのことです)

  COL2

  COL3

 

TABLE_02

  KEY1

  KEY2

  COL4

  COL5 

 

以下、SQL Developerでの作業メモです。

 

普通にやると、

--現在の値確認用SQL

select * from TABLE_01 where KEY1='xxx' and KEY2='yyy';

 

--TABLE_01のデータ更新用SQL

update TABLE_01 set COL1='A' where KEY1='xxx' and KEY2='yyy';

 

--TABLE_02のデータ更新用SQL

update TABLE_02 set COL4='D' where KEY1='xxx' and KEY2='yyy'; 

 

などなど書いて、COL1をCOL2に書き換えたり、'A'を'B'に書き換えたりしながら

ゴリゴリやるわけです。

書き換える箇所が多くて面倒です。

 

■そんなあなたにDEFINEコマンド

置換変数を事前に定義して値を設定することができます。

設定した変数は、スクリプトの中で繰り返し使用できます。

 

Oracleの置換変数って?

アンパサンド2つの後ろに変数名を定義 -> 永続変数 (ex. &&value)

アンパサンド1つの後ろに変数名を定義 -> 一時変数 (ex. &value)

 

■例

DEFINE key1 = 'xxx';

DEFINE key2 = 'yyy';

変数key1に'xxx'という文字列、key2に'yyy'という文字列をセットしました。

セットした内容は、一般的にはDEFINEコマンドで確認するようです。

DEFINE;

実行すると定義された変数の一覧が表示されます。

が、個人的には不要な変数までズラズラ出てくるのが邪魔なので、

select &key1, &key2 from dual; 

のように確認しています。 

自分で定義したやつだけ見れればいいんです。

 

以上の内容をまとめて、最初に書いたものを書き換えてみます。

-- 別のkeyのデータを扱う場合はここを1回実行して変数を書き換えます

DEFINE key1 = 'xxx';

DEFINE key2 = 'yyy';

select &key1, &key2 from dual;

 

-- 現在の値確認用SQL

select * from TABLE_01 where KEY1 = &key1 and KEY2 = &key2;

 

-- TABLE_01のデータ更新用SQL

update TABLE_01 set COL1='A' where KEY1 = &key1 and KEY2 = &key2;

 

--TABLE_02のデータ更新用SQL

update TABLE_02 set COL4='D' where KEY1 = &key1 and KEY2 = &key2;

 

こんなスクリプトになりました。

COL1とかCOL4とかは直接書いたほうが分かりやすいのでそのままにしています。

元のコードでは各SQLのwhereのkeyを合計6箇所書き換える必要がありましたが、

新しいコードではDEFINEの2箇所を書き換えて実行するだけになりました。

 

以上、地味に便利なOracle置換変数の利用方法でした。

 

---------

ここで書いたのはあくまで我流なので、本来の使い方はグーグル先生に聞いてください