DynamoDBばかり触っていたのだが必要に迫られて久しぶりにRDB（Oracle Database）を触っている。SQL忘れた……ましてやPL/SQLなんて。

[やりたいこと：複数スキーマ一括カラム追加]

今回実装しているアプリケーションには、動的にデータベーススキーマを追加する機能がある。そして、それぞれのスキーマに作成されるテーブルは全て同じ名前で、同じテーブルレイアウトとなっている。

とある新機能の実装が予定されており、そのために上記の同一名称のテーブルすべてにカラムを追加する必要が生じた。

テーブル1つだけであれば、ALTER TABLE (テーブル名) ADD COLUMN 〜とかやれば行けるだろう。しかし、テーブルが動的に追加されるという前述の仕様のせいで、事前にテーブル名を指定してSQLを用意することが困難である。

そういう事情で、泣きながらPL/SQLを調べてなんとか実装した。

[できあがったPL/SQL]

完成形は以下のようになる。流石に製品コードは公開できないので、あくまでイメージである。

CREATE OR REPLACE PROCEDURE add_column AS 
  CURSOR c IS 
    SELECT schema_name || '.target_table' AS table_name
      FROM main_schema.my_table
      ORDER BY main_schema.my_table.id ;
  sql_str varchar2(200);
BEGIN 
  FOR c_rec IN c LOOP 
    sql_str := 'ALTER TABLE '
      || c_rec.table_name 
      || ' ADD new_column VARCHAR2(200) ';
    EXECUTE IMMEDIATE sql_str;   
  END LOOP; 
END add_column;

今回は、たまたまmain_schemaスキーマのmy_tableテーブルのschema_nameカラムに、動的に追加されたスキーマ名が保存されていたので、それを元にカラム追加のためのALTER TABLE文を組み立てた。

より汎用的に書くとすれば、user_tablesテーブルやall_tablesテーブルを参照するようにすればなんとかなりそうな気がする。

docs.oracle.com

[ハマったポイント]

いくつかのサイトを参考にしながら作成した初期のバージョンでは以下のような実装になっていた。

CREATE OR REPLACE PROCEDURE add_column IS
DECLARE
  CURSOR c IS
    SELECT schema_name || '.target_table' AS table_name
      FROM main_schema.my_table
      ORDER BY main_schema.my_table.id ;
BEGIN
  FOR c_rec IN c LOOP
    ALTER TABLE c_rec.table_name
      ADD new_column VARCHAR2(200);
  END LOOP;
END add_column;

このSQLを流した後で、プロシジャのステータスを次のようにSQLで確認してみると、ステータスが無効（INVALID）となっていた。

SQL> SELECT object_name, status, created, last_ddl_time FROM all_objects WHERE object_name = 'ADD_COLUMN';
ADD_COLUMN	INVALID    2019/03/XX XX:XX:XX	2019/03/XX XX:XX:XX

原因が分からなかったので、user_errorsテーブルのエラーの内容を確認すると、DECLAREが間違っているとのメッセージが。

SQL> SELECT name, type, line, text FROM user_errors;
ADD_COLUMN	PROCEDURE	2	PLS-00103: 記号"DECLARE"が見つかりました。…(後略)

どうやらDECLAREは使えないらしい。かつ、1行目の末尾はISではなくてASが正しいらしい。

heppoen.seesaa.net

該当箇所を修正して、再度all_objectsテーブルを確認すると、まだステータスが無効となっている。エラーの内容には次のように変化が見られた。

SQL> SELECT object_name, status, created, last_ddl_time FROM all_objects WHERE object_name = 'ADD_COLUMN';
ADD_COLUMN	PROCEDURE	8	PLS-00103: 記号"ALTER"が見つかりました。…(後略)

どうやらループを回しながらその中でSQL文を直接書くことはできないらしい。

stackoverflow.com

というわけでsql_strを変数として宣言して、EXECUTE IMMEDIATE sql_str;で実行する形式に修正して最終版のできあがり。

無事CALL add_column();で対象テーブルにカラムを追加することができた。