7 SQLセマンティクスとLOB
LOBに対しては様々なSQLセマンティクスがサポートされています。
この章で説明する技法では、一部の操作にLOB固有のAPIを使用するかわりに、SQLコードでLOBを直接使用できます。
内容は次のとおりです。
7.1 SQLでのLOBの使用について
SQL文字列演算子やSQLファンクションなど、SQL VARCHAR2
セマンティクスを使用して、CLOB
データ型とNCLOB
データ型にアクセスできます。(LENGTH
ファンクションは、CLOB
およびNCLOB
データ型と同様に、BLOB
データ型でも使用できます)。これらの技法にメリットがあるのは、次の場合です。
-
比較的小さいサイズ(約100KB以下)のLOBに対して操作を実行する場合。
-
データベースを
LONG
列からLOBデータ型に移行した後も、既存のPL/SQLアプリケーションのSQL文字列関数がすべて引き続き機能する場合。
次の場合には、SQLセマンティクスをお薦めしません。
-
ランダム・アクセスやピース単位のフェッチなど、高度な機能を使用する場合は、LOB APIを使用してください。
-
比較的大きいサイズ(1MBを超える場合)のLOBに対しSQLセマンティクスを使用して操作を実行すると、パフォーマンスが低下する可能性がある場合。この場合はLOB APIを使用することをお薦めします。
ノート:
SQLセマンティクスは、永続LOBおよび一時LOBとともに使用されます。(
BFILE
は読取り専用データ型のため、SQLセマンティクスはBFILE
列には適用されません。)
7.2 LOBでの使用がサポートされるSQLファンクションおよび演算子
引数としてVARCHAR2
列を取る多数のSQL演算子およびファンクションは、LOB列も受け入れます。
7.2.1 LOBのSQLファンクションおよび操作について
次のリストに、LOBでの使用がサポートされるSQLファンクションおよび演算子のカテゴリを示します。個々のファンクションおよび演算子の詳細は、表7-1を参照してください。
-
連結
-
比較
(一部の比較関数は、LOBでの使用がサポートされません。)
-
文字ファンクション
-
変換
(一部の変換関数は、LOBでの使用がサポートされません。)
次のカテゴリのファンクションは、LOBでの使用がサポートされません。
-
集計ファンクション
事前定義済の集計関数はLOBでの使用がサポートされませんが、ユーザー定義の集計関数を作成してLOBで使用できることに注意してください。
-
Unicodeファンクション
各ファンクションと演算子の詳細は表7-1のとおりです。この表には、オペランドまたは引数としてVARCHAR2
型を取る、またはVARCHAR2
値を戻すSQL演算子およびファンクションがリストされています。「SQL」列は、CLOB
データ型とNCLOB
データ型でサポートされるファンクションと演算子を示します。(LENGTH
ファンクションは、BLOB
データ型でもサポートされます。)
Oracle Databaseで提供されるDBMS_LOB
PL/SQLパッケージでは、表7-1の「PL/SQL」列に示すように、ほとんどのファンクションでLOBの使用がサポートされます。
ノート:
表7-1の「SQL」列が「いいえ」となっている演算子およびファンクションは、PL/SQLブロックで使用されるSQL問合せには機能しません。ただし、一部の演算子およびファンクションの場合は、PL/SQLコードでの直接使用がサポートされます。
関連項目:
ユーザー定義の集計関数の詳細は、Oracle Databaseデータ・カートリッジ開発者ガイドを参照してください
7.2.2 CLOB型からCHAR型への暗黙的な変換
表7-1の「SQL」列または「PL/SQL」列が「CNV」となっているファンクションは、CLOB
からVARCHAR2
などの文字データ型に変換することで実行されます。SQL環境ではCLOB
の最初の4KBのみが変換されて操作に使用され、PL/SQL環境ではCLOB
の最初の32KBのみが変換されて操作に使用されます。
表7-1 LOBでのSQL VARCHAR2ファンクションおよび演算子
カテゴリ | 演算子およびファンクション | SQLの例/コメント | SQL | PL/SQL |
---|---|---|---|---|
連結 |
|
|
はい |
はい |
比較 |
|
|
いいえ |
はい |
比較 |
|
|
いいえ |
はい |
比較 |
|
|
いいえ |
該当なし |
比較 |
|
|
いいえ |
はい |
比較 |
|
|
はい |
はい |
比較 |
|
|
はい |
はい |
文字ファンクション |
|
|
CNV |
CNV |
文字ファンクション |
|
|
はい |
はい |
文字ファンクション |
|
|
はい |
はい |
文字ファンクション |
|
|
はい |
はい |
文字ファンクション |
|
|
はい |
はい |
文字ファンクション |
|
|
CNV |
CNV |
文字ファンクション |
|
|
はい |
はい |
文字ファンクション |
|
|
CNV |
CNV |
文字ファンクション |
|
|
CNV |
CNV |
文字ファンクション |
|
|
はい |
はい |
文字ファンクション |
|
|
はい |
はい |
文字ファンクション |
|
|
CNV |
CNV |
文字ファンクション |
|
この3つのファンクションは、シングルバイト文字セットを使用する |
はい |
はい |
文字関数: 正規表現 |
|
このファンクションは、文字列内でパターンを検索します。このファンクションを問合せの 関連項目:
|
はい |
はい |
文字関数: 正規表現 |
|
このファンクションは文字列内でパターンを検索し、そのパターンの各出現箇所を指定したパターンで置き換えます。 |
はい |
はい |
文字関数: 正規表現 |
|
このファンクションは、指定した正規表現パターンの出現箇所を文字列内で検索します。検索する出現箇所と検索開始位置を指定します。文字列内で一致が検出された位置を示す整数が戻されます。 |
はい |
はい |
文字関数: 正規表現 |
|
このファンクションは、指定した正規表現パターンと一致する実際の部分文字列を戻します。 |
はい |
はい |
変換 |
|
|
CNV |
CNV |
変換 |
|
|
CNV |
CNV |
変換 |
|
Unicode文字の引数で有効です。入力と同じ文字セットで分解後にUnicode文字列を戻します。o-umlautコード・ポイントは、oのコード・ポイントの後にウムラウトのコード・ポイントを続けて戻されます。 |
CNV |
CNV |
変換 |
|
|
いいえ |
CNV |
変換 |
|
|
はい |
CNV |
変換 |
|
|
CNV |
CNV |
変換 |
|
|
CNV |
CNV |
変換 |
|
|
いいえ |
CNV |
変換 |
|
|
CNV |
CNV |
変換 |
|
|
はい |
はい |
変換 |
|
|
はい |
はい |
変換 |
|
|
該当なし |
該当なし |
変換 |
|
|
はい |
はい |
変換 |
|
|
はい |
はい |
集計ファンクション |
|
|
いいえ |
該当なし |
集計ファンクション |
|
|
いいえ |
該当なし |
集計ファンクション |
|
|
いいえ |
該当なし |
その他の関数 |
|
|
いいえ |
CNV |
その他の関数 |
|
|
CNV |
CNV |
その他の関数 |
|
|
はい |
はい |
その他の関数 |
|
|
いいえ |
該当なし |
その他の関数 |
|
|
いいえ |
該当なし |
Unicode |
|
これらのファンクションは、UCS2コード・ポイント・セマンティクスを使用します。 |
いいえ |
CNV |
Unicode |
|
これらのファンクションは、UCS4コード・ポイント・セマンティクスを使用します。 |
いいえ |
CNV |
Unicode |
|
これらのファンクションは、完全なキャラクタ・セマンティクスを使用します。 |
いいえ |
CNV |
7.2.3 セッション照合設定に従わないCLOBおよびNCLOB
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オプションをサポートします。関連項目:
NLS_COMP
の詳細は、Oracle Databaseリファレンスを参照してください。
7.2.4 Unicodeのサポート
Unicodeをサポートするために特定のファンクションにはバリエーションが提供されています。
Unicodeサポート用に、INSTR
、SUBSTR
、LENGTH
およびLIKE
ファンクションのバリエーションが用意されています。(これらのバリエーションの場合は、表7-1の「カテゴリ」列が「Unicode」となっています。)
関連項目:
-
Unicodeファンクションの使用に関する詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください
7.2.5 コードポイント・セマンティクス
表7-1に示した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の既存のデータにのみ適用されます。書込み操作で受け取るバッファの開始と終了が完全な文字になっていることは、ユーザーが確認する必要があります。
-
7.2.6 LOBのSQLセマンティクスの戻り値
LOBまたはVARCHAR2
を取るファンクションまたは演算子の戻り型は、そのファンクションや演算子に渡された引数のデータ型と同じです。
CONCAT
など、2つ以上の引数を取るファンクションは、1つ以上の引数がLOBであればLOBデータ型を戻します。たとえば、CONCAT(CLOB, VARCHAR2)
はCLOB
を戻します。
関連項目:
CONCATファンクションと連結演算子(||)の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
LOBインスタンスは、常にLOBロケータを介してアクセスおよび操作されます。これは戻り値の場合も同じで、SQLファンクションと演算子は、戻り値がLOBインスタンスであればLOBロケータを戻します。
SQLファンクションから戻されるLOBインスタンスは、すべて一時LOBインスタンスです。SQLファンクションが問合せのSELECT
構文のリストに使用されている場合にも、そのファンクションにより表内のLOBインスタンス(永続LOB)が変更されることはありません。
7.3 SQLでのLOBデータ型の暗黙的な変換
一部のLOBデータ型では暗黙的な変換がサポートされ、型間の割当てやパラメータの受渡しなどの操作に使用できます。このような変換はSQLレイヤーで処理され、LOB型を使用するすべてのクライアント・インタフェースで実行できます。
7.3.1 SQLでのCLOBデータ型とNCLOBデータ型の間の暗黙的な変換
データベースでは、CLOB
データ型とNCLOB
データ型の間で、型間の割当てや型間でのパラメータの受渡しなどの操作を実行できます。これらの型間での暗黙的な変換は、文字セット・フォーマットなどのプロパティを保つために必要に応じて実行されます。
暗黙的な変換が発生すると、必要に応じて変換元LOBの各文字が変換先LOBの文字セットに変更されることに注意してください。この場合にデータのサイズが大きいと、パフォーマンスが低下する可能性があります。変換先と変換元の文字セットが同一であれば、パフォーマンスは低下しません。
CLOB
型とNCLOB
型の間で暗黙的な変換が発生した後、変換先LOBが一時LOBとして暗黙的に作成されます。この新しい一時LOBは、変換元LOBに依存しません。SELECT
文で定義操作の一部として暗黙的な変換が発生する場合、次の例に示すように、変換先LOBの変更はそのLOBが選択された表内の永続LOBには影響しません。
SQL> -- check lob length before update SQL> select dbms_lob.getlength(ad_sourcetext) from Print_media 2 where product_id=3106 and ad_id = 13001; DBMS_LOB.GETLENGTH(AD_SOURCETEXT) --------------------------------- 205 SQL> SQL> declare 2 clob1 clob; 3 amt number:=10; 4 BEGIN 5 -- select a clob column into a clob, no implicit convesion 6 SELECT ad_sourcetext INTO clob1 FROM Print_media 7 WHERE product_id=3106 and ad_id=13001 FOR UPDATE; 8 9 dbms_lob.trim(clob1, amt); -- Trim the selected lob to 10 bytes 10 END; 11 / PL/SQL procedure successfully completed. SQL> -- Modification is performed on clob1 which points to the SQL> -- clob column in the table SQL> select dbms_lob.getlength(ad_sourcetext) from Print_media 2 where product_id=3106 and ad_id = 13001; DBMS_LOB.GETLENGTH(AD_SOURCETEXT) --------------------------------- 10 SQL> SQL> rollback; Rollback complete. SQL> -- check lob length before update SQL> select dbms_lob.getlength(ad_sourcetext) from Print_media 2 where product_id=3106 and ad_id = 13001; DBMS_LOB.GETLENGTH(AD_SOURCETEXT) --------------------------------- 205 SQL> SQL> declare 2 nclob1 nclob; 3 amt number:=10; 4 BEGIN 5 6 -- select a clob column into a nclob, implicit conversion occurs 7 SELECT ad_sourcetext INTO nclob1 FROM Print_media 8 WHERE product_id=3106 and ad_id=13001 FOR UPDATE; 9 10 dbms_lob.trim(nclob1, amt); -- Trim the selected lob to 10 bytes 11 END; 12 / PL/SQL procedure successfully completed. SQL> -- Modification to nclob1 does not affect the clob in the table, SQL> -- because nclob1 is a independent temporary LOB SQL> select dbms_lob.getlength(ad_sourcetext) from Print_media 2 where product_id=3106 and ad_id = 13001; DBMS_LOB.GETLENGTH(AD_SOURCETEXT) --------------------------------- 205
関連項目:
-
CLOB
型とVARCHAR2
型の間の暗黙的な変換に対するPL/SQLセマンティクスのサポートの詳細は、"「CLOBとVARCHAR2の間の暗黙的な変換」を参照してください。 -
BILE
からLOBをロードする際の文字セットの暗黙的な変換の詳細は、「LOBを使用した文字セットの暗黙的な変換」を参照してください。 -
すべてのデータ型に対してサポートされる暗黙的な変換の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
7.4 SQLでサポートされないLOBの使用
表7-2に、LOB列でサポートされないSQL操作を示します。
表7-2 SQLでサポートされないLOBの使用
サポートされないSQL操作 | サポートされない使用方法の例 |
---|---|
|
|
|
|
|
|
( |
|
問合せの結合 |
|
索引列 |
|
7.5 LOBのVARCHAR2およびRAWセマンティクス
VARCHAR2
データ型とRAW
データ型で使用されるセマンティクスは、LOBにも適用されます。
7.5.1 LOBに対するVARCHAR2およびRAWセマンティクスについて
VARCHAR2
データ型とRAW
データ型で使用される次のセマンティクスは、LOBにも適用されます。
-
CLOB
でのCHAR
バッファの定義CLOB
列に対してVARCHAR2
を、BLOB
列に対してRAW
を定義できます。また、VARCHAR2
列とRAW
列に対してCLOB
型とBLOB
型を定義できます。 -
CHAR
バッファまたはVARCHAR2
へのCLOB
列の選択CLOB
列がVARCHAR2
変数に選択されると、CLOB
列に格納されたデータが取り出され、CHAR
バッファに格納されます。バッファがすべてのCLOB
データを格納できるほど大きくない場合、切捨てエラーが発生し、データはバッファに書き込まれません。SELECT
操作が正常に完了すると、VARCHAR2
変数は正規の文字バッファとして保持されます。対照的に、
CLOB
列がローカルのCLOB
変数に選択された場合、CLOB
ロケータがフェッチされます。 -
RAW
へのBLOB
列の選択BLOB
列がRAW
変数に選択されると、BLOB
データがRAW
バッファにコピーされます。BLOB
のサイズがバッファのサイズを超える場合は、切捨てエラーが発生し、データはバッファに書き込まれません。
7.5.2 SQLファンクションから戻されるLOB
SQLファンクションからLOBが戻される場合、結果として戻されるのは一時LOBです。
アプリケーションでは、一時LOBを、SELECT
操作によって戻されたデータ用のローカル記憶域として、次のように参照する必要があります。
-
PL/SQLでは、一時LOBの存続期間は、他のローカルPL/SQLプログラム変数と同じです。後続のSQLまたはPL/SQLの
VARCHAR2
ファンクションまたは問合せに、PL/SQLのローカル変数として渡すことができます。一時LOBは、プログラム・ブロックの最後にスコープから抜け、LOBが解放されます。これらは、PL/SQLのVARCHAR2
変数と同じセマンティクスです。ただし、随時DBMS_LOB.FREETEMPORARY()
コールを使用して、ローカルの一時LOBが使用したリソースを解放できます。ノート:
SQL文によりLOBが戻される場合、またはLOBがPL/SQLファンクションまたはプロシージャの
OUT
パラメータである場合は、一時LOBであるかどうかテストし、一時LOBである場合には操作完了後に解放する必要があります。 -
OCIでは、SQL問合せから戻された一時LOBは、常にセッションの存続期間中は存続します。ただし、ユーザー定義の存続期間が存在する場合、一時LOBはユーザー定義の存続期間中に存続します。
警告:
一時表領域が、プログラムの問合せから戻されるすべての一時LOB結果を格納するために十分大きいことを確認してください。
次に、CLOB
列をVARCHAR2
に選択し、結果を、宣言したサイズのCHAR
バッファとして戻す例を示します。
DECLARE vc1 VARCHAR2(32000); lb1 CLOB; lb2 CLOB; BEGIN SELECT clobCol1 INTO vc1 FROM tab WHERE colID=1; -- lb1 is a temporary LOB SELECT clobCol2 || clobCol3 INTO lb1 FROM tab WHERE colID=2; lb2 := vc1|| lb1; -- lb2 is a still temporary LOB, so the persistent data in the database -- is not modified. An update is necessary to modify the table data. UPDATE tab SET clobCol1 = lb2 WHERE colID = 1; DBMS_LOB.FREETEMPORARY(lb2); -- Free up the space taken by lb2
<... some more queries ...>
END; -- at the end of the block, lb1 is automatically freed
7.5.3 VARCHAR2とCLOBでのIS NULLとIS NOT NULLの使用方法
LOB列でIS NULL
演算子とIS NOT NULL
演算子を使用できます。
LOBで使用すると、IS NULL
およびIS NOT NULL
演算子によってLOBロケータが行に格納されているかどうかが判別されます。
7.5.4 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;
7.6 リモートLOBおよびBFILE用の組込みファンクション
関連項目:
リモートのLOB
とBFILE
でサポートされる組込みファンクションおよびユーザー定義ファンクションの詳細は、「リモートLOBおよびBFILE用の組込みファンクション」を参照してください