データ型の比較規則

ここでは、Oracle Databaseが各データ型の値を比較する方法について説明します。

数値

多い値は少ない値よりも大きいと見なされます。すべての負の数は0および正の数よりも小さいと見なされます。このため、-1は100より小さく、-100は-1より小さい数です。

浮動小数点値NaN(非数値)は、その他の数値よりも大きく、NaNとは等しいとみなされます。

関連項目:

比較セマンティクスの詳細は、数値の優先順位および浮動小数点数を参照してください。

日時値

後の日付またはタイムスタンプは前の日付またはタイムスタンプよりも大きいとみなされます。たとえば、29-MAR-2005 (2005年3月29日)に相当する日付は05-JAN-2006 (2006年1月5日)に相当する日付よりも小さく、05-JAN-2006 1:35pm (2006年1月5日午後1時35分)に相当するタイムスタンプは05-JAN-2005 10:09am (2005年1月5日午前10時9分)に相当するタイムスタンプよりも大きいとみなされます。

タイムゾーン付きの2つのタイムスタンプを比較する場合、これらのタイムスタンプは、まずUTC、つまりタイムゾーン・オフセット+00:00に正規化されます。たとえば、16-OCT-2016 05:59am Europe/Warsaw (2016年10月16日午前5時59分、ヨーロッパ/ワルシャワ)に相当するタイムゾーン付きタイムスタンプは、15-OCT-2016 08:59pm US/Pacific (2016年10月15日午前8時59分、米国/太平洋)に相当するタイムスタンプと同じです。両方とも同じ絶対時点を表し、UTCでは2016年10月16日午前3時59分と表されます。

バイナリ値

データ型RAWまたはBLOBのバイナリ値は、バイト・シーケンスです。2つのバイナリ値を比較する場合、2つのバイト・シーケンスの対応する連続したバイトが順番に比較されます。両方の比較対象の値の最初のバイトが異なる場合は、数値が小さいバイトを含むバイナリ値の方が小さいとみなされます。最初のバイトが等しい場合は2つ目のバイトが比較され、以降、比較対象のバイトが異なるか比較処理がいずれかの値の最後に到達するまで同様に比較されます。後者の場合、短い値の方が小さいとみなされます。

データ型BLOBのバイナリ値は、比較条件では直接比較できません。ただし、PL/SQLファンクションDBMS_LOB.COMPAREを使用して比較できます。

関連項目:

DBMS_LOB.COMPAREファンクションの詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。

文字値

文字値は、2つのメジャーに基づいて比較されます。

  • バイナリ照合または言語照合

  • 空白埋め比較セマンティクスまたは非空白埋め比較セマンティクス

次の項で、2つのメジャーについて説明します。

バイナリ照合および言語照合

デフォルトであるバイナリ照合では、文字値はバイナリ値と同様に比較されます。記憶域文字セットで2つの文字値のエンコーディングを形成する2つのバイト・シーケンスは、バイナリ値として処理され、「バイナリ値」で説明されているように比較されます。この比較の結果は、ソース文字値のバイナリ比較の結果として戻されます。

関連項目:

文字セットの詳細は、『Oracle Databaseグローバリゼーション・サポート・ガイド』を参照してください。

多くの言語では、バイナリ照合によって、文字値の順序が言語的に正しくなくなる場合があります。たとえば、最も一般的な文字セットでは、すべてのラテン大文字の文字コードは、ラテン小文字よりも小さい値です。そのため、バイナリ照合では、順序は次のようになります。

MacDonald
MacIntosh
Macdonald
Macintosh

しかし、ほとんどのユーザーは、これらの4つの値が次の順序で表示されると想定します。

MacDonald
Macdonald
MacIntosh
Macintosh

これは、バイナリ照合が英語の文字値にも適さない場合があることを示しています。

Oracle Databaseでは、様々な話し言葉の規則に従って文字列を順序付ける言語照合がサポートされています。文字値を大/小文字またはアクセントを区別せずに照合する照合の変形もサポートされています。言語照合の方がコストは高くなりますが、ユーザー操作に優れています。

関連項目:

言語ソートについては、『Oracle Databaseグローバリゼーション・サポート・ガイド』を参照してください。

言語照合の制限事項

