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テーブルを参照するようにすればなんとかなりそうな気がする。
[ハマったポイント]
いくつかのサイトを参考にしながら作成した初期のバージョンでは以下のような実装になっていた。
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
が正しいらしい。
該当箇所を修正して、再度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文を直接書くことはできないらしい。
というわけでsql_str
を変数として宣言して、EXECUTE IMMEDIATE sql_str;
で実行する形式に修正して最終版のできあがり。
無事CALL add_column();で対象テーブルにカラムを追加することができた。
[まとめ]
SQL、基本的な書き方も忘れているので、どこかで復習しないとな……。
ちなみにこれを実装した後、ALTER TABLEの実行権限が足りてなくてCALLが1回こけたのはナイショ。