データ型の比較規則
ここでは、Oracle Databaseが各データ型の値を比較する方法について説明します。
日時値
後の日付またはタイムスタンプは前の日付またはタイムスタンプよりも大きいとみなされます。たとえば、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 BY、GROUP 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組の文字を比較した結果を示します。通常、空白埋め比較と非空白埋め比較の結果は同じです。表に示されている最後の比較では、空白埋め比較と非空白埋め比較の違いが明確になっています。
| 空白埋め比較 | 非空白埋め比較 |
|---|---|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
データ・バインドされた照合
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では、列、表またはスキーマに対して大/小文字を区別しない照合を宣言できるため、表またはスキーマ内の列またはすべての文字の列は、常に大/小文字を区別しない方法で比較できます。
関連項目:
-
詳細な照合導出ルールや照合決定ルールなどのデータ・バインドされた照合アーキテクチャの詳細は、『Oracle Databaseグローバリゼーション・サポート・ガイド』を参照してください。
オブジェクト値
オブジェクト値は、MAPとORDERの2つの比較ファンクションのいずれかを使用して比較されます。どちらのファンクションでもオブジェクト型インスタンスは比較されますが、両者は別のものです。これらのファンクションは、他のオブジェクト型と比較するオブジェクト型の一部として指定される必要があります。
関連項目:
MAPメソッドとORDERメソッド、およびこれらのメソッドが戻す値の詳細は、「CREATE TYPE」を参照してください。
データ型の優先順位
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の暗黙的な変換のマトリックスについて示します。この表は、変換の方向または変換されるコンテキストにかかわらず、すべての可能な変換を示します。
セルの'X'は、最初の列とヘッダー行に示されたデータ型の暗黙的な変換を示します。
表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
LONGをINTERVALに直接は変換できませんが、TO_CHAR(interval)を使用してLONGをVARCHAR2に変換し、そのVARCHAR2の値をINTERVALに変換できます。
暗黙的なデータ型変換ルール
-
INSERTおよびUPDATE操作中に、Oracleは変更する列のデータ型に値を変換します。 -
SELECTFROM操作中に、Oracleは列からターゲット変数の型にデータを変換します。 -
数値を操作する際、Oracleは、通常、最大容量を確保するために精度および位取りを調整します。この場合、このような操作によって変換された数値データ型は、基礎となる表に含まれる数値データ型と異なることがあります。
-
数値と文字値を比較する場合、Oracleは文字データを数値に変換します。
-
文字値または
NUMBERの値と浮動小数点数の値の間では、変換が正確に行われない場合があります。これは、文字型およびNUMBERでは10進精度で数値が示されるのに対し、浮動小数点数では2進精度が使用されているためです。 -
CLOB値をVARCHAR2などの文字データ型に変換する場合、またはBLOBをRAWデータに変換する場合、変換するデータがターゲットのデータ型より大きいと、データベースはエラーを戻します。 -
タイムスタンプ値から
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/VARCHAR2とNCHAR/NVARCHAR2の演算処理では、OracleはNUMBERに変換します。 -
ほとんどのSQL文字ファンクションは、
CLOBをパラメータとして指定できます。また、OracleはCLOBと文字型間で暗黙的な変換を実行します。このため、CLOBを使用できないファンクションは、暗黙的な変換を使用してCLOBを受け入れます。このような場合、Oracleはファンクションが起動される前にCLOBをCHARまたはVARCHAR2に変換します。CLOBが4000バイトより大きい場合、Oracleは最初の4000バイトのみをCHARに変換します。 -
RAWまたはLONGRAWデータを文字データとの間で変換する場合、バイナリ・データは16進形式で表され、16進文字1文字ごとに4ビットのRAWデータを表します。詳細は、「RAWデータ型とLONG RAWデータ型」を参照してください。 -
CHARとVARCHAR2、NCHARとNVARCHAR2の比較では、異なる文字セットが必要な場合があります。このような場合のデフォルトの変換の方向は、データベース文字セットから各国語文字セットです。表2-9に、異なるキャラクタ・タイプ間での暗黙的な変換の方向を示します。
表2-9 異なるキャラクタ・タイプの変換方向
| SourceData型 | CHARへ | VARCHAR2へ | NCHARへ | NVARCHAR2へ |
|---|---|---|---|---|
|
CHARから |
-- |
|
|
|
|
VARCHAR2から |
|
-- |
|
|
|
NCHARから |
|
|
-- |
|
|
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-06がDATE値に暗黙的に変換されます。
SELECT last_name FROM employees WHERE hire_date = '24-JUN-06';
明示的なデータ変換
SQL変換ファンクションを使用すると、データ型の変換を明示的に指定できます。表2-10に、値をあるデータ型から別のデータ型に明示的に変換するSQLファンクションを示します。
Oracleが暗黙的なデータ型変換を行うことができる場合には、LONGおよびLONG RAWの値を指定できません。たとえば、ファンクションや演算子を含む式では、LONGとLONG 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から |
|
|
|
|
|
|
|
|
|
|
NUMBERから |
|
|
|
|
|
|
|
|
|
|
Datetime Intervalから |
|
|
|
|
|
|
|
|
|
|
RAWから |
|
|
|
|
|
|
|
|
|
|
ROWIDから |
|
|
|
|
|
|
|
|
|
|
LONG/LONG RAWから |
|
|
|
|
|
|
|
|
|
|
CLOB、NCLOB、BLOBから |
|
|
|
|
|
|
|
|
|
|
CLOB、NCLOB、BLOBから |
|
|
|
|
|
|
|
|
|
|
BINARY_FLOATから |
|
|
|
|
|
|
|
|
|
|
BINARY_DOUBLEから |
|
|
|
|
|
|
|
|
|
関連項目:
すべての明示的な変換ファンクションの詳細は、変換ファンクションを参照してください。
データ変換のセキュリティ上の考慮事項
暗黙的な変換、または書式モデルを指定しない明示的な変換のいずれかによって日時値がテキストに変換される場合、書式モデルはグローバリゼーション・セッション・パラメータの1つによって定義されます。ソースのデータ型に応じて、パラメータ名はNLS_DATE_FORMAT、NLS_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インジェクションが発生する可能性があります。
関連項目:
-
セッションのグローバリゼーション・パラメータの詳細は、『Oracle Databaseグローバリゼーション・サポート・ガイド』を参照してください。
-
書式モデルの詳細は、書式モデルを参照してください。