言語照合を使用する場合は、比較条件、ORDER BYGROUP BYおよびMATCH_RECOGNIZE問合せ句、COUNT(DISTINCT)および統計集計ファンクション、LIKE条件、ORDER BYおよびPARTITION BY分析句により、照合キーが生成されます。照合キーは、NLSSORTファンクションによって戻されるものと同じ値であり、「NLSSORT」で説明されているものと同じ制限事項があります。

空白埋め比較セマンティクスおよび非空白埋め比較セマンティクス

空白埋め比較セマンティクスでは、2つの値の長さが異なる場合、Oracleはまず短い方の値の最後に空白を追加して、2つの値が同じ長さになるようにします。次に、その2つの値を、最初に異なる文字まで1文字ずつ比較します。最初に異なる文字の位置で、大きい方の文字を持つ値の方が大きいとみなされます。2つの値に異なる文字がない場合、その2つの値は等しいとみなされます。この規則では、2つの値の後続空白数のみが異なる場合、その2つの値は等しいとみなされます。Oracleでは、比較する両方の値が、CHARデータ型、NCHARデータ型、テキスト・リテラルのいずれかの式の場合、またはUSERファンクションの戻り値の場合のみ空白埋め比較セマンティクスを使用します。

非空白埋め比較セマンティクスでは、Oracleは、2つの値を、最初に異なる文字まで1文字ずつ比較します。最初に異なる文字の位置で、大きい方の文字を持つ値の方が大きいとみなされます。長さが異なる2つの値を短い方の値の最後まで比較して、すべて同じ文字だった場合、長い方の値が大きいとみなされます。同じ長さの2つの値に異なる文字がない場合、その2つの値は等しいとみなされます。Oracleでは、比較する片方または両方の値がVARCHAR2データ型またはNVARCHAR2データ型の場合、非空白埋め比較セマンティクスを使用します。

これらの異なる比較セマンティクスを使用して2つの文字値を比較した場合、その結果が異なることもあります。次の表に、それぞれの比較セマンティクスを使用して5組の文字を比較した結果を示します。通常、空白埋め比較と非空白埋め比較の結果は同じです。表に示されている最後の比較では、空白埋め比較と非空白埋め比較の違いが明確になっています。

空白埋め比較 非空白埋め比較

'ac' > 'ab'

'ac' > 'ab'

'ab' > 'a '

'ab' > 'a '

'ab' > 'a'

'ab' > 'a'

'ab' = 'ab'

'ab' = 'ab'

'a ' = 'a'

'a ' > 'a'

データ・バインドされた照合

Oracle Database 12cリリース2 (12.2)以降、特定の文字値を比較または照合するときに使用する照合は、値自体に関連付けられます。これをデータ・バインドされた照合といいます。データ・バインドされた照合は、値のデータ型の属性として表示できます。

以前のリリースのOracle Databaseでは、データベース・セッションのすべての照合依存のSQL操作の照合は、セッション・パラメータNLS_COMPおよびNLS_SORTによって大まかに決定されていました。データ・バインドされた照合アーキテクチャを使用すると、アプリケーションによって、言語固有の比較規則を、これらの規則を必要とするデータに常に正確に適用できます。

Oracle Database 12cリリース2 (12.2)では、表の列の照合を宣言できます。列を照合依存のSQL操作に引数として渡すと、そのSQL操作は、列の宣言された照合を使用して列の値を処理します。SQL操作に複数の文字引数があり、それらが相互に比較される場合、使用する照合は照合決定ルールによって決定されます。

データ・バインドされた照合には次の2つのタイプがあります。

  • 名前付き照合: この照合は、照合名で指定された照合ルールの特定のセットです。名前付き照合は、NLS_SORTパラメータの値として指定される照合と同じです。名前付き照合として、バイナリ照合または言語照合を使用できます。

  • 疑似照合: この照合では、SQL操作の照合ルールを直接指定しません。かわりに、使用する実際の名前付き照合のセッション・パラメータNLS_SORTおよびNLS_COMPの値を確認するよう操作に指示します。疑似照合は、新しい宣言的な照合指定方法とセッション・パラメータを使用する古い方法の橋渡しとなります。特に、疑似照合USING_NLS_COMPは、Oracle Database 12cリリース2より前の動作とまったく同じように動作するようSQL操作に指示します。

