Oracleアカウント

テクノロジー：PL/SQL

PL/SQLでの数値の操作
Steven Feuerstein著

PL/SQLの理解と利用に関するシリーズ記事のパート4

このPL/SQL入門シリーズの前回の記事では、PL/SQLベースのアプリケーションで文字列を操作する方法について取り上げました。間違いなく、文字列はPL/SQL開発者が扱うデータ型の中でもっともよく使用されますが、それに加えて数値を使用しないアプリケーションはほぼ存在しません。項目の量など、さまざまな数値を追跡する必要があるからです。

クイズにチャレンジ

PL/SQLの基礎に関する著者の記事では、記事の中で説明した情報の知識をテストするクイズを毎回出題しています。このクイズは次に示す他、PL/SQL ( Challenge plsqlchallenge.com ）にも掲載されます。PL/SQL Challengeは、PL/SQL言語のオンライン・クイズを提供するWebサイトです。このOracle Magazineの記事でクイズを読んで回答した場合、正解について次のパートで確認できます。一方、PL/SQL Challengeでクイズに挑戦した場合は、O’Reilly Media（oreilly.com）から電子書籍を選ぶチャンスに応募できます。

質問1

次のファンクションに渡す数値が正の整数の場合、どのような戻り値になりますか。

Copy


  
REATE OR REPLACE FUNCTION 
plch_ceil_and_floor (number_in IN NUMBER)
   RETURN PLS_INTEGER
IS
BEGIN
   RETURN CEIL (number_in) - FLOOR (number_in);
END;

質問2

MODとREMAINDERについて、正しい説明文を選択してください。

a. 両方とも、除算の余りを返す。

b. 引数が同じ場合は、REMAINDERの戻り値はかならずMODの戻り値以下になる。

c. MODとREMAINDERの戻り値はかならず整数となる。

そのため、次のことがかなり頻繁に必要になります。

数値の変数と定数を宣言する
値を変更する（例：数値を丸める）ための組込みファンクションを使用する

この記事では、PL/SQLプログラムで数値の操作を始めるために必要となるすべての情報を提供します。

PL/SQLの数値

PL/SQLは、各種の目的に合ったさまざまな数値データ型を提供しています。

NUMBER：金額の操作に適した正確な10進数データ型。PL/SQLで唯一、プラットフォーム非依存として実装される数値型です。
PLS_INTEGER：ハードウェアの基盤の整数表現に従った整数データ型。ハードウェアのネイティブのマシン命令を使用した演算が実行されます。この型の値を表に保存することはできません。この型は、PL/SQL固有のデータ型です。
SIMPLE_INTEGER：Oracle Database 11g Release 1より導入されました。SIMPLE_INTEGERデータ型は、ネイティブにコンパイルされたコードの実行時間を大幅に短縮します。このデータ型の説明については、この記事では割愛します。
BINARY_FLOAT、BINARY_DOUBLE：IEEE-754単精度/倍精度バイナリ浮動小数点データ型。これらのBINARYデータ型は高度に専門化されています。計算集中型操作のパフォーマンスを改善する必要がある場合に効果があります。これらのデータ型の説明については、この記事では割愛します。

実際には、この他の数値型（FLOAT、INTEGER、DECIMALなど）を目にするかもしれません。これらは、上記のリストにある4つの主要な数値型のサブタイプです。

それでは、NUMBERとPLS_INTEGERについて詳しく見ていきましょう。

NUMBERデータ型：NUMBERデータ型は、OracleとPL/SQLプログラミングの世界で圧倒的によく使用される数値データ型です。あらゆるサイズの整数、固定小数点数、または浮動小数点数を格納するために使用できます。Oracle Database 10g以前では、NUMBERはOracle Databaseエンジンで直接サポートされる唯一の数値データ型でした。しかし現在では、BINARY_FLOATとBINARY_DOUBLEも使用できます。NUMBERはプラットフォーム非依存として実装されており、NUMBER値に対する演算の結果は、実行されるハードウェア・プラットフォームを問わず同じものになります。

PL/SQLプログラムで数値を操作するには、数値を格納するための変数を宣言します。NUMBERデータ型を使用する変数は、次のように宣言します。

Copy


  
DECLARE     l_salary NUMBER;

PL/SQL Challengeの正解：文字列の操作

前号の文字列の操作で出題されたPL/SQL Challengeの各質問の正解は次のとおりです。

正解1：INSTRの戻り値は11です。

正解2：誤り。リテラル値には一重引用符を含めることができます。そのためには、2つの一重引用符を並べるか、"Q"構文を使用してリテラルの代替的なデリミタを指定します。

正解3：次の2行のテキストが表示されます。

Copy


  
REPLACE=stvn furstin  TRANSLATE=

これらの正解の詳しい説明については、 www.plsqlchallenge.com にアクセスして登録またはログインし、Play a Quizの「Closed/Taken」タブをクリックするか、 http://bit.ly/r1SwvP にアクセスしてください。

この宣言は、浮動小数点数となります。Oracle Databaseで最大40桁の領域が割り当てられ、変数に代入する値をもっとも適切に格納できるように小数点の位置が設定されます。NUMBER変数には10-130（1.0E-130）から10126-1（1.0E126-1）までの値を格納できます。10-130よりも小さな値は0に丸められ、結果が10126以上の値となる計算は未定義となるため、実行時の問題が発生しますが例外は発生しません。

この値の範囲について、リスト1のコード・ブロックで実行例を示します（TO_CHARと書式マスクについてはこの記事の後半で取り上げます）。

コード・リスト1：NUMBERデータ型の値の範囲に関するコード実行例

Copy


  
DECLARE
   tiny_nbr NUMBER := 1e-130;
   test_nbr NUMBER;

   --                              1111111111222222222233333333334
   --                     1234567890123456789012345678901234567890
   big_nbr      NUMBER := 9.999999999999999999999999999999999999999e125;

   --                                 1111111111222222222233333333334444444
   --                        1234567890123456789012345678901234567890123456
   fmt_nbr VARCHAR2(50) := '9.99999999999999999999999999999999999999999EEEE';
BEGIN
   DBMS_OUTPUT.PUT_LINE(
      'tiny_nbr          =' || TO_CHAR(tiny_nbr, '9.9999EEEE'));
   
   /* NUMBERs that are too small round down to zero.*/
   test_nbr := tiny_nbr / 1.0001;
   DBMS_OUTPUT.PUT_LINE(
      'tiny made smaller =' || TO_CHAR(test_nbr, fmt_nbr));

   /* NUMBERs that are too large throw an error:*/
   DBMS_OUTPUT.PUT_LINE(
      'big_nbr           =' || TO_CHAR(big_nbr, fmt_nbr));
   test_nbr := big_nbr * 1.0001;        -- too big
   DBMS_OUTPUT.PUT_LINE(
      'big made bigger   =' || TO_CHAR(test_nbr, fmt_nbr));
END;

このブロックの出力は次のとおりです。

tiny_nbr          = 1.0000E-130
tiny made smaller =   .00000000000000000000000000000000000000000E+00
big_nbr           = 9.99999999999999999999999999999999999999900E+125
big made bigger   =#################################################

NUMBER変数に大きすぎる数値を明示的に代入しようとすると、数値のオーバーフローまたはアンダーフローの例外が発生しますが、最大の有効値を超える計算結果を代入する場合は、例外は発生しません。アプリケーションで本当にそのような大きな値を扱う必要がある場合は、範囲外の値を未然に防ぐための検証用ルーチンを作成するか、BINARY_DOUBLEを使用することを検討する必要があります。バイナリ・データ型の使用には、丸めに関する注意点があります。そのため、バイナリ・データ型に関するオラクルのドキュメントをかならず確認してください。ほとんどの場合、そのような丸めエラーが発生する可能性のため、NUMBERデータ型を選択することになるでしょう。

NUMBER型変数を宣言する際には、多くの場合、精度（precision）と位取り（scale）による制約を付けます。これらは、次のように指定できます。

Copy


  
NUMBER ( precision, scale )

たとえば、最大$999,999の金額を保持し、ドルとセントにより構成される（つまり、小数点右側の小数部はちょうど2桁である）変数を宣言するとします。これは次のように宣言します。

Copy


  
NUMBER (8,2)

この宣言は、固定小数点数となります。精度は、数値における有効桁数の合計です。位取りは小数点の右側（正の位取り）または左側（負の位取り）の桁数を表し、丸められる位置にも影響します。精度と位取りの値はともに、リテラル整数値である必要があります。宣言内で変数や定数を使用することはできません。精度の有効値は1～38、位取りの有効値は-84～127です。

固定小数点数を宣言した場合、通常は位取りの値は精度の値よりも小さくなります。

PLS_INTEGERデータ型：PLS_INTEGERデータ型は、-2,147,483,648から2,147,483,647までの符号付き整数を格納します。値はハードウェア・プラットフォームのネイティブの整数形式で表現されます。

PLS_INTEGER型変数の宣言の例を次に示します。

Copy


  
DECLARE 
		loop_counter PLS_INTEGER;

PLS_INTEGERデータ型は、処理速度向上の目的で作成されました。PLS_INTEGERの値を使用して演算を実行すると、Oracleソフトウェアではネイティブ・マシンの演算が使用されます。そのため、NUMBERデータ型で整数を操作するよりも、PLS_INTEGER値を操作する方が、処理が速くなります。

プログラムが計算集中型で、整数計算が必要な場合に（さらに、値がこの型の整数の有効範囲を外れることが絶対にない場合に）、PLS_INTEGERを使用することを検討してください。ただし、PLS_INTEGERの使用時にNUMBER型との変換が頻繁に行われる場合は、まずはNUMBERを使用する方がよいでしょう。PLS_INTEGERは、NUMBER型と相互に変換する必要がない場合に、整数演算（およびループ・カウンタ）に使用すると、最大の効率を発揮できます。

数値の組込みファンクション

Oracle Databaseには、数値の操作および数値と文字列の変換のための、非常に多くの組込みファンクションがあります。頻繁に必要となるファンクションは次のとおりです。

ROUND：ROUNDファンクションは、ある数値を受け取り、指定した小数点以下の桁数に丸めた数値を返します。その桁数を指定しない場合は、もっとも近い整数に丸めた数値を返します。

リスト2に、いくつかのROUNDのコール例を示します。

コード・リスト2：ROUNDのコール

Copy


  
BEGIN
   DBMS_OUTPUT.put_line (ROUND (10.25));
   DBMS_OUTPUT.put_line (ROUND (10.25, 1));
   DBMS_OUTPUT.put_line (ROUND (10.23, 1));
   DBMS_OUTPUT.put_line (ROUND (10.25, 2));
   DBMS_OUTPUT.put_line (ROUND (10.25, -2));
   DBMS_OUTPUT.put_line (ROUND (125, -2));
END;


このブロックの出力は次のとおりです。

10
10.3
10.2
10.25
0
100

第2引数に負の値を指定した例では、10の位（小数点の左側）に丸められています。

TRUNC：TRUNCは、小数点の右側または左側の桁数を指定できるという点でROUNDに似ています。ROUNDとの違いは、TRUNCでは単純に桁を削除する、つまり切り捨てる点にあります。またROUNDと同様に、負の数値を指定できます。その場合は、小数点の左側の整数部で桁が切り捨てられ、0（ゼロ）が適用されます。

リスト3に、いくつかのTRUNCのコール例を示します。

コード・リスト3：TRUNCのコール

Copy


  
BEGIN
   DBMS_OUTPUT.put_line (TRUNC (10.23, 1));
   DBMS_OUTPUT.put_line (TRUNC (10.25, 1));
   DBMS_OUTPUT.put_line (TRUNC (10.27, 1));
   DBMS_OUTPUT.put_line (TRUNC (123.456, -1));
   DBMS_OUTPUT.put_line (TRUNC (123.456, -2));
END;

このブロックの出力は次のとおりです。

10.2
10.2
10.2
120
100

FLOOR、CEIL：FLOORファンクションは、指定した数値以下の最大の整数を返します。

CEILファンクションは、指定した数値以上の最小の整数を返します。

次のブロックと出力により、これら2つのファンクションの実行例を示します。

Copy


  
BEGIN
   DBMS_OUTPUT.put_line (FLOOR (1.5));
   DBMS_OUTPUT.put_line (CEIL (1.5));
END;
/
1
2

MOD、REMAINDER：MODとREMAINDERはともに、除算の余りを返しますが、その余りの計算方法がそれぞれのファンクションで異なります。

Oracle Databaseで使用されるMODの計算式は次のとおりです。

Copy


  
MOD (m, n) = m - n * FLOOR (m/n)

この計算式は、mとnの符号（正または負）が同じ場合に使用されます。mとnの符号が異なる場合は、次の計算式が使用されます。

Copy


  
MOD (m,n) = ( m - n * CEIL(m/n) )

一方、REMAINDERに使用される計算式は次のとおりです。

Copy


  
n2 - (n1*N)

ここで、n1は0（ゼロ）ではなく、Nはn2/n1にもっとも近い整数です。n2/n1がx.5に等しい場合は、Nはもっとも近い偶数となります。

リスト4に、これら2つのファンクションの実行結果と違いについて示します。

コード・リスト4：MODとREMAINDERのコール

Copy


  
BEGIN
   DBMS_OUTPUT.put_line (MOD (15, 4));
   DBMS_OUTPUT.put_line (REMAINDER (15, 4));
   DBMS_OUTPUT.put_line (MOD (15, 6));
   DBMS_OUTPUT.put_line (REMAINDER (15, 6));
END;
/

このブロックの出力は次のとおりです。

Copy

TO_CHAR：TO_CHARは、数値を文字列に変換するために使用します。もっとも単純な形式では、TO_CHARに1つの引数（数値）を渡すと、その数値の文字列表現が、ちょうどすべての有効桁を含む長さで返されます。

リスト5は、TO_CHARの例を示すブロックです。この例から分かるように、先頭と末尾の0（ゼロ）は数値の文字列表現に含まれません。

コード・リスト5：TO_CHARのコール

Copy


  
BEGIN
   DBMS_OUTPUT.put_line (TO_CHAR (100.55));
   DBMS_OUTPUT.put_line (TO_CHAR (000100.5500));
   DBMS_OUTPUT.put_line (TO_CHAR (10000.00));
END;

このブロックの出力は次のとおりです。

100.55 100.55 10000

次のステップ

詳細情報

PL/SQLのデータ型

組込みファンクション

ダウンロード Oracle Database 11g

テスト PL/SQLの知識

その他の記事 PL/SQLの基礎、パート1、2、3

数値を文字列に変換する場合に、その数値を特定の書式に合わせる必要があるのはよくあることです。たとえば、数値を通貨として表示し、セントがなくても".00"を含める必要があるとします。そのような場合に、TO_CHARコールの第2引数（書式マスク）を追加する必要があります。

数値の変換先となる文字列の書式を指定するには、TO_CHARの第2引数として、特別な書式要素の組合せを含む文字列を指定します。たとえば、大きな数値を3桁ごとのデリミタ付きで表示する場合、つまり、"10000"ではなく"10,000"と表示する場合は、次の書式を使用します。

Copy


  
BEGIN
   DBMS_OUTPUT.put_line (
      'Amount='||
      TO_CHAR (
        10000
      , '9G999G999'));
END;

G要素は、文字列内でのグループ・セパレータの位置を示します（セパレータに使用する文字は、各国語設定データベース・パラメータのNLS_NUMERIC_CHARACTERSによって定義します）。9要素は、Oracle Databaseに対して、その位置に有効桁の数字または空白を配置するように指示します。その結果、このブロックの出力は次のようになります。

Copy


  
Amount=    10,000

空白の代わりに0（ゼロ）を表示する場合は、9の代わりに0を使用できます。

Copy


  
BEGIN
   DBMS_OUTPUT.put_line (
      'Amount=' ||
      TO_CHAR (
         10000
       , '0G000G999'));
END;
Amount= 0,010,000

変換後の数値に先頭の0（ゼロ）、余白、または空白を表示させない場合は、FM要素を使用します。

Copy


  
BEGIN
   DBMS_OUTPUT.put_line (
      'Amount=' ||
      TO_CHAR (
         10000
      , 'FM9G999G999'));
END;
Amount=10,000

数値が実際はドル（あるいはユーロ）とセントで構成される通貨単位であり、通貨記号とセント部分も表示したい場合は、次の書式を使用できます。

Copy


  
BEGIN
   DBMS_OUTPUT.put_line (
     'Salary=' ||
     TO_CHAR (
        14500.77
     , 'FML999G999D99'));
END;
Salary=$14,500.77

L要素は、戻り値におけるローカルの通貨記号（$や€など）の位置を示します（ローカルの通貨記号は、NLS_CURRENCYパラメータによって指定します）。D要素は、小数点の位置を示します（小数点として使用する文字は、データベース・パラメータのNLS_NUMERIC_CHARACTERSによって指定します）。

数値の書式で使用できる数多くの要素をすべて説明することはこの記事の対象外です（たとえば、通貨単位を示すだけでも、少なくとも4つの要素があります）。詳しい説明については、Oracle Database SQL Language Reference 11g Release 2 (11.2)を参照してください。

PL/SQLでの日付、タイムスタンプ、時間隔

ほとんどのアプリケーションで、日付や時刻を保管し操作する必要があります。文字列や数値とは異なり、日付は非常に複雑です。高度な書式付きのデータであり、さらに有効値や有効計算を判定するための多くのルールが存在します（うるう日とうるう年、サマータイムへの変更、法定休日や会社規定の休日、日付範囲など）。

幸いにも、Oracle DatabaseとPL/SQLは、内部的な標準形式で日付と時刻の両方を格納する正確な日付/時刻データ型セットを提供しています。また、日付と時刻を操作するための非常に多くの組込みファンクションもあります。

PL/SQLの基礎に関する次回の記事では、日付、タイムスタンプ、時間隔について取り上げます。

Steven Feuerstein（steven.feuerstein@quest.com）は、Quest SoftwareのPL/SQLエヴァンジェリストです。これまで、Oracle PL/SQLに関する著書（O’Reilly Media）を10冊発行しており、Oracle ACE Directorでもあります。詳細は、stevenfeuerstein.comをご覧ください。

ご意見ご感想をお寄せください。