Oracleのスクリプト作成について - foolmacky/DevelopDocs GitHub Wiki

■ Oracleのスクリプトのレイヤー

わかりにくいのだが、Oracleのスクリプティングには2つのレイヤーがあり、コマンドや変数の扱いが異なっている
一番外側はSQL Plusのスクリプトで、当然ながらSQL Plus上でしか処理できない。
その下にPL/SQLのスクリプティングがある。
コマンド類や制約が異なっているのは、当然だが、ついつい混乱するのはそれぞれのレイヤーで定義される変数と、スクリプト内での参照の仕方、更新方法だ。
コマンドはそれぞれの環境上でしか使えないが、変数については、SQL Plusで定義した変数をPL/SQLでも参照できてしまうのが、混乱の元だ。

■ SQL Plusの変数

defineコマンドを使って設定する変数。参照するには変数名に&をつけて使う。単純なスクリプト・ソースの置換機能に過ぎないため、SQLの中で、文字列変数として利用するには参照の前後にクオーテーションをつけなくてはならない。

def table_name=scot
def temp=12
select * from scot where month = '&temp';
select * from &table_name where month = '&temp';

値に再度代入するのもdefineコマンドを使う。
この変数はPL/SQLブロックでもそのまま参照することができ、使い方もSQL Plusレイヤーと同じ。ただし、PL/SQLレイヤーでは値を更新することはできない。
スクリプトの置換機能に過ぎないため、テーブル名などをこの変数を使って動的に切り替えたりできる。

■ variable変数

variableコマンドを使って定義する。型の概念を持つちゃんとした変数。SQL Plusのスクリプト上(PL/SQLのヘッダー部?)で定義し参照もできるが、値の代入はPL/SQLブロック内でのみ可能。
なのでSQL Plusスクリプトで代入する場合は、無名ブロックを使って行う。
参照するときは、SQL Plusスクリプト上ではそのまま参照するが、PL/SQLブロックでは「:」をつける必要がある。

def table_name=HOGE
variable v_table_name varchar2(128)

begin
    for i in 0..10 loop
        :v_limit := i;
        @sqls/&table_name
        commit;
    end loop;
    :v_table_name := '&table_name';
    dbms_output.put_line(:v_table_name || ' executed.');
    dbms_output.put_line('&table_name' || ' executed.');
    end;
/

print v_table_name

表現の仕方からしても、SQLのnamed paramと同じメモリ格納をしているのかもしれない。
型が明確なため、テーブル名、カラム名などをこの変数を使って動的に制御することはできない。その場合は一旦文字列としてSQL文を構築し、execute immediateコマンドに与えて実行する。

■ declareセクションで宣言する変数

PL/SQLブロック内でのみ使用する変数。declareセクションで型とともに宣言し、続くbeginブロックで使用する。
SQL plusスクリプト部からは使うことはできない。最終的なrun(/)コマンドを跨いで使用できるかどうかはわからない。

■ その他注意すべきこと

SQL文を別ファイルに保存して、PL/SQLブロック内でそれを「@」コマンドで呼び出して実行する場合、variable変数は「:」をつけて参照する
selectするテーブルを変数で切り替えたりする場合は、def変数は直接指定することが可能だが、型を持っているvariable変数はそれはできない。一旦文字列としてSQLを構築してからexecute immediate コマンドで実行する。
PL/SQLブロックは実行前に静的にコンパイルされる。その際にif分岐などで、実際には実行されない部分であっても、正しくないSQL(カラムがないなど)が構築される場合は、コンパイルでエラーになってしまう。