LOBに対するSQL操作のセマンティクスの詳細

6.2 LOBに対するSQL操作のセマンティクスの詳細

この項では、LOBに対するSQL操作のセマンティクスの詳細を説明します。

LOBに対するSQL操作用の戻りデータ型
LOBに対するSQLファンクションの戻りデータ型は、入力パラメータに依存します。
NULLとEMPTY LOB: LOBとVARCHAR2のセマンティクスの違い
VARCHAR2データ型の場合、長さゼロの文字列は列のNULL値と区別できません。
WHERE句のLOBでの使用方法
WHERE句の述語では、LOB値を比較する関数を除き、LOBを引数として使用するSQL関数を使用できます。
CLOBおよびNCLOBはセッション照合設定に従わない
CLOBおよびNCLOBの様々な演算子について説明し、この項のLOBに関するVARCHAR2およびNVARCHAR2変数の操作を比較します。
コードポイント・セマンティクス
INSTR、SUBSTR、LENGTHおよびLIKE関数のコードポイント・セマンティクスは、ファンクションに渡される引数のデータ型に応じて異なります。

6.2.1 LOBに対するSQL操作の戻りデータ型

LOBに対するSQL関数の戻りデータ型は、入力パラメータによって異なります。

LOBまたはVARCHAR2を取るファンクションまたは演算子の戻り型は、そのファンクションや演算子に渡された引数のデータ型と同じです。CONCATなど、2つ以上の引数を取るファンクションは、1つ以上の引数がLOBであればLOBデータ型を戻します。

例6-1 CLOBを返すCONCAT関数

CONCAT(CLOB, VARCHAR2)は、CLOBを返します。

SQLファンクションから戻されるLOBインスタンスは、すべて一時LOBインスタンスです。SQLファンクションが問合せのSELECT構文のリストに使用されている場合にも、そのファンクションにより表内のLOBインスタンス(永続LOB)が変更されることはありません。

親トピック: LOBに対するSQL操作のセマンティクスの詳細

6.2.2 NULLとEMPTY LOB: LOBとVARCHAR2のセマンティクスの違い

VARCHAR2データ型の場合、長さゼロの文字列は列のNULL値と区別できません。

LOBデータ型の列には、次の3つの状態が考えられます。

NULL: 列にLOBロケータがないことを意味します。
長さゼロの値: これを実現するには、EMPTY LOBを列に挿入するか、DBMS_LOB.TRIM()などのAPIを使用して長さをゼロに切り捨てます。いずれの場合も、列には有効なLOBロケータがありますが、LOB値の長さはゼロです。
ゼロ以外の長さの値。

この違いのため、LENGTH関数は、渡される引数がLOBであるか文字列であるかに応じて異なります。

長さがゼロの文字列の場合、LENGTH関数はNULLを返します。
長さ0(ゼロ)のCLOBの場合、またはEMPTY_CLOB()の戻り値のように空のロケータの場合、LENGTHおよびDBMS_LOB.GETLENGTHファンクションは0(ゼロ)を戻します。

同様に、LOBで使用すると、IS NULLおよびIS NOT NULL演算子によってLOBロケータが行に格納されているかどうかが判別されます。

IS NULL関数に長さ0 (ゼロ)の初期化済LOBを渡すと、FALSEが返されます。これらのセマンティクスは、SQL 92標準に準拠しています。
IS NULL関数に長さ0(ゼロ)のVARCHAR2を渡すと、TRUEが戻されます。

親トピック: LOBに対するSQL操作のセマンティクスの詳細

6.2.3 LOBでのWHERE句の使用方法

WHERE句の条件には、LOB値を比較するファンクションを除き、引数としてLOBを取るSQLファンクションを使用できます。

たとえば、WHERE句の条件にLENGTHファンクションを含めることができます。

CREATE TABLE t (n NUMBER, c CLOB);
INSERT INTO t VALUES (1, 'abc');

