RDBで複数テーブルに一括でカラム追加するためにPL/SQLを実装した

DynamoDBばかり触っていたのだが必要に迫られて久しぶりにRDBOracle 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();で対象テーブルにカラムを追加することができた。

 

[まとめ]

SQL、基本的な書き方も忘れているので、どこかで復習しないとな……。

 

ちなみにこれを実装した後、ALTER TABLEの実行権限が足りてなくてCALLが1回こけたのはナイショ。