オーロラさんの勉強帳

SESのロースキル客先常駐勤務。データベース、Excel、Excel VBA、ネットワーク、LinuxなどIT関連のことを主に書いていきます。

【SQL】DECODE関数の使い方 (ORACLE DATABASE 12C SQL基礎)

ORACLE DATABASE 12c SQL基礎(1Z0-061)の試験範囲でもある、
DECODE関数の使用方法を紹介します。


目次

DECODE関数について

DECODE関数は以下のような書式で使います。
列の値が条件値1に合致する場合は値1を返し、条件値nに合致する場合は値nを返します。いずれの条件値にも合致しない場合はデフォルト値を返します。(デフォルト値を指定しない場合はNULLを返します)



書式: DECODE(列, 条件値1 , 値1[, ・・・条件値n, 値n] [, デフォルト値])


以下のテーブルを使って、実際にDECODE関数の使い方を確認してみましょう。

EMPLOYEE_ID DEPARTMENT_ID NAME PHONE SALARY MANAGER_ID
1 10 山田 00000000000 500000 « NULL »
2 10 佐藤 00000000001 450000 1
3 10 鈴木 00000000002 450000 1
4 10 田中 00000000003 300000 1
5 10 山本 00000000004 220000 1
6 20 00000000005 470000 « NULL »
7 20 中村 00000000006 250000 6
8 20 松本 00000000007 220000 6
9 30 井上 00000000008 210000 10
10 30 木村 00000000009 300000 « NULL »

DECODE関数の使用例

DEPARTMENT_IDが10の場合は経営管理部、20の場合は営業部、それ以外はその他と返すSQLを考えます。

SELECT EMPLOYEE_ID,NAME,DECODE(DEPARTMENT_ID, 10, '経営管理部', 20, '営業部','その他') FROM TEST5;


SQLの結果
f:id:auroralights:20210617220217p:plain


列名が「DECODE(DEPARTMENT_ID, 10, '経営管理部', 20, '営業部','その他')」と非常に長いので、以下のように列別名をつけてみましょう。
可読性を高めるために、インデントもつけてみます。

SELECT 
  EMPLOYEE_ID,
  NAME,
  DECODE(DEPARTMENT_ID, 10, '経営管理部',
                          20, '営業部',
                          'その他') AS 部署
FROM TEST5;


SQLの結果
f:id:auroralights:20210617220546p:plain

DECODE関数の使用例2

MANAGER_IDに値がある場合は〇〇さんの部下、MANAGER_IDに値がない場合は部署長と返すSQLを考えてみましょう。

SELECT 
  EMPLOYEE_ID,
  NAME,
  DECODE(MANAGER_ID, NULL, '部署長',
                                        1, '山田さんの部下',
					6, '森さんの部下',
                                        10, '木村さんの部下') 
FROM TEST5;

f:id:auroralights:20210617221836p:plain


以下のように部署長をデフォルトにする書き方もできます。

SELECT 
  EMPLOYEE_ID,
  NAME,
  DECODE(MANAGER_ID, 1, '山田さんの部下',
					 6, '森さんの部下',
                                         10, '木村さんの部下',
                                          '部署長') 
FROM TEST5;

【SQL】NULLを扱う単一行関数 NVL、NVL2、NULLIF、COALESCEの使い方 (ORACLE DATABASE 12C SQL基礎)

ORACLE DATABASE 12c SQL基礎(1Z0-061)の試験範囲でもある、
NVL関数、NVL2関数、NULLIF関数、COALESCE関数の使用方法を紹介します。


目次



NVL関数、NVL2関数、NULLIF関数、COALESCE関数の概要

NVL関数、NVL2関数、NULLIF関数、COALESCE関数の使い方の概要は以下の通りです。

関数 書式 説明
NVL NVL(列, 値) 列に含まれるNULLだけを指定した値に変換して返す
NVL2 NVL2(列, 値1, 値2) 列値がNULLではない場合は値1、NULLの場合は値2を返す
NULLIF NULLIF(列1, 列2) 2つの列値を比較し等価であればNULLを返す。等価でなければ列1を返す
COALESCE COALESCE(列1,列2,・・・,列n) 指定した列を順番に評価して、最初に検出されたNULL以外の値を返す