SELECT * FROM t WHERE c IS NOT NULL;
SELECT * FROM t WHERE LENGTH(c) > 0;
SELECT * FROM t WHERE c LIKE '%a%';
SELECT * FROM t WHERE SUBSTR(c, 1, 2) LIKE '%b%';
SELECT * FROM t WHERE INSTR(c, 'b') = 2;

親トピック: LOBに対するSQL操作のセマンティクスの詳細

6.2.4 セッション照合設定に従わないCLOBおよびNCLOB

CLOBおよびNCLOBの様々な演算子について学習し、この項のLOBに関するVARCHAR2およびNVARCHAR2変数の操作を比較します。

CLOBおよびNCLOBを最初にVARCHAR2またはNVARCHAR2に変換せずに操作する標準演算子は、表7-1のSQL列またはPL/SQL列で「はい」とマークされます。これらの演算子は、REGEXP関数を除き、言語的には動作しません。文字データのバイナリ比較は、NLS_COMPおよびNLS_SORTパラメータ設定に関係なく実行されます。

次のREGEXPファンクションは、CLOBまたはNCLOBデータが渡され、言語的比較がVARCHAR2およびNVARCHAR2の値の比較と類似している場合に、例外です。

REGEXP_LIKE
REGEXP_REPLACE
REGEXP_INSTR
REGEXP_SUBSTR
REGEXP_COUNT

ノート:

CLOBとNCLOBはデフォルトのUSING NLS_COMPオプションをサポートします。

6.2.5 コードポイント・セマンティクス

INSTR、SUBSTR、LENGTHおよびLIKE関数のコードポイント・セマンティクスは、ファンクションに渡される引数のデータ型に応じて異なります。

これらのファンクションでは、次のように、引数がVARCHAR2型かCLOB型かに応じて、異なるコードポイント・セマンティクスが使用されます。

引数がCLOBの場合は、すべての文字セットにUCS2コードポイント・セマンティクスが使用されます。
引数がVARCHAR2などのキャラクタ・タイプの場合は、指定の文字セットにデフォルトのコードポイント・セマンティクスが使用されます。
- AL16UTF16およびUTF8文字セットにはUCS2コードポイント・セマンティクスが使用されます。
- AL32UTF8など、他のすべての文字セットにはUCS4コードポイント・セマンティクスが使用されます。
文字データをCLOBまたはNCLOBに格納している場合、CLOBやNCLOBに対してデータの読取りまたは書込みを行うすべてのAPIの量パラメータとオフセット・パラメータが、UCS2コードポイントで指定されることに注意してください。一部の文字セットには、完全な1文字が、サロゲート・ペアと呼ばれる1つ以上のUCS2コードポイントで構成されるものがあります。この場合、指定した量またはオフセットが、完全な1文字に分かれないようにする必要があります。これは、文字の部分的な読取りや書込みを回避するためです。
Oracle Databaseは、SQL関数の場合やLOB APIを介して読取り/書込みを行う場合に、読取りまたは書込み境界で半サロゲート・ペアを検出するのに役立ちます。動作は次のとおりです。
- 開始オフセットがサロゲート・ペアの途中にある場合、読取り操作と書込み操作の両方でエラーが発生します。
- 読取り量で文字の一部のみが読み取られる場合は、1ずつ増分または減分して完全な文字を読み取ります。
  
  ノート:
  
  出力量が入力量と異なる場合があります。
- 書込み量によって文字の一部が上書きされる場合は、宛先CLOBまたはNCLOBの文字の一部が上書きされて既存のデータが破損しないようにするためにエラーが発生します。
  
  ノート:
  
  このチェックは、CLOBまたはNCLOBの既存のデータにのみ適用されます。書込み操作で受け取るバッファの開始と終了が完全な文字になっていることは、ユーザーが確認する必要があります。

親トピック: LOBに対するSQL操作のセマンティクスの詳細