SQL関数を使って面倒な処理を簡単に済ませる：Webブラウザで気軽に学ぶ実践SQL講座（6）

今回のテーマは、SQL関数です。SQL関数を使うと、一見面倒に見える処理を、意外と簡単に済ませることができます。Oracle Databaseは数多くの関数を用意していますが、今回は、その中でも初学者が最初に学ぶべきSQL関数を紹介し、その使い方を解説します（編集部）

» 2012年02月20日 00時00分公開

[須々木尚子（すすきなおこ），日本オラクル]

SELECT句で関数を使ってみよう

連載バックナンバー

Webブラウザで気軽に学ぶ実践SQL講座記事一覧

　SQL関数（以下：関数）とは、単一の値、もしくはデータの集合を処理して、その結果を返すものです。関数を使うことで、「四捨五入」や「合計値や平均値の算出」といった処理を簡単に記述することができます。さらに、「日付を特定のフォーマットで表示する」というように、データの表示形式を変えることも可能です。

　冒頭で説明したように、Oracle Databaseは、数多くの関数を用意しています。関数にはOracle Database固有のものもありますし、合計値を出す「SUM」のようにMicrosoft Excelなどでもよく見るような関数もあります。また、事前定義された関数では処理できない、独自の処理がある場合には、本連載の後半で解説する「PL/SQL」を使って自分で関数を作る（ユーザー定義ファンクション）こともできます。

　表1に、代表的な関数を挙げます。この表にある関数は、Oracle Databaseで利用できる関数のうち、ごく一部ですが、利用頻度の高いものです。

数値関数
関数の名前と引数	機能	備考
TRUNC(数値データ, n)	数値を小数第n位までに切り捨てた値を戻す	数値データ（1つ目の引数）に、数値データが格納された列名を指定することも可能
ROUND(数値データ, n)	数値を小数第n位までに四捨五入した値を戻す	数値データ（1つ目の引数）に、数値データが格納された列名を指定することも可能
文字関数
関数の名前と引数	機能	備考
UPPER(文字データ)	文字をすべてを大文字にして戻す	文字データの部分に、文字列が格納された列名を指定することも可能
LOWER(文字データ)	文字をすべて小文字にして戻す
SUBSTR(文字データ, n1, n2)	n1番目の文字を先頭に、n2文字分切り取って戻す
日付関数
関数の名前と引数	機能	備考
EXTRACT(日付要素 FROM 日付データ)	日付データから、1つ目の引数で指定した要素を戻す	日付データの部分（2つ目の引数）に、日付データが格納された列名を指定することも可能
ADD_MONTHS(日付データ, n)	日付データに月数nを加えて戻す	日付データの部分（1つ目の引数）に、日付データが格納された列名を指定することも可能

　表1　Oracle Databaseで使用できる関数のうち、ごく基本的なもの

　関数を使ってデータを加工して表示するには、以下のように、関数で処理したい列名の前に関数を指定します。

SELECT        関数(列名),　関数(列名)…
FROM          表名
……;

　それでは、実際に関数を使ってみましょう。本連載で利用しているEMP表には、社員名がアルファベットの大文字で格納されています。関数を利用して、社員名を小文字で表示させてみましょう。以下のSQL文を実行してください。図1のような結果になります。

SELECT ename, LOWER(ename)
FROM   emp;

図1　LOWER関数の実行例。ENAME列にあるデータすべてを小文字で表示させている

　次に、社員名の2文字目から3文字分を取り出し、小文字で表示してみましょう。このようなときは、1つの列（今回の例ならENAME）に対して、複数の関数を指定する必要があります。表1の関数を組み合わせて、以下のように実行します。

SELECT ename, LOWER(SUBSTR(ename,2,3))
FROM   emp;

　実行すると、図2のような結果になります。

図2　LOWER関数とSUBSTR関数を組み合わせて使った例

関数をWHERE句で使ってみよう

　関数は、SELECT句だけでなく、WHERE句でも使えます。例えば、WHERE句で「EXTRACT」関数を使用して、9月入社の社員だけを検索することができます。以下のSQL文を実行してみてください。

SELECT ename, hiredate
FROM   emp
WHERE  EXTRACT(MONTH FROM hiredate)=09;

　実行結果は図3の通りです。9月に入社した社員2名のデータが表示されました。

図3　EXTRACT関数の実行例。EMPテーブルのデータから、9月入社のメンバーのデータだけを取り出した

　同様に、「同じ年に入社した社員」や「同じ日に入社した社員」を検索することもできます。例えば「EXTRACT(YEAR FROM hiredate)=1980」と指定すると「1980年に入社した社員」を、「EXTRACT(DAY FROM hiredate)=1」とすると「1日付けで入社した社員」を検索することができます。

変換関数を使って表示形式を整える

　関数を使って、データの書式を変えて表示することもできます。書式を変換する関数を、変換関数と呼びます。代表的な変換関数には表2のようなものがあります。


関数の名前と引数	機能
TO_CHAR(日付や数値のデータ, '書式')	日付や数値のデータを、指定した書式で整形して文字データとして戻す
TO_DATE(文字データ, '書式')	文字データを、指定した書式の日付データとして戻す
TO_NUMBER(文字データ, '書式')	文字データを、指定した書式の数値データとして戻す
表2　Oracle Databaseで使える、代表的な変換関数。それぞれの関数の第1引数にはデータが並んだ列名も指定できる

　例えば、日付書式について考えると、Oracle Databaseにおける日付データの標準書式は「RR（西暦表記の下2けた）-MM（月）-DD（日）」です。書式を何も変えずにデータを取り出せば、「12-02-20」のような形式になります。

　変換関数を使えば、このようにハイフン(-)を使った表記を、「年」「月」「日」などの文字で区切った表記や「/」で区切った表記に変えられます。西暦表示を日本の元号表記に変えることもできます。

　では、TO_CHAR関数を使って、HIREDATE列のデータの書式を変えてみましょう。TO_CHAR関数の2つ目の引数となる書式は、シングルクォート「’」で囲んで指定します。書式の中に文字リテラル（「年」などの文字）を指定する場合には、それぞれの文字リテラルをダブルクォート「"」で囲みます。下記の例で表示したい文字リテラルである「年」「月」「日」を、それぞれダブルクォートで囲んでいることに注意してください。

SELECT hiredate, TO_CHAR(hiredate, 'YYYY"年"MM"月"DD"日"')
FROM   emp;

　このSQL文を実行した結果が、図4です。HIREDATE列のそのままのデータと、書式を整えたデータを見比べてください。

図4　TO_CHAR関数を使って、日付の書式を整えた例

　このSQL文の実行に成功したら、書式を指定している「'YYYY"年"MM"月"DD"日"'」の部分を、「'YYYY/MM/DD'」などに変えて実行してみてください。また違う形でデータを見ることができますよ。

「今日」の日付を表示する

　Oracle Databaseを使っていると「今日から30日後」のように、「今日」を基準にしたデータを使わなければならない場面があります。このようなときは、「SYSDATE」関数を使うと便利です。SQL文を受け取ったデータベースサーバが稼働しているオペレーティングシステムの現在の日時データを得られます。

　しかし、ここで1つ問題があります。「現時点の日時データ」はどこかの表に記録されているデータではありません。つまり、表を対象にしてSYSDATE関数を実行しようにも、対象となる表がないのです。

　このようなときのために、Oracle Databaseは「DUAL」という表を用意しています。DUAL表は、DUMMYという列だけを持ち、「X」というデータが1行だけ入った表です。興味のある方は、「SELECT * FROM dual;」を実行してみてください。

　DUAL表は、表に格納されていないデータをSELECT句で指定して、表示する際に使用します。例えば、先ほど紹介したSYSDATE関数で現時点の日付を取得するには、以下のようなSQL文を実行します。

SELECT sysdate
FROM   dual;

　実行すると、図5のように、SQL文を実行した時点の日付データを得られます。

図5　関数SYSDATEの実行例。DUALテーブルを利用して関数を実行し、データを取得する

　このように、SYSDATE関数を使うと「現在の日付」を表示することができますが、実はSYSDATE関数が返した値には、秒単位のデータも入っています。先ほどの例と同じように、TO_CHAR関数を使って表示書式を変えてみましょう。以下のように指定すると「時(HH)」「分(MI)」「秒(SS)」のデータを表示することができます。

SELECT TO_CHAR(sysdate,'YYYY/MM/DD HH:MI:SS')
FROM   dual;

　実行すると図6のように、時間、分、秒の単位まで確認できます。

図6　SYSDATE関数の出力を、TO_CHAR関数で書式を整えて出したもの。秒単位のデータまで見える

DUAL表を利用して関数の動作を確認しよう

　今回の連載では、代表的な関数を紹介しましたが、すべての実行例をお見せすることはできませんでした。ぜひ、今回ご紹介した例以外にも、関数を使って動作を試してみてください。

　この時に役に立つのが、DUAL表です。例えば、TRUNC関数や、ROUND関数を試そうとしても、小数のデータが格納された表がないと、細かい動作を確認することは難しいでしょう。

　そのような場合に、DUAL表を使うと、ROUND関数の動作を簡単に確かめることができます。

SELECT round(3.4567, 2)
FROM   dual;

　実行すると図7のようになります。

図7　DUAL表を使って、ROUND関数の動作を確かめたところ

　この例のように、DUAL表を使って関数の動作を簡単に確認することができます。ぜひいろいろな関数を使ってみてください。

　今回紹介した関数はOracle Databaseで利用できるものの中でもごく一部です。これ以外にもさまざまな関数を利用できます。ほとんどに使わない関数もありますが、知っておくと便利な関数も多くあります。オラクルが用意している「Oracle Database SQL言語リファレンス 11gリリース2（11.2）」でいろいろな関数を探してみてください。

コラム---日付関数とタイムゾーン

　国際化が進む昨今、「海外で開発作業をしている」「海外にあるデータベースを検索する」ということも少なくないでしょう。こういう時、「どの地域の時間」が基準になっているのでしょうか。例えば、アメリカにあるデータベースサーバに日本から接続し、「現在の日付」を検索すると、その日付はアメリカの日付になるのでしょうか？それとも、日本の日付なのでしょうか？

　今回の連載で紹介した「SYSDATE」のほかにも、日付を戻す関数はいくつか存在します。SYSDATEは、データベースサーバが動作しているローカルサーバの現在の日付と時刻のセットを戻す関数です。つまり、データベースサーバがアメリカにあれば、アメリカの時間が結果として返ってくるのです。

　一方、セッション（SQL*Plusを起動しているマシン）の時間を戻す関数に「CURRENT_DATE」があります。下記の例では、日本（タイムゾーン「+9:00」）からSQL*Plusを使って、アメリカ（タイムゾーン「-5:00」）にあるデータベースサーバに接続し、それぞれの現在時刻を表示しています（図a）。

補足：本連載で使っている「apex.oracle.com」では、APEXとOracle Databaseが同じマシンで稼働しているため、「SYSDATE」を指定しても「CURRENT_DATE」を指定しても同じ時間が表示されます。そのため、この例では、SQL*Plusを使っています。