Database
技術記事
SQL 101
テクノロジー：SQLの基礎
状況に合った型の選択著者：Melanie Caffrey
リレーショナル・データベースとSQLの基本に関するシリーズ記事のパート8 このシリーズ記事のパート7 "FLOOR、CEILINGなどの数値関数やその他の機能的なケースについて"（Oracle Magazine、2012年9/10月）では、よく使用されるSQL数値関数を紹介し、問合せを利用して、数値で構成される結果セット・データの表示を変更する方法について説明しました。また、SQL置換関数を紹介し、よりわかりやすい結果を得るために、SQL置換関数を利用して結果セット・データを操作する方法についても説明しました。同様に、SQLの日付関数とデータ型変換関数を使用すれば、データベースに格納された形式とは異なる形式で表示するようにデータを操作できます。この記事では、比較的よく使用されるSQL日付関数のほか、便利なデータ型変換関数をいくつか紹介します。 このシリーズ記事の例を試すには、Oracle Databaseインスタンスにアクセスする必要があります。必要に応じて、お使いのオペレーティング・システムに対応したOracle Databaseエディションをダウンロードし、インストールしてください。筆者がインストールをお勧めするエディションは、Oracle Database, Express Edition 11g Release 2です。 このOracle Databaseソフトウェアをインストールする場合は、データベースの作成と構成が可能なインストール・オプションを選択してください。サンプルのユーザー・アカウントと関連する新しいスキーマを含む新しいデータベースが作成されます（SQL_101は、このシリーズ記事の例で使用するユーザー・アカウントです。また、データベースの表やその他のオブジェクトが作成されるスキーマでもあります）。インストール・プロセスの実行中にスキーマのパスワードを指定するように求められたら、SYSおよびSYSTEMのパスワードを入力して確認し、そのパスワードを覚えておいてください。 最後に、このデータベース・ソフトウェアをゼロからインストールした場合でも、既存のOracle Databaseインスタンスにアクセスする場合でも、SQLスクリプトをダウンロードして解凍し、実行して、この記事の例で必要となるSQL_101スキーマ用の表を作成します（このスクリプトをテキスト・エディタで開き、実行方法の説明を参照してください）。一部の例ではDUAL表も使用します。すでに説明したとおり、DUALは、SQL_101スキーマではなくSYSユーザーが所有するOracleシステム表です。DUAL表自体には意味のあるデータは含まれませんが、リテラルに対して動作する関数を試す手段として問合せの対象とすれば便利です。 最適な日付書式DATEデータ型は、Oracle Database内では日付と時刻（世紀、年、月、日、時、分、秒）の両方の情報で構成される内部形式として格納されます。すべてのOracle Databaseインスタンスには、日付の入出力のためのデフォルトの日付書式モデル（別名：マスク）があります。 RRという書式マスクは2桁の年を表すもので、2000年問題などの世紀末の問題に対処するために導入されました。RRを使用した2桁の年は、現在の年と問合せで指定した2桁の年に応じて、前世紀、今世紀、次世紀のいずれかの年を表します。表1に、現在の年と2桁の年の範囲の組合せ、その結果として想定される世紀の関係を示します。
たとえば、現在の年（2012年）の末尾2桁は12で、00～49の範囲内にあります。2012年のうちに実行したSQL問合せで、RRの年の値として15を指定した場合は、今世紀（21世紀）の末尾15の年（2015年）と見なされます（15が0～49の範囲内にあるため）。2012年に実行した問合せで、RRの年の値として98を指定した場合は、前世紀（20世紀）の末尾98の年（1998年）と見なされます（98が50～99の範囲内にあるため）。 リスト1の問合せでは、この記事のサンプル・スキーマ内にあるEMPLOYEE表を利用します。この問合せによって、雇用日が新しい順でソートされた従業員情報が表示されます。ご覧のとおり、雇用日のデータはDD-MON-RRという書式で表示されます。たとえば、Roger Friedliは16-MAY-07に雇用されています。このデータの表示方法を変更するには、TO_CHAR変換関数を任意の書式モデルとともに使用します（TO_CHARについては前回の記事で、数値をテキスト文字列に変換するために使用できることを簡単に紹介しました）。 コード・リスト1：Oracle Databaseのデフォルトの日付書式による日付データの表示 SQL> set feedback on SQL> select first_name, last_name, hire_date 2 from employee 3 order by hire_date desc, last_name, first_name; FIRST_NAME LAST_NAME HIRE_DATE ——————————————— ——————————————— ————————————— Theresa Wong 27-FEB-10 Thomas Jeffrey 27-FEB-10 mark leblanc 06-MAR-09 michael peterson 03-NOV-08 Roger Friedli 16-MAY-07 Betsy James 16-MAY-07 Matthew Michaels 16-MAY-07 Donald Newton 24-SEP-06 Frances Newton 14-SEP-05 Emily Eckhardt 07-JUL-04 10 rows selected.
リスト2の問合せで、リスト1での日付データの表示方法を変更します。DATEデータ型のデータを特定の日付書式モデルに変換するためのTO_CHARは、必須パラメータ1つとオプションのパラメータ1つを指定します。必須パラメータは、列、式、またはリテラルのDATEデータ型のデータです。オプションのパラメータは、表示される日付の書式マスクをテキストで表現したものです。リスト2では、DD-MON-RRというデフォルトの書式マスクを、YYYY-MM-DDと表示するように変更しています。 コード・リスト2：TO_CHARを書式マスク付きで使用して、異なる書式で日付データを表示する例 SQL> select first_name, last_name, TO_CHAR(hire_date, 'YYYY-MM-DD') hire_date 2 from employee 3 order by hire_date desc, last_name, first_name; FIRST_NAME LAST_NAME HIRE_DATE ——————————————— ——————————————— ————————————— Thomas Jeffrey 2010-02-27 Theresa Wong 2010-02-27 mark leblanc 2009-03-06 michael peterson 2008-11-03 Roger Friedli 2007-05-16 Betsy James 2007-05-16 Matthew Michaels 2007-05-16 Donald Newton 2006-09-24 Frances Newton 2005-09-14 Emily Eckhardt 2004-07-07 10 rows selected.
リスト3の例では、TO_CHARの第2パラメータがオプションであることがわかります。このパラメータを指定しない場合、返される日付データの書式マスクは単にデフォルトの書式マスクです。また、返される日付のデータ型がVARCHAR2である点にも注意が必要です。リスト3の出力はHIRE_DATEの降順でソートされていますが、日付ではなく文字の降順です。TO_CHAR変換関数を適用するとデータが文字列として返されるため、それに応じた計画とソートが必要です。 コード・リスト3：オプションのパラメータを指定しない場合にデフォルトの日付書式マスクが使用される例 SQL> select first_name, last_name, TO_CHAR(hire_date) hire_date_formatted 2 from employee 3 order by hire_date_formatted desc, last_name, first_name; FIRST_NAME LAST_NAME HIRE_DATE ——————————————— ——————————————— ————————————— Thomas Jeffrey 27-FEB-10 Theresa Wong 27-FEB-10 Donald Newton 24-SEP-06 Roger Friedli 16-MAY-07 Betsy James 16-MAY-07 Matthew Michaels 16-MAY-07 Frances Newton 14-SEP-05 Emily Eckhardt 07-JUL-04 mark leblanc 06-MAR-09 michael peterson 03-NOV-08 10 rows selected.
文字列付きの日付日付を文字列に変換できるのと同様に、文字列リテラルを日付に変換できます。その結果の式は、他のDATEデータ型列のデータや他の日付式と比較できます。この変換は、テキスト文字列にTO_DATE変換関数を適用することで実行します（リスト4を参照）。リスト4の問合せでは、HIRE_DATEの値が01-JAN-2008という日付値よりも大きい従業員がすべて返されます。また、リスト4より、TO_DATE変換関数はSELECT構文のリスト内でもWHERE句内でも使用できることがわかります。TO_DATE関数は01-JAN-2008という文字列リテラルに適用されます。この際に、指定したリテラルを日付としてデータベースで解釈できるようにするための書式マスクも記述しています。 コード・リスト4：WHERE句でのTO_DATE変換関数の使用例
SQL> select first_name, last_name, TO_CHAR(hire_date, 'DD-MON-YYYY') hire_date
2 from employee
3 where hire_date > TO_DATE('01-JAN-2008', 'DD-MON-YYYY')
4 order by hire_date desc, last_name, first_name;
FIRST_NAME LAST_NAME HIRE_DATE
——————————————— ——————————————— —————————————
Thomas Jeffrey 27-FEB-2010
Theresa Wong 27-FEB-2010
mark leblanc 06-MAR-2009
michael peterson 03-NOV-2008
4 rows selected.
TO_DATE関数に書式マスクを指定する場合には、指定する文字列リテラル内で使用されるマスクと同じものを選択する必要があります。この2つが異なると、リスト5のようなエラー・メッセージが表示されます。テキスト・リテラルを変換する際には、TO_DATE変換関数を使用して適切な書式マスクを明示的に指定することをお勧めします。このようにすれば、データベース、インスタンス、またはセッションのデフォルトの日付設定が何であっても、記述した文が独立して解釈されます。 コード・リスト5：指定した文字列リテラルに書式マスクが一致しない場合のエラー
SQL> select first_name, last_name, TO_CHAR(hire_date, 'DD-MON-YYYY') hire_date
2 from employee
3 where hire_date > TO_DATE('01-JAN-2008', 'MM/DD/RR')
4 order by hire_date desc, last_name, first_name;
where hire_date > TO_DATE('01-JAN-2008', 'MM/DD/RR')
*
ERROR at line 3:
ORA-01858: a non-numeric character was found where a numeric was expected
Oracle Databaseでは、可能な場合に日付の暗黙的変換が実行されます。この変換が実行されるのは、リテラルがすでにデフォルトの日付書式で指定されている場合（のみ）です。ただし、暗黙的変換を実行しないようにすることをお勧めします。暗黙的変換を実行するようなコードは脆弱で、長期的に機能する可能性が低いからです。 コード・リスト6：日付の暗黙的変換（非推奨）により結果セットが返される例 SQL> select first_name, last_name, TO_CHAR(hire_date, 'DD-MON-YYYY') hire_date 2 from employee 3 where hire_date > '01-JAN-2008' 4 order by hire_date desc, last_name, first_name; FIRST_NAME LAST_NAME HIRE_DATE ——————————————— ——————————————— ————————————— Thomas Jeffrey 27-FEB-2010 Theresa Wong 27-FEB-2010 mark leblanc 06-MAR-2009 michael peterson 03-NOV-2008 4 rows selected.
コード・リスト7：日付の暗黙的変換の試行が失敗する例
SQL> select first_name, last_name, TO_CHAR(hire_date, 'DD-MON-YYYY') hire_date
2 from employee
3 where hire_date > '01/01/2008'
4 order by hire_date desc, last_name, first_name;
where hire_date > '01/01/2008'
*
ERROR at line 3:
ORA-01843: not a valid month
デフォルトの日付書式は変更される可能性があるため、予想されるデフォルト書式に依存した問合せにしないことが最善策です。代わりに、日付の文字列リテラルでは常にTO_DATE関数を使用してください。現在のセッションで使用しているデフォルトの日付書式を把握する1つの方法として、リスト8に示す問合せを実行します。SYS_CONTEXT関数はすべてのセッション（すなわち、すべてのユーザー）が使用できる関数であり、現在のセッション属性を確認できます。 コード・リスト8：現在のセッションにおけるデフォルトの日付書式の確認
SQL> select sys_context ('USERENV', 'NLS_DATE_FORMAT')
2 from dual;
SYS_CONTEXT('USERENV','NLS_DATE_FORMAT')
————————————————————————————————————————————————
DD-MON-RR
1 row selected.
日付データによる時刻の取得すでに説明したとおり、Oracle DATEデータ型には時刻部分が含まれています。時刻部分については、この記事のこれまでの例のように無視することもできますが、表示や比較の目的で結果に含めることもできます。リスト9に、時刻部分を含む問合せを示します。この問合せでは、EMPLOYEE表内のすべての従業員について、各HIRE_DATEの値に時刻部分が含まれます。ここで、Theresa Wong以外のすべての従業員レコードの時刻値が12:00:00である点に注意してください。DATEデータ型の列に値を挿入するときに時刻を含めない場合、時刻はデフォルト値の午前0時（12:00:00 a.m.または24時間制の00:00:00）に設定されます。24時間制で日付値の表示や比較を行うには、HHではなくHH24という書式マスクを使用します。 コード・リスト9：DATEデータ型の値に関する時刻部分の表示
SQL> set lines 32000
SQL> select first_name, last_name, TO_CHAR(hire_date, 'DD-MON-YYYY HH:MI:SS') hire_date
2 from employee
3 order by hire_date desc, last_name, first_name;
FIRST_NAME LAST_NAME HIRE_DATE
——————————————— ——————————————— —————————————
Thomas Jeffrey 27-FEB-2010 12:00:00
Theresa Wong 27-FEB-2010 09:02:45
Donald Newton 24-SEP-2006 12:00:00
Roger Friedli 16-MAY-2007 12:00:00
Betsy James 16-MAY-2007 12:00:00
Matthew Michaels 16-MAY-2007 12:00:00
Frances Newton 14-SEP-2005 12:00:00
Emily Eckhardt 07-JUL-2004 12:00:00
mark leblanc 06-MAR-2009 12:00:00
michael peterson 03-NOV-2008 12:00:00
10 rows selected.
フィルタリング対象の日付値について正確な時刻を知っている場合、あるいは日付値の時刻部分のすべてがすでに午前0時に設定されている場合を除き、WHERE句内で日付値を使用すると予期せぬ結果になることがあります。リスト10の問合せを見てみましょう。これまでのリストの結果より、2人の従業員が2010年2月27日に雇用されたことがわかっていますが、リスト10の結果セットには1人しか返されていません。その理由として、WHERE句のTO_DATE関数で正確な時刻を指定していないため、Oracle Databaseにより時刻が午前0時であると見なされ、指定した日付値で、かつ時刻部分が午前0時であるレコードのみが返されます。 コード・リスト10：TO_DATEを使用したWHERE句で、可能な値のすべてが取得されない例 SQL> select first_name, last_name, TO_CHAR(hire_date, 'DD-MON-YYYY HH:MI:SS') hire_date
2 from employee
3 where hire_date = TO_DATE('27-FEB-2010', 'DD-MON-YYYY')
4 order by last_name, first_name;
FIRST_NAME LAST_NAME HIRE_DATE
——————————————— ——————————————— —————————————
Thomas Jeffrey 27-FEB-2010 12:00:00
1 row selected.
日付の短縮化ある特定の日付に基づいてフィルタリングしながらも、個々の時刻部分を含めない場合に、いくつかの方法を利用できます。1つの方法は、TRUNC関数を使用することです（この関数については、前回の記事で紹介しました）。 コード・リスト11：DATE値から時刻を切り捨てて、特定の日付と合致するすべてのレコードを返す例 SQL> select first_name, last_name, TO_CHAR(hire_date, 'DD-MON-YYYY HH:MI:SS') hire_date
2 from employee
3 where TRUNC(hire_date) = TO_DATE('27-FEB-2010', 'DD-MON-YYYY')
4 order by last_name, first_name;
FIRST_NAME LAST_NAME HIRE_DATE
——————————————— ——————————————— —————————————
Thomas Jeffrey 27-FEB-2010 12:00:00
Theresa Wong 27-FEB-2010 09:02:45
2 rows selected.
ただし、WHERE句内にある表の列値に対して関数を適用すればパフォーマンスが低下するおそれがある点に注意してください。索引（このシリーズ記事では取り上げませんが、データ・アクセスの効率化のために使用します）により、特定の状況での問合せパフォーマンスが向上します。しかし、ある表の列に関数を適用すると、その列の索引が使用されなくなる場合があります。また、この関数は、すべての行のその列に対応するすべての値に適用されます。 コード・リスト12：特定の日付に合致するレコードを返す日付範囲 SQL> select first_name, last_name, TO_CHAR(hire_date, 'DD-MON-YYYY HH:MI:SS') hire_date
2 from employee
3 where hire_date >= TO_DATE('27-FEB-2010', 'DD-MON-YYYY')
4 and hire_date < TO_DATE('28-FEB-2010', 'DD-MON-YYYY')
5 order by last_name, first_name;
FIRST_NAME LAST_NAME HIRE_DATE
——————————————— ——————————————— —————————————
Thomas Jeffrey 27-FEB-2010 12:00:00
Theresa Wong 27-FEB-2010 09:02:45
2 rows selected.
日付を正しく取得するためのシステム日付計算を実行しなければならないこともよくあります。この際の便利な組込み関数（Oracle Databaseにすでに組み込まれている関数）がSYSDATEです。この関数は、データベースがインストールされたコンピュータのオペレーティング・システムに設定されている現在の日付と時刻を返します。この関数にはパラメータはありません。リスト13に、SYSDATE関数を使用し、現在の日付と時刻を返して表示する例を示します。 コード・リスト13：SYSDATE関数 SQL> select SYSDATE, TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') sysdate_with_time
2 from dual;
SYSDATE SYSDATE_WITH_TIME
——————— ——————————————————————————————
08-AUG-12 08-AUG-2012 14:25:08
1 row selected.
SYSDATEは日付計算において非常に便利です。リスト14の問合せは、本日（例では2012年8月8日）から数えた2012年の残りの日数を示します。この例で、仮にSYSDATE値を切り捨てないとすると、返されるDAYS_TILL_2013値には、SYSDATE値の断片的なデータ（時刻部分に対応するもの）が含まれます。しかし、この例ではSYSDATE値が切り捨てられるため、本日1日分を含めて2013年1月1日から減算され、この年は残り146日であるという結果が出されます。リスト15では、EMPLOYEE表のHIRE_DATE列に対してSYSDATEと日付計算（MONTHS_BETWEENという日付関数）を使用して、各従業員の勤務年数を表示します。 コード・リスト14：日付計算でのSYSDATEの使用例
SQL> select SYSDATE, (TO_DATE('01-JAN-2013', 'DD-MON-YYYY') - TRUNC(SYSDATE))
Days_till_2013
2 from dual;
SYSDATE DAYS_TILL_2013
—————————— ——————————————
08-AUG-12 146
1 row selected.
コード・リスト15：SYSDATEおよび日付計算とDATEデータとの組合せ SQL> select substr(last_name, 1, 10) last_name, substr(first_name, 1, 10) first_name, hire_date, ROUND(MONTHS_BETWEEN(TRUNC(SYSDATE), TRUNC(HIRE_ DATE))/12, 2) YEARS_OF_SERVICE 2 from employee 3 order by years_of_service desc, last_name, first_name; LAST_NAME FIRST_NAME HIRE_DATE YEARS_OF_SERVICE ————————— ——————————— ————————— —————————————— Eckhardt Emily 07-JUL-04 8.09 Newton Frances 14-SEP-05 6.9 Newton Donald 24-SEP-06 5.88 Friedli Roger 16-MAY-07 5.23 James Betsy 16-MAY-07 5.23 Michaels Matthew 16-MAY-07 5.23 peterson michael 03-NOV-08 3.77 leblanc mark 06-MAR-09 3.42 Jeffrey Thomas 27-FEB-10 2.45 Wong Theresa 27-FEB-10 2.45 10 rows selected.
日付計算には、BETWEEN演算子を使用する方法もあります。この使用例について、リスト16の問合せで示します。注意点として、BETWEEN演算子では、日付範囲の比較において、範囲の最大値の時刻部分として午前0時（00:00:00）が使用されます。日付の比較で範囲の最大値に指定した日付について、可能な値をすべて含めるには、日付範囲の最大値に時刻部分全体を含めるようにしてください。リスト16の例の場合、日付範囲の最大値に27-FEB-2010 23:59:59と指定すれば、結果セット内に、HIRE_DATE値が27-FEB-2010である2人の従業員レコードが含まれるようになります。 コード・リスト16：BETWEEN演算子では、日付範囲の比較で午前0時の時刻が使用される
SQL> select last_name, first_name, hire_date
2 from employee
3 where hire_date BETWEEN TO_DATE('26-FEB-2010', 'DD-MON-YYYY')
4 AND TO_DATE('27-FEB-2010', 'DD-MON-YYYY');
FIRST_NAME LAST_NAME HIRE_DATE
——————————————— ——————————————— —————————————
Jeffrey Thomas 27-FEB-10
1 row selected.
結論この記事では、よく使用される日付関数の一部と、それらの関数を使用してデータの表示を操作する方法について説明しました。変換関数のTO_CHARとTO_DATEを使用する方法や、これらの違いについて確認しました。また、すべての日付には時刻部分が含まれ、必要に応じて時刻部分を利用することも切り捨てることもできることがわかりました。SYSDATE関数と日付計算についても紹介しました。最後に重要なこととして、WHERE句内でTO_DATEやBETWEENを使用してDATEの比較を行う際に気をつけるべき落とし穴や、予期せぬ結果を避けるための対策についても学習しました。この記事ではすべてのOracle Databaseの日付関数やデータ型変換関数を説明していません。詳しくは、数値書式要素および日時関数のドキュメントを確認してください。このSQLの基礎に関する次回の記事では、集計関数について説明します。
Melanie Caffreyはオラクルの上級開発マネージャーです。Expert PL/SQL Practices for Oracle Developers and DBAs（Apress、2011年）およびExpert Oracle Practices: Oracle Database Administration from the Oak Table（Apress、2010年）の共著者でもあります。 |
その他の記事 
Printer View