連載
» 2005年08月16日 00時00分 公開

ORACLE MASTER Bronze SQL基礎I 講座(4):SQLの関数を使いこなす

Oracleデータベースエンジニアとしての実力を証明するORACLE MASTER資格。その入り口であるBronze資格の取得は難しくない。本連載と、同時掲載中の連載「Bronze DBA講座」で合格を目指そう!

[有限会社 G.F.インフィニティ,Project - ∞]

 前回に引き続き、単一行関数について学びます。前回「SQLで使える関数の基礎知識」で、文字関数、数値関数、日付関数を紹介しました。今回は変換関数と一般関数を学びましょう。

理解しておきたいこと

1.確認しておきたい内容

  • 変換関数の使用
  • 一般関数の使用

2.変換関数

 変換関数は、入力値のデータ型を別のデータ型に変換して戻します。

 Oracleデータベースはもともと、変換できると判断した場合、自動的にデータ型を変換(暗黙変換)します。 暗黙変換が行われるパターンは次のとおりです。

表1 暗黙変換の例
変換前 変換後
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を文字データに変換して比較する

 日付データなどは変換しきれないところも多いので、明示的に変換するために変換関数を使用します。多くの変換関数がありますが、以下のものは覚えておきましょう。

表2 変換関数の例
変換関数 説明
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'

 変換関数で使用される数値データの書式モデルで使用できる要素はいろいろありますが、以下のものは確認しておきましょう。

表3 数値データの書式モデルに使用する要素の例
要素 説明
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」の違い、「,」と「.」の違いには注意しましょう。また、整数部のけた数が不足していると、すべてのけたが「#」と表示されてしまいますので気を付けてください。小数部のけた数が足りない場合は四捨五入が行われます。

 同じく、日付データの書式モデルで使用できる要素もいろいろあります。以下のものは確認しておきましょう。

表4 日付データの書式モデルに使用する要素の例
要素 説明
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関数)する関数を一般関数と呼びます。多くの一般関数がありますが、以下のものは覚えておきましょう。

表5 一般関数の例
一般関数 説明
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.

RSSについて

アイティメディアIDについて

メールマガジン登録

@ITのメールマガジンは、 もちろん、すべて無料です。ぜひメールマガジンをご購読ください。