Database
技術記事
SQL 101
テクノロジー：SQLの基礎
FLOOR、CEILINGなどの数値関数やその他の機能的なケースについて著者：Melanie Caffrey
リレーショナル・データベースとSQLの基本に関するシリーズ記事のパート7 このシリーズ記事のパート6"文字関数"（Oracle Magazine、2012年7/8月）では、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表自体には意味のあるデータは含まれませんが、リテラルに対して動作する関数を試す手段として問合せの対象とすれば便利です。 適切に丸められた数値よく使用する数値関数の1つにROUNDがあります。この関数により、結果セット内に返される数値を丸めることができます。たとえば、リスト1の単純な問合せではこの関数を使用して、2つの数値に従来の丸め処理を適用しています。一方の数値を切り下げて、もう一方の数値を切り上げています。 コード・リスト1：ROUND関数を使用した切り上げと切り下げ
SQL> set feedback on
SQL> select ROUND(7534.1238, 2), ROUND(99672.8591, 2)
2 from dual;
ROUND(7534.1238,2) ROUND(99672.8591,2)
—————————————————— ———————————————————
7534.12 99672.86
1 row selected.
数値関数には、数値の入力パラメータが必要です。この入力パラメータは、NUMBERデータ型の列か数値リテラルのいずれかになります。ROUNDは、2つのパラメータ（必須のパラメータとオプションのパラメータ）を受け取ります。必須のパラメータには、丸める数値を指定します。オプションのパラメータには、丸めの精度を示す整数を指定します。精度は、小数点の右側（正の整数で示す）または左側（負の整数で示す）の何桁まで数値を丸めるかを表します。リスト1の問合せではROUND数値関数を2つの数値リテラルの値に適用しています。両方の数値が小数点以下第2位で丸められます。 ROUND関数の第2パラメータを省略した場合、指定した数値がもっとも近い整数に丸められます（リスト2を参照）。リスト3の問合せは、ROUND関数のオプションのパラメータに負の整数を渡した場合に、小数点の左側で数値が丸められることを示しています。 コード・リスト2：ROUND関数を使用して数値を整数へと丸める例
SQL> select ROUND(7534.1238), ROUND(99672.8591)
2 from dual;
ROUND(7534.1238) ROUND(99672.8591)
———————————————— —————————————————
7534 99673
1 row selected.
コード・リスト3：ROUND関数を使用して数値を小数点の左側で丸める例
SQL> select ROUND(7534.1238, -1), ROUND(99672.8591, -3)
2 from dual;
ROUND(7534.1238,-1) ROUND(99672.8591,-3)
——————————————————— ————————————————————
7530 100000
1 row selected.
データの切捨てTRUNC関数は、特定の小数位で切り捨てた数値を返します。ROUND関数と同様に、1つの必須パラメータと1つのオプションのパラメータを受け取ります。必須パラメータには、切り捨てる数値を指定します。オプションのパラメータは正または負の整数です。正の整数の場合は、切捨ての基準となる小数位を指定します。リスト4に、TRUNC関数のオプションのパラメータに正の値を渡した場合の動作を示します。この問合せでは単純に戻り値が切り捨てられ、小数第2位よりも小さな桁が削除されます。 コード・リスト4：TRUNC関数を使用して小数点の右側の桁を切り捨てる例
SQL> select TRUNC(7534.1238, 2), TRUNC(99672.8591, 2)
2 from dual;
TRUNC(7534.1238,2) TRUNC(99672.8591,2)
—————————————————— ———————————————————
7534.12 99672.85
1 row selected.
オプションのパラメータを省略した場合、戻り値は小数第0位で切り捨てられます（リスト5を参照）。リスト6のようにオプションのパラメータに負の整数を使用した場合は、表示結果で0へと変更される小数点の左側の桁数を指定することになります。 コード・リスト5：TRUNC関数を使用して数値を整数へと切り捨てる例
SQL> select TRUNC(7534.1238), TRUNC(99672.8591)
2 from dual;
TRUNC(7534.1238) TRUNC(99672.8591)
———————————————— —————————————————
7534 99672
1 row selected.
コード・リスト6：TRUNC関数を使用して数値を小数点の左側の桁で切り捨てる例
SQL> select TRUNC(7534.1238, -1), TRUNC(99672.8591, -3)
2 from dual;
TRUNC(7534.1238,-1) TRUNC(99672.8591,-3)
——————————————————— ————————————————————
7530 99000
1 row selected.
コード・リスト5：TRUNC関数を使用して数値を整数へと切り捨てる例
SQL> select TRUNC(7534.1238), TRUNC(99672.8591)
2 from dual;
TRUNC(7534.1238) TRUNC(99672.8591)
———————————————— —————————————————
7534 99672
1 row selected.
もっとも近い整数ROUNDやTRUNCに似ている数値関数が、FLOORとCEILです。FLOOR関数は、特定の数値以下の最大の整数を計算します。逆に、CEIL関数は、特定の数値以上の最小の整数を計算します。FLOORとCEILには（ROUNDやTRUNCとは異なり）、精度を表すオプションのパラメータはありません。出力がかならず整数になるからです。リスト7のように、これまで紹介した4つの関数のすべてを正数に適用した場合、FLOORはオプションのパラメータを指定しないTRUNCと同様に振る舞い、CEILはオプションのパラメータを指定しないROUNDと同様に振る舞います。一方、これらの関数を負数に適用した場合は、FLOORはROUNDと同様に振る舞い、CEILはTRUNCと同様に振る舞います。 コード・リスト7：ROUND、CEIL、TRUNC、FLOORの各関数の利用と比較
SQL> select ROUND(99672.8591), CEIL(99672.8591), TRUNC(99672.8591), FLOOR(99672.8591)
2 from dual;
ROUND(99672.8591) CEIL(99672.8591) TRUNC(99672.8591) FLOOR(99672.8591)
————————————————— ———————————————— ————————————————— —————————————————
99673 99673 99672 99672
1 row selected.
算術演算と余り+、–、*、/（それぞれ加算、減算、乗算、除算）の4つの算術演算子をSQL文内で使用し、互いに組み合わせたり、任意の数値関数と組み合わせたりすることができます。リスト8は、EMPLOYEE表から各従業員の年間基本給与、賞与（給与値の3%として計算）、週給の値（賞与を含む）をレポートする問合せです。 コード・リスト8：算術演算子とROUND数値関数との組合せ
SQL> select first_name, last_name, salary, salary*.03 "BONUS", ROUND((salary/52)+((salary*.03)/52)) "Weekly Sal w/Bonus" 2 from employee 3 order by salary desc, last_name; FIRST_NAME LAST_NAME SALARY BONUS Weekly Sal w/Bonus —————————— ————————— ——————— —————— —————————————————— Emily Eckhardt 100000 3000 1981 michael peterson 90000 2700 1783 Donald Newton 80000 2400 1585 Frances Newton 75000 2250 1486 Matthew Michaels 70000 2100 1387 mark leblanc 65000 1950 1288 Roger Friedli 60000 1800 1188 Betsy James 60000 1800 1188 8 rows selected.
MODとREMAINDERという2つの数値関数はともに、ある値を別の値で除算した余りを計算するために使用できます。両方の関数とも、被除数と除数を表す2つのパラメータが必要です。MOD関数では計算ロジックでFLOOR関数を使用します。これに対して、REMAINDER関数ではROUND関数を使用します。このため、2つの関数の戻り値は異なる場合があります（リスト9を参照）。 コード・リスト9：MOD関数とREMAINDER関数の結果の違い
SQL> select MOD(49, 18) modulus, REMAINDER(49, 18) remaining
2 from dual;
MODULUS REMAINING
—————————— —————————
13 -5
1 row selected.
不明な値から既知の値への変換すでに説明したとおり、表のNULL値は値がないことを示します。NULL値は相互に比較できず、相互の値を使用した計算もできません。しかし、NVLという補助関数をNULLに適用することで、NULL値をNULL以外の値に置換できます。NVL関数には2つの入力パラメータが必要です。それぞれ、NULLであるかの検証対象の式（列の値、リテラル値、または計算結果）と、結果でNULLの式を置換するために使用する式です。 たとえば、リスト10の問合せは、各従業員の情報と、その従業員のマネージャーのEMPLOYEE IDの値を並べて表示します。MANAGERの値がない従業員、つまりデータベース内でMANAGERの値がNULLである従業員については、マネージャーのEMPLOYEE IDに0と表示されます。このように表示されるのは、この問合せでそれぞれのMANAGERの値にNVL関数を適用し、NULLを0に置換しているためです。 コード・リスト10：MANAGERのNULL値を値0に置換する例
SQL> select employee_id, last_name, first_name, NVL(manager, 0) manager
2 from employee
3 order by manager, last_name, first_name;
EMPLOYEE_ID LAST_NAME FIRST_NAME MANAGER
——————————— ——————————— ————————————— —————————
28 Eckhardt Emily 0
37 Newton Frances 0
6569 peterson michael 0
6567 Friedli Roger 28
6568 James Betsy 28
7895 Michaels Matthew 28
1234 Newton Donald 28
6570 leblanc mark 6569
8 rows selected.
また、リスト10から分かるように、検証対象の式の元の値がNULLでない場合は、その元の値が返されます。 リスト10の例では、戻り値が0では、特定の従業員レコードにマネージャー値が割り当てられていないという事実を自分が思うほど明確に伝わらない可能性もあります。そうではなく、この事実を明示的に表すテキストを返す方が良い場合もあるでしょう。 そのため、リスト11の問合せで、NULL値をより説明的なテキスト・リテラルに置換することを試みます。しかし、この問合せではエラーが返されます。NVL関数では、置換用の値として、比較対象の値のデータ型に変換可能な値を指定する必要があるからです。それでも、テキスト出力が得られる方法はいくつかあります。リスト12にその方法の1つを示します。これは、データ型変換関数のTO_CHARを追加する方法です。データ型変換関数について詳しくは、このシリーズの今後の記事で説明します。 コード・リスト11：戻り値のNULLをテキスト値に置換しようと試みた例
SQL> select employee_id, last_name, first_name, NVL(manager,
'Has no manager') manager
2 from employee
3 order by manager, last_name, first_name;
select employee_id, last_name, first_name, NVL(manager, 'Has no manager') manager
*
ERROR at line 1:
ORA-01722: invalid number
コード・リスト12：TO_CHARを使用して戻り値のNULLをテキスト値に置換する例
SQL> select employee_id, last_name, first_name, NVL(TO_CHAR(manager),
'Has no manager') manager
2 from employee
3 order by manager, last_name, first_name;
EMPLOYEE_ID LAST_NAME FIRST_NAME MANAGER
——————————— ————————— —————————— ———————————————
6567 Friedli Roger 28
6568 James Betsy 28
7895 Michaels Matthew 28
1234 Newton Donald 28
6570 leblanc mark 6569
28 Eckhardt Emily Has no manager
37 Newton Frances Has no manager
6569 peterson michael Has no manager
8 rows selected.
DECODEを使用した詳細情報の追加NVLなどの単純な置換関数では必要なすべての選択肢が提供されない場合もあります。一方、DECODE関数ではif-then-elseロジックを使用して、複数の置換候補を提供できます。 DECODE関数の構文では、まず入力式を記述します。その式が比較対象の値と比較されます。2つの値が一致する場合（DECODEロジックの"if-then"の部分）、DECODE関数は置換用の値を返します。2つの値が一致しない場合は、入力式がさらに、次に利用可能な比較対象の値と比較されます。他の比較対象の値がない場合は、オプションのデフォルト置換値（DECODEロジックの"else"部分）が返されます。リスト13に、DECODE関数の構文を示します。この例では、DECODE関数を別のDECODE関数内にネストする方法についても示しています。 コード・リスト13：DECODE置換関数
SQL> select employee_id, first_name, last_name, DECODE(manager, 28,
'Emily Eckhardt', 6569, 'Michael Peterson', DECODE(employee_id, 28,
'Is Emily', 6569, 'Is Michael', 'Neither Emily nor Michael')) manager
2 from employee
3 order by manager, last_name, first_name;
EMPLOYEE_ID LAST_NAME FIRST_NAME MANAGER
——————————— ——————————— ————————————— ——————————————————
6567 Roger Friedli Emily Eckhardt
6568 Betsy James Emily Eckhardt
7895 Matthew Michaels Emily Eckhardt
1234 Donald Newton Emily Eckhardt
28 Emily Eckhardt Is Emily
6569 michael peterson Is Michael
6570 mark leblanc Michael Peterson
37 Frances Newton Neither Emily nor Michael
8 rows selected.
リスト13の問合せは、リスト12の問合せと同様に、各従業員レコードの実際のMANAGER値をテキスト値に置換しています。ただし、DECODEでは、検証と置換値の繰り返し、つまりif-thenロジックの繰り返しを、必要な回数だけ実行できます。NVLとのもう一つの相違点として、DECODEではNULLであるかという条件以外の条件も検証できます。たとえば、リスト13の問合せでは、特定の値が存在するかどうかを検証しています。 DECODEでNULLであるかの検証を行う場合は、次のような問合せを記述できます。 SELECT DECODE(manager, NULL, 'Has no Manager', manager) FROM employee;
Oracle Databaseは、この例のような状況で、数値を文字列へと暗黙的に変換します（文字列から数値への変換は行われず、実際そのような変換は不可能です）。ただし、Oracle Databaseでデータ型の暗黙的変換が起こるようにすることはお勧めしません。データ型の変換が必要な場合は、かならず明示的にデータ型変換関数をコールしてください。 比較検索のためのCASEDECODE関数はNVL関数よりも強力ですが、等価性（または非等価性）以外の比較に使用することは（簡単には）できません。検索CASE式はDECODE関数の代わりに使用できるばかりか、大なり、小なりの比較でももっと簡単に使用できます。 リスト14は、検索CASE式を使用する問合せです。見てのとおり、検索CASE式はCASEキーワードで開始し、ENDキーワードで終了します。それぞれのWHEN句で条件を検証します。条件が真の場合、このCASE式により、関連付けられたTHEN句で指定されている値が返されます。DECODE関数のELSE条件と同様に、検索CASE式でもオプションでデフォルトのELSE条件を指定できます。CASE式は、リスト15のようにWHERE句でも使用できます。さらに、リスト16のようにネストすることも可能です。 コード・リスト14：小なりの比較における検索CASE式
SQL> select employee_id, first_name, last_name, salary, 2 CASE WHEN manager = 28 THEN 'Emily is the manager.No bonus this year.' 3 WHEN salary < 80000 THEN 'Bonus this year.' 4 ELSE 'No bonus this year.' 5 END "Bonus?" 6 from employee 7 order by last_name, first_name; EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY Bonus? ——————————— —————————— ————————— ———————— ———————————————————————————————————————— 28 Emily Eckhardt 100000 No bonus this year. 6567 Roger Friedli 60000 Emily is the manager.No bonus this year. 6568 Betsy James 60000 Emily is the manager.No bonus this year. 7895 Matthew Michaels 70000 Emily is the manager.No bonus this year. 1234 Donald Newton 80000 Emily is the manager.No bonus this year. 37 Frances Newton 75000 Bonus this year. 6570 mark leblanc 65000 Bonus this year. 6569 michael peterson 90000 No bonus this year. 8 rows selected.
コード・リスト15：WHERE句での検索CASE式
SQL> select employee_id, first_name, last_name, salary
2 from employee
3 where salary + CASE
4 WHEN ROUND((salary/52)+((salary*.03)/52)) > 1500
5 THEN 0
6 WHEN ROUND((salary/52)+((salary*.03)/52)) < 1300
7 THEN 500
8 ELSE 200
9 END > 75000
10 order by last_name, first_name;
EMPLOYEE_ID FIRST_NAME LAST_NAME SALARY
——————————— —————————— ——————————— ———————————
28 Emily Eckhardt 100000
1234 Donald Newton 80000
37 Frances Newton 75000
6569 michael peterson 90000
4 rows selected.
コード・リスト16：ネストされた検索CASE式
SQL> select employee_id, first_name, last_name,
2 CASE manager WHEN 28 THEN 'Emily Eckhardt'
3 WHEN 6569 THEN 'Michael Peterson'
4 ELSE
5 CASE employee_id WHEN 28 THEN 'Is Emily'
6 WHEN 6569 THEN 'Is Michael'
7 ELSE 'Neither Emily nor Michael'
8 END
9 END manager
10 from employee
11 order by manager, last_name, first_name;
EMPLOYEE_ID LAST_NAME FIRST_NAME MANAGER
——————————— ——————————— ————————————— ——————————————————
6567 Roger Friedli Emily Eckhardt
6568 Betsy James Emily Eckhardt
7895 Matthew Michaels Emily Eckhardt
1234 Donald Newton Emily Eckhardt
28 Emily Eckhardt Is Emily
6569 michael peterson Is Michael
6570 mark leblanc Michael Peterson
37 Frances Newton Neither Emily nor Michael
8 rows selected.
結論この記事では、比較的よく使用される数値関数の一部と、それらの関数を使用してデータの表示を操作する方法について説明しました。数値データの値を切り上げる方法や切り下げる方法、および切り捨てる方法について確認しました。数値関数のFLOORおよびCEILと比較したROUNDおよびTRUNCの振る舞いについて理解しました。さらに、数値関数のMODとREMAINDERが、それぞれで使用する計算の種類が異なるために、戻り値が異なる場合があることも確認しました。最後に重要なこととして、NVL、DECODE、検索CASE式などの置換関数の機能と相違点について理解しました。 この記事ではすべてのOracle数値関数や置換関数、補助関数を説明していません。詳しくは、単一行ファンクションとCASE式のドキュメントを確認してください。 この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