SQLの関数を使いこなす:ORACLE MASTER Bronze SQL基礎I 講座(4)
Oracleデータベースエンジニアとしての実力を証明するORACLE MASTER資格。その入り口であるBronze資格の取得は難しくない。本連載と、同時掲載中の連載「Bronze DBA講座」で合格を目指そう!
前回に引き続き、単一行関数について学びます。前回「SQLで使える関数の基礎知識」で、文字関数、数値関数、日付関数を紹介しました。今回は変換関数と一般関数を学びましょう。
ORACLE MASTER Bronze SQL基礎I 講座 各回のインデックス
第4回 SQLの関数を使いこなす
理解しておきたいこと
1.確認しておきたい内容
- 変換関数の使用
- 一般関数の使用
2.変換関数
変換関数は、入力値のデータ型を別のデータ型に変換して戻します。
Oracleデータベースはもともと、変換できると判断した場合、自動的にデータ型を変換(暗黙変換)します。 暗黙変換が行われるパターンは次のとおりです。
変換前 | 変換後 | 例 |
---|---|---|
CHAR、 VARCHAR2 |
NUMBER | 数値型の列に対して「WHERE 列 > '1000'」と指定された場合、'1000'を数値データに変換して比較する |
CHAR、 VARCHAR2 |
DATE | 日付型の列に対して「WHERE 列 > '1985-01-01'」と指定された場合、'1985-01-01'を日付データに変換して比較する |
NUMBER | VARCHAR2 | 文字型の列に対して「WHERE 列 = 1000」と指定された場合、1000を文字データに変換して比較する |
DATE | VARCHAR2 | 文字型の列に対して「WHERE 列 = SYSDATE」と指定された場合、SYSDATEを文字データに変換して比較する |
日付データなどは変換しきれないところも多いので、明示的に変換するために変換関数を使用します。多くの変換関数がありますが、以下のものは覚えておきましょう。
変換関数 | 説明 | 例 |
---|---|---|
TO_CHAR(数値データ) | 数値データを文字データに変換する。TO_CHAR(m, 'fmt')のように2つの引数を取る場合、fmtで指定した書式モデルで変換される | TO_CHAR(1000)→'1000' TO_CHAR(1000, 'L99,999.00')→'\1,000.00' |
TO_CHAR(日付データ) | 日付データを文字データに変換する。TO_CHAR('date', 'fmt')のように2つの引数を取る場合、fmtで指定した書式モデルで変換される | TO_CHAR('2005-08-15')→'2005-08-15' TO_CHAR(SYSDATE, 'YYYY"年"MM"月"DD"日"')→2005年08月12日 |
TO_NUMBER(文字データ) | 文字データを数値データに変換する。TO_NUMBER('str', 'fmt')のように2つの引数を取る場合、strはfmtで指定された書式であると判断される | TO_NUMBER('1000')→1000 TO_NUMBER('$1,000', '$9,999')→1000 |
TO_DATE(文字データ) | 文字データを日付データに変換する。TO_DATE('str', 'fmt')のように2つの引数を取る場合、strはfmtで指定された書式であると判断される | TO_DATE('2005-08-15')→'2005-08-15' TO_DATE('20050815', 'YYYYMMDD')→'2005-08-15' |
変換関数で使用される数値データの書式モデルで使用できる要素はいろいろありますが、以下のものは確認しておきましょう。
要素 | 説明 | 例 |
---|---|---|
9 | 数値1けたを表す。先行ゼロは表示しない | 「99,999」の場合、1000→1,000 |
0 | 数値1けたを表す。先行ゼロは0と表示 | 「00,000」の場合、1000→01,000 |
$ | ドル記号($)の表示 | 「$99,999」の場合、1000→$1,000 |
L | ローカル通貨記号の表示 | 「L99,999」の場合、1000→\1,000 |
. | 指定位置に小数点との区切り文字を表示 | 「99999.99」の場合、1000→1000.00 「999.99」の場合、100.555→100.56 |
, | 指定位置にけた区切りを表示 | 「99,999」の場合、1000→1,000 |
特に「9」と「0」の違い、「$」と「L」の違い、「,」と「.」の違いには注意しましょう。また、整数部のけた数が不足していると、すべてのけたが「#」と表示されてしまいますので気を付けてください。小数部のけた数が足りない場合は四捨五入が行われます。
同じく、日付データの書式モデルで使用できる要素もいろいろあります。以下のものは確認しておきましょう。
要素 | 説明 | 例 |
---|---|---|
YYYY | 4けたの数値による年の表記 | '2005-08-15'→'2005' |
YEAR | 英字つづりによる年の表記 | 「YEAR」の場合、'2005-08-15'→'TWO THOUSAND FIVE' 「year」の場合、'2005-08-15'→'two thousand five' 「Year」の場合、'2005-08-15'→'Two Thousand Five' |
YY | 現在の西暦の下2けたの数値による年の表記 | 現在が2005年であれば、'03'→2003年、'95'→2095年 現在が1998年であれば、'05'→1905年、'93'→1993年 |
RR | 現在の世紀を考慮(49を境として前世紀、今世紀を判断)した下2けたの数値による年の表記 | 現在が2005年であれば、'03'→2003年、'95'→1995年 現在が1998年であれば、'05'→2005年、'93'→1993年 |
MM | 2けたの数値による月の表記 | '2005-08-15'→'08' |
MONTH | 月名の完全表記 | 日本語環境の場合、'2005-08-15'→'8月' 英語環境で「MONTH」の場合、'2005-08-15'→'AUGUST' 英語環境で「month」の場合、'2005-08-15'→'august' 英語環境で「Month」の場合、'2005-08-15'→'August' |
MON | 月名の略式表記 | 日本語環境の場合、'2005-08-15'→'8月' 英語環境で「MON」の場合、'2005-08-15'→'AUG' 英語環境で「mon」の場合、'2005-08-15'→'aug' 英語環境で「Mon」の場合、'2005-08-15'→'Aug' |
DD | 2けたの数値による日の表記 | '2005-08-15'→'15' |
DAY | 曜日の完全表記 | 日本語環境の場合、'2005-08-15'→'月曜日' 英語環境で「DAY」の場合、'2005-08-15'→'MONDAY' 英語環境で「day」の場合、'2005-08-15'→'monday' 英語環境で「Day」の場合、'2005-08-15'→'Monday' |
DY | 曜日の略式表記 | 日本語環境の場合、'2005-08-15'→'月' 英語環境で「DY」の場合、'2005-08-15'→'MON' 英語環境で「dy」の場合、'2005-08-15'→'mon' 英語環境で「Dy」の場合、'2005-08-15'→'Mon' |
HH、HH12 | 1〜12による時間の表記 | '2005-08-15 17:30:15'→'05' |
AM、PM | 正午標識(午前、午後) | 日本語環境で「HH AM」の場合、'2005-08-15 11:30:15'→'11 午前' 日本語環境で「HH AM」の場合、'2005-08-15 17:30:15'→'05 午後' 英語環境で「HH AM」の場合、'2005-08-15 11:30:15'→'11 AM' 英語環境で「HH AM」の場合、'2005-08-15 17:30:15'→'05 PM' |
HH24 | 1〜24による時間の表記 | '2005-08-15 17:30:15'→'17' |
MI | 0〜59による分の表記 | '2005-08-15 17:30:15'→'30' |
SS | 0〜59による秒の表記 | '2005-08-15 17:30:15'→'15' |
SP | 数字を英字つづりで表記 | 「MISP」の場合、'2005-08-15 17:30:15'→'THIRTY' 「mmSP」の場合、'2005-08-15 17:30:15'→'eight' 「DdSP」の場合、'2005-08-15 17:30:15'→'Fifteen' |
TH | 序数(末尾にTH)を付けた表記 | 「MITH」の場合、'2005-08-15 17:30:15'→'30TH' 「mmTH」の場合、'2005-08-15 17:30:15'→'08th' 「DdTH」の場合、'2005-08-15 17:30:15'→'15th' |
SPTH | 数字を序数を付けた英字つづりで表記 | 「MISPTH」の場合、'2005-08-15 17:30:15'→'THIRTIETH' 「mmSPTH」の場合、'2005-08-15 17:30:15'→'eighth' 「DdSPTH」の場合、'2005-08-15 17:30:15'→'Fifteenth' |
FM | 埋め込まれた空白や先行ゼロを削除する | 「fmYYYY-MM-DD」の場合、'2005-08-05'→'2005-8-5' |
日付データでは、「/」「.」「,」「-」といった記号はそのまま認識できますが、「月」とか「日」といった文字を埋め込んだ書式にするとき、埋め込みたい文字列を「"」で囲んでください。
実際の試験では英語表記が多いので、テストを行う場合は、NLS_LANGUAGEセッションパラメータをAmericanにしておくとよいでしょう。
例:
SQL> ALTER SESSION SET NLS_LANGUAGE=American;
SQL> SELECT TO_CHAR(SYSDATE, 'Month') FROM dual;
TO_CHAR(SYSDA
-------------
August
問題
■問題1
TO_CHAR関数のみを使用して行える操作を選択しなさい。
a.10をTENに変換する
b.TENを10に変換する
c.10を'10'に変換する
d.'10'を10に変換する
正解:c
■解説
TO_CHAR関数を使用して、数値や日付を文字データとして扱うことができます。TO_CHAR関数のみでできるのは、文字データに変換することと、書式要素を使用して見せ方を変換することくらいです。
選択肢dの文字リテラル'10'を10に変換するのは、TO_NUMBER関数です。選択肢aと選択肢bのような、数字と文字の変換を行う関数はありません。
■問題2
日付データを引数として扱うことができる関数を2つ選択しなさい。
a.ROUND
b.TO_CHAR
c.TO_DATE
d.TO_NUMBER
正解:a、b
■解説
日付関数には、MONTHS_BETWEENやADD_MONTHSのように日付データしか扱えないものもありますが、ROUNDやTRUNCのように数値と日付を扱えるものもあります。
SELECT ROUND(SYSDATE) FROM dual;
また、TO_CHAR関数のように日付データを文字リテラルに変換する関数もあります。
SELECT TO_CHAR(SYSDATE, 'YYYY') FROM dual;
不正解となる選択肢cのTO_DATE関数は、入力値として日付データを使用するのではなく、結果値として日付データを扱います。選択肢dのTO_NUMBER関数では、日付データを扱うことはできません。
■問題3
現在の日付から年の部分(「1998」など)のみを取り出して表示する文を選択しなさい。
a.SELECT TO_CHAR(SUBSTR(SYSDATE, 1, 1), 'YYYY') FROM dual;
b.SELECT TO_CHAR(SYSDATE, 'YYYY') FROM dual;
c.SELECT TO_DATE(SYSDATE, 'YYYY') FROM dual;
d.SELECT SUBSTR(SYSDATE, 'year') FROM dual;
正解:b
■解説
年を表す日付要素は「YYYY」です。表示書式を変更するには、TO_CHAR関数を使用します。
選択肢aと選択肢dで使用しているSUBSTR関数は切り出し関数です。SUBSTR関数を使って次のように指定すれば、年の部分のみを抽出することができます。
SELECT SUBSTR(TO_CHAR(SYSDATE,'YYYYMMDD'), 1, 4) FROM dual;
選択肢cのTO_DATE関数は日付データに変換する関数です。間違えないようにしましょう。
■問題4
2000を$2,000.00と表示するSELECT文を3つ選択しなさい。
a.SELECT TO_CHAR(2000,'$#,###.##') FROM dual;
b.SELECT TO_CHAR(2000,'$9,999.99') FROM dual;
c.SELECT TO_CHAR(2000,'$9,999.00') FROM dual;
d.SELECT TO_CHAR(2000,'$9,000.00') FROM dual;
e.SELECT TO_CHAR(2000,'$N,NNN.NN') FROM dual;
f.SELECT TO_CHAR(2000,'$#,###.00') FROM dual;
正解:b、c、d
■解説
数値データを表現する要素は「0」と「9」です。この問題の値の場合は、正解となる選択肢による結果はいずれも同じです。数字ゼロを表現するとき、次のように結果が変わります。
SQL> SELECT TO_CHAR(0,'$9,999.99') FROM dual;
TO_CHAR(0,
----------
$.00
SQL> SELECT TO_CHAR(0,'$9,999.00') FROM dual;
TO_CHAR(0,
----------
$.00
上記は正解bと正解cです。この2つは整数部に「9」を使用していますので、結果は同じです。「9」と「0」は小数点以下の場合は、どちらも数字ゼロを出力します。
一方、整数部では、「9」を使用すれば数字ゼロを出力しませんが、「0」を使用すると数字ゼロを出力します。
SQL> SELECT TO_CHAR(0,'$9,000.00') FROM dual;
TO_CHAR(0,
----------
$000.00
上記は正解dです。
数値要素「9」と「0」は間違いやすいので注意しましょう。選択肢a、e、fで使用している「#」や「N」といった要素は存在しません。
3.一般関数
NULL値を値に置き換えたり(NVL関数、NVL2関数)、条件によって異なる値を戻したり(CASE述語、DECODE関数)する関数を一般関数と呼びます。多くの一般関数がありますが、以下のものは覚えておきましょう。
一般関数 | 説明 | 例 |
---|---|---|
NVL | NULL値を実際の値に置き換える。NVL('expr1', 'expr2')のように2つの引数を取り、expr1に値があればexpr1を戻し、expr1がNULL値ならばexpr2を戻す | NVL(comm, 0) comm列がNULL→0 comm列が1000→1000 |
NVL2 | 値がある場合もNULL値の場合も値を置き換える。NVL2('expr1', 'expr2', 'expr3')のように3つの引数を取り、expr1に値があればexpr2を戻し、expr1がNULL値ならばexpr3を戻す | NVL2(comm, 1, 0) comm列がNULL→0 comm列が1000→1 |
NULLIF | NULLIF('expr1', 'expr2')のように2つの引数を取り、expr1とexpr2が等しければNULL値を戻し、等しくなければexpr1を戻す | NULLIF(deptno, 99) deptno列が10→10 deptno列が99→NULL |
COALESCE | COALESCE(式リスト)のように複数の引数を取り、リスト内の最初のNULL値以外の値を戻す | COALESCE(c1, c2, c3) c1列に10→10 c1列がNULL、c2列に20→20 c1、c2列がNULL、c3列に30→30 |
CASE | 条件に一致した値を戻す。構文は次のとおり。 CASE 式 WHEN 条件1 THEN 値1 [WHEN 条件n THEN 値n] [ELSE デフォルト値] END 式と一致する条件nの値nを戻し、どの条件とも一致しない場合、デフォルト値を戻す |
CASE c1 WHEN 'A' THEN 1 WHEN 'B' THEN 2 ELSE 0 END |
DECODE | 条件に一致した値を戻す。構文は次のとおり。 DECODE(式, 条件1, 値1 [, 条件n, 値n] [, デフォルト値] 式と一致する条件nの値nを戻し、どの条件とも一致しない場合、デフォルト値を戻す |
DECODE(c1, 'A', 1, 'B', 2, 0) c1列が'A'→1 c1列が'B'→2 c1列が'C'→0 |
NULL値は、計算後もNULL値になってしまうので、NVL関数などを使用して値に置き換えることを覚えておきましょう。NULLIF関数は、デフォルト値がある列において「デフォルト値と等しければNULL値とする」ような場合に便利です。
問題
■問題1
関数に関する説明として正しいものを3つ選択しなさい。
a.INSTRは、文字の位置を戻す
b.TRUNCATEは、文字、日付、数値の切り捨てを行う
c.DECODEは、文字を比較し、変換することができる
d.NVLは、2つの値を比較し、等しかったらNULLを戻し、等しくなかったら式1を戻す
e.NULLIFは、2つの値を比較し、等しかったらNULLを戻し、等しくなかったら式1を戻す
正解:a、c、e
■解説
前回の宿題にした問題です。正解とした関数は次のように使用できます。
■INSTR関数(正解a)
列または式から、指定した文字列の位置を数値で戻します。検索開始位置、出現回数を指定することもできます。
■DECODE関数(正解c)
CASE式と同様、式と条件値が等しいかどうかを評価して戻り値を決定します。
■NULLIF関数(正解e)
2つの値を比較し、等しければNULLを戻し、等しくなければ式1を戻す関数です。
そのほかの選択肢の不正解の理由は次のとおりです。
■選択肢b:「TRUNCATE」関数は存在しません。TRUNC関数では、日付と数値の切り捨てを行うことができます。
■選択肢d:NVL関数は、式1に値があれば式1を戻し、式1がNULLならば式2を戻します。
2つの値を比較して、等しかったらNULLを戻し、等しくなかったら式1を戻すのはNULLIF関数の特徴です。
まとめ
前回と今回で学んだ単一行関数については、次の内容をチェックしておきましょう。
- TRIM関数とSUBSTR関数の構文
- 各関数の戻りデータ型
- 数値に対するTRUNC関数とROUND関数の引数
- 日付データの計算(日付データ同士の減算はできるが、加算はできないなど)
- 日付データの要素(TO_CHAR関数によるフォーマットの変更)
- 数値データの要素。「0」は整数部の数値ゼロを表示し、「9」は整数部の数値ゼロは表示しない
宿題
次回は、「グループ関数を使用したデータの集計」を確認します。次の宿題を解いておいてください。
CUST表から、ロサンゼルス(Los Angeles)に住んでいる顧客のロケーション(loc)ごとの人数を求める文を選択しなさい。
a.SELECT COUNT(DISTINCT loc) FROM cust WHERE address = 'Los Angels';
b.SELECT COUNT DISTINCT loc FROM cust WHERE address = 'Los Angels';
c.SELECT DISTINCT(COUNT loc) FROM cust WHERE address = 'Los Angels';
d.SELECT COUNT(loc) FROM cust WHERE address = 'Los Angels';
Copyright © ITmedia, Inc. All Rights Reserved.