列の名前付き照合を宣言する場合は、列値の比較方法を静的に決定します。疑似照合を宣言する場合は、セッション・パラメータNLS_COMPおよびNLS_SORTを使用して比較動作を動的に制御できます。ただし、疑似照合で宣言された列に定義されている索引や制約などの静的オブジェクトでは、バイナリ照合が使用されます。動的に設定可能な照合ルールを使用して静的オブジェクトの値を比較することはできません。

式で使用される文字リテラルまたはバイナリ変数の照合は、その式が含まれるデータベース・オブジェクト(ビューまたはマテリアライズド・ビュー問合せ、PL/SQLストアド・ユニット・コード、ユーザー定義型のメソッド・コード、スタンドアロンのDMLまたは問合せ文など)のデフォルト照合から導出されます。Oracle Database 12cリリース2では、PL/SQLストアド・ユニット、ユーザー定義型メソッドおよびスタンドアロンのSQL文のデフォルトの照合は、常に疑似照合のUSING_NLS_COMPです。ビューおよびマテリアライズド・ビューのデフォルト照合は、CREATE VIEWおよびCREATE MATERIALIZED VIEW文のDEFAULT COLLATION句で指定できます。

SQL操作によって文字値が戻される場合、照合導出ルールによってその結果の導出照合が決定されるため、結果が式ツリー内の別の照合依存のSQL操作またはトップレベルのコンシューマ(SELECT文のSQL文の句など)の引数として渡されるとき、照合は既知です。SQL操作の対象が文字引数値の場合、その文字結果の導出照合は、引数の照合に基づきます。それ以外の場合、導出ルールは文字リテラルの場合と同じです。

単純式や演算子結果などの式ノードの導出照合は、COLLATE演算子を使用して上書きできます。

Oracle Databaseでは、列、表またはスキーマに対して大/小文字を区別しない照合を宣言できるため、表またはスキーマ内の列またはすべての文字の列は、常に大/小文字を区別しない方法で比較できます。

関連項目:

オブジェクト値

オブジェクト値は、MAPORDERの2つの比較ファンクションのいずれかを使用して比較されます。どちらのファンクションでもオブジェクト型インスタンスは比較されますが、両者は別のものです。これらのファンクションは、他のオブジェクト型と比較するオブジェクト型の一部として指定される必要があります。

関連項目:

MAPメソッドとORDERメソッド、およびこれらのメソッドが戻す値の詳細は、「CREATE TYPE」を参照してください。

VARRAYとネストした表

ネストした表の比較の詳細は、比較条件を参照してください。

データ型の優先順位

Oracleでは、データ型の優先順位によって、暗黙的にデータ型を変換するかどうかを判断します(次の項を参照)。Oracleデータ型の優先順位は、次のとおりです。

  • 日時および期間データ型

  • BINARY_DOUBLE

  • BINARY_FLOAT

  • NUMBER

  • 文字データ型

  • その他のすべての組込みデータ型

データ変換

通常、式では異なるデータ型の値を使用できません。たとえば、式では10に5を掛けた値に'JAMES'を加えることはできません。ただし、Oracleでは値をあるデータ型から別のデータ型へ変換する場合の、暗黙的な変換と明示的な変換をサポートしています。

暗黙的なデータ変換と明示的なデータ変換

暗黙的または自動的な変換に依存するかわりに、Oracleでは明示的な変換の指定をお薦めします。これは、次の理由によります。

  • 明示的なデータ型変換ファンクションを使用すると、SQL文がわかりやすくなります。

  • 暗黙的なデータ型変換(特に列値のデータ型が定数に変換される場合)は、パフォーマンスに悪影響を及ぼす可能性があります。

  • 暗黙的な変換はその変換が行われるコンテキストに依存し、どんな場合でも同様に機能するとはかぎりません。たとえば、日時値からVARCHAR2型へ値を暗黙的に変換すると、NLS_DATE_FORMATパラメータに指定されている値によっては、予期しない年が戻される場合があります。

  • 暗黙的な変換のアルゴリズムはソフトウェア・リリース間やOracle製品間で変更される場合があります。明示的な変更では、動作がより安定しています。

  • 索引式で暗黙的なデータ型変換が発生した場合、その索引が変換前のデータ型に対して定義されているために、Oracle Databaseがその索引を使用しないことがあります。これは、パフォーマンスに悪影響を与えるおそれがあります。

