TIPS sqlコーディングルール

1 はじめに 1.1 目的 　統一したルールによる、保守およびメンテナンスがしやすいコーディング。

2 可読性 　同じレコードセットを取り出すにも様々な記述方法があるが、

　保守およびメンテナンスを考慮し、以下のルールで統一を図ることとします。

2.1 大文字と小文字 SQL文はすべて大文字

→　JavaのDTOより渡されるバインド変数については、この限りではない

2.2 テーブルの別名（エイリアス） テーブルには別名を付与

→　別名については特に制限を設けないが、テーブル名の短縮文字 または A,B,C…などとする

カラムには必ずテーブルのエイリアスを付与

→どのテーブルのカラムか判断しやすいため

カラムには原則として AS を使用してエイリアスを付与 ただし、チーム内でエイリアスを付与しないことを共有できていれば、この限りではない

SELECT -- $Id$ A.COLUMN1 AS COLUMN1 FROM TABLE1 A

SELECT -- $Id$ COUNT(COLUMN1) FROM TABLE1 2.3 レイアウト 「句」で区切る

→　SELECT句、FROM句、WHERE句、GROUP BY句、ORDER BY句 →　「句」の後ろには続けて記述しない（改行する） インデントはタブ（スペース×4の見た目になるようにタブでインデント）

上下の予約語で揃える場合は先頭を合わせる ＝、＞などの演算子は両端、関数内のカンマなどは右端にスペースを空ける

① テーブル結合してデータを抽出する例（SELECT ～ FROM ～ WHERE）

SELECT -- $Id$ A.COLUMN5 AS COLUMN5, CASE WHEN A.COLUMN6 > 100 THEN '1' ELSE '2' END AS COLUMN6 FROM TABLE1 A INNER JOIN TABLE2 B ON A.COLUMN1 = B.OOLUMN1 AND A.COLUMN2 = B.OOLUMN2 WHERE A.COLUMN3 = 'KEY1' AND A.COLUMN4 = 'KEY2'

SELECT -- $Id$ A.COLUMN3 AS COLUMN3 FROM TABLE1 A INNER JOIN TABLE2 B ON A.COLUMN1 = B.OOLUMN1 WHERE A.COLUMN2 = 'KEY1' GROUP BY A.COLUMN2 ORDER BY A.COLUMN2

② 集計関数、ソートを使用する例（SUM、GROUP BY、ORDER BY）

SELECT -- $Id$ A.COLUMN1 AS COLUMN1, SUM(A.COLUMN2) AS SUM_COL2 FROM TABLE1 A GROUP BY A.COLUMN1 ORDER BY A.COLUMN1

SELECT -- $Id$ A.COLUMN1 AS COLUMN1, SUM(A.COLUMN2) AS SUM_COL2 FROM TABLE1 A GROUP BY A.COLUMN1 ORDER BY A.COLUMN1

③ 複雑な条件を使用する例（IN、EXISTS）

SELECT -- $Id$ A.COLUMN3 AS COLUMN3 FROM TABLE1 A WHERE A.COLUMN1 IN ('01', '02') AND EXISTS ( SELECT 1 FROM TABLE2 B WHERE A.COLUMN2 = B.COLUMN2 )

SELECT -- $Id$ A.COLUMN3 AS COLUMN3 FROM TABLE1 A WHERE A.COLUMN1 IN('01','02') AND EXISTS(SELECT 1 FROM TABLE2 B WHERE A.COLUMN2=B.COLUMN2)

2.4 結合条件 INNER JOIN （内部結合）、LEFT OUTER JOIN （外部結合）、CROSS JOINを使用して結合 →　WHERE句での結合は、絞り込み条件と区別しにくくなるため使用しない 　　　ただし、PL/SQL内での結合条件の書き方についてはこの限りではありません →　省略した書き方 LEFT JOIN は使用しない →　RIGHT OUTER JOIN（RIGHT JOIN）は、主テーブルがわかりにくくなるため使用しない

① INNER JOIN （内部結合）を使用する例

SELECT -- $Id$ A.COLUMN2 AS COLUMN2 FROM TABLE1 A INNER JOIN TABLE2 B ON A.COLUMN1 = B.COLUMN1

