LOBに対しては様々なSQLセマンティクスがサポートされています。
この章で説明する技法では、一部の操作にLOB固有のAPIを使用するかわりに、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
列には適用されません。)
引数としてVARCHAR2
列を取る多数のSQL演算子およびファンクションは、LOB列も受け入れます。
次のリストに、LOBでの使用がサポートされるSQLファンクションおよび演算子のカテゴリを示します。個々のファンクションおよび演算子の詳細は、表16-1を参照してください。
連結
比較
(一部の比較関数は、LOBでの使用がサポートされません。)
文字関数
変換
(一部の変換関数は、LOBでの使用がサポートされません。)
次のカテゴリのファンクションは、LOBでの使用がサポートされません。
集計関数
事前定義済の集計関数はLOBでの使用がサポートされませんが、ユーザー定義の集計関数を作成してLOBで使用できることに注意してください。ユーザー定義の集計関数の詳細は、『Oracle Databaseデータ・カートリッジ開発者ガイド』を参照してください。
Unicodeファンクション
各ファンクションと演算子の詳細は表16-1のとおりです。この表には、オペランドまたは引数としてVARCHAR2
型を取る、またはVARCHAR2
値を戻すSQL演算子およびファンクションがリストされています。「SQL」列は、CLOB
データ型とNCLOB
データ型でサポートされるファンクションと演算子を示します。(LENGTH
ファンクションは、BLOB
データ型でもサポートされます。)
Oracle Databaseで提供されるDBMS_LOB
PL/SQLパッケージでは、表16-1の「PL/SQL」列に示すように、ほとんどのファンクションでLOBの使用がサポートされます。
注意:
表16-1の「SQL」列が「いいえ」となっている演算子およびファンクションは、PL/SQLブロックで使用されるSQL問合せには機能しません。ただし、一部の演算子およびファンクションの場合は、PL/SQLコードでの直接使用がサポートされます。
表16-1の「SQL」列または「PL/SQL」列が「CNV」となっているファンクションは、CLOB
からVARCHAR2
などの文字データ型に変換することで実行されます。SQL環境ではCLOB
の最初の4KBのみが変換されて操作に使用され、PL/SQL環境ではCLOB
の最初の32KBのみが変換されて操作に使用されます。
表16-1 LOBでのSQL VARCHAR2ファンクションおよび演算子
カテゴリ | 演算子およびファンクション | SQLの例/コメント | SQL | PL/SQL |
---|---|---|---|---|
連結 |
|
|
はい |
はい |
比較 |
|
|
いいえ |
はい |
比較 |
|
|
いいえ |
はい |
比較 |
|
|
いいえ |
利用不可 |
比較 |
|
|
いいえ |
はい |
比較 |
|
|
はい |
はい |
比較 |
|
|
はい |
はい |
文字関数 |
|
|
CNV |
CNV |
文字関数 |
|
|
はい |
はい |
文字関数 |
|
|
はい |
はい |
文字関数 |
|
|
はい |
はい |
文字関数 |
|
|
はい |
はい |
文字関数 |
|
|
CNV |
CNV |
文字関数 |
|
|
はい |
はい |
文字関数 |
|
|
CNV |
CNV |
文字関数 |
|
|
CNV |
CNV |
文字関数 |
|
|
はい |
はい |
文字関数 |
|
|
はい |
はい |
文字関数 |
|
|
CNV |
CNV |
文字関数 |
|
この3つのファンクションは、シングルバイト・キャラクタ・セットを使用する |
はい |
はい |
文字関数: 正規表現 |
|
このファンクションは、文字列内でパターンを検索します。このファンクションを問合せの 正規表現用のSQLファンクションの構文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。データベースでの正規表現の使用方法は、『Oracle Database開発ガイド』を参照してください。 |
はい |
はい |
文字関数: 正規表現 |
|
このファンクションは文字列内でパターンを検索し、そのパターンの各出現箇所を指定したパターンで置き換えます。 |
はい |
はい |
文字関数: 正規表現 |
|
このファンクションは、指定した正規表現パターンの出現箇所を文字列内で検索します。検索する出現箇所と検索開始位置を指定します。文字列内で一致が検出された位置を示す整数が戻されます。 |
はい |
はい |
文字関数: 正規表現 |
|
このファンクションは、指定した正規表現パターンと一致する実際の部分文字列を戻します。 |
はい |
はい |
変換 |
|
|
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 |
CLOB
およびNCLOB
に対して、最初にVARCHAR2
またはNVARCHAR2
に変換せずに操作する標準の演算子(表16-1のSQL列またはPL/SQL列で「はい」とマークされているもの)は、、REGEXP
ファンクション以外は、言語的に動作しません。文字データのバイナリ比較は、NLS_COMP
およびNLS_SORT
パラメータ設定に関係なく実行されます。
次のREGEXP
ファンクションは、CLOB
またはNCLOB
データが渡され、言語的比較がVARCHAR2
およびNVARCHAR2
の値の比較と類似している場合に、例外です。
REGEXP_LIKE
REGEXP_REPLACE
REGEXP_INSTR
REGEXP_SUBSTR
REGEXP_COUNT
Unicodeをサポートするために特定のファンクションにはバリエーションが提供されています。
Unicodeサポート用に、INSTR
、SUBSTR
、LENGTH
およびLIKE
ファンクションのバリエーションが用意されています。(これらのバリエーションの場合は、表16-1の「カテゴリ」列が「Unicode」となっています。)
関連項目:
『Oracle Databaseグローバリゼーション・サポート・ガイド』
『Oracle Database開発ガイド』
『Oracle Database SQL言語リファレンス』
Unicodeファンクションの使用に関する詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください
表16-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文字に分かれないようにする必要があります。これは、文字の部分的な読取りや書込みを回避するためです。
10gから、Oracle Databaseではこのような場合に、読取り/書込み境界で半サロゲート・ペアを検出できます。読取りの場合、オフセットと量が適宜調整されて半分の文字が返されないようになり、その場合、戻される量は要求より少なくなります。書込みの場合はエラーが発生し、宛先のCLOB
またはNCLOB
に部分的な文字を上書きして既存のデータを破損しないようにします。
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)が変更されることはありません。
一部のLOBデータ型では暗黙的な変換がサポートされ、型間の割当てやパラメータの受渡しなどの操作に使用できます。このような変換はSQLレイヤーで処理され、LOB型を使用するすべてのクライアント・インタフェースで実行できます。
データベースでは、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言語リファレンス』を参照してください。
表16-2に、LOB列でサポートされないSQL操作を示します。
表16-2 SQLでサポートされないLOBの使用
サポートされないSQL操作 | サポートされない使用方法の例 |
---|---|
|
|
|
|
|
|
( |
|
問合せの結合 |
|
列の索引付け |
|
VARCHAR2
データ型とRAW
データ型で使用されるセマンティクスは、LOBにも適用されます。
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
のサイズがバッファのサイズを超える場合は、切捨てエラーが発生し、データはバッファに書き込まれません。
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
LOB列でIS NULL
演算子とIS NOT NULL
演算子を使用できます。
LOBで使用すると、IS NULL
およびIS NOT NULL
演算子によってLOBロケータが行に格納されているかどうかが判別されます。
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でないかぎり、ローカルLOBとBFILE
でサポートされるすべてのSQL組込みファンクションおよびユーザー定義型ファンクションは、リモートLOBとBFILE
でもサポートされます。これには、リモートの永続および一時LOBとBFILE
用のファンクションも含みます。
リモート・サイトで実行されるSQL組込みファンクションは、SELECT
、INSERT
、UPDATE
およびDELETE
と同様に、SQL文内に含めることができます。次に例を示します。
SELECT LENGTH(ad_sourcetext) FROM print_media@remote_site -- CLOB SELECT LENGTH(ad_fltextn) FROM print_media@remote_site; -- NCLOB SELECT LENGTH(ad_composite) FROM print_media@remote_site; -- BLOB SELECT product_id from print_media@remote_site WHERE LENGTH(ad_sourcetext) > 3; UPDATE print_media@remote_site SET product_id = 2 WHERE LENGTH(ad_sourcetext) > 3; SELECT TO_CHAR(foo@dbs2(...)) FROM dual@dbs2; -- where foo@dbs2 returns a temporary LOB
SQLファンクションは、次のカテゴリに分類されます(ただし、必ずしもこれに限定されません)。
LOBでサポートされていないSQLファンクションは次のとおりです。
これらの関数は、CLOB
にのみ関連します。たとえばDECODE
などです。
これらのファンクションは、ローカルのLOBでサポートされないため、リモートのLOBではサポートされません。
1つのLOB引数のみ(その他の引数は他のデータ型)を受け入れ、LOBを戻さないファンクション:
これらのファンクションは、CLOB
、NCLOB
およびBLOB
のみに該当します。たとえばLENGTH
であり、これはサポートされています。次に例を示します。
SELECT LENGTH(ad_composite) FROM print_media@remote_site; SELECT LENGTH(ad_header.logo) FROM print_media@remote_site; -- LOB in object SELECT product_id from print_media@remote_site WHERE LENGTH(ad_sourcetext) > 3;
LOBを戻すファンクション:
これらのファンクションは、すべてCLOBおよびNCLOBのみに該当します。これらのファンクションは、元のLOBを戻すか、または一時LOBを生成する場合があります。これらのファンクションは、ローカル・サイトに戻される結果がLOBでないかぎり、リモート・サイトで実行できます。
一時LOBを戻すファンクションには、REPLACE
、SUBSTR
、CONCAT
、||、TRIM
、LTRIM
、RTRIM
、LOWER
、UPPER
、NLS_LOWER
、NLS_UPPER
、LPAD
、およびRPAD
があります。
元のLOBロケータを戻すファンクションには、NVL
、DECODE
およびCASE
があります。現在、DECODE
およびCASE
ではLOBの操作はサポートされていませんが、他のデータ型を操作でき、LOBを戻すことができます。
たとえば、次の文がサポートされています。
SELECT TO_CHAR(CONCAT(ad_sourcetext, ad_sourcetext)) FROM print_media@remote_site; SELECT TO_CHAR(SUBSTR(ad_fltextnfs, 1, 3)) FROM print_media@remote_site;
しかし、次の文はサポートされていません。
SELECT CONCAT(ad_sourcetext, ad_sourcetext) FROM print_media@remote_site; SELECT SUBSTR(ad_sourcetext, 1, 3) FROM print_media@remote_site;
1つ以上のLOB引数を受け入れるファンクション。
これらには、INSTR
、LIKE
、REPLACE
、CONCAT
、||、SUBSTR
、TRIM
、LTRIM
、RTRIM
、LPAD
およびRPAD
があります。これらのファンクションは、すべてCLOB
およびNCLOB
のみに該当します。
すべてのLOBが同じdblink
内にあり、戻される値がLOBでない場合にのみ、これらのファンクションがサポートされます。たとえば、次がサポートされています。
SELECT TO_CHAR(CONCAT(ad_sourcetext, ad_sourcetext)) FROM print_media@remote_site; -- CLOB SELECT TO_CHAR(CONCAT(ad_fltextn, ad_fltextn)) FROM print_media@remote_site; -- NCLOB
しかし、次はサポートされていません。
SELECT TO_CHAR(CONCAT(a.ad_sourcetext, b.ad_sourcetext)) FROM print_media@db1 a, print_media@db2 b WHERE a.product_id = b.product_id;