ヘッダーをスキップ
Oracle Database グローバリゼーション・サポート・ガイド
10g リリース2(10.2)
B19218-02
  目次へ
目次
索引へ
索引

前へ
前へ
次へ
次へ
 

9 グローバル環境でのSQLとPL/SQLのプログラミング

この章では、グローバリゼーション・サポート環境でのSQLプログラミングに役立つ情報について説明します。 この章の内容は、次のとおりです。

オプションのNLSパラメータを伴うロケール依存のSQL関数

動作をグローバリゼーション・サポートの規則に依存しているすべてのSQL関数で、NLSパラメータを指定できます。これらの関数は、次のとおりです。

TO_CHAR
TO_DATE
TO_NUMBER
NLS_UPPER
NLS_LOWER
NLS_INITCAP
NLSSORT

これらの関数のNLSパラメータを明示的に指定すると、セッションのNLSパラメータに依存せずに関数を評価できます。この機能は、数字や日付が文字列リテラルとして含まれているSQL文で重要となる場合があります。

たとえば、次の問合せは、日付に対する言語がAMERICANに指定されている場合、正しく評価されます。

SELECT last_name FROM employees WHERE hire_date > '01-JAN-1999';

このような問合せを現行の日付言語に依存しないようにするには、次のような文を使用します。

SELECT last_name FROM employees
     WHERE hire_date > TO_DATE('01-JAN-1999','DD-MON-YYYY',
     'NLS_DATE_LANGUAGE = AMERICAN');

このようにして、セッションの言語に依存しないSQL文を、必要に応じて定義できます。文字列リテラルが、ビュー、CHECK制約またはトリガー内のSQL文にある場合には、このような文の指定が必要となる場合があります。


注意:

ロケール依存のSQL関数でオプションのNLSパラメータを明示的に指定する必要があるのは、セッションのNLSパラメータ値に依存させないことが必要となるSQL文のみです。通常、SQL関数でNLSパラメータにセッションのデフォルト値を使用するほうが、パフォーマンスが改善されます。

すべての文字関数は、シングルバイトとマルチバイトの両方の文字をサポートします。単位を明示的に示した場合を除いて、文字関数は、バイト単位ではなく文字単位で動作します。

これ以降の内容は、次のとおりです。

SQL関数のNLSパラメータのデフォルト値

SQL関数でビューやトリガーが評価される場合、NLS関数パラメータには現行のセッションからのデフォルト値が使用されます。SQL関数でCHECK制約が評価される場合は、データベースの作成時にNLSパラメータに指定されたデフォルト値が使用されます。

SQL関数のNLSパラメータの指定

NLSパラメータは、SQL関数で次のように指定できます。

'parameter = value'

次に例を示します。

'NLS_DATE_LANGUAGE = AMERICAN'

SQL関数では、次のNLSパラメータを指定できます。

NLS_DATE_LANGUAGE
NLS_NUMERIC_CHARACTERS
NLS_CURRENCY
NLS_ISO_CURRENCY
NLS_DUAL_CURRENCY
NLS_CALENDAR
NLS_SORT

表9-1に、特定のSQL関数に有効なNLSパラメータを示します。

表9-1 SQL関数と有効なNLSパラメータ

SQL関数 有効なNLSパラメータ

TO_DATE

NLS_DATE_LANGUAGE NLS_CALENDAR

TO_NUMBER

NLS_NUMERIC_CHARACTERS NLS_CURRENCY NLS_DUAL_CURRENCY NLS_ISO_CURRENCY

TO_CHAR

NLS_DATE_LANGUAGE NLS_NUMERIC_CHARACTERS NLS_CURRENCY NLS_ISO_CURRENCY NLS_DUAL_CURRENCY NLS_CALENDAR

TO_NCHAR

NLS_DATE_LANGUAGE NLS_NUMERIC_CHARACTERS NLS_CURRENCY NLS_ISO_CURRENCY NLS_DUAL_CURRENCY NLS_CALENDAR