※列には、列または式を指定します。
※値には、列または式、定数を指定します。


以下でNVL関数、NVL2関数、NULLIF関数、COALESCE関数の使い方を詳しく紹介します。
その際、以下のようなテスト用のデータを使います。テスト用のテーブルを作成するSQLは本記事の末尾に記載してますので、参考にしてください。


<test4テーブル>
ID、AGE列はNUMBER型、NAMEはCHAR型です。

ID NAME AGE
1 佐藤 25
2 鈴木 28
3 « NULL » 22
4 松本 « NULL »
5 « NULL » « NULL »
6 山田 31
7 « NULL » 25


NVL関数の使い方

NVL関数は引数で指定した列のNULLを、引数で指定した値に変換して返す関数です。
注意点としては、引数で指定する列のデータ型と置換する値のデータ型が一致している必要があります。
※「NVL」は、NULL VALUE LOGICの略です。

NVL関数の書式


NVL(列, 値)

列値がNULLの場合は、値に変換します。

NVL関数の使用例

以下の例では、NAME列がNULLの場合にNULLを未登録という文字列に変換して返します。

select id,NVL(name,'未登録') from test4;

f:id:auroralights:20210609211906p:plain


次はエラーの例になる例です。
以下のSQLはage列がNULLの場合に文字列の「未登録」を返そうとしてますが、age列はNUMBER型であり、文字列の「未登録」とデータ型が異なるため「ORA-01722:数値が無効です。」エラーとなります。

select id,NVL(age,'未登録') from test4;

f:id:auroralights:20210609212325p:plain


次はNAME列がNULLの場合は文字列「未登録」、AGE列がNULLの場合は「0」を返す例です。

select id,NVL(name,'未登録'),NVL(age,0) from test4;

f:id:auroralights:20210609212559p:plain


NVL2関数の使い方

NVL2関数は引数で指定した列値がNULLでない場合は値1を返し、NULLの場合は値2を返します。
値1、値2は同じデータ型である必要がありますが、列値と値1・値2は同じデータ型である必要はりません。

NVL2関数の書式


NVL2(列, 値1, 値2)


NVL2関数の使用例

以下SQLの例では、AGE列とAGE列がNULL以外の場合は文字列「登録あり」、NULLの場合は「登録なし」を返しています。
NVL関数と違い列と値1・値2が同じデータ型である必要はありません。(値1・値2は同じデータ型である必要があります)

select  age,NVL2(age,'登録あり','登録なし') from test4;

f:id:auroralights:20210609222902p:plain


次はエラーにならないSQLとエラーになるSQL例です。

①のSQLでは、AGE列がNULL以外の場合はAGE列をそのまま返し、NULLの場合は数値「0」を返します。NUMBER型のAGE列と数値の0ですのでデータ型が一致するためエラーにはなりません。
②のSLQでは、AGE列がNULL以外の場合はAGE列、NULLの場合は文字列「未登録」を返すように書いてますが、NUMBER型のAGE列と文字型の「未登録」でデータが違うので「ORA-01722:数値が無効です。」エラーとなります。

--①エラーにならない例
select  age,NVL2(age,age,0) from test4;
--②エラーになる例
select  age,NVL2(age,age,'登録なし') from test4;

NULLIF関数の使い方

NULLIF関数は引数で指定した2つの列値を比較して、2つの列値が等しい場合にはNULLを返し、等しくない場合には列1を返します。

NULLIF関数の書式


NULLIF(列1, 列2)


NULLIF関数の使用例

以下SQLの例では、ID列、AGE列、AGE列の1文字目とID列を比較して等しい場合はNULLを返し、等しくない場合はAGE列の1文字目を返しています。

※ID列の4、5のように比較する列にNULLがある場合もNULLが返るようです。NULLが比較できないような不確かなデータなので、NULLが返るみたいですね。

select id,age,NULLIF(to_number(substr(age,1,1),'9'),id) from test4;

f:id:auroralights:20210609225328p:plain

COALESCE関数の使い方

COALESCE関数は引数で指定した複数の列の中で最初にNULL出ない値を返します。
指定する列はすべて同じデータ型である必要があります。
引数は最低でも2つ指定する必要があります。
引数で指定した列がすべてNULLの場合はNULLを返します。

COALESCE関数の書式


COALESCE(列1, 列2, ・・・列n)


COALESCE関数の使用例

以下のSQL例は、AGE列、ID列、COALESCE関数でAGE列、ID列を順番に評価して最初に検出されたNULL以外の値を返します。

select age,id,COALESCE(age,id) from test4;

f:id:auroralights:20210609230352p:plain



テスト用データ

今回の記事で使用したテーブル「test4」を作成して、データを挿入するSQLは以下の通りです。

テーブル作成
create table test4 (
id number(3) constraint test4_id_pk primary key,
name char(20),
age number(3)
);

データ挿入
insert into test4 values(1,'佐藤',25);
insert into test4 values(2,'鈴木',28);
insert into test4 values(3,NULL,22);
insert into test4 values(4,'松本',NULL);
insert into test4 values(5,NULL,NULL);
insert into test4 values(6,'山田',31);
insert into test4 values(7,NULL,25);

【SQL】INSTR(インストリング)関数の使い方 (ORACLE DATABASE 12C SQL基礎)

ORACLE DATABASE 12c SQL基礎(1Z0-061)の試験範囲でもある、
INSTR関数の使用方法を紹介します。


目次

INSTR関数の使い方

INSTR関数は引数で指定した文字データ中に検索文字がある場合、その検索文字が文字データの先頭から何文字目にあるかを数値で返す関数です。
検索文字が存在しない場合は「0」を返します。


※INSTR関数は、全角・半角混在の文字データの場合でもあくまで「文字数」を返します。全角・半角ともに1文字は1としてカウントします。


INSTR関数の書式

INSTR(文字データ,検索文字,検索開始位置,検知回数)

引数 説明
文字データ 対象の文字列
検索文字 検索する文字列
開始位置 検索文字の検索開始位置1
※開始位置が負の場合は、文字列の末尾から(文頭に向けて)数えた位置が検索開始位置となり、検索も開始位置から文頭に向けて検索する。
負の値を指定したとしてもINSTR関数で返す文字数は文字データの先頭から数えた文字数であることに注意。
※0を指定した場合は常に未検出の「0」を返す。
デフォルトは「1」。
検知回数 検索文字の出現回数を指定する
例:2を指定した場合は、検索文字が2回目に検知された位置を返す。
デフォルトは「1」。



※開始位置・検索位置は省略が可能です。
 省略例:INSTR(文字データ,検索文字)、INSTR(文字データ,検索文字,開始位置)
※検索は大文字・小文字を区別します。検索できなかった場合は「0」を返します。
※検索文字には2文字以上の文字を指定することも可能です。その場合はその文字の最初の1文字が文字データの何文字目にあるかを返します。


1.INSTR関数 引数をすべて指定した場合の使用例

INSTR関数のすべての引数を指定したシンプルなSQLで実際の動作を確認しましょう。

--①2つ目の半角スペースの位置検索
SELECT INSTR('ORACLE DATABASE 学習',' ',1,2) FROM DUAL;
--②1つ目のAの位置検索
SELECT INSTR('ORACLE DATABASE 学習','A',1,1) FROM DUAL;
--③6文字目から検索開始して1つ目のEの位置検索
SELECT INSTR('ORACLE DATABASE 学習','E',6,1) FROM DUAL;
--④6文字目から検索開始して2つ目のEの位置検索
SELECT INSTR('ORACLE DATABASE 学習','E',6,2) FROM DUAL;

<SQLの実行結果>
f:id:auroralights:20210506234553p:plain


<解説>
①~④すべて文字データ「ORACLE DATABASE 学習」に対して検索文字の文字位置を返しています。

  • ①のSQLでは検索文字「半角スペース」、開始位置「1」、検索回数「2」を指定。
    文字データの1文字目から検索して2回目に半角スペースが存在する文字位置の文字数を返しています。
  • ②のSQLでは検索文字「A」、開始位置「1」、検索回数「1」を指定。
    文字データの1文字目から検索して1回目にAが存在する文字位置の文字数を返しています。
  • ③のSQLでは検索文字「E」、開始位置「6」、検索回数「1」を指定。
    文字データの6文字目から検索して1回目にEが存在する文字位置の文字数を返しています。
    検索を開始するのは6文字目からですが、INSTR関数で返す文字位置の文字数は文字データの文頭から数えた数値であることを意識しましょう。
  • ④のSQLでは検索文字「E」、開始位置「6」、検索回数「2」を指定。
    文字データの6文字目から検索して2回目にEが存在する文字位置の文字数を返しています。


開始位置を何文字目に指定しようが、INSTR関数で返す文字数は、文字データの先頭から数えて何文字目に検索文字が存在するかの文字数であることに注意です。

ここでは試してませんが検索文字に2文字以上の文字を指定した場合も試してみましょう。

2.INSTR関数 「全角半角」「大文字小文字」「開始位置が負の値」の使用例

INSTR関数での全角半角、大文字小文字の扱い、開始位置が負の値の場合の動作を実際にSQLを使って確認してみましょう。

--①全角半角の区別(全角スペースの位置検索)
SELECT INSTR('ORACLE DATABASE 学習',' ',1,2) FROM DUAL;
--②全角半角の区別(全角Aの位置検索)
SELECT INSTR('ORACLE DATABASE 学習','A',6,1) FROM DUAL;
--③大文字小文字の区別(小文字aの位置検索)
SELECT INSTR('ORACLE DATABASE 学習','a',6,1) FROM DUAL;
--④開始位置が負の値の場合(小文字aの位置検索)
SELECT INSTR('ORACLE DATABASE 学習','A',-1,1) FROM DUAL;
--⑤開始位置が負の値の場合(小文字aの位置検索)
SELECT INSTR('ORACLE DATABASE 学習','DAT',-5,1) FROM DUAL;


<SQLの実行結果>
f:id:auroralights:20210507002158p:plain


<解説>
①~⑤すべて文字データ「ORACLE DATABASE 学習」に対して検索文字の文字位置を返しています。

  • ①のSQLでは検索文字「全角スペース」、開始位置「1」、検索回数「2」を指定。
    文字データの1文字目から検索して2回目に全角スペースが存在する文字位置の文字数を返しますが、全角スペースはないため「0」を返しています。
  • ②のSQLでは検索文字「A」(全角)、開始位置「6」、検索回数「1」を指定。
    文字データの6文字目から検索して1回目にA(全角)が存在する文字位置の文字数を返しますが、全角のAはないため「0」を返しています。
  • ③のSQLでは検索文字「a」、開始位置「6」、検索回数「1」を指定。
    文字データの6文字目から検索して1回目にaが存在する文字位置の文字数を返しますが、小文字「a」はないので「0」を返しています。
  • ④のSQLでは検索文字「A」、開始位置「-1」、検索回数「1」を指定。
    文字データの末尾の1文字目から検索して1回目にAが存在する文字位置の文字数を返しています。文字数は文頭からカウントします。

f:id:auroralights:20210507003250p:plain

  • ⑤のSQLでは検索文字「DAT」、開始位置「-5」、検索回数「1」を指定。
    文字データの末尾5文字目から検索して1回目にDATが存在する文字位置の文字数を返しています。

f:id:auroralights:20210507003500p:plain


INSTR関数は大文字小文字、全角半角を区別します。
開始位置が負の値の場合、検索は文末から文頭に検索をしますが、INSTR関数で返す文字位置については、必ず文頭からの文字数を返すことに注意です。