1. データベース開発ガイド
  2. アプリケーション開発者用のSQL
  3. データベース・アプリケーションにおけるSQLデータ型の使用

9 データベース・アプリケーションにおけるSQLデータ型の使用

この章では、データベース・アプリケーション用として作成するデータベース列に対して正しいSQLデータ型を選択する方法について説明します。

トピック:

注意:

Oracleプリコンパイラでは、埋込みSQLプログラム内のSQLおよびPL/SQLデータ型以外のデータ型が認識されます。これらの外部データ型は、ホスト変数に関連付けられています。

関連項目:

正確かつ最も固有のデータ型の使用

データベース・アプリケーション用として作成するデータベース列ごとに正確かつ最も固有のデータ型を使用することにより、データの整合性が増し、記憶域要件が減り、パフォーマンスが向上します。

トピック:

正しいデータ型によるデータ整合性の向上

データ型が正しければ、このデータ型が制約として機能するため、データの整合性が増します。たとえば、日付列に対して日付時間データ型を使用すると、この列に格納できるのは日付のみになります。ただし、この列に対して文字または数値データ型を使用すると、結果として、日付を表さない文字または数値がいずれかのユーザーによって格納されます。この問題を阻止するコードを作成することもできますが、正しいデータ型を使用する方が効率的です。このため、文字は文字データ型、数値は数値データ型、日付および時間は日付時間データ型に格納します。

関連項目:

データ整合性および制約の詳細は、「データベース・アプリケーションにおけるデータ整合性のメンテナンス」を参照してください

最も固有のデータ型による記憶域要件の削減

正しいデータ型を使用する以外にも、最も固有の長さまたは精度を使用します。次はその例です。

  • 最大n文字の文字列を目的としたVARCHAR2列を作成する場合、VARCHAR2(n)を指定します。

  • 整数を目的とした列を作成する場合、NUMBERではなくデータ型NUMBER(38)を使用します。

制約として機能することによってデータの整合性を増すのみでなく、長さおよび精度は記憶域要件にも影響します。

すべての列でそのデータ型に対して最大の長さまたは精度を指定すると、数MBのRAMが不必要に割り当てられます。たとえば、問合せによってVARCHAR2(4000)が10列が選択され、一括フェッチ操作によって100行が戻されるとします。割当てが必要なRAMは、10 x 4,000 x 100で、約4MBになります。対照的に、列の長さが80である場合、割当てが必要なRAMは、10 x 80 x 100で、約78KBになります。この違いは単一の問合せにとって重要であり、アプリケーションは多くの問合せを同時に処理します。このため、アプリケーションは接続ごとに4MBまたは78KBのRAMを割り当てる必要があります。

したがって、プロパティの増加が後で必要になる可能性があるという理由のみで、列のデータ型に対して最大の長さまたは精度を指定しないようにしてください。列を作成後に変更する必要がある場合は、ALTER TABLE文を使用します。たとえば、列を長くする場合は、次を使用します。 

ALTER TABLE table_name MODIFY column_name VARCHAR2(larger_number)

注意:

MAX_STRING_SIZE初期化パラメータがEXTENDEDである場合、VARCHAR2、NVARCHAR2およびRAWデータ型の最大の長さは32,767バイトです。

関連項目:

正しいデータ型によるパフォーマンスの向上

データ型が正しいと、パフォーマンスが向上します。データ型が正しくない場合、実行計画が正しくなくなる可能性があるためです。

例9-1では、同じ概念の操作(データ型が異なる3つの列に対して、日付が2000年12月31日と2001年1月1日の間にある行を選択)を実行し、問合せごとに実行計画を表示しています。これら3つの実行計画で、行(カーディナリティ)、コストおよび操作を比較します。

例9-1 3つのデータ型のパフォーマンス比較

3つの列に同じ日付を格納する表を作成します(データ型VARCHAR2のstr_date、データ型DATEのdate_date、およびデータ型NUMBERのnumber_date)。 

CREATE TABLE t (str_date, date_date, number_date, data)
AS
SELECT TO_CHAR(dt+rownum,'yyyymmdd')              str_date,    -- VARCHAR2
       dt+rownum                                  date_date,   -- DATE
       TO_NUMBER(TO_CHAR(dt+rownum,'yyyymmdd'))   number_date, -- NUMBER
       RPAD('*',45,'*')                           data
FROM (SELECT TO_DATE('01-jan-1995', 'dd-mm-yyyy') dt
      FROM all_objects)
ORDER BY DBMS_RANDOM.VALUE
/

列ごとに索引を作成します。

CREATE INDEX t_str_date_idx ON t(str_date);
CREATE INDEX t_date_date_idx ON t(date_date);
CREATE INDEX t_number_date_idx ON t(number_date);

表の統計を収集します。

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS (
    'HR',
    'T',
    method_opt => 'for all indexed columns size 254',
    cascade => TRUE
  );
END;
/

後続のSQL文(SQL*Plusコマンド)の実行計画を表示します。

SET AUTOTRACE ON EXPLAIN

str_dateの日付が2000年12月31日と2001年1月1日の間にある行を選択します。 

SELECT * FROM t WHERE str_date BETWEEN '20001231' AND '20010101'
ORDER BY str_date;

結果および実行計画は、次のとおりです。

STR_DATE DATE_DATE NUMBER_DATE DATA
-------- --------- ----------- ---------------------------------------------
20001231 31-DEC-00    20001231 *********************************************
20010101 01-JAN-01    20010101 *********************************************
 
2 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 948745535
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   236 | 11092 |   216   (8)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |   236 | 11092 |   216   (8)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T    |   236 | 11092 |   215   (8)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("STR_DATE"<='20010101' AND "STR_DATE">='20001231')

number_dateの日付が2000年12月31日と2001年1月1日の間にある行を選択します。 

SELECT * FROM t WHERE number_date BETWEEN 20001231 AND 20010101;
ORDER BY str_date;

結果および実行計画は、次のとおりです。

STR_DATE DATE_DATE NUMBER_DATE DATA
-------- --------- ----------- ---------------------------------------------
20001231 31-DEC-00    20001231 *********************************************
20010101 01-JAN-01    20010101 *********************************************
 
2 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 948745535
 
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   234 | 10998 |   219  (10)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |   234 | 10998 |   219  (10)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| T    |   234 | 10998 |   218   (9)| 00:00:01 |
---------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("NUMBER_DATE"<=20010101 AND "NUMBER_DATE">=20001231)

date_dateの日付が2000年12月31日と2001年1月1日の間にある行を選択します。 

SELECT * FROM t WHERE date_date
  BETWEEN TO_DATE('20001231','yyyymmdd')
  AND     TO_DATE('20010101','yyyymmdd');
  ORDER BY str_date;

結果と実行計画は次のとおりです(ページにあわせてフォーマットを変更しています)。

STR_DATE DATE_DATE NUMBER_DATE DATA
-------- --------- ----------- ---------------------------------------------
20001231 31-DEC-00    20001231 *********************************************
20010101 01-JAN-01    20010101 *********************************************
 
2 rows selected.
 
 
Execution Plan
----------------------------------------------------------
Plan hash value: 2411593187
 
--------------------------------------------------------------------------------
 
| Id  | Operation                            | Name            | Rows  | Bytes |
|   1 |  SORT ORDER BY                       |                 |     1 |    47 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T               |     1 |    47 |
|*  3 |    INDEX RANGE SCAN                  | T_DATE_DATE_IDX |     1 |       |
|   0 | SELECT STATEMENT                     |                 |     1 |    47 |
 
------------------------
 Cost (%CPU)| Time     |
 
     4  (25)| 00:00:01 |
     4  (25)| 00:00:01 |
     3   (0)| 00:00:01 |
     2   (0)| 00:00:01 |
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("DATE_DATE">=TO_DATE(' 2000-12-31 00:00:00',
   'syyyy-mm-dd hh24:mi:ss') AND
 
  "DATE_DATE"<=TO_DATE(' 2001-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

最後の問合せのパフォーマンスが向上しました。これは、DATEデータ型の場合、2000年12月31日と2001年1月1日の間に1日のみが存在したことを確認できたためです。このため、索引範囲スキャンが実行されましたが、これは全表スキャンより高速です。

関連項目:

文字データの表現

表9-1 に、文字データを格納するSQLデータ型の概要を示します。

表9-1 SQL文字データ型

データ型 格納される値

CHAR

固定長文字リテラル

VARCHAR2

可変長文字リテラル

NCHAR

固定長Unicode文字リテラル

NVARCHAR2

可変長Unicode文字リテラル

CLOB

最大(4 GB - 1) * (DBMS_LOB.GETCHUNKSIZEから取得した値)のシングルバイトおよびマルチバイト文字列

NCLOB

最大(4 GB - 1) *(DBMS_LOB.GETCHUNKSIZEから取得した値)のシングルバイトおよびマルチバイトUnicode文字列

LONG

最大2 GB - 1の可変長文字データ。下位互換性のためだけに提供されています。

注意:

VARCHARデータ型は、使用しないでください。かわりにVARCHAR2データ型を使用してください。現在、VARCHARデータ型はVARCHAR2データ型と同じ意味で使用されていますが、VARCHARデータ型は、異なる比較セマンティクスで比較される別の可変長文字列のデータ型に変更される予定です。

CHARとVARCHAR2を選択する場合、次を考慮してください。

  • 使用領域

    Oracle Databaseでは、CHAR列に格納されている値には空白を埋めますが、VARCHAR2列に格納された値にはこれを行いません。そのため、VARCHAR2列の方がCHAR列よりもより効率的に領域を使用します。

  • パフォーマンス

    空白を埋める違いのため、VARCHAR2列のある大規模な表の全表スキャンでは、CHAR列に同じデータが格納されている表の全表スキャンより、読み込むデータ・ブロックが少なくなる可能性があります。アプリケーションが文字データを含む大規模な表に対して全表スキャンを行う場合、CHAR列よりVARCHAR2列にデータを格納することによってパフォーマンスを改善できる可能性が高くなります。

  • 比較セマンティクス

    比較セマンティクスでANSI互換性が必要な場合は、CHARデータ型を使用します。文字列の比較において後続空白が重要である場合は、VARCHAR2データ型を使用します。

クライアント/サーバー・アプリケーションで、クライアント側のキャラクタ・セットがサーバー側と異なる場合、Oracle DatabaseはCHAR、VARCHAR2およびLONGデータをデータベース・キャラクタ・セット(NLS_LANGUAGEパラメータで決定)からユーザー・セッション用に定義したキャラクタ・セットに変換します。

関連項目:

数値データの表現

数値データを格納するSQLデータ型は、NUMBER、BINARY_FLOATおよびBINARY_DOUBLEです。

NUMBERデータ型は、実数を固定小数点形式または浮動小数点形式で格納します。NUMBERは、最大で10進数38桁の精度を提供します。NUMBER列には、1 x 10-130から9.99 x10125の正と負の数値および0(ゼロ)を格納できます。すべてのOracle DatabaseプラットフォームはNUMBER値をサポートします。

BINARY_FLOATデータ型およびBINARY_DOUBLEデータ型は、それぞれ単精度(32ビット)IEEE 754形式、および倍精度(64ビット)IEEE 754形式で、浮動小数点データを格納します。高精度の値は、BINARY_FLOATおよびBINARY_DOUBLEとして格納するほうが、NUMBERとして格納するよりも必要とする領域は少なくなります。浮動小数点データの算術演算は通常、BINARY_FLOATおよびBINARY_DOUBLE値のほうが、NUMBER値よりも高速です。

Oracle Databaseでサポートされているクライアント・インタフェースでは、BINARY_FLOATデータ型およびBINARY_DOUBLE値の算術演算は、ハードウェアのベンダーが提供する固有の命令セットによって実行されます。ネイティブ浮動小数点データ型という用語は、データ型BINARY_FLOATおよびBINARY_DOUBLE、およびサポートされているクライアント・インタフェースで実装されているこれらの型すべてを指します。

ネイティブ浮動小数点データ型は、電気電子技術者協会(IEEE)の2進浮動小数点演算についての規格であるIEEE規格754-1985(IEEE754)に実質的に準拠しています。

注意:

NUMBERのサブタイプとしてFLOATのかわりにBINARY_FLOATおよびBINARY_DOUBLEを使用することをお薦めします。

トピック:

関連項目:

データ・タイプの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

浮動小数点数の構成要素

浮動小数点数の値は、次の式で表されます。

(-1)sign.significand.baseexponent

たとえば、4.31という値は次のように表されます。

(-1)0.431.10-2

この表現の構成要素は、次のとおりです。

コンポーネント名 構成要素の値

符号

0

仮数

431

基数

10

指数

-2

浮動小数点数形式

浮動小数点数形式は、浮動小数点数の構成要素の表記方法を指定し、それにより、形式が表す値の範囲および精度が決定されます。範囲とは最小値から最大値までの間隔であり、精度とは仮数の桁数です。範囲と精度のどちらも有限です。指定された形式よりも高い精度の浮動小数点数は丸められます。

数値を丸める方法は、10進または2進のいずれかが可能な、形式の基数によって決まります。10進の形式に格納された数値は、最も近い10進の桁に丸められます(例: 1000、10または0.01)。2進の形式に格納された数値は、最も近い2進の桁に丸められます(例: 1024、512または1/64)。

NUMBERの値は、10進の形式で格納されます。10進の桁への丸めを必要とする計算の場合は、NUMBERデータ型を使用してください。

ネイティブ浮動小数点の値は、2進の形式で格納されます。

表9-2に、IEEE 754の単精度と倍精度形式およびOracle Database NUMBERの範囲と精度を示します。範囲の制限は、正数として表しますが、この制限は負数の絶対値にも適用されます(ここで使用される「数値e指数」という表記法は、数値*10指数という意味です)。

表9-2 浮動小数点データ型の範囲および精度

範囲および精度 単精度32ビット(1) 倍精度64ビット1 Oracle Database NUMBERデータ型

正の最大正規数

3.40282347e+38

1.7976931348623157e+308

< 1.0e126

正の最小正規数

1.17549435e-38

2.2250738585072014e-308

1.0e-130

正の最大非正規数

1.17549421e-38

2.2250738585072009e-308

該当なし

正の最小非正規数

1.40129846e-45

4.9406564584124654e-324

該当なし

精度(10進数)

6 - 9

15 - 17

38 - 40

脚注1これらの数値は『IEEE Numerical Computation Guide』から引用されています。

浮動小数点2進形式

2進形式を使用する浮動小数点数の値は、次に示す算式によって決まります。

(-1)sign 2E (bit0 bit1 bit2 ... bitp-1)

表9-3に、算式の構成要素を示します。

表9-3 浮動小数点2進形式の構成要素

コンポーネント 構成要素の値

sign

0または1

E (指数)

単精度(32ビット)データ型の場合、-126以上127以下の整数です。

倍精度(64ビット)データ型の場合、-1022以上1023以下の整数です。

biti

0または1。(ビットの連続は基数2の数値を表します)。

p (精度)

単精度データ型の場合、24。

倍精度データ型の場合、53。

仮数の先頭のビットb0は、非正規の数値(後述)の場合を除いて、(1)に設定する必要があります。したがって、先頭のビットは格納されないため、2進形式では精度としてnビットを指定しますが、格納されるのはn-1ビットになります。IEEE 754標準は、単精度および倍精度データ型のインメモリー形式を定義します。表9-4に概要を示します。

表9-4 2進形式の記憶域パラメータの概要

データ型 符号ビット 指数ビット 仮数ビット ビット数合計

単精度

1

8

24 (23が格納されます)

32

倍精度

1

11

53 (52が格納されます)

64

注意:

IEEE 754標準で定義されている拡張単精度および拡張倍精度形式は、Oracle Databaseではサポートされていません。

仮数の先頭ビットが設定されている場合、正規化されていると呼ばれます。IEEE 754標準では非正規化数(subnormal numbers、denormal numbers)という、小さすぎて正規化された仮数で表せない数値を定義します。非正規化数の仮数を正規化すると、指数が大きくなりすぎます。非正規数では、条件がx-y==0.0(浮動小数点減算を使用)、結果がx==y、というプロパティが保持されます。

ネイティブ浮動小数点データ型の特殊な数値の表現

表9-5 に、IEEE 754標準がサポートする特殊な数値を示します。

表9-5 ネイティブ浮動小数点形式の特殊な数値

値 意味

+INF

正の無限大

-INF

負の無限大

+0

正のゼロ

-0

負のゼロ

NaN

非数

表9-5に示す各値は、NaNを除き、特殊なビット・パターンで表現されます。NaNは未定義の演算の結果であり、多数のビット・パターンで表されます。ビット・パターンには、符号ビット・セットを含むものも含まないものもありますが、符号ビットに意味はありません。

IEEE 754標準では、quiet NaN(ほとんどの演算への伝播時に追加の例外が発生しない)とsignalling NaN(発生する)が区別されます。IEEE 754標準では、例外が有効および無効の場合の動作が指定されています。

Oracle Databaseでは、例外の有効化は許可されません。Oracle Databaseの動作は、IEEE 754標準で指定されている、例外が無効化されている場合の動作になります。特に、Oracle Databaseではquietとsignaling NaNは区別されません。Oracle Call Interface(OCI)を使用して、Oracle DatabaseからNaN値を取得できますが、取得されるNaN値がsignallingかquietかは、クライアント・プラットフォームによって決まり、Oracle Databaseの制御外です。

IEEE 754標準は次の特殊な値のクラスを定義します。

  • ゼロ

  • 非正規

  • 標準

  • 無限大

  • NaN

NaNを除き、このリストの各クラスの値は、小さい方から順に示されています(符号は考慮していません)。NaNは、他の特殊な値のクラスともNaN自体とも順序付けることはできません。

Oracle Databaseの場合

  • NaNはすべてquietです。

  • すべての非NaN value < NaN

  • 任意のNaN == 他のNaN

  • NaNはすべて同じビット・パターンに変換されます。

  • -0は+0に変換されます。

  • IEEE 754例外は発生しません。

関連項目:

浮動小数点条件の詳細は、『Oracle Database SQL言語リファレンス』を参照してください(浮動小数点条件では、式が無限か、または演算の未定義の結果(非数値(NaN))かを判断します)。

ネイティブ浮動小数点値の比較

数式を比較する場合、Oracle Databaseは数値の優先順位を使用して、その条件がNUMBER、BINARY_FLOATまたはBINARY_DOUBLEのうちどの値を比較するかを判断します。

比較では、ゼロの符号は無視されます(-0は、+0と等しいとみなされます)。

関連項目:

ネイティブ浮動小数点データ型を使用した算術演算

IEEE 754では、浮動小数点算術に厳密な再現性は求められません。このため、演算の結果を、演算のオペランドが使用するよりも大きい範囲を使用する格納先に格納できます。

倍精度乗算の結果は、拡張倍精度格納先での計算に使用できます。しかし、計算結果は、格納先が単精度または倍精度であるかのように丸められます。結果の範囲(指数のビット数)としては、より高精度(拡張倍精度)の格納先でサポートされている範囲が使用できます。ただし、二重丸め誤差(結果の最下位ビットが不正)が発生する可能性があります。

この問題は、IA-32およびIA-64命令セット・アーキテクチャを実装するハードウェアで、倍精度乗算および除算を実行した場合のみ発生します。したがって、この例を除き、これらのデータ型に対する算術をプラットフォームを渡って再現できます。計算の結果がNaNの場合、すべてのプラットフォームでIS NANがTRUEの値が作成されます。ただし、すべてのプラットフォームで同じビット・パターンを使用する必要はありません。

関連項目:

算術演算の詳細は、『Oracle Database SQL言語リファレンス』を参照してください

ネイティブ浮動小数点データ型の変換関数

Oracle Databaseでは、浮動小数点と他のデータ型(10進数精度を使用する文字列形式など)の間で変換を行う関数が定義されています(変換中に精度が失われることがあります)。例:

Oracle Databaseでは、変換時に例外が発生する可能性があります。IEEE 754標準では、次の例外が定義されています。

  • 無効

  • 不正確

  • ゼロによる除算

  • アンダーフロー

  • オーバーフロー

ただし、Oracle Databaseでは、ネイティブ浮動小数点データ型に対して、これらの例外は発生しません。例外が発生する操作では、通常、表9-6に示す値が生成されます。

表9-6 例外により生成される値

例外 値

アンダーフロー

0

オーバーフロー

-INF、+INF

無効な演算

NaN

ゼロによる除算

-INF、+INF、NaN

不正確

任意の値: 端数処理が実行されました。

ネイティブ浮動小数点データ型のクライアント・インタフェース

Oracle Databaseは、ネイティブ浮動小数点データ型を次のクライアント・インタフェースでサポートします。

  • SQLおよびPL/SQL

    BINARY_FLOATおよびBINARY_DOUBLEのサポートには、SQL文CREATE TYPEを使用して作成する抽象データ型(ADT)の属性としての使用が含まれます(『Oracle Database PL/SQL言語リファレンス』を参照)。

  • Oracle Call Interface(OCI)

    OCIを使用したBINARY_FLOATおよびBINARY_DOUBLEの使用方法の詳細は、『Oracle Call Interfaceプログラマーズ・ガイド』を参照してください。

  • Oracle C++ Call Interface(OCCI)

    OCCIを使用したBINARY_FLOATの使用方法の詳細は、『Oracle C++ Call Interfaceプログラマーズ・ガイド』を参照してください。

    OCCIを使用したBINARY_DOUBLEの使用方法の詳細は、『Oracle C++ Call Interfaceプログラマーズ・ガイド』を参照してください。

  • Pro*C/C++プリコンパイラ

    BINARY_FLOATおよびBINARY_DOUBLEを使用するには、アプリケーションのコンパイル時に、Pro*C/C++プリコンパイラ・コマンドライン・オプションNATIVE_TYPESをYESに設定します。NATIVE_TYPESオプションの詳細は、『Pro*C/C++プログラマーズ・ガイド』を参照してください。

  • Oracle JDBC

    Oracle JDBCを使用したBINARY_FLOATおよびBINARY_DOUBLEの使用方法の詳細は、『Oracle Database JDBC開発者ガイド』を参照してください。

日時データの表現

Oracle Databaseは、世紀、年、月、日、時、分、秒、および必要に応じて小数秒とタイムゾーンを表すバイナリ形式でDATEおよびTIMESTAMP (日時)データを格納します。

表9-7に、SQL日時データ型の概要を示します。

表9-7 SQL日時データ型

日付タイプ 使用方法

DATE

日時の値を表に格納します。たとえば、ジョブの日付に使用します。

TIMESTAMP

小数秒を含む正確な日付の値を格納します。たとえば、発生順序を決定するために比較する必要があるイベントの時間に使用します。

TIME WITH TIME ZONE

複数の地域にまたがって収集または調整する必要がある日時の値を格納します。

TIMESTAMP WITH LOCAL TIME ZONE

タイムゾーンが重要でない場合に日時の値を格納します。たとえば、テレビ会議のスケジュールを立てるアプリケーションでは、参加者は自分のタイムゾーンで開始時間および終了時間を確認します。

クライアント・システムのタイムゾーンを使用して日時を表示する必要がある2層アプリケーションに適しています。通常、3層アプリケーションでこのデータ型を使用するのは不適切です。これは、Webブラウザに表示されるデータがブラウザのタイムゾーンではなくWebサーバーのタイムゾーンによって書式化されるためです。Webサーバーはデータベース・クライアントであるため、ローカル時間が使用されます。

INTERVAL YEAR TO MONTH

年および月のみが重要な場合に、2つの日時の値の違いを格納します。たとえば、アラームを18か月後の日付に設定したり、特定の日付から6か月経過したことを確認できます。

INTERVAL DAY TO SECOND

2つの日時の値の正確な違いを格納します。たとえば、アラームを36時間後に設定したり、レースの開始から終了までの時間を記録できます。長い時間を高精度で表すには、日の部分に大きな値を使用します。

関連項目:

トピック:

現在の日付および時刻の表示

現在の日付および時刻を表示する最も簡単な方法は次のとおりです。

SELECT TO_CHAR(SYSDATE, format_model) FROM DUAL

デフォルトの書式モデルは、初期化パラメータNLS_DATE_FORMATに依存します。

Oracle Databaseのデフォルトの標準日付書式はDD‐MON-RRです。RR日時書式要素では、年の下2桁のみを指定することにより、20世紀の日付を21世紀に格納できます。たとえば、DD-MON-YY日付書式の13-NOV-54は、1950年から2049年に発行された問合せでは1954年を指しますが、2050から2149年に発行される問合せでは2054年を指します。

注意:

プログラムの正確さを維持し、SQLインジェクションおよび動的SQLに関する問題を回避するために、すべての日時値に対して書式モデルを指定することをお薦めします。

書式モデルを使用して現在の日付および時刻を表示する最も簡単な方法は次のとおりです。

SELECT TO_CHAR(SYSDATE, format_model) FROM DUAL

例9-2では、TO_CHARを書式モデルを指定して使用して、SYSDATEをBCまたはAD修飾子付きの書式で表示します。(デフォルトではSYSDATEは修飾子なしで表示されます)。

例9-2 現在の日付および時刻の表示

SELECT TO_CHAR(SYSDATE, 'DD-MON-YYYY BC') NOW FROM DUAL;

結果:

NOW
-----------------------
18-MAR-2009 AD
 
1 row selected.

ヒント:

SYSDATEを使用するコードをテストするときに、SYSDATEを定数に設定できるのは便利です。これは、初期化パラメータFIXED_DATEを使用して実行します。

関連項目:

日付の挿入および表示

日付を表示および挿入する場合、日時書式モデルとともにTO_CHARおよびTO_DATEファンクションをそれぞれ使用することをお薦めします。

例9-3では、DATE列を含む表を作成し、書式モデルを指定して日付を挿入します。その後、書式モデルを指定する場合と指定しない場合で日付を表示します。

例9-3 日付の挿入および表示

表を作成します。

DROP TABLE dates;
CREATE TABLE dates (d DATE);

書式モデルを指定して、指定した日付を表に挿入します。

INSERT INTO dates VALUES (TO_DATE('OCT 27, 1998', 'MON DD, YYYY'));

書式モデルを指定しないで日付を表示します。

SELECT d FROM dates;

結果:

D
---------
27-OCT-98
 
1 row selected.

書式モデルを指定して日付を表示します。

SELECT TO_CHAR(d, 'YYYY-MON-DD') D FROM dates;

結果:

D
--------------------
1998-OCT-27
 
1 row selected.

注意:

YY日時書式要素を使用して現在の世紀の年を表示する場合は注意します。たとえば、21世紀では、書式DD-MON-YY、31-DEC-92は2092年の12月31日です( 推察のとおり、1992年12月31日ではありません)。年の末尾2桁のみを指定して20世紀の日付を21世紀に格納するには、RR日時書式要素を使用します(デフォルト)。

関連項目:

時刻の挿入および表示

時刻を表示および挿入する場合、日時書式モデルとともにTO_CHARおよびTO_DATEファンクションをそれぞれ使用することをお薦めします。

DATE列では

  • デフォルトの時刻は12:00:00 A.M.(真夜中)です。

    デフォルトの時刻は、時刻部分に何も指定されなかった、または値が切捨てられたという理由のどちらの場合でも、時刻部分のない列の値に適用されます。

  • デフォルトの日は現在の月の最初の日です。

    デフォルトの日付は、日付部分に何も指定されなかった場合、列の値に適用されます。

例9-4では、DATE列を含む表を作成し、日付ごとに異なる書式モデルを指定して3つの日付を挿入します。最初の書式モデルには日付および時刻部分の両方があり、2番目には時刻部分がなく、3番目には日付部分がありません。次に、日付および時刻部分の両方が含まれる書式モデルを指定して3つの日付を表示します。

例9-4 日付と時刻の挿入および表示

表を作成します。

DROP TABLE birthdays;
CREATE TABLE birthdays (name VARCHAR2(20), day DATE);

日付ごとに異なる書式モデルを指定して3つの日付を挿入します。

INSERT INTO birthdays (name, day)
VALUES ('Annie',
        TO_DATE('13-NOV-92 10:56 A.M.','DD-MON-RR HH:MI A.M.')
       );
 
INSERT INTO birthdays (name, day)
VALUES ('Bobby',
        TO_DATE('5-APR-02','DD-MON-RR')
       );
 
INSERT INTO birthdays (name, day)
VALUES ('Cindy',
        TO_DATE('8:25 P.M.','HH:MI A.M.')
       );

格納された日時の値の日付および時刻部分の両方を表示します。

SELECT name,
       TO_CHAR(day, 'Mon DD, RRRR') DAY,
       TO_CHAR(day, 'HH:MI A.M.') TIME
FROM birthdays;

結果:

NAME                 DAY                   TIME
-------------------- --------------------- ----------
Annie                Nov 13, 1992          10:56 A.M.
Bobby                Apr 05, 2002          12:00 A.M.
Cindy                Nov 01, 2010          08:25 P.M.
 
3 rows selected.

日時データ型を使用した算術演算

日時値に対する算術演算の結果は、『Oracle Database SQL言語リファレンス』に記載のルールによって決定されます。

SQLには、日時の式に使用できる多くの日時ファンクションがあります。たとえば、関数ADD_MONTHSは、指定した日付から指定した月数である日付を戻します。

関連項目:

日時データ型の変換関数

表9-8に、日時データ型間で変換を行うSQLファンクションの概要を示します。これらのファンクションの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

表9-8 日時データ型のSQL変換関数

関数 変換元 変換先

NUMTODSINTERVAL

NUMBER

INTERVAL DAY TO SECOND

NUMTOYMINTERVAL

NUMBER

INTERVAL DAY TO MONTH

TO_CHAR

DATE

TIMESTAMP

TIME WITH TIME ZONE

TIMESTAMP WITH LOCAL TIME ZONE

INTERVAL DAY TO SECOND

INTERVAL YEAR TO MONTH

VARCHAR2

TO_DATE

CHAR

VARCHAR2

NCHAR

NVARCHAR2

DATE

TO_DSINTERVAL

CHAR

VARCHAR2

NCHAR

NVARCHAR2

INTERVAL DAY TO SECOND

TO_TIMESTAMP

CHAR

VARCHAR2

NCHAR

NVARCHAR2

TIMESTAMP

TO_TIMESTAMP_TZ

CHAR

VARCHAR2

NCHAR

NVARCHAR2

TIME WITH TIME ZONE

TO_YMINTERVAL

CHAR

VARCHAR2

NCHAR

NVARCHAR2

INTERVAL DAY TO MONTH

日時データ型のインポートおよびエクスポート

TIMESTAMP WITH TIME ZONEおよび TIMESTAMP WITH LOCAL TIME ZONE値はデータベースに正規化された書式で格納されるため、タイムゾーンのオフセットを考慮することなく、インポート、エクスポートおよび比較ができます。

DATEおよびTIMESTAMP値はデータベースでタイムゾーンを格納しないため、ソース・データベースとターゲット・データベース間のタイムゾーンの違いを考慮して調整する必要があります。

特殊なデータの表現

トピック:

空間データの表現

空間データは、位置情報アプリケーション、地理情報システム(GIS)・アプリケーションおよびジオイメージング・アプリケーションによって使用されます。

関連項目:

Oracle Databaseでの空間データの表現の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

大量のデータの表現

大量のデータを表現するために、Oracle Databaseでは次のものが提供されています。

ラージ・オブジェクト(LOB)

ラージ・オブジェクト(LOB)は、アプリケーションが効率的にアクセスおよび操作できるような方法で大量のデータを格納することを目的としたデータ型です。

表9-9に、LOBの概要を示します。

表9-9 ラージ・オブジェクト(LOB)

データ型 説明:

BLOB

バイナリ・ラージ・オブジェクト

すべての種類のデータがバイナリ形式で格納されます。

通常、イメージ、音声および映像などのマルチメディア・データに使用されます。

CLOB

キャラクタ・ラージ・オブジェクト

文字列データがデータベース・キャラクタ・セット形式で格納されます。

データベース・キャラクタ・セットを使用する大きい文字列またはドキュメント専用です。

NCLOB

各国語キャラクタ・ラージ・オブジェクト

文字列データが各国語キャラクタ・セット形式で格納されます。

各国語キャラクタ・セットを使用する大きい文字列またはドキュメントに使用します。

BFILE

外部ラージ・オブジェクト

バイナリ・ファイルをホストのオペレーティング・システム・ファイル・システム内のデータベース外に格納します。アプリケーションはBFILEに対し読取り専用のアクセス権を持ちます。

イメージ・データなど、アプリケーションが操作しない静的データに使用されます。

あらゆる種類のデータ(つまりオペレーティング・システム・ファイル)はBFILEに格納できます。たとえば、キャラクタ・データをBFILEに格納し、キャラクタ・セットを指定してBFILEデータをCLOBにロードします。

BLOB、CLOBまたはNCLOB型のインスタンスは一時的(アプリケーションのスコープ内で宣言される)か、または永続的(データベースで作成および格納される)のいずれも可能です。

関連項目:

LONGおよびLONG RAWデータ型

注意:

OracleではLONGおよびLONG RAWデータ型を下位互換性のためにサポートしますが、LONG列をLOB列に、LONG RAW列をBLOB列に変換することを強くお薦めします。

LONG列には最大2 GB - 1バイトの可変長文字列が格納されます。

LONG RAWデータ型(およびRAWデータ型)は、Oracle Databaseによって、異なるシステム間でデータを移動するときに明示的に変換されないデータを格納します。これらのデータ型は、2進データおよびバイト列のために用意されています。

関連項目:

データ・タイプの詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

JSONデータの表現

JSONデータをOracle Databaseにネイティブに格納するための新しいJSONデータ型。

Oracle Databaseでは、JSONデータ型がネイティブに格納されます。

JSON形式で格納されている情報がある場合は、Oracle提供の型JSONを使用して、ネイティブにデータベースに格納できます。Oracleには、索引付けや、JSONデータを操作できる豊富なパッケージおよび演算子が提供されています。

JSON値を使用して、次を使用できます。
  • JSON型の標準関数
  • IS_JSON/IS_NOT_JSON制約

関連項目:

  • Oracle JSONデータ型と、それを使用してデータベース内のJSONデータの格納、生成、操作、管理および問合せを行う方法の詳細は、Oracle Database JSON開発者ガイド を参照してください。

検索可能なテキストの表現

全文検索を行うには、低レベルのコードを作成するのではなく、Oracle Textを使用します。Oracle Textは、問合せアプリケーションおよびドキュメント分類アプリケーションのテキスト用の索引付け、語とテーマの検索および表示機能を提供します。また、Oracle Textを使用してXMLデータを検索することもできます。

関連項目:

Oracle Textの詳細は、『Oracle Textアプリケーション開発者ガイド』を参照してください。

XMLデータの表現

XML形式のファイルとして格納されている情報がある場合、またはADTをXML形式で格納する場合、Oracle提供のXMLType型を使用できます。

XMLType値を使用して、次を使用できます。

関連項目:

動的に型指定されたデータの表現

いくつかの言語では、実行時にデータ型を変更したり、プログラムに変数の型を確認させることができます。たとえば、C言語にはunionキーワードおよびvoid*ポインタがあり、Javaにはtypeof演算子およびNumberなどのラッパー型があります。

Oracle Databaseでは、すべての型のデータを保持できる変数および列を作成することができ、その値をテストして基礎となる表現を判断できます。たとえば、表の単一の列で、数値、文字列およびオブジェクトをそれぞれ別の行に表現できます。

Oracleが提供するSYS.ANYDATA ADTを使用すると、すべてのスカラー型またはADTの値を表現できます。SYS.ANYDATAには、任意の型のスカラー値を受け取り、スカラーまたはオブジェクトに戻すメソッドがあります。同様に、Oracleが提供するSYS.ANYDATASET ADTを使用すると、すべてのコレクション型の値を表現できます。

型の情報を確認および操作するには、例9-5に示すDBMS_TYPESパッケージを使用します。

OCIを使用するときは、OCIAnyDataおよびOCIAnyDataSetインタフェースを使用します。

例9-5 SYS.ANYDATA列の情報へのアクセス

CREATE OR REPLACE TYPE employee_type AS
  OBJECT (empno NUMBER, ename VARCHAR2(10));
/
 
DROP TABLE mytab;
CREATE TABLE mytab (id NUMBER, data SYS.ANYDATA);
 
INSERT INTO mytab (id, data)
VALUES (1, SYS.ANYDATA.ConvertNumber(5));
 
INSERT INTO mytab (id, data)
VALUES (2, SYS.ANYDATA.ConvertObject(Employee_type(5555, 'john')));
 
CREATE OR REPLACE PROCEDURE p IS
  CURSOR cur IS SELECT id, data FROM mytab;
  v_id                        mytab.id%TYPE;
  v_data                      mytab.data%TYPE;
  v_type                      SYS.ANYTYPE;
  v_typecode                  PLS_INTEGER;
  v_typename                  VARCHAR2(60);
  v_dummy                     PLS_INTEGER;
  v_n                         NUMBER;
  v_employee                  employee_type;
  non_null_anytype_for_NUMBER exception;
  unknown_typename            exception;
BEGIN
  FOR x IN cur LOOP
    FETCH cur INTO v_id, v_data;
    EXIT WHEN cur%NOTFOUND;
 
    /* typecode signifies type represented by v_data.
       GetType also produces a value of type SYS.ANYTYPE with methods you
       can call to find precision and scale of a number, length of a
       string, and so on. */
 
       v_typecode := v_data.GetType (v_type /* OUT */);
 
    /* Compare typecode to DBMS_TYPES constants to determine type of data
       and decide how to display it. */
  
    CASE v_typecode
      WHEN DBMS_TYPES.TYPECODE_NUMBER THEN
        IF v_type IS NOT NULL THEN  -- This condition should never happen.
          RAISE non_null_anytype_for_NUMBER;
        END IF;
 
      -- For each type, there is a Get method.
      v_dummy := v_data.GetNUMBER (v_n /* OUT */);
      DBMS_OUTPUT.PUT_LINE
        (TO_CHAR(v_id) || ': NUMBER = ' || TO_CHAR(v_n) );
 
       WHEN DBMS_TYPES.TYPECODE_OBJECT THEN
         v_typename := v_data.GetTypeName();
         IF v_typename NOT IN ('HR.EMPLOYEE_TYPE') THEN
           RAISE unknown_typename;
         END IF;
         v_dummy := v_data.GetObject (v_employee /* OUT */);
         DBMS_OUTPUT.PUT_LINE
           (TO_CHAR(v_id) || ': user-defined type = ' || v_typename ||
            ' ( ' || v_employee.empno || ', ' || v_employee.ename || ' )' );
    END CASE;
  END LOOP;
EXCEPTION
  WHEN non_null_anytype_for_NUMBER THEN
    RAISE_Application_Error (-20000,
      'Paradox: the return AnyType instance FROM GetType ' ||
      'should be NULL for all but user-defined types');
  WHEN unknown_typename THEN
    RAISE_Application_Error( -20000, 'Unknown user-defined type ' ||
      v_typename || ' - program written to handle only HR.EMPLOYEE_TYPE');
END;
/
 
SELECT t.data.gettypename() AS "Type Name" FROM mytab t;

結果:

Type Name
--------------------------------------------------------------------------------
SYS.NUMBER
HR.EMPLOYEE_TYPE
 
2 rows selected.

関連項目:

ANSI、DB2およびSQL/DSデータの表現

表およびクラスタを作成するSQL文は、ANSIデータ型と、IBM社の製品SQL/DSおよびDB2のデータ型を使用できます(次の段落の注意を除く)。Oracle DatabaseはANSIまたはIBMデータ型を対応するOracleデータ型に変換し、Oracleデータ型を列データ型の名前として記録し、列データをOracleデータ型で格納します。

注意:

SQL文ではSQL/DSおよびDB2のTIME、GRAPHIC、VARGRAPHICおよびLONG VARGRAPHICデータ型を使用できません。対応するOracleデータ型がないからです。

関連項目:

変換の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

アドレスによる行の識別

データベース表の行にアクセスするには、そのアドレス、または行を一意に識別するROWIDを使用するのが最も高速な方法です。同じデータ・ブロック内の異なる行は、これらが異なるクラスタ化表にある場合のみ、同じROWIDを持つことができます。行が1データ・ブロックよりも長い場合、ROWIDによって最初の行断片が識別されます。

ROWIDを表示するには、ROWID疑似列を問い合せます。疑似列ROWIDの各値は、行のアドレスを表す文字列です。文字列のデータ型はROWIDまたはUROWIDです。

注意:

ハイブリッド列圧縮(HCC)で圧縮された表の行を更新すると、行のROWIDが変更されます。HCCは、特定のOracleストレージ・システムの機能です。

例9-6では、ROWIDデータ型の列を持つ表を作成し、INSERT文でROWID擬似列を問い合せることでROWIDを移入し、表示します。表のROWID列により、格納方法が表示されます。

注意:

ROWID型の列(例9-6のcol1など)を作成するのみでは、その値が有効なROWIDであるという保証はありません。

関連項目:

  • 疑似列ROWIDの概要は、『Oracle Database概要』を参照してください

  • ROWIDデータ型の概要は、『Oracle Database概要』を参照してください

  • ROWID疑似列の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。

  • ROWIDデータ型の詳細は、『Oracle Database SQL言語リファレンス』を参照してください

  • UROWIDデータ型の詳細は、『Oracle Database SQL言語リファレンス』を参照してください

  • CでのROWIDデータ型の使用方法の詳細は、『Oracle Call Interfaceプログラマーズ・ガイド』を参照してください

  • Pro*C/C++プリコンパイラでのROWIDデータ型の使用方法の詳細は、『Pro*C/C++プログラマーズ・ガイド』を参照してください

  • JavaでのROWIDデータ型の使用方法の詳細は、『Oracle Database JDBC開発者ガイド』を参照してください

  • HCCの詳細は、『Oracle Database概要』を参照してください。

例9-6 ROWID擬似列の問合せ

DROP TABLE t_tab;  -- in case it exists
CREATE TABLE t_tab (col1 ROWID);
 
INSERT INTO t_tab (col1)
SELECT ROWID
FROM employees
WHERE employee_id > 199;

問合せ:

SELECT employee_id, rowid
FROM employees
WHERE employee_id > 199;

ROWIDは異なりますが、次に類似した結果が得られます。 

EMPLOYEE_ID ROWID
----------- ------------------
        200 AAAPeSAAFAAAABTAAC
        201 AAAPeSAAFAAAABTAAD
        202 AAAPeSAAFAAAABTAAE
        203 AAAPeSAAFAAAABTAAF
        204 AAAPeSAAFAAAABTAAG
        205 AAAPeSAAFAAAABTAAH
        206 AAAPeSAAFAAAABTAAI
 
7 rows selected.

問合せ:

SELECT * FROM t_tab;

COL1は異なりますが、次に類似した結果が得られます。 

COL1
------------------
AAAPeSAAFAAAABTAAC
AAAPeSAAFAAAABTAAD
AAAPeSAAFAAAABTAAE
AAAPeSAAFAAAABTAAF
AAAPeSAAFAAAABTAAG
AAAPeSAAFAAAABTAAH
AAAPeSAAFAAAABTAAI
 
7 rows selected.

SQL演算子とファンクションのメタデータの表示

動的パフォーマンス・ビューV$SQLFN_METADATAには、SQL演算子とファンクションのメタデータが表示されます。V$SQLFN_METADATAによって表示されるすべてのファンクションに対し、動的パフォーマンス・ビューV$SQLFN_ARG_METADATAには、それぞれのファンクションの引数に関するメタデータの1行が含まれます。関数の引数が反復可能な場合(関数LEASTおよびGREATEST内など)、V$SQLFN_ARG_METADATAには、それぞれの反復引数向けに1行のみが含まれます。列FUNC_IDでビューV$SQLFN_METADATAおよびV$SQLFN_ARG_METADATAを結合できます。

これらのビューによって、サード・パーティ・ツールは、アプリケーション・レイヤーでメタデータをメンテナンスすることなくSQLファンクションを活用できます。

トピック:

関連項目:

ARGnデータ型

ビューV$SQLFN_METADATAの中で列DATATYPEは、関数のデータ型(つまり、関数が戻すデータ型)です。このデータ型は、Oracleデータ型、データ型ファミリ、またはARGnにできます。ARGnは、関数のn番目の引数のデータ型です。例:

  • MAX関数は、その最初の引数のデータ型を持つ値を戻すため、MAX関数が戻すデータ型はARG1です。

  • DECODE関数は、その3番目の引数のデータ型を持つ値を戻すため、DECODE関数のデータ型は、ARG3です。

関連項目:

DISP_TYPEデータ型

ビューV$SQLFN_METADATAの中では、列DISP_TYPEが任意の式を持つ引数のデータ型です。式は、単一の値、または値と単一の値を持つSQLファンクションの組合せから構成されています。

表9-10 SQLファンクションの表示タイプ

表示タイプ 説明: 例

NORMAL

FUNC(A,B,...)

LEAST(A,B,C)

ARITHMETIC

A FUNC B)

A+B

PARENTHESIS

FUNC()

SYS_GUID()

RELOP

A FUNC B

A IN B

CASE_LIKE

CASE文またはDECODEデコード

NOPAREN

FUNC

SYSDATE

SQLデータ型ファミリ

通常、SQLファンクションの引数には、データ型ファミリの任意のデータ型を含めることができます。表9-11に、SQLデータ型ファミリとそのメンバーのデータ型を示します。

表9-11 SQLデータ型ファミリ

ファミリ データ型

STRING

  • CHARACTER

  • VARCHAR2

  • CLOB

  • NCHAR

  • NVARCHAR2

  • NCLOB

  • LONG

NUMERIC

  • NUMBER

  • BINARY_FLOAT

  • BINARY_DOUBLE

DATETYPE

  • DATE

  • TIMESTAMP

  • TIME WITH TIME ZONE

  • TIMESTAMP WITH LOCAL TIME ZONE

  • INTERVAL YEAR TO MONTH

  • INTERVAL DAY TO SECOND

BINARY

  • BLOB

  • RAW

  • LONG RAW