intra-martのIM-LogicDesignerのユーザ定義「SQL」(以下SQL定義)に関する記事です。
IM-LogicDesignerとはビジネスロジックをローコードで作成することができる機能です。 エレメントと呼ばれる様々な処理の箱(変数代入、ループ、SQL定義、JavaScript定義)を組み合わせて処理を実現します。
IM-LogicDesigner www.intra-mart.jp
エレメントの一つであるSQL定義では、あらかじめ記述したSQL文を元にデータ操作を行うことができます。
SQL定義には、SQLコメント文/*param*/
を指定することで条件による動的なSQL生成が可能になるのですが、
開発・保守時に使うときに忘れがちだったり、調べてもなかなか良さげな情報にたどり着けないことが多いので情報をまとめることにしました。
目次
- 目次
- 環境
- 入力情報
- パラメータ例に利用するテーブル
- 1. パラメータ単純利用
- 2. IN句に指定:配列パラメータ(param4)
- 3. LIKE句に指定
- 4. IFコメント
- 5. レアケース:配列パラメータの要素を利用
- 6. レアケース:パラメータでクエリ実行対象テーブルを指定する(SQLリテラル指定1)
- 7. レアケース:パラメータでテーブルとカラムを指定する(SQLリテラル指定2)
- 最後に
環境
- DB:Oracle Database
- intra-martバージョン:intra-mart Accel Platform 2019 Summer (これ以外の環境だと、動かないこともあるかもしれません)
入力情報
項目名 | 型 |
---|---|
param1 | string |
param2 | string |
param3 | string |
param4 | string配列 |
param5 | integer |
param6 | integer |
パラメータ例に利用するテーブル
- IMFR_T_IMW_MATTER
- Formaアプリのアプリケーション種類「IM-Workflow」において、Forma画面で登録したデータ(INSERT_ID)の主キーや対象のワークフロー案件の主キー(システム案件ID)を管理するテーブル
項目名 | 型 |
---|---|
INSERT_ID | VARCHAR2(20) |
APPLICATION_ID | VARCHAR2(100) |
FLOW_ID | VARCHAR2(20) |
CONTENTS_ID | VARCHAR2(20) |
SYSTEM_MATTER_ID | VARCHAR2(20) |
MATTER_NUMBER | VARCHAR2(20) |
MATTER_NAME | VARCHAR2(400) |
APPLY_DATE | DATE |
AUTH_USER_CD | VARCHAR2(100) |
EXEC_USER_CD | VARCHAR2(100) |
1. パラメータ単純利用
SELECT * FROM IMFR_T_IMW_MATTER WHERE INSERT_ID = /*param1*/'' OR INSERT_ID = /*param2*/'' OR INSERT_ID = /*param3*/''
2. IN句に指定:配列パラメータ(param4)
SELECT * FROM IMFR_T_IMW_MATTER WHERE INSERT_ID IN /*param4*/('')
3. LIKE句に指定
- param1: '8%'・・・INSERT_IDが8から始まるものを検索
- インプットパラメータにパーセントを含める必要があります
- strから始まる: 'str%'
- strで終わる:'%str'
- strを含む:'%str%'
SELECT * FROM IMFR_T_IMW_MATTER WHERE INSERT_ID LIKE /*param1*/'S%'
4. IFコメント
4-1. NULLでない場合に条件として利用
- param2がnullでない場合に
OR INSERT_ID = /*param2*/''
をSQLとして利用
SELECT * FROM IMFR_T_IMW_MATTER WHERE INSERT_ID = /*param1*/'' /*IF param2 != null */ OR INSERT_ID = /*param2*/'' /*END*/
4-2. パラメータと値の比較(以上、以下、より大きい、より小さい)
- param5が1000以下ならば
INSERT_ID = /*param1*/''
を利用 - param5が1000より大きければ
INSERT_ID = /*param2*/''
を利用
SELECT * FROM IMFR_T_IMW_MATTER WHERE /*IF param5 <= 1000 */ INSERT_ID = /*param1*/'' /*END*/ /*IF param5 > 1000 */ INSERT_ID = /*param2*/'' /*END*/
- param6が2000以上ならば
INSERT_ID = /*param1*/''
を利用 - param5が2000より小さければ
INSERT_ID = /*param2*/''
を利用
SELECT * FROM IMFR_T_IMW_MATTER WHERE /*IF param6 >= 2000 */ INSERT_ID = /*param1*/'' /*END*/ /*IF param6 < 2000 */ INSERT_ID = /*param2*/'' /*END*/
4-3. パラメータが特定の値に一致する場合にSQLとして利用
- param3が
EDIT
の場合にOR INSERT_ID = /*param2*/''
を実行
SELECT * FROM IMFR_T_IMW_MATTER WHERE INSERT_ID = /*param1*/'' /*IF param3 == "EDIT" */ OR INSERT_ID = /*param2*/'' /*END*/
4-4. 複数パラメータのOR条件に一致する場合にSQLとして利用
- param3が
EDIT
またはREGISTRATION
の場合にOR INSERT_ID = /*param2*/''
を実行
SELECT * FROM IMFR_T_IMW_MATTER WHERE INSERT_ID = /*param1*/'' /*IF param3 == "EDIT" || param3 == "REGISTRATION" */ OR INSERT_ID = /*param2*/'' /*END*/
4-5. 複数パラメータのAND条件に一致する場合にSQLとして利用
- param2が
MATTERCOMPLETE
または param3がPOSTSCRIPT
の場合にOR AUTH_USER IN /*param4*/('')
を実行
SELECT * FROM IMFR_T_IMW_MATTER WHERE INSERT_ID = /*param1*/'' /*IF param2 == "MATTERCOMPLETE" && param3 == "POSTSCRIPT" */ OR AUTH_USER_CD IN /*param4*/('') /*END*/
4-6. WHERE句の不正なAND/ORの排除
- param5が1000以下で、param6が2000以上の場合に、
WHERE OR INSERT_ID = /*param2*/''
となりSQLエラーとなる - これを排除し回避するために
/*BEGIN*/
/*END*/
で囲む- BEGINやENDについて、*の間に半角スペース等を入れると、排除してくれないので要注意。
SELECT * FROM IMFR_T_IMW_MATTER WHERE /*BEGIN*/ /*IF param5 <= 1000 */ INSERT_ID = /*param1*/'' /*END*/ /*IF param6 >= 2000 */ OR INSERT_ID = /*param2*/'' /*END*/ /*END*/
5. レアケース:配列パラメータの要素を利用
- param4の0番目の要素を条件として利用する
SELECT * FROM IMFR_T_IMW_MATTER WHERE INSERT_ID = /*param4[0]*/''
- 上記でNGの場合は以下を試す
SELECT * FROM IMFR_T_IMW_MATTER WHERE INSERT_ID = /*param4[0]*/()
- 返却値を定義する必要がありますが、「データ定義を取得する」でしか設定できません。したがって、このケースを利用する場合、あらかじめリテラルで/param4[0]/''を記載したうえで、「データ定義を取得する」を実施した後にパラメータ化/param4[0]/()する必要があります
6. レアケース:パラメータでクエリ実行対象テーブルを指定する(SQLリテラル指定1)
- param2に
IMFR_T_IMW_MATTER
を設定
SELECT * FROM /*$param2*/ WHERE INSERT_ID = /*param1*/''
- 返却値を定義する必要がありますが、「データ定義を取得する」でしか設定できません。したがって、このケースを利用する場合、あらかじめリテラルでテーブル名を記載したうえで、「データ定義を取得する」を実施した後にパラメータ化する必要があります
7. レアケース:パラメータでテーブルとカラムを指定する(SQLリテラル指定2)
- param2に
IMFR_T_IMW_MATTER
を設定 - param3に
INSERT_ID
を設定
SELECT /*$param3*/ FROM /*$param2*/ WHERE INSERT_ID = /*param1*/''
つまり、その他ORDER BY句などに指定する際も同様。
最後に
これで次回以降にSQL定義を作成するときには困らずに済みそうです。
これ以降、新規に利用したものがあったら追記します。