NLS_UPPER

NLS_SORT

NLS_LOWER

NLS_SORT

NLS_INITCAP

NLS_SORT

NLSSORT

NLS_SORT


次の例に、SQL関数でNLSパラメータを使用する方法を示します。

TO_DATE ('1-JAN-99', 'DD-MON-YY',
   'nls_date_language = American')

TO_CHAR (hire_date, 'DD/MON/YYYY',
   'nls_date_language = French')

TO_CHAR (SYSDATE, 'DD/MON/YYYY',
    'nls_date_language=''Traditional Chinese'' ')

TO_NUMBER ('13.000,00', '99G999D99',
   'nls_numeric_characters = '',.''')

TO_CHAR (salary, '9G999D99L', 'nls_numeric_characters = '',.''
   nls_currency = ''EUR''')

TO_CHAR (salary, '9G999D99C', 'nls_numeric_characters = ''.,''
   nls_iso_currency = Japan')

NLS_UPPER (last_name, 'nls_sort = Swiss')

NLSSORT (last_name, 'nls_sort = German')

注意:

一部の言語では、様々な小文字が複数の大文字に、またはその逆に対応する場合もあります。その結果、NLS_UPPERNLS_LOWERおよびNLS_INITCAP関数の出力の長さが入力の長さと異なる場合があります。

SQL関数で受け入れられないNLSパラメータ

次のNLSパラメータは、NLSSORTを除くSQL関数では受け入れられません。

  • NLS_LANGUAGE

  • NLS_TERRITORY

  • NLS_DATE_FORMAT

NLS_DATE_FORMATおよびNLS_TERRITORY_FORMATは、必要な書式マスクを妨げる可能性があるため、パラメータとしては受け入れられません。TO_CHAR関数またはTO_DATE関数にNLSパラメータがある場合は、常に、日付書式を指定する必要があります。このため、NLS_DATE_FORMATおよびNLS_TERRITORY_FORMATは、TO_CHARまたはTO_DATE関数に対する有効なNLSパラメータではありません。TO_CHARまたはTO_DATE関数でNLS_DATE_FORMATまたはNLS_TERRITORY_FORMATを指定すると、エラーが戻されます。

NLS_LANGUAGEは、NLS_DATE_LANGUAGEのセッション値を妨げる可能性があります。たとえば、TO_CHAR関数でNLS_LANGUAGEを指定した場合、その値がNLS_DATE_LANGUAGEのセッション値と異なっていれば無視されます。

ロケール依存のその他のSQL関数

この項の内容は、次のとおりです。

CONVERT関数

CONVERT関数は、キャラクタ・セット間で文字データを変換します。

CONVERT関数は、あるキャラクタ・セットの文字列のバイナリ表現を別のキャラクタ・セットのバイナリ表現に変換します。この関数は、データベースとクライアントの間のキャラクタ・セット変換と同じ方法を使用します。したがって、この関数では置換文字を使用するため、同じ制限があります。

CONVERTの構文は、次のとおりです。

CONVERT(char, dest_char_set[, source_char_set])

charは変換される値です。source_char_setは変換元キャラクタ・セット、dest_char_setは変換先キャラクタ・セットです。source_char_setパラメータが指定されていない場合は、データベース・キャラクタ・セットがデフォルト値として使用されます。


関連項目:

  • CONVERT関数の詳細は、『Oracle Database SQLリファレンス』を参照してください。

  • CONVERT関数にのみ使用されるキャラクタ・セット・エンコーディングについては、「キャラクタ・セット変換のサポート」を参照してください。


様々な長さセマンティクスに使用するSQL関数

Oracleには、様々な長さセマンティクスに従って動作するSQL関数が用意されています。この種のSQL関数は、SUBSTRLENGTHおよびINSTRという3つのグループに分かれています。グループ内の各関数は異なる種類の長さセマンティクスに基づいており、関数名に追加される文字または数字で区別されます。たとえば、SUBSTRBはバイト・セマンティクスに基づいています。

SUBSTR関数は、サブストリングの要求された部分を戻します。LENGTH関数は、文字列の長さを戻します。INSTR関数は、文字列内のサブストリングを検索します。

SUBSTR関数は、文字列の長さを様々な方法で計算します。表9-2に、計算方法を示します。

表9-2 SUBSTR関数による文字列の長さの計算方法

関数 計算方法

SUBSTR

文字列の長さを、データ型のキャラクタ・セットに関連付けられた長さセマンティクスに基づいて、文字数単位で計算します。たとえば、AL32UTF8の文字は、UCS-4文字の数で計算されます。UTF8およびAL16UTF16の文字は、UCS-2文字の数で計算されます。1つの補助文字は、AL32UTF8では1文字、UTF8およびAL16UTF16では2文字としてカウントされます。VARCHARおよびNVARCHARでは異なるキャラクタ・セットが使用されている場合があるため、2つの文字列が同一の場合にも、データ型が異なるとSUBSTRでの結果が異なる可能性があります。アプリケーションで一貫性が必要な場合は、SUBSTR2またはSUBSTR4を使用して、すべてのセマンティクスの計算を強制的にそれぞれUCS-2またはUCS-4にしてください。

SUBSTRB

文字列の長さをバイト数単位で計算します。

SUBSTR2

文字列の長さを、Java文字列とWindowsクライアント環境に準拠するUCS-2文字の数で計算します。各文字は、UCS-2または16ビットUnicode値で表されます。補助文字は2文字としてカウントされます。

SUBSTR4

文字列の長さをUCS-4文字数で計算します。各文字は、UCS-4または32ビットUnicode値で表されます。補助文字は1文字としてカウントされます。

SUBSTRC

文字列の長さをUnicode合成文字数で計算します。補助文字と合成文字は、1文字としてカウントされます。


LENGTHおよびINSTR関数は、関数名に追加された文字または数字に従って、文字列の長さを同様の方法で計算します。

次に、データベース・キャラクタ・セットがAL32UTF8のデータベースで、SUBSTRSUBSTRBを実行した場合の相違がわかる例を示します。

Fußball文字列の場合、次の文では2文字目からの長さ4文字のサブストリングが戻されます。

SELECT SUBSTR ('Fußball', 2 , 4) SUBSTR FROM DUAL;

SUBS
----
ußba

Fußball文字列の場合、次の文では2バイト目からの長さ4バイトのサブストリングが戻されます。

SELECT SUBSTRB ('Fußball', 2 , 4) SUBSTRB FROM DUAL;

SUB
---
ußb

関連項目:

SUBSTRLENGTHおよびINSTR関数の詳細は、『Oracle Database SQLリファレンス』を参照してください。

様々な長さセマンティクスに使用するLIKE条件

LIKE条件によって、パターン一致を使用するテストを指定します。等価演算子(=)は、ある文字の値と別の文字の値を完全に照合しますが、LIKE条件は、1番目の値の中に2番目で指定されたパターンがあるかどうかを検索し、ある文字の値と別の文字の値の一部を照合します。

LIKEでは、入力のキャラクタ・セットに関連付けられた長さセマンティクスを使用して、文字列の長さが文字数単位で計算されます。表9-3に、LIKE2LIKE4およびLIKECの各条件を示します。

表9-3 LIKE条件

関数 説明

LIKE2

文字がUCS-2のセマンティクスで表される場合に使用します。補助文字は2文字とみなされます。

LIKE4

文字がUCS-4のセマンティクスで表される場合に使用します。補助文字は1文字とみなされます。

LIKEC

文字が完全なUnicodeキャラクタ・セマンティクスで表される場合に使用します。合成文字は1文字として処理されます。


LIKEB条件はありません。

キャラクタ・セットのSQL関数

2つのSQL関数NLS_CHARSET_NAMEおよびNLS_CHARSET_IDで、キャラクタ・セットID番号とキャラクタ・セット名の間の変換ができます。これらの関数は、OCIを介して変数をバインドするために、キャラクタ・セットID番号の判断が必要なプログラムで使用されます。

もう1つのSQL関数NLS_CHARSET_DECL_LENは、列のバイト単位の長さが指定されている場合、列の宣言の長さを文字数で戻します。

この項の内容は、次のとおりです。

キャラクタ・セット番号からキャラクタ・セット名への変換

NLS_CHARSET_NAME(n)関数は、ID番号n に対応するキャラクタ・セットの名前を戻します。nがキャラクタ・セットIDとして認識されない値である場合、関数はNULLを戻します。

キャラクタ・セット名からキャラクタ・セット番号への変換

NLS_CHARSET_ID(text)は、textによって指定された名前に対応するキャラクタ・セットIDを戻します。textには、ランタイムVARCHAR2の値としてキャラクタ・セット名が定義されます。textの値は、データベース・キャラクタ・セットや各国語キャラクタ・セット以外のキャラクタ・セットに変換されるNLSRTL名とすることができます。

CHAR_CStextに入力すると、この関数はサーバーのデータベース・キャラクタ・セットIDを戻します。値NCHAR_CStextに入力すると、この関数はサーバーの各国語キャラクタ・セットIDを戻します。textが名前として認識されない場合、この関数はNULLを戻します。


注意:

textの値は、大文字で入力してください。

NCHAR列の長さを戻す

NLS_CHARSET_DECL_LEN(BYTECNT, CSID )は、列のバイト単位の長さが指定されている場合、列の宣言の長さを文字数で戻します。BYTECNT は列のバイト単位の長さです。CSID は列のキャラクタ・セットIDです。

NLSSORT関数

NLSSORT関数では、ORDER BY句に言語ソートを使用できます。文字列は言語ソート・メカニズムで使用される等価のソート文字列で置換されるため、置換文字列をソートして必要なソート基準を生成できます。バイナリ・ソートの場合、ソート文字列は入力文字列と同じです。

ORDER BY句で使用される言語ソートの種類はNLS_SORTセッション・パラメータによって決定されますが、NLSSORT関数を明示的に使用することでオーバーライドできます。

例9-1では、NLS_SORTセッション・パラメータを使用したドイツ語ソートを指定しています。

例9-1 NLS_SORTセッション・パラメータを使用したドイツ語ソートの指定

ALTER SESSION SET NLS_SORT = GERMAN;
SELECT * FROM table1
     ORDER BY column1;

例9-2 NLSSORT関数を使用したフランス語ソートの指定

この例では、最初にNLS_SORTセッション・パラメータをGERMANに設定しますが、NLSSORT関数でフランス語ソートを指定することでオーバーライドします。

ALTER SESSION SET NLS_SORT = GERMAN;
SELECT * FROM table1
     ORDER BY NLSSORT(column1, 'NLS_SORT=FRENCH');

WHERE句では、NLS_COMPBINARYに設定されている場合、バイナリ比較が使用されますが、これはWHERE句にNLSSORT関数を使用してオーバーライドできます。

例9-3 WHERE句を使用した言語上の比較

ALTER SESSION SET NLS_COMP = BINARY;
SELECT * FROM table1
WHERE NLSSORT(column1, 'NLS_SORT=FRENCH')>
      NLSSORT(column2, 'NLS_SORT=FRENCH');

NLS_COMPセッション・パラメータをLINGUISTICに設定すると、WHERE句にはNLS_SORT値が使用されます。

これ以降の内容は、次のとおりです。

NLSSORTの構文

NLSSORTは、次の4通りの方法で使用できます。

  • NLSSORT()NLS_SORTパラメータに従います。)

  • NLSSORT(column1, 'NLS_SORT=xxxx')

  • NLSSORT(column1, 'NLS_LANG=xxxx')

  • NLSSORT(column1, 'NLS_LANGUAGE=xxxx')

NLSSORT関数のNLS_LANGパラメータは、クライアント環境設定のNLS_LANGとは異なります。NLSSORT関数では、NLS_LANGによって言語の略称(米語はUS、ポーランド語はPLなど)が指定されます。次に例を示します。

SELECT * FROM table1
ORDER BY NLSSORT(column1, 'NLS_LANG=PL');

WHERE句の文字列の比較

NLSSORTによって、アプリケーションは、アルファベットの規則に従った文字列の一致を実行できます。通常、WHERE句の文字列は、文字のバイナリ値を使用して比較されます。データベース・キャラクタ・セットでのバイナリ値が大きい場合、その文字は他の文字よりも大きいとみなされます。文字のバイナリ値に基づいた文字順が言語のアルファベット順と一致しない場合があるため、このような比較では、アルファベットの規則に従わないことがあります。たとえば、列(column1)にISO 8859-1の8ビット・キャラクタ・セットの値ABC、ABZ、BCDおよびÄBCが含まれている場合、Äの数値はBより大きいため、次の問合せではBCDÄBCの両方が戻されます。

SELECT column1 FROM table1 WHERE column1 > 'B';

Äは、ドイツ語ではアルファベット順でBの前にソートされますが、スウェーデン語ではÄは、Zの後にソートされます。WHERE句でNLSSORTを使用すると、次のように言語上の比較を行うことができます。

WHERE NLSSORT(col) comparison_operator NLSSORT(comparison_string)

NLSSORTは、比較演算子の両側に指定する必要があります。次に例を示します。

SELECT column1 FROM table1 WHERE NLSSORT(column1) > NLSSORT('B');

ドイツ語の言語ソートを設定していると、この文ではÄで始まる文字列は戻されません。これは、ドイツ語では、アルファベット順でÄBより前になるためです。スウェーデン語の言語ソートを設定していると、Äで始まる文字列が戻されます。これは、スウェーデン語では、アルファベット順でÄZより後になるためです。

NLS_COMPパラメータを使用したWHERE句での比較の簡素化

デフォルトでは、WHERE句またはPL/SQLブロックでの比較はバイナリで行われます。言語上の比較にNLSSORT関数を使用するのは、特にNLS_SORTセッション・パラメータにすでに言語ソートが指定されている場合は、面倒な場合があります。NLS_COMPパラメータを使用すると、WHERE句またはPL/SQLブロックでの比較に、NLS_SORTセッション・パラメータに従った言語を使用するように指定できます。


注意:

NLS_COMPパラメータは、パーティション表に対する比較動作には影響しません。VALUES LESS THANパーティションに基づく文字列の比較は、常にバイナリで行われます。


関連項目:

「NLS_COMP」

ORDER BY句の制御

言語ソートを使用中の場合、ORDER BY句では文字データに対して暗黙的なNLSSORTが使用されます。ORDER BY句のソート方法(言語またはバイナリ)は、アプリケーション側で意識することはありません。ただし、NLSSORT関数がORDER BY句に明示的に指定されている場合、暗黙的なNLSSORTは実行されません。

NLS_SORTセッション・パラメータで言語ソートが定義されている場合、アプリケーションではORDER BY句に暗黙的なNLSSORT関数が使用されます。明示的なNLSSORT関数を指定すると、暗黙的なNLSSORT関数がオーバーライドされます。

ソート方法が言語として定義されている場合、通常、ORDER BY句にNLSSORT関数は不要です。

ソート方法がデフォルトであるか、バイナリとして定義されている場合、次のような問合せではバイナリ・ソートが使用されます。

SELECT last_name FROM employees
     ORDER BY last_name;

次の問合せによって、ドイツ語の言語ソートを取得できます。

SELECT last_name FROM employees
     ORDER BY NLSSORT(last_name, 'NLS_SORT = GERMAN');

グローバル環境でのSQLとPL/SQLのプログラミングに関するその他のトピック

この項の内容は、次のとおりです。

SQLの日付書式マスク

TO_CHARTO_DATEおよびTO_NUMBERの各関数には、複数の書式マスクが用意されています。

RM(Roman Month)書式要素は、ローマ数字で月を戻します。RMまたはrmを使用して、大文字または小文字を指定できます。たとえば、1998年9月7日の場合、DD-rm-YYYYでは07-ix-1998が戻り、DD-RM-YYYYでは07-IX-1998が戻ります。

書式マスクMONおよびDYは、長さ3文字以内の月および曜日の略称を明示的にサポートします。たとえば、フランス語のLundiとMardiに対して、それぞれの略称LuとMaを指定できます。

週番号の計算

WW書式マスクで戻される週番号は、アルゴリズムint(dayOfYear+6)/7に従って算出されます。 この週番号のアルゴリズムは、ISO規格(2015、1992-06-15)に従っていません。

ISO規格をサポートするために、IW書式要素が用意されています。この書式要素はISO週番号を戻します。また、YYYYYYおよびYYYYの各書式要素の動作と同等の書式要素IIYIYYおよびIYYYは、ISO週番号に関連する年を戻します。

ISO規格では、ISO週番号に関連する年が、暦年と異なることがあります。たとえば、1988年1月1日は、1987年のISO週番号53になります。週は、常に月曜日に始まって日曜日に終わります。週番号は、次の規則に従って決定されます。

  • 1月1日が金曜日、土曜日または日曜日の場合、1月1日を含む週は、その週の大半の日が前年に属するため、前年の最後の週になります。

  • 1月1日が月曜日、火曜日、水曜日または木曜日の場合は、1月1日を含む週は、その週の大半の日が新しい年に属するため、新しい年の最初の週になります。

たとえば、1991年1月1日は火曜日であるため、1990年12月31日の月曜日から1991年1月6日の日曜日までが第1週になります。したがって、1990年12月31日のISO週番号および年は、1および1991となります。ISO週番号を取得するには、週番号にIW書式マスクを使用し、年にIY書式のいずれかを使用します。

SQLの数値書式マスク

数値を書式設定するための書式要素がいくつか用意されています。

要素 説明 用途
D Decimal 小数点文字を戻します。
G Group グループ・セパレータを戻します。
L Local currency 各国通貨記号を戻します。
C International currency ISO通貨記号を戻します。
RN Roman numeral 数値を同等のローマ数字として戻します。

ローマ数字の場合は、RNまたはrnを使用して、大文字または小文字のいずれかを指定できます。変換される数値は、1〜3999の範囲の整数であることが必要です。

LOB列への外部BFILEデータのロード

DBMS_LOB PL/SQLパッケージを使用すると、外部BFILEデータをLOB列にロードできます。以前のリリースのOracleでは、バイナリ・データをCLOB列またはNCLOB列にロードする前のキャラクタ・セット変換は実行されませんでした。そのため、正常に動作させるには、BFILEデータをデータベース・キャラクタ・セットまたは各国語キャラクタ・セットと同じキャラクタ・セットにする必要がありました。 Oracle9iリリース2(9.2)で導入されたAPIでは、新規パラメータを使用してBFILEデータのキャラクタ・セットIDを指定できます。各APIによって、指定したBFILEキャラクタ・セットから、CLOBデータ型の場合はデータベース・キャラクタ・セット、NCLOBデータ型の場合は各国語キャラクタ・セットへとデータが変換されます。クライアント上ではBFILEデータはサポートされないため、ロードはサーバー上で発生します。

  • BLOB列をロードするには、DBMS_LOB.LOADBLOBFROMFILEを使用します。

  • CLOB列およびNCLOB列をロードするには、DBMS_LOB.LOADCLOBFROMFILEを使用します。


    関連項目:

    • 『PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』

    • 『Oracle Databaseアプリケーション開発者ガイド - ラージ・オブジェクト』