ダイナミックに SQL を実行！～動的 SQL の使い方【前編】～

PowerBuilder 開発では、データベース操作やデータウィンドウではできない DDL 文を発行したい場合や、データウィンドウを使うまでもない DML 文を発行する場合は「埋め込み SQL」を利用しているかと思います。

埋め込み SQL はスクリプトに直接記述でき、コンパイル時に構文チェックも行われるので開発時は非常に便利なのですが、ステートメントを動的に変更することができないため「条件によって使用するテーブルや列を変える」といった処理の実装がちょっと面倒ですよね。

条件分岐ごとにそれぞれ SQL 文を丸ごとコーディングしてしまうとスクリプトも長くなってしまうし、メンテナンスも大変になってしまいます。

そこで便利なのが「動的 SQL」です。 動的 SQL はスクリプトで SQL 文やパラメーターの定義などを文字列として生成してデータベースに発行することができるので、SQL を 1 か所だけ変えたいだけなのに・・・という場合に活用できます。

今回は、そのように便利な「動的 SQL」について、使い方とポイントをまとめてみました。

それではダイナミックにいってみましょう！

---

構文の種類

まずは構文の種類の説明から。

動的 SQL には 4 つの構文があります。 主に入力パラメーターが使用可能か、結果集合を返すことができるかといったポイントで使い分けます。

構文 3、4 は結果集合が受け取る SELECT 文の SQL の発行に適しています。 逆に構文 1、2 は結果集合が受け取れないので、データの取得以外の用途、つまり UPDATE／DELETE 文であったり、CREATE／ALTER 文といった各種 DDL 文を発行したい場合に利用します。

---

[構文 1] 入力パラメーターを持たず、結果集合を返さない SQL

最もシンプルな構文です。 文字列で指定した SQL 文をそのまま実行します。

[構文]

EXECUTE IMMEDIATE SQL文 {USING TransactionObject} ;

まずは簡単な例から。 UPDATE 文を実行します。 EMP_ID が 110 のレコードの EMP_LNAME を ‘明石’ に変更しています。

// UPDATE 文を実行
EXECUTE IMMEDIATE "UPDATE EMPLOYEE SET EMP_LNAME = '明石' WHERE EMP_ID = 110" ;

// データの更新をコミット
COMMIT ;

これなら埋め込み SQL でも全然問題ないですが、あくまでサンプルとしてのご紹介です。

なお、トランザクションオブジェクトの指定を省略しているので、SQLCA で実行します。 今回のブログでは以降の例でも同様ですので、トランザクションを指定したい場合は USING 句を使用してください。

次の例は、条件 (ai_arg) の値によってテーブルを切り替えて DELETE 文でデータを削除しています。 各テーブルには DEL_FLG という共通の列が定義されています。

string ls_sql
string ls_table

// 条件によってテーブルを変更する
CHOOSE CASE ai_arg
    CASE 1
        ls_table = "TABLE_A"
    CASE 2
        ls_table = "TABLE_B"
    CASE ELSE
        ls_table = "TABLE_C"
END CHOOSE

// SQL 文を生成 (DELETE 文で DEL_FLG が 1 のレコードを削除)
ls_sql = "DELETE FROM " + ls_table + " WHERE DEL_FLG = 1"

// SQL を実行
EXECUTE IMMEDIATE :ls_sql;

使用するテーブルを動的に変更できるというのは、動的 SQL ならではのテクニックですね。

このくらい短い SQL であれば、構文を丸ごと記述して分岐させてもたいしたボリュームではないですが、SQL が長くなるとコード量も増えるし読みにくくなってしまいますよね。 動的 SQL は、そのような場合に効果を発揮します。

とくに SQL 文は可読性を高めようとすると項目や条件ごとに改行したりするのでコードが長くなりがちです。 そんな SQL が 1 つの処理に 3 つも 4 つも並んでいると、どの処理を見ているのか、どこを修正しているのかわからなくなっちゃいますよね。

動的 SQL なら変更したい部分だけを分岐で指定すれば良いだけなのでスッキリしますし、対象データの条件などに変更があった場合は一部のみをメンテナンスすれば OK になります。

文字列の SQL を実行する、とにかくシンプルでわかりやすいのが構文 1 です。

---

[構文 2] 入力パラメーターを持ち、結果集合を返さない SQL

こちらは、あらかじめ入力パラメーターを定義できる構文です。 構文 1 と比較した利点というと、文字結合という方式を取らずにパラメーターが指定できるので、より 可読性が高い コーディングができるという点でしょうか。

[構文]

PREPARE DynamicStagingArea FROM SQL文 {USING TransactionObject} ;
EXECUTE DynamicStagingArea USING {ParameterList} ;

PREPARE 文で準備をし、EXECUTE 文で実行するという流れです (そのまんまですね・・・)。 USING 句が 2 つあるのでややこしいですが、PREPARE 文ではトランザクションオブジェクトの指定、EXECUTE 文ではパラメーターの指定になっています。

DynamicStagingArea というのは PowerBuilder のオブジェクトで、発行される SQL に関する情報を保持します。 SQLCA と同様にデフォルトで SQLSA というグローバル変数が用意されているので、ひとつの処理で複数の動的 SQL を使用しない限り、通常はこの SQLSA を使います。

ParameterList は PREPARE 文で使用している “?” 記号の順番に対応して、カンマで値を区切って列挙します。 百聞は一見に如かず、実際に例を見てみましょう。

次の例は構文 2 の動的 SQL で DEPARTMENT テーブルにデータを追加する例です。 1 つの PREPARE 文で 2 つのレコードを INSERT しています。

integer li_dept_id, li_mgr_id
string ls_dept_name

// SQL を準備 ()
PREPARE SQLSA FROM "INSERT INTO DEPARTMENT VALUES (?, ?, ?)" ;


// 人事部のレコードを追加
li_dept_id = 18          // 部署ID
ls_dept_name = "人事部"  // 部署名
li_mgr_id = 435          // マネージャID

EXECUTE SQLSA USING :li_dept_id, :ls_dept_name, :li_mgr_id ;


// 経理部のレコードを追加
li_dept_id = 23          // 部署ID
ls_dept_name = "経理部"  // 部署名
li_mgr_id = 377          // マネージャID

EXECUTE SQLSA USING :li_dept_id, :ls_dept_name, :li_mgr_id ;


// コミット
COMMIT ;

文字列への型変換や文字結合が必要ないのですっきりですね。

また、上記の例はループ処理で実装できることにお気づきかと思います。 パラメーターの値を変更しながら EXECUTE 文の呼び出しを繰り返すことで、複数のデータの追加などがさらに簡潔になります。

---

前編のまとめ

今回は前編として、データの取得を行わない「構文 1」、「構文 2」を紹介しました。

埋め込み SQL ではコンパイル時に構文チェックが行われるため (構文チェックを行わないようにするオプションもありますが)、DBMS 固有の構文を利用する場合にコンパイルエラーになってしまう場合があります。 たとえば SQL Server の「FROM 句を省略した SELECT 文」などですね。 そのような SQL 文も定義できるため、融通の利くコーディングが可能になります。

ただし、構文チェックが行われないことで、構文に誤りがあった場合は実行時にエラーが発生してしまうので、しっかりとテストを実施してください (私から言うまでもないですね)。

このまま構文 3、4 を解説していきたかったんですが、ちょっとボリュームが大きくなってしまうので、次回に説明します。

構文 3、4 ではデータの取得が可能になります。 お楽しみに！

 

以上、エイタでした！