PL/SQLで、カラム名と同じ名前の変数を定義してはいけない

知らんくて激しくハマった。

説明

以下のようなテーブルがあったとして、

TEST_TABLE

ID	VALUE
1	HOGE
2	FUGA
3	PIYO

以下のような PL/SQL を書いたとする。

DECLARE
  id NUMBER;
BEGIN
  id := 2;

  UPDATE TEST_TABLE
     SET VALUE='update'
   WHERE ID=id
  ;
END;
/

SELECT * FROM TEST_TABLE;

ID=2 のレコードの VALUE を "update" に更新しているつもりの実装。

これを動かすと、 TEST_TABLE は以下のようになる。

TEST_TABLE（更新後）

ID	VALUE
1	update
2	update
3	update

全レコード "update" に更新されてしまう！

なにが起こったか

要は、 UPDATE 文で使っている id は、変数の id ではなく、 TEST_TABLE の ID カラムを参照しているのが原因。

PL/SQL の仕様上、カラム名と同じ変数が SQL 内で使用されている場合、カラム名が優先されるようになっている。

列名の優先順位 | PL/SQLの名前解決

ローカル変数または仮パラメータのいずれかと列の両方に属する名前をSQL文で参照すると、列名が優先されます。

注意:
変数名またはパラメータ名が列名として解釈されると、データが誤って削除、変更または挿入されることがあります。

しっかり公式ドキュメントに注意書きが書いてあった。

つまり、カラム名と同じ名前の変数を定義するのは超危険

知っている人にとっては当たり前の話かもしれないけど、知らんとかなりハマるので要注意。

SQLFiddle サンプル