暗黙的なデータ変換

あるデータ型から別のデータ型への変換が意味を持つ場合、Oracle Databaseは値を自動的に変換します。

表2-8に、Oracleの暗黙的な変換のマトリックスについて示します。この表は、変換の方向または変換されるコンテキストにかかわらず、すべての可能な変換を示します。詳細は、表の後の説明を参照してください。

表2-8 暗黙的な型変換のマトリックス

DataType CHAR VARCHAR2 NCHAR NVARCHAR2 DATE DATETIME/INTERVAL NUMBER BINARY_FLOAT BINARY_DOUBLE LONG RAW ROWID CLOB BLOB NCLOB

CHAR

--

X

X

X

X

X

X

X

X

X

X

X

X

X

X

VARCHAR2

X

--

X

X

X

X

X

X

X

X

X

X

X

--

X

NCHAR

X

X

--

X

X

X

X

X

X

X

X

X

X

--

X

NVARCHAR2

X

X

X

--

X

X

X

X

X

X

X

X

X

--

X

DATE

X

X

X

X

--

--

--

--

--

--

--

--

--

--

--

DATETIME/ INTERVAL

X

X

X

X

--

--

--

--

--

X

--

--

--

--

--

NUMBER

X

X

X

X

--

--

--

X

X

--

--

--

--

--

--

BINARY_FLOAT

X

X

X

X

--

--

X

--

X

--

--

--

--

--

--

BINARY_DOUBLE

X

X

X

X

--

--

X

X

--

--

--

--

--

--

--

LONG

X

X

X

X

--

X脚注1

--

--

--

--

X

--

X

--

X

RAW

X

X

X

X

--

--

--

--

--

X

--

--

--

X

--

ROWID

X

X

X

X

--

--

--

--

--

--

--

--

--

--

--

CLOB

X

X

X

X

--

--

--

--

--

X

--

--

--

--

X

BLOB

--

--

--

--

--

--

--

--

--

--

X

--

--

--

--

NCLOB

X

X

X

X

--

--

--

--

--

X

--

--

X

--

--

脚注 1

LONGINTERVALに直接は変換できませんが、TO_CHAR(interval)を使用してLONGVARCHAR2に変換し、そのVARCHAR2の値をINTERVALに変換できます。

暗黙的なデータ型変換は、次に示す規則によって決まります。

  • INSERTおよびUPDATE操作中に、Oracleは変更する列のデータ型に値を変換します。

  • SELECT FROM操作中に、Oracleは列からターゲット変数の型にデータを変換します。

  • 数値を操作する際、Oracleは、通常、最大容量を確保するために精度および位取りを調整します。この場合、このような操作によって変換された数値データ型は、基礎となる表に含まれる数値データ型と異なることがあります。

  • 数値と文字値を比較する場合、Oracleは文字データを数値に変換します。

  • 文字値またはNUMBERの値と浮動小数点数の値の間では、変換が正確に行われない場合があります。これは、文字型およびNUMBERでは10進精度で数値が示されるのに対し、浮動小数点数では2進精度が使用されているためです。

  • CLOB値をVARCHAR2などの文字データ型に変換する場合、またはBLOBRAWデータに変換する場合、変換するデータがターゲットのデータ型より大きいと、データベースはエラーを戻します。

  • タイムスタンプ値からDATE値への変換では、タイムスタンプ値の秒の小数部は切り捨てられます。この動作は、タイムスタンプ値の秒の小数部が丸められていた、以前のリリースのOracle Databaseの動作とは異なります。

  • BINARY_FLOATからBINARY_DOUBLEへの変換は正確に行われます。

  • BINARY_DOUBLEの値がBINARY_FLOATでサポートされている精度のビット数よりも多いビット数を使用している場合、BINARY_DOUBLEからBINARY_FLOATへの変換は正確に行われません。

  • DATEの値と文字の値を比較する場合、Oracleは文字データをDATEに変換します。

  • SQLファンクションまたは演算子に不当なデータ型の引数を指定して使用する場合、Oracleはその引数を正当なデータ型に変換します。

  • 代入を実行する場合、Oracleは等号(=)の右側の値を左側の代入ターゲットのデータ型に変換します。

  • 連結中に、Oracleは非文字データ型をCHARまたはNCHARに変換します。

  • 文字データ型と非文字データ型に対する演算処理および比較中に、Oracleはすべての文字データ型を数値、日付またはROWIDのいずれかの適切なデータ型に変換します。CHAR/VARCHAR2NCHAR/NVARCHAR2の演算処理では、OracleはNUMBERに変換します。

  • ほとんどのSQL文字ファンクションは、CLOBをパラメータとして指定できます。また、OracleはCLOBと文字型間で暗黙的な変換を実行します。このため、CLOBを使用できないファンクションは、暗黙的な変換を使用してCLOBを受け入れます。このような場合、Oracleはファンクションが起動される前にCLOBCHARまたはVARCHAR2に変換します。CLOBが4000バイトより大きい場合、Oracleは最初の4000バイトのみをCHARに変換します。

  • RAWまたはLONGRAWデータを文字データとの間で変換する場合、バイナリ・データは16進形式で表され、16進文字1文字ごとに4ビットのRAWデータを表します。詳細は、「RAWデータ型とLONG RAWデータ型」を参照してください。

  • CHARVARCHAR2NCHARNVARCHAR2の比較では、異なる文字セットが必要な場合があります。このような場合のデフォルトの変換の方向は、データベース文字セットから各国語文字セットです。表2-9に、異なるキャラクタ・タイプ間での暗黙的な変換の方向を示します。

表2-9 異なるキャラクタ・タイプの変換方向

SourceData型 CHARへ VARCHAR2へ NCHARへ NVARCHAR2へ

CHARから

--

VARCHAR2

NCHAR

NVARCHAR2

VARCHAR2から

VARCHAR2

--

NVARCHAR2

NVARCHAR2

NCHARから

NCHAR

NCHAR

--

NVARCHAR2

NVARCHAR2から

NVARCHAR2

NVARCHAR2

NVARCHAR2

--

コレクションなどのユーザー定義型は暗黙的に変換できないため、CAST ... MULTISETを使用して明示的に変換する必要があります。

暗黙的なデータ変換の例

テキスト・リテラルの例

テキスト・リテラル10のデータ型はCHARです。次の文のように数式で使用すると暗黙的にNUMBERデータ型に変換されます。

SELECT salary + '10'
  FROM employees;

文字値および数値の例

条件で文字値とNUMBER型の値を比較する場合、NUMBER型の値は文字値に変換されず、文字値が暗黙的にNUMBER型の値に変換されます。次の文では、'200'が暗黙的に200に変換されます。

SELECT last_name
  FROM employees
  WHERE employee_id = '200';

日付の例

次の文では、デフォルトの日付書式DD-MON-YYを使用して、24-JUN-06DATE値に暗黙的に変換されます。

SELECT last_name
  FROM employees 
  WHERE hire_date = '24-JUN-06';

明示的なデータ変換

SQL変換ファンクションを使用すると、データ型の変換を明示的に指定できます。表2-10に、値をあるデータ型から別のデータ型に明示的に変換するSQLファンクションを示します。

Oracleが暗黙的なデータ型変換を行うことができる場合には、LONGおよびLONG RAWの値を指定できません。たとえば、ファンクションや演算子を含む式では、LONGLONG RAWの値を使用できません。LONGデータ型およびLONG RAWデータ型の制限については、「LONGデータ型」を参照してください。

表2-10 明示的な型の変換

SourceData型 CHAR、VARCHAR2、NCHAR、NVARCHAR2へ NUMBERへ Datetime/Intervalへ RAWへ ROWIDへ LONG、LONG RAWへ CLOB、NCLOB、BLOBへ BINARY_FLOATへ BINARY_DOUBLEへ

CHAR、VARCHAR2、NCHAR、NVARCHAR2から

TO_CHAR (char.)

TO_NCHAR (char.)

TO_NUMBER

TO_DATE

TO_TIMESTAMP

TO_TIMESTAMP_TZ

TO_YMINTERVAL

TO_DSINTERVAL

HEXTORAW

CHARTO­=ROWID

--

TO_CLOB

TO_NCLOB

TO_BINARY_FLOAT

TO_BINARY_DOUBLE

NUMBERから

TO_CHAR (number)

TO_NCHAR (number)

--

TO_DATE

NUMTOYM- INTERVAL

NUMTODS- INTERVAL

--

--

--

--

TO_BINARY_FLOAT

TO_BINARY_DOUBLE

Datetime Intervalから

TO_CHAR (date)

TO_NCHAR (datetime)

--

--

--

--

--

--

--

--

RAWから

RAWTOHEX

RAWTONHEX

--

--

--

--

--

TO_BLOB

--

--

ROWIDから

ROWIDTOCHAR

--

--

--

--

--

--

--

--

LONG/LONG RAWから

--

--

--

--

--

--

TO_LOB

--

--

CLOB、NCLOB、BLOBから

TO_CHAR

TO_NCHAR

--

--

--

--

--

TO_CLOB

TO_NCLOB

--

--

CLOB、NCLOB、BLOBから

TO_CHAR

TO_NCHAR

--

--

--

--

--

TO_CLOB

TO_NCLOB

--

--

BINARY_FLOATから

TO_CHAR (char.)

TO_NCHAR (char.)

TO_NUMBER

--

--

--

--

--

TO_BINARY_FLOAT

TO_BINARY_DOUBLE

BINARY_DOUBLEから

TO_CHAR (char.)

TO_NCHAR (char.)

TO_NUMBER

--

--

--

--

--

TO_BINARY_FLOAT

TO_BINARY_DOUBLE

関連項目:

すべての明示的な変換ファンクションの詳細は、変換ファンクションを参照してください。

データ変換のセキュリティ上の考慮事項

暗黙的な変換、または書式モデルを指定しない明示的な変換のいずれかによって日時値がテキストに変換される場合、書式モデルはグローバリゼーション・セッション・パラメータの1つによって定義されます。ソースのデータ型に応じて、パラメータ名はNLS_DATE_FORMATNLS_TIMESTAMP_FORMATまたはNLS_TIMESTAMP_TZ_FORMATです。これらのパラメータの値は、クライアント環境で、またはALTER SESSION文で指定できます。

書式モデルがセッション・パラメータに依存している場合、明示的な書式モデルが指定されていない変換が動的SQL文のテキストに連結される日時値に適用されると、データベースのセキュリティに悪影響を及ぼす可能性があります。動的SQL文は、実行のためにデータベースに渡される前にそのテキストがフラグメントから連結される文です。動的SQLは、組込みPL/SQLパッケージDBMS_SQLまたはPL/SQL文EXECUTE IMMEDIATEに関連付けられる場合が多いですが、動的に構成されたSQLテキストを引数として渡すことができる場所はこれらのみではありません。次に例を示します。

EXECUTE IMMEDIATE
'SELECT last_name FROM employees WHERE hire_date > ''' || start_date || '''';

start_dateのデータ型はDATEです。

前述の例では、start_dateの値はセッション・パラメータNLS_DATE_FORMATで指定された書式モデルを使用してテキストに変換されます。結果は、連結されてSQLテキストになります。日時書式モデルは、単純に二重引用符で囲んだリテラル・テキストで構成できます。したがって、セッションのグローバリゼーション・パラメータを明示的に設定できるすべてのユーザーが、前述の変換で生成されるテキストを決定できます。SQL文がPL/SQLプロシージャによって実行される場合、そのプロシージャはセッション・パラメータによるSQLインジェクションに対して脆弱になります。セッション自体より強い権限である定義者の権限でプロシージャが実行されると、ユーザーは機密データに不正にアクセスすることができます。

関連項目:

他の例およびこのセキュリティ・リスクを回避する場合の推奨事項については、『Oracle Database PL/SQL言語リファレンス』を参照してください。

ノート:

このセキュリティ・リスクは、データベースまたはOCI日時ファンクションによってテキストに変換された日時値からSQLテキストを構成する中間層アプリケーションにも該当します。セッションのグローバリゼーション・パラメータがユーザー・プリファレンスから取得される場合、それらのアプリケーションは脆弱になります。

数値の暗黙的および明示的な変換でも、変換結果がセッション・パラメータNLS_NUMERIC_CHARACTERSに依存する場合があるため、類似した問題が発生する可能性があります。このパラメータは、小数点区切り文字および桁区切り文字を定義します。小数点区切りを一重引用符または二重引用符と定義すると、SQLインジェクションが発生する可能性があります。

関連項目: