SQL関連のカテゴリ
oracleの独自関数のCOALESCE（コアレス）関数の用途についてメモしておきます。
COALESCE関数は引数のリストから最初のNULL以外の値を返却するOracle独自関数となります。
NVL2関数との違いは必ずしも引数へ指定するデータ型を全て合わせる必要はないことです。
ただデータの一貫性を保ちたい場合は極力データ型は合わせた方が望ましいです。
|
1 |
SELECT COALESCE(START_DATE, END_DATE, sysdate) FROM CLIENT_ADDRESS; |
上記例ではSTART_DATEがNULLであればEND_DATEの値を、END_DATEもNULLであればsysdateを返却します。
oracleの独自関数としてNVL関数やNVL2関数があります。
知ってると結構便利な関数なので、この２つの関数の違いについて整理しておきます。
NVL関数は第１引数がNULLなら第２引数の値（代替値）を返します。
もし第１引数の結果がNULLでなければ、そのまま第１引数の値を返します。
注意点として第１引数と第２引数へは同じデータ型を指定する必要があります。
|
1 |
SELECT NVL(END_DATE, sysdate) FROM CLIENT_ADDRESS; |
NVL2関数は第１引数がNULLの場合に、第２引数の値を返却し、第１引数がNULLの場合は第３引数の値を返却します。
注意点として第１引数、第２引数、第３引数に指定する値は全て同じデータ型を指定する必要があります。
|
1 |
SELECT NVL2(END_DATE, START_DATE, sysdate) FROM CLIENT_ADDRESS; |
SQLで単語の先頭1文字目のみ小文字から大文字へ変更したい場合は、INITCAP関数を使用することで可能となります。引数に変換したい文字を指定すればOKです。単語の区切りはスペースかアルファベット以外の文字（ハイフン「-」、カンマ「,」、アンダースコア「-」など）を区切り文字として見做して変換されます。
|
1 2 |
SELECT INITCAP(g.NAME) FROM GOODS g; |
SQLで指定した日付項目（該当年月）の月末日を取得するには「LAST_DAY」関数を使用します。うるう年でも正確な月の最終日を取得出来ます。
| 関数＼DBMS | MySQL | PostgreSQL | SQL Server | Oracle |
|---|---|---|---|---|
| LAST_DAY | ○ | ○ | ✕ | ○ |
| EOMONTH | ✕ | ✕ | ○ | ✕ |
|
1 2 |
SELECT LAST_DAY(b.BIRTHDAY) FROM BIRTHDAY b |
顧客の有効住所などを管理しているテーブルからある特定の日付時点で有効な住所を取得する方法をメモしておきます。
|
1 2 3 4 5 |
SELECT ca.* FROM CLIENT_ADDRESS ca WHERE TRUNC(ca.START_DATE) <= '2022-03-31' AND ('2022-03-31' <= TRUNC(ca.END_DATE) OR ca.END_DATE IS NULL); |
SQLでは特定の項目の昇順、降順ではなく任意の順序で並べ替えて取得することも出来ます。
任意の順序でソートするにはORDER BY句でCASE文を指定する事で取得することが出来ます。
「BIRTHDAY」テーブル
|
1 2 3 4 5 6 |
FROM BIRTHDAY b ORDER BY CASE b.NAME WHEN '佐藤 花子' THEN 1 WHEN '石川 一' THEN 2 WHEN '山田 太郎' THEN 3 END; |
SQLでsysdateなどの日付型へ加算、減算してxx日後、xxヶ月後、xx年後を求める方法をメモしておきます。
|
1 2 3 4 5 |
SELECT sysdate "本日の日付", sysdate + 30/86400 "本日の日付 + 30秒", sysdate - 30/86400 "本日の日付 - 30秒" FROM dual; |
|
1 2 3 4 5 |
SELECT sysdate "本日の日付", sysdate + 5/1440 "本日の日付 + 5分", sysdate - 5/1440 "本日の日付 - 5分" FROM dual; |
|
1 2 3 4 5 |
SELECT sysdate "本日の日付", sysdate + 2/24 "本日の日付 + 2時間", sysdate - 2/24 "本日の日付 - 2時間" FROM dual; |
|
1 2 3 4 5 |
SELECT sysdate "本日の日付", TO_CHAR(sysdate + 1,'YYYY-MM-DD') "本日の日付 + 1日", TO_CHAR(sysdate - 1,'YYYY-MM-DD') "本日の日付 - 1日" FROM dual; |
|
1 2 3 4 5 |
SELECT sysdate "本日の日付", TO_CHAR(ADD_MONTHS(sysdate, 1),'YYYY-MM-DD') "本日の日付 + 1ヶ月", TO_CHAR(ADD_MONTHS(sysdate, -1),'YYYY-MM-DD') "本日の日付 - 1ヶ月" FROM dual; |
|
1 2 3 4 5 |
SELECT sysdate "本日の日付", TO_CHAR(ADD_MONTHS(sysdate, 12),'YYYY-MM-DD') "本日の日付 + 1年", TO_CHAR(ADD_MONTHS(sysdate, -12),'YYYY-MM-DD') "本日の日付 - 1年" FROM dual; |
Oracleでのセッションやプロセス数には上限があり、それを超えてしまうとデータベースにアクセス出来ずに予期せぬエラー（ORA-12519）が発生するなどの不具合が発生してしまいます。
Oracleで最大プロセス数や最大セッション数は初期化パラメータを管理しているSPFILEに定義されています。変更前にSPFILEのバックアップを取得しておくのをオススメします。
Oracle DB 18cの場合の例です。
|
1 2 3 |
SELECT NAME, VALUE FROM V$SYSTEM_PARAMETER WHERE NAME IN('processes', 'sessions'); |
|
1 |
ALTER SYSTEM SET PROCESSES = 1000 SCOPE=SPFILE; |
|
1 2 3 |
SELECT NAME, VALUE FROM V$SYSTEM_PARAMETER WHERE NAME IN('processes', 'sessions'); |
SQLに慣れてない頃だとnullを判定する際は「xx = null」などと書いてしまいがちですが、SQLでNULLを判定するには「xx is null」 or 「xx is not null」と記載します。
「GOODS」
|
1 2 3 |
SELECT * FROM GOODS g WHERE GOODS_CODE IS NULL; |
|
1 2 3 |
SELECT * FROM GOODS g WHERE GOODS_CODE IS NOT NULL; |
SQLではSELECT時などにCASE文を入れる事で「IF-THEN-ELSE式」を記載することも出来ます。
「GOODS」
|
1 2 3 4 5 6 7 |
SELECT NAME, GOODS_CODE, (CASE GOODS_CODE WHEN 101 THEN '【PC】' || NAME WHEN 201 THEN '【DESK】' || NAME ELSE '【OTHER】' || NAME END) test FROM GOODS g; |