SELECT -- $Id$ A.COLUMN2 AS COLUMN2 FROM TABLE1 A, TABLE2 B WHERE A.COLUMN1 = B.COLUMN1

② LEFT OUTER JOIN（外部結合）を使用する例

SELECT -- $Id$ A.COLUMN2 AS COLUMN2_A, B.COLUMN2 AS COLUMN2_B, C.COLUMN2 AS COLUMN2_C, D.COLUMN2 AS COLUMN2_D FROM TABLE1 A LEFT OUTER JOIN TABLE2 B ON A.COLUMN1 = B.COLUMN1 LEFT OUTER JOIN TABLE3 C ON A.COLUMN1 = C.COLUMN1 LEFT OUTER JOIN TABLE4 D ON A.COLUMN1 = D.COLUMN1

SELECT -- $Id$ A.COLUMN2 AS COLUMN2_A, B.COLUMN2 AS COLUMN2_B, C.COLUMN2 AS COLUMN2_C, D.COLUMN2 AS COLUMN2_D FROM TABLE2 B RIGHT OUTER JOIN TABLE1 A ON A.COLUMN1 = B.COLUMN1 LEFT JOIN TABLE3 C ON A.COLUMN1 = C.COLUMN1, TABLE4 D WHERE A.COLUMN1 = D.COLUMN1(+)

③ CROSS JOIN（クロス結合）を使用する例

SELECT -- $Id$ A.COLUMN2 AS COLUMN2_A, B.COLUMN2 AS COLUMN2_B FROM TABLE1 A CROSS JOIN TABLE2 B

SELECT -- $Id$ A.COLUMN2 AS COLUMN2_A, B.COLUMN2 AS COLUMN2_B FROM TABLE1 A, TABLE2 B

2.5 カンマ（ , ） カンマはカラムの後に付与 SELECT -- $Id$ A.COLUMN1 AS COLUMN1, A.COLUMN2 AS COLUMN2 FROM TABLE1 A

SELECT -- $Id$ A.COLUMN1 AS COLUMN1 ,A.COLUMN2 AS COLUMN2 FROM TABLE1 A 2.6 コメント（Jenkinsによるコメント置き換え用） SELECT、UPDATE、INSERT、DELETE句の直後に「-- $Id$」を記載

コメントの記載行については、1行目でも2行目でも SELECT句などの直後であれば特に指定はしない SQLを一意に識別するために使用 https://wiki.misosiru.io/pages/viewpage.action?pageId=359043696

2.7 WITH句の使用は禁止（Batchを除く） パフォーマンス悪化、DB負荷高騰の原因になる可能性があるため禁止

→　WITH句を使用すると、一時表が作成され、commit待機が発生

3 パフォーマンス 　少量データでは影響が出ない場合も、大量データではパフォーマンスに多大な影響を及ぼす場合があります。

　以下はパフォーマンスを悪化させる原因となりうるケースですので、

　当てはまる記載は避ける、または十分にパフォーマンスの考慮がされているか注意してください。

　※パフォーマンスに関係する修正を行う場合、必ずSQLの有識者（DBAなど）へのレビューを実施してください

3.1 必要なカラムのみを記載 ワイルドカード（＊）の記載はしない ただし、必ず副問い合わせで記載したSELECTと同一の項目になる場合は、この限りではない

① データ取得の例

SELECT -- $Id$ A.COLUMN1 AS COLUMN1, A.COLUMN2 AS COLUMN2 FROM TABLE1 A

SELECT -- $Id$ A.* FROM TABLE1 A

SELECT -- $Id$ B.* FROM ( SELECT A.COLUMN1 AS COLUMN1, A.COLUMN2 AS COLUMN2 FROM TABLE1 A ) B

② カウント取得の例

カウントはワイルドカード（＊）の使用可

SELECT -- $Id$ COUNT(*) AS CNT FROM TABLE1 A

特にパフォーマンスを考慮する場合はPKのカラムを指定 →　NULLが許容されているカラムを指定すると正しくカウントされないため要注意 →　PKの付け替えが発生した場合は注意が必要となる

SELECT -- $Id$ COUNT(A.COLUMN1) AS CNT FROM TABLE1 A

3.2 索引が使用されなくなる（または効率の悪い索引アクセス）SQLの記述 インデックスを利用したいカラムを左辺で演算（関数を使用）

→ 右辺のみで演算（関数を使用）するように変更

① 演算する例

SELECT -- $Id$ A.COLUMN3 AS COLUMN3 FROM TABLE1 A WHERE A.COLUMN1 > A.COLUMN2 + 10000

SELECT -- $Id$ A.COLUMN3 AS COLUMN3 FROM TABLE1 A WHERE A.COLUMN1 - A.COLUMN2 > 10000

② 関数を使用する例

SELECT -- $Id$ A.COLUMN3 AS COLUMN3 FROM TABLE1 A WHERE A.COLUMN1 = TRIM(A.COLUMN2)

SELECT -- $Id$ A.COLUMN3 AS COLUMN3 FROM TABLE1 A WHERE TRIM(A.COLUMN2) = A.COLUMN1

暗黙の型変換

→ 常に明示的な型変換を行う

→ 型変換は右辺で行う

SELECT -- $Id$ A.COLUMN2 AS COLUMN2 FROM TABLE1 A WHERE A.COLUMN1 > TO_CHAR(SYSDATE, 'YYYY/MM/DD')

SELECT -- $Id$ A.COLUMN2 AS COLUMN2 FROM TABLE1 A WHERE A.COLUMN1 > SYSDATE

OR条件が多数にわたる場合

→ 効率の悪い索引アクセスになっている可能性があるため、UNION ALL での記述を検討

SELECT -- $Id$ A.COLUMN2 AS COLUMN2 FROM TABLE1 A WHERE A.COLUMN1 = 1 OR A.COLUMN1 = 2 OR A.COLUMN1 = 3

SELECT -- $Id$ A.COLUMN2 AS COLUMN2 FROM TABLE1 A WHERE A.COLUMN1 = 1 UNION ALL SELECT B.COLUMN2 AS COLUMN2 FROM TABLE1 B WHERE B.COLUMN1 = 2 UNION ALL SELECT C.COLUMN2 AS COLUMN2 FROM TABLE1 C WHERE C.COLUMN1 = 3

LIKE検索を使用

→　中間一致、後方一致は使用しない

SELECT -- $Id$ A.COLUMN2 AS COLUMN2 FROM TABLE1 A WHERE A.COLUMN1 LIKE '123%'

SELECT -- $Id$ A.COLUMN2 AS COLUMN2 FROM TABLE1 A WHERE A.COLUMN1 LIKE '%123%'

SELECT -- $Id$ A.COLUMN2 AS COLUMN2 FROM TABLE1 A WHERE A.COLUMN1 LIKE '%123'

3.3 INとEXISTSについて 　① INを使用した方が効率がいい場合

テーブルをフルスキャンする必要がある

副問合せ結果が少ない

副問合せを繰り返し呼び出す必要がない

SELECT -- $Id$ A.COLUMN2 AS COLUMN2 FROM TABLE1 A WHERE A.COLUMN1 IN ('01', '02')

　② EXISTSを使用した方が効率がいい場合

テーブルをフルスキャンする必要がない（データの存在チェックのみ）

親問合せ結果が少ない

副問合せを繰り返し呼び出す必要がある

SELECT -- $Id$ A.COLUMN3 AS COLUMN3 FROM TABLE1 A WHERE A.COLUMN1 = '123' AND EXISTS ( SELECT 1 FROM TABLE2 B WHERE A.COLUMN2 = B.COLUMN2 ) 3.4 ソートについて 件数が多いほどソート処理は負荷が大きいため、少ない件数で最小限のソート回数にする →　先頭10件だけ必要な場合などは、ROWNUMを使用して10件に絞ることでコスト削減となる

COUNT関数を使用する場合はソートを使用しないこと →　COUNT関数はソート有無によって結果が変わらないため SELECT -- $Id$ COUNT(A.COLUMN1) AS CNT FROM TABLE1 A ORDER BY A.COLUMN1 ORDER BY 以外でも以下は暗黙的にソートされるため注意が必要 DISTINCT UNION GROUP BY HAVING

3.5 r2framework固有の問題 ページング処理は必ずDTOで開始と終了の行を渡し、rownumで制御すること