1. SQL言語リファレンス
  2. Oracle SQLの基本要素
  3. 書式モデル

書式モデル

書式モデルは、格納された日時または数値データの書式を文字列で記述する文字リテラルです。書式モデルによってデータベース内の値の内部表現が変更されることはありません。文字列を日付または数値に変換する場合、書式モデルによって、Oracle Databaseによる文字列の変換方法が決まります。SQL文では、書式モデルをTO_CHARファンクションやTO_DATEファンクションの引数として使用して、次の書式を指定できます。

  • Oracleがデータベースから値を戻す場合に使用する書式

  • Oracleがデータベースに格納するために指定した値の書式

たとえば:

  • '17:45:29'の日時書式モデルは、'HH24:MI:SS'です。

  • '11-Nov-1999'の日時書式モデルは、'DD-Mon-YYYY'です。

  • '$2,304.25'の数値書式モデルは、'$9,999.99'です。

日時および数値書式モデルの要素のリストは、表2-15および表2-17を参照してください。

いくつかの書式の値は、初期化パラメータの値によって決まります。これらの書式要素によって戻される文字は、初期化パラメータNLS_TERRITORYを使用して暗黙的に指定することもできます。デフォルト日付書式をセッションごとに変更するには、ALTER SESSION文を使用します。

関連項目:

この項の後半では、次の書式モデルの使用方法について説明します。

数値書式モデル

次の関数で、数値書式モデルを使用できます。

  • NUMBER、BINARY_FLOATまたはBINARY_DOUBLEデータ型の値をVARCHAR2データ型の値に変換するTO_CHARファンクション

  • CHARまたはVARCHAR2データ型の値をNUMBERデータ型に変換するTO_NUMBERファンクション

  • CHARおよびVARCHAR2式をBINARY_FLOATまたはBINARY_DOUBLEの値に変換するTO_BINARY_FLOATファンクションおよびTO_BINARY_DOUBLEファンクション

すべての数値書式モデルでは、数値が指定された有効桁数に丸められます。小数点左の有効桁数が書式で指定された桁数より多い場合、シャープ記号(#)が値のかわりに戻されます。通常、このイベントは、TO_CHARファンクションを制限的な数値書式文字列で使用して、丸め処理が行われた場合に発生します。

  • 正のNUMBERの値が非常に大きく、指定の書式で表せない場合、無限大記号(~)が値のかわりに戻されます。同様に、負のNUMBERの値が非常に小さく、指定の書式で表せない場合、負の無限大記号(-~)が戻されます。

  • BINARY_FLOATまたはBINARY_DOUBLEの値がCHARまたはNCHARに変換される場合、無限大またはNaN(非数値)のいずれかが入力されると、Oracleは常に値のかわりにシャープ記号を戻します。ただし、書式モデルを省略すると、OracleはInfまたはNanを文字列として戻します。

数値書式の要素

数値書式モデルは、1つ以上の数値書式要素で構成されます。次の表に、数値書式モデルの要素とその例を示します。

数値書式モデルに書式要素MI、SまたはPRが指定されないかぎり、負の戻り値の先頭には自動的に負の符号が付けられ、正の戻り値の先頭には空白が付けられます。

表2-15 数値書式の要素

要素 例 説明

, (カンマ)

9,999

指定した位置にカンマを戻します。1つの数値書式モデルに複数のカンマを指定できます。

制限事項:

  • 数値書式モデルは、カンマ要素で始めることはできません。

  • 数値書式モデルでは、カンマを小数点文字やピリオドの右側に指定できません。

. (ピリオド)

99.99

指定した位置に小数点(ピリオド(.))を戻します。

制限事項: 1つの数値書式モデルには1つのピリオドのみ指定できます。

$

$9999

値の前にドル記号を付けて戻します。

0

0999

9990

先行0 (ゼロ)を戻します。

後続0 (ゼロ)を戻します。

9

9999

指定された桁数の値を戻します。正の場合は先行空白が含まれ、負の場合は先行マイナスが含まれます。先行0(ゼロ)は空白ですが、0(ゼロ)値の場合は固定小数点数の整数部分に0(ゼロ)を戻します。

B

B9999

整数部が0(ゼロ)の場合、書式モデル内の0(ゼロ)にかかわらず、固定小数点数の整数部に対して空白を戻します。

C

C999

指定した位置にISO通貨記号(NLS_ISO_CURRENCYパラメータの現在の値)を戻します。

D

99D99

指定した位置に小数点文字(NLS_NUMERIC_CHARACTERパラメータの現在の値)を戻します。デフォルトはピリオド(.)です。

制限事項: 1つの数値書式モデルには1つの小数点文字のみ指定できます。

EEEE

9.9EEEE

科学表記法で値を戻します。

G

9G999

指定した位置に桁区切り(NLS_NUMERIC_CHARACTERパラメータの現在の値)を戻します。単一の数値書式モデルに複数の桁区切りを指定できます。

制限事項: 数値書式モデルにおいて、桁区切りは小数点文字やピリオドの右側に指定できません。

L

L999

指定した位置にローカル通貨記号(NLS_CURRENCYパラメータの現在の値)を戻します。

MI

9999MI

負の値の後に負の符号(-)を戻します。

正の値の後に空白を戻します。

制限事項: 書式要素MIは、数値書式モデルの最後の位置にのみ指定できます。

PR

9999PR

山カッコ<>の中に負の値を戻します。

正の値の前後に空白を付けて戻します。

制限事項: 書式要素PRは、数値書式モデルの最後の位置にのみ指定できます。

RN

rn

RN

rn

大文字のローマ数字で値を戻します。

小文字のローマ数字で値を戻します。

値は1から3999の整数となります。

S

S9999

9999S

負の値の前に負の符号(-)を戻します。

正の値の前に正の符号(+)を戻します。

負の値の後に負の符号(-)を戻します。

正の値の後に正の符号(+)を戻します。

制限事項: 書式要素Sは、数値書式モデルの最初または最後の位置にのみ指定できます。

TM

TM

テキストの最小数値書式モデルは、文字の最小数を(10進出力で)戻します。この要素の大文字と小文字は区別されません。

デフォルトはTM9で、出力が64文字を超えないかぎり、固定表記法で数値を戻します。戻り値が64文字を超える場合、Oracle Databaseは自動的に科学表記法で文字数を戻します。

制限事項:

  • この要素の前に他の要素を指定することはできません。

  • この要素の後には、9、E(1つ)またはe(1つ)のみを指定できます。次の文は、エラーを戻します。

    SELECT TO_CHAR(1234, 'TM9e') FROM DUAL;

U

U9999

指定した位置にユーロ(または他の)第2通貨記号(NLS_DUAL_CURRENCYパラメータの現在の値によって決定される)を戻します。

V

999V99

値を10のn乗にして戻します(必要に応じて数値を丸めます)。ここで、nはVの後の9の数です。

X

XXXX

xxxx

指定の桁数の16進数値を戻します。指定した値が整数でない場合、Oracle Databaseはその値を整数に丸めます。

制限事項:

  • この要素は、正の値または0(ゼロ)のみを受け入れます。負の値の場合、エラーを戻します。

  • この要素の前には、0(先行0(ゼロ)を戻す)またはFMのみを指定できます。その他の要素の場合、エラーを戻します。0(ゼロ)またはFMをXと同時に指定しないと、戻り値の前に常に空白が1つ追加されます。詳細は、書式モデル修飾子「FM」を参照してください。

表2-16に、異なる値のnumberと'fmt'に対して次の問合せを行った場合の結果を示します。 

SELECT TO_CHAR(number, 'fmt')
  FROM DUAL;

表2-16 数値変換の結果

number 'fmt' 結果

-1234567890

9999999999S

'1234567890-'

0

99.99

' .00'

+0.1

99.99

' .10'

-0.2

99.99

' -.20'

0

90.99

' 0.00'

+0.1

90.99

' 0.10'

-0.2

90.99

' -0.20'

0

9999

' 0'

1

9999

' 1'

0

B9999

' '

1

B9999

' 1'

0

B90.99

' '

+123.456

999.999

' 123.456'

-123.456

999.999

'-123.456'

+123.456

FM999.009

'123.456'

+123.456

9.9EEEE

' 1.2E+02'

+1E+123

9.9EEEE

' 1.0E+123'

+123.456

FM9.9EEEE

'1.2E+02'

+123.45

FM999.009

'123.45'

+123.0

FM999.009

'123.00'

+123.45

L999.99

' $123.45'

+123.45

FML999.99

'$123.45'

+1234567890

9999999999S

'1234567890+'

日時書式モデル

次のファンクションで日時書式モデルを使用できます。

  • デフォルト書式以外の書式の文字値を日時値に変換するTO_*日時ファンクション(TO_*日時ファンクションとは、TO_DATE、TO_TIMESTAMPおよびTO_TIMESTAMP_TZです。)

  • デフォルト書式以外の書式の日時値を文字値に変換する(たとえば、アプリケーションから日付を出力する)TO_CHARファンクション

日時書式モデルの合計長は最大22文字です。

デフォルト日時書式は、NLSセッション・パラメータNLS_DATE_FORMAT、NLS_TIMESTAMP_FORMATおよびNLS_TIMESTAMP_TZ_FORMATで明示的に指定することも、NLSセッション・パラメータNLS_TERRITORYで暗黙的に指定することもできます。セッションのデフォルトの日時書式は、ALTER SESSION文で変更できます。

関連項目:

NLSパラメータの詳細は、ALTER SESSIONおよび『Oracle Databaseグローバリゼーション・サポート・ガイド』を参照してください。

日時書式要素

日時書式モデルは、表2-17に示す、1つ以上の日時書式要素で構成されます。

  • 入力書式モデルの場合、同じ書式項目は2回指定できません。また、類似した情報を表す書式項目を組み合せることもできません。たとえば、'SYYYY'と'BC'を同一の書式文字列内で使用することはできません。

  • 2番目の列は、書式要素がTO_*日付ファンクションで使用可能かどうかを示します。TO_CHARファンクションでは、すべての書式要素が使用できます。

  • 日時書式要素FF、TZD、TZH、TZMおよびTZRは、タイムスタンプおよび期間書式モデルでは使用できますが、元のDATE書式モデルでは使用できません。

  • 多くの場合、日時書式要素は一定の長さになるまで空白または先行0(ゼロ)が埋め込まれます。詳細は、書式モデル修飾子「FM」を参照してください。

ノート:

年要素は、短いものではなく4桁のもの(YYYY)を使用することをお薦めしますが、その理由は次のとおりです。

  • 4桁の年要素ならば、あいまいさが排除されます。

  • 短い年要素は、問合せ最適化に影響を及ぼすことがあります(問合せコンパイルの時点では年が未知であり、実行時にならないと判明しないことがあるため)。

日付書式要素における大文字

スペルアウトした単語、省略形またはローマ数字での大文字の使用方法は、対応する書式要素での大文字の使用方法に従います。たとえば、日付書式モデル「DAY」は「MONDAY」、「Day」は「Monday」、「day」は「monday」を生成します。

日時書式モデルにおける句読点と文字リテラル

日付書式モデルでは、次の文字を指定できます。

  • ハイフン、スラッシュ、カンマ、ピリオドおよびコロンなどの句読点

  • 二重引用符で囲んだ文字リテラル

これらの文字は、書式モデル内と同じ位置で戻り値に表示されます。

表2-17 日時書式要素

要素 TO_*日時ファンクションで指定可能かどうか 説明 
-
/
,
.
;
:
"text"

可

句読点と引用符付きテキストは、結果で複製されます。

 
AD
A.D.

可

ピリオド付き/なしで西暦を示します。

 
AM
A.M.

可

ピリオド付き/なしで子午線インジケータを示します。

 
BC
B.C.

可

ピリオド付き/なしで紀元前を示します。

 
CC
SCC

世紀。

  • 4桁の年の下2桁が01から99(01および99を含む)の場合、世紀はその年の上2桁に1を加えた値になります。

  • 4桁の年の下2桁が00の場合、世紀はその年の上2桁と等しい値になります。

たとえば、2002は21を戻し、2000は20を戻します。

 
D

可

曜日(1から7)。この要素はセッションのNLS地域に依存します。

DAY

可

曜日。

 
DD

可

月における日(1から31)。

 
DDD

可

年における日(1から366)。

 
DL

可

Oracle DatabaseのDATE書式の拡張である長い日付書式の値(NLS_DATE_FORMATパラメータの現在の値によって決定される)を戻します。日付の構成要素(曜日や月など)の表示が、NLS_TERRITORYパラメータおよびNLS_LANGUAGEパラメータによって決まるようにします。たとえば、AMERICAN_AMERICAロケールでは、これは書式'fmDay, Month dd, yyyy'を指定することと同じです。GERMAN_GERMANYロケールでは、書式'fmDay, dd.Month yyyy'。

制限: この書式は、TS要素でのみ、空白で区切って指定可能です。 

DS

可

短い日付書式の値を戻します。日付の構成要素(曜日や月など)の表示が、NLS_TERRITORYパラメータおよびNLS_LANGUAGEパラメータによって決まるようにします。たとえば、AMERICAN_AMERICAロケールでは、'MM/DD/RRRR'形式で指定したのと同じです。ENGLISH_UNITED_KINGDOMロケールでは、'DD/MM/RRRR'形式で指定したのと同じです。

制限: この書式は、TS要素でのみ、空白で区切って指定可能です。 

DY

可

曜日の省略形。

 
E

可

年号(日本の元号暦、台湾暦およびタイ仏教暦)の省略形。

 
EE

可

年号(日本の元号暦、台湾暦およびタイ仏教暦)の完全形。

 
FF [1..9]

可

小数部。基数は出力されません。基数文字の追加には、X書式要素を使用します。FFの後に1から9の数字を設定して、戻される日時値のミリ秒部分の桁数を指定します。数字を指定しない場合は、日時データ型に指定された精度またはデータ型のデフォルトの精度が使用されます。タイムスタンプ書式および期間書式では有効ですが、DATE書式では有効ではありません。

例: 'HH:MI:SS.FF'

SELECT TO_CHAR(SYSTIMESTAMP, 'SS.FF3') from DUAL;

 
FM

可

先行および後続する空白を持たない値を返します。

関連項目: FM

 
FX

可

文字データと書式モデルの完全一致を必要とします。

関連項目: FX

 
HH
HH12

可

時間(1から12)。

 
HH24

可

時間(0から23)。

 
IW

ISO 8601標準で定義されている年の暦週(1-52または1-53)。

  • 暦週は月曜日に始まります。

  • 年の最初の暦集は、1月4日を含みます。

  • 年の最初の暦集は、12月19、30、31日を含む場合があります。

  • 年の最後の暦集は、1月1、2、3日を含む場合があります。

 
IYYY

ISO 8601標準で定義されている暦週を含む4桁で表される年。

 
IYY
IY
I

ISO 8601標準で定義されている暦週を含む、下3桁、2桁、1桁で表される年。

 
J

可

ユリウス日(紀元前4712年1月1日からの経過日数)。Jで指定する数字は、整数である必要があります。

 
MI

可

分(0から59)。

 
MM

可

月(01から12。1月は01)。

 
MON

可

月の名前の省略形。

 
MONTH

可

月の名前。

 
PM
P.M.

可

ピリオド付き/なしで子午線インジケータを示します。

 
Q

年の四半期(1、2、3、4。1月から3月は1)。

 
RM

可

ローマ数字による月(IからXII。1月はI)。

 
RR

可

2桁のみを使用して、21世紀に20世紀の日付を格納できます。

関連項目: RR日時書式要素

 
RRRR

可

年。4桁または2桁で入力できます。2桁の場合、RRの場合と同様の結果が戻ります。この機能を使用しない場合は、4桁の年を入力します。

 
SS

可

秒(0から59)。

 
SSSSS

可

午前0時から経過した秒(0から86399)。

 
TS

可

短い時間書式で値を戻します。時間の構成要素(時間や分など)の表示が、NLS_TERRITORY初期化パラメータおよびNLS_LANGUAGE初期化パラメータによって決まるようにします。

制限: この書式は、DLまたはDS要素でのみ、空白で区切って指定可能です。 

TZD

可

夏時間の情報。TZDの値は、夏時間の情報を持つタイムゾーン文字列の省略形です。TZRで指定した地域と対応している必要があります。タイムスタンプ書式および期間書式では有効ですが、DATE書式では有効ではありません。

例: PST(アメリカ太平洋標準時)、PDT(アメリカ太平洋夏時間)。 

TZH

可

タイムゾーンの時間。(TZM書式要素を参照。)タイムスタンプ書式および期間書式では有効ですが、DATE書式では有効ではありません。

例: 'HH:MI:SS.FFTZH:TZM' 

TZM

可

タイムゾーンの分。(TZH書式要素を参照。)タイムスタンプ書式および期間書式では有効ですが、DATE書式では有効ではありません。

例: 'HH:MI:SS.FFTZH:TZM' 

TZR

可

タイムゾーン地域の情報。値は、データベースでサポートされるタイムゾーン地域名である必要があります。タイムスタンプ書式および期間書式では有効ですが、DATE書式では有効ではありません。

例: US/Pacific 

WW

年における週(1から53)。第1週はその年の1月1日で始まり、1月7日で終了します。

 
W

月における週(1から5)。第1週はその月の1日で始まり、7日で終了します。

 
X

可

ローカル基数文字。

例: 'HH:MI:SSXFF' 

Y,YYY

可

指定の位置にカンマを付けた年。

 
YEAR
SYEAR

スペルアウトした年。Sの場合、紀元前の日付の先頭にマイナス記号(-)が付加されます。 

YYYY
SYYYY

可

4桁の年。Sの場合、紀元前の日付の先頭にマイナス記号が付加されます。 

YYY
YY
Y

可

年の最後の3桁、2桁または1桁。

Oracle Databaseは、一定の柔軟性によって文字列を日付に変換します。たとえば、TO_DATEファンクションの使用時、句読点文字を含む書式モデルと、句読点文字がないか、一部を使用した入力文字列は同じになり、入力文字列の各数値要素に最大許容桁数が含まれます(たとえば、'MM'に2桁の'05'、'YYYY'に4桁の'2007')。次の文は、エラーを戻しません。 

SELECT TO_CHAR(TO_DATE('0207','MM/YY'), 'MM/YY') FROM DUAL;

TO_CH
-----
02/07

次の書式文字列はエラーを戻しませんが、FX(厳密な書式一致)書式修飾子では、式および書式文字列が完全に一致する必要があります。

SELECT TO_CHAR(TO_DATE('0207', 'fxmm/yy'), 'mm/yy') FROM DUAL;
SELECT TO_CHAR(TO_DATE('0207', 'fxmm/yy'), 'mm/yy') FROM DUAL;
                       *
ERROR at line 1:
ORA-01861: literal does not match format string

この書式モデルでは、英数字以外の任意の1文字を句読点文字に一致させることができます。たとえば、次の文は、エラーを戻しません。

SELECT TO_CHAR (TO_DATE('02#07','MM/YY'), 'MM/YY') FROM DUAL;

TO_CH
-----
02/07

関連項目:

詳細は、書式モデルの修飾子および文字列から日付への変換に関する規則を参照してください。

日時書式要素およびグローバリゼーション・サポート

いくつかの日時書式要素の機能は、Oracle Databaseを使用している国および言語に依存します。たとえば、次の日時書式要素は、フルスペルで値が戻されます。

  • MONTH

  • MON

  • DAY

  • DY

  • BC、AD、B.C.またはA.D.

  • AM、PM、A.M.またはP.M.

これらの値を戻す言語は、初期化パラメータNLS_DATE_LANGUAGEによって明示的に指定することも、初期化パラメータNLS_LANGUAGEによって暗黙的に指定することもできます。日時書式要素YEARとSYEARによって戻される値は常に英語です。

日時書式要素Dは、週の何日目であるか(1から7)を返します。週の第1日目は、初期化パラメータNLS_TERRITORYで暗黙的に指定されます。

関連項目:

グローバリゼーション・サポートの初期化パラメータの詳細は、『Oracle Databaseリファレンス』および『Oracle Databaseグローバリゼーション・サポート・ガイド』を参照してください。

ISO標準日付書式要素

Oracleでは、ISO規格に従って、日時書式要素IYYY、IYY、IY、IおよびIWによって返された値が計算されます。これらの値と、日時書式要素YYYY、YYY、YY、YおよびWWによって返された値との違いについては、『Oracle Databaseグローバリゼーション・サポート・ガイド』のグローバリゼーション・サポートの説明を参照してください。

RR日時書式要素

RR日時書式要素は、YY日時書式要素に似ていますが、より柔軟に、他の世紀に日付の値を格納できます。RR日時書式要素では、年の下2桁のみを指定することにより、20世紀の日付を21世紀に格納できます。

TO_DATEファンクションとYY日時書式要素を使用する場合、返される年の上2桁は常に、現在の年と同一になります。かわりにRR日時書式要素を使用する場合、戻り値の世紀は、指定した2桁の年および現在の年の下2桁によって異なります。

つまり、次のようになります。

  • 指定した2桁の年が00から49である場合、次のようになります。

    • 現在の年の下2桁が00から49の場合、返される年の上2桁は、現在の年と同一になる。

    • 現在の年の下2桁が50から99の場合、返される年の上2桁は、現在の年の上2桁に1を加えた値になる。

  • 指定した2桁の年が50から99である場合、次のようになります。

    • 現在の年の下2桁が00から49の場合、返される年の上2桁は、現在の年の上2桁から1を引いた値になる。

    • 現在の年の下2桁が50から99の場合、返される年の上2桁は、現在の年と同一になる。

次に、RR日時書式要素の特長を具体的に説明します。

RR日時書式の例

次の問合せが、1950年から1999年の間に発行されるとします。

SELECT TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-RR'), 'YYYY') "Year" FROM DUAL;

Year
----
1998

SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR'), 'YYYY') "Year" FROM DUAL; 

Year
----
2017

次の問合せが、2000年から2049年の間に発行されるとします。 

SELECT TO_CHAR(TO_DATE('27-OCT-98', 'DD-MON-RR'), 'YYYY') "Year" FROM DUAL; 

Year
----
1998 

SELECT TO_CHAR(TO_DATE('27-OCT-17', 'DD-MON-RR'), 'YYYY') "Year" FROM DUAL; 

Year
----
2017

発行される年(2000年の前後)にかかわらず、問合せが同じ値を戻していることに注目してください。RR日時書式要素によって、年の上2桁が異なっても同じ値を戻すSQL文を記述できます。

日時書式要素の接尾辞

表2-18に、日時書式要素に付加できる接尾辞を示します。

表2-18 日時書式要素の接尾辞

接尾辞 意味 要素の例 値の例

TH

序数

DDTH

4TH

SP

フルスペルで表した数

DDSP

FOUR

SPTHまたはTHSP

フルスペルで表した序数

DDSPTH

FOURTH

日時書式要素の接尾辞のノート:

  • 前述の接尾辞のいずれかを日時書式要素に追加する場合、戻り値は必ず英語になります。

  • 日時の接尾辞は、書式の出力にのみ有効です。データベースに日付を挿入するためには使用できません。

書式モデルの修飾子

TO_CHARファンクションの書式モデルで修飾子FMとFXを使用して、空白の埋め方および書式検査を制御できます。

同じ修飾子が1つの書式モデルに2回以上出現してもかまいません。その場合は、2回目以降の出現のたびにその修飾子の効果は逆になります。その効果は、モデルの中の最初の出現に続く部分に対しては有効になり、2番目の出現に続く部分に対しては無効になり、3番目の出現に続く部分に対しては再び有効になり、以降も同様です。

FM

Fill mode (埋込みモード)です。書式要素が固定幅になるまで後続空白文字と先行0(ゼロ)が埋め込まれます。幅は、関連する書式モデルの最大要素の表示幅と等しくなります。

  • 数値要素には、その要素に指定できる最大値の幅まで先行0(ゼロ)が埋め込まれます。たとえば、YYYY要素は4桁(9999の長さ)まで、HH24は2桁(23の長さ)まで、DDDは3桁(366の長さ)まで埋め込まれます。

  • 文字要素MONTH、MON、DAYおよびDYには、NLS_DATE_LANGUAGEパラメータおよびNLS_CALENDARパラメータの値によって指定される有効な名前の中で、それぞれ最長のフルスペルの月名、最長の省略形の月名、最長のフルスペルの曜日または最長の省略形の曜日の幅まで、後続空白が埋め込まれます。たとえば、NLS_DATE_LANGUAGEがAMERICANでNLS_CALENDARがGREGORIAN(デフォルト)の場合、MONTHの最大要素はSEPTEMBERであるため、MONTH書式要素のすべての値は表示文字が9文字になるまで埋め込まれます。NLS_DATE_LANGUAGEパラメータおよびNLS_CALENDARパラメータの値は、TO_CHARおよびTO_*日時ファンクションの3番目の引数で指定されるか、または現行セッションのNLS環境から取得されます。

  • 文字要素RMには、長さ4(viiiの長さ)まで後続空白が埋め込まれます。

  • その他の文字要素とフルスペルで表した数(SP、SPTHおよびTHSP接尾辞)には埋め込まれません。

FM修飾子を使用すると、TO_CHARファンクションの戻り値で前述の埋込みは行われなくなります。

FX

Format exact (厳密な書式一致)です。この修飾子は、TO_DATEファンクションの文字引数と日時書式モデルに対して、厳密な一致を指定します。

  • 文字引数における句読点と引用符で囲まれたテキストは、書式モデルの対応する部分と(大/小文字の違いを除いて)厳密に一致する必要があります。

  • 文字引数には余分な空白を含めることはできません。FXを指定していない場合、Oracleは余分な空白を無視します。

  • 文字引数における数値データの桁数は、書式モデルの対応する要素と同じ桁数である必要があります。FXを指定していない場合、文字引数における数値によっては先行0(ゼロ)が省略されます。

    FXが使用可能になっているとき、FM修飾子を指定して、この先行0(ゼロ)のチェックを使用禁止にできます。

文字引数の位置がこれらの条件に違反する場合、Oracleはエラー・メッセージを戻します。

書式モデルの例

次の文は、日付書式モデルを使用して文字式を戻します。

SELECT TO_CHAR(SYSDATE, 'fmDDTH') || ' of ' ||
       TO_CHAR(SYSDATE, 'fmMonth') || ', ' ||
       TO_CHAR(SYSDATE, 'YYYY') "Ides" 
  FROM DUAL; 

Ides 
------------------ 
3RD of April, 2008

この文はFM修飾子も使用していることに注目してください。FMを指定しないと、月は、次のように空白を埋め込んで9文字にして戻されます。 

SELECT TO_CHAR(SYSDATE, 'DDTH') || ' of ' ||
   TO_CHAR(SYSDATE, 'Month') || ', ' ||
   TO_CHAR(SYSDATE, 'YYYY') "Ides"
  FROM DUAL; 

Ides 
----------------------- 
03RD of April    , 2008

次の文は、2つの連続した一重引用符を含む日付書式モデルを使用することによって、戻り値に一重引用符を含めます。

SELECT TO_CHAR(SYSDATE, 'fmDay') || '''s Special' "Menu"
  FROM DUAL; 

Menu 
----------------- 
Tuesday's Special

書式モデル内の文字リテラルにおいても、同じ目的で一重引用符を2つ連続して使用できます。

表2-19に、char値と'fmt'の様々な組合せに対し、次の文がFXを使用した一致条件を満たしているかどうかを示します(tableという名前の表にはDATEデータ型の列date_columnがあります)。 

UPDATE table 
  SET date_column = TO_DATE(char, 'fmt');

表2-19 FX書式モデル修飾子による文字データと書式モデルの一致

char 'fmt' 一致とエラー

'15/ JAN /1998'

'DD-MON-YYYY'

Match

' 15! JAN % /1998'

'DD-MON-YYYY'

Error

'15/JAN/1998'

'FXDD-MON-YYYY'

Error

'15-JAN-1998'

'FXDD-MON-YYYY'

Match

'1-JAN-1998'

'FXDD-MON-YYYY'

Error

'01-JAN-1998'

'FXDD-MON-YYYY'

Match

'1-JAN-1998'

'FXFMDD-MON-YYYY'

Match

戻り値の書式: 例

書式モデルを使用して、データベースから値を戻すために使用するOracleの書式を指定できます。

次の文は、部門80の従業員の給与を選択し、TO_CHARファンクションを使用して、その給与を数値書式モデル'$99,990.99'で指定した書式の文字値に変換します。 

SELECT last_name employee, TO_CHAR(salary, '$99,990.99')
  FROM employees
  WHERE department_id = 80;

Oracleはこの書式モデルによって、ドル記号を先頭に付け、3桁ごとにカンマで区切り、小数点以下2桁を持つ給与を戻します。

次の文は、部門20の各従業員の入社した日付を選択し、TO_CHARファンクションを使用して、その日付を日付書式モデル'fmMonth DD, YYYY'で指定した書式の文字列に変換します。 

SELECT last_name employee, TO_CHAR(hire_date,'fmMonth DD, YYYY') hiredate
  FROM employees
  WHERE department_id = 20;

Oracleはこの書式モデルによって、2桁の日、世紀も含めた4桁の年で示された入社日付(fmで指定)を空白で埋めないで戻します。

関連項目:

fm書式要素の詳細は、書式モデルの修飾子を参照してください。

正しい書式モデルの指定: 例

列の値を挿入または更新する場合、指定する値のデータ型が列の列データ型に対応する必要があります。書式モデルを使用して、あるデータ型の値を列が必要とする別のデータ型の値に変換する書式を指定できます。

たとえば、DATE列に挿入する値は、DATEデータ型の値か、またはデフォルト日付書式の文字列値である必要があります(Oracleは、暗黙的にデフォルト日付書式の文字列をDATEデータ型に変換します)。値が別の書式で与えられる場合、TO_DATEファンクションを使用して値をDATEデータ型に変換する必要があります。また、文字列の書式を指定する場合にも、書式モデルを使用する必要があります。

次の文は、TO_DATEファンクションを使用してHunoldの入社日を更新します。文字列'2008 05 20'をDATE値に変換するために、書式マスク'YYYY MM DD'を指定します。 

UPDATE employees 
  SET hire_date = TO_DATE('2008 05 20','YYYY MM DD') 
  WHERE last_name = 'Hunold';

文字列から日付への変換に関する規則

次の追加の書式化規則は、文字列値を日付値に変換する場合に適用されます(ただし、書式モデルで修飾子FXまたはFXFMを使用して書式検査を制御した場合は適用できません)。

  • 先行0(ゼロ)を含む数値書式要素の桁がすべて指定されている場合は、日付文字列から書式文字列に含まれる句読点を省略できます。たとえば、MM、DD、YYなどの2桁の書式要素については、2のかわりに02を指定した場合です。

  • 日付文字列から、書式文字列の最後にある時刻フィールドを省略できます。

  • 日付文字列では、英数字以外の任意の1文字を使用して、書式文字列の句読点記号に一致させることができます。

  • 日時書式要素と日付文字列内の対応する文字の一致に失敗した場合、表2-20に示すとおり、元の書式要素のかわりに、別の書式要素の適用が試みられます。

表2-20 Oracleの書式一致

元の書式要素 元の書式要素のかわりに試行する書式要素 
'MM'

'MON'および'MONTH'

'MON

'MONTH'

'MONTH'

'MON'

'YY'

'YYYY'

'RR'

'RRRR'

XML書式モデル

SYS_XMLAggファンクションとSYS_XMLGenファンクション(非推奨)は、XML文書を格納するXMLType型のインスタンスを返します。Oracleには、これらのファンクションの出力を書式設定できるXMLFormatオブジェクトが用意されています。

表2-21に、XMLFormatオブジェクトの属性を示します。表に示すように、ファンクションはこの型を実装します。

関連項目:

表2-21 XMLFormatオブジェクトの属性

属性 データ型 目的

enclTag

VARCHAR2(4000)またはVARCHAR2(32767)脚注 1

SYS_XMLAggファンクションまたはSYS_XMLGenファンクション(非推奨)の結果の囲みタグの名前です。

SYS_XMLAgg: デフォルトはROWSETです。

SYS_XMLGen: ファンクションへの入力が列名の場合、その列名がデフォルトになります。それ以外の場合、デフォルトはROWです。schemaTypeをUSE_GIVEN_SCHEMAに設定すると、この属性によってXMLスキーマの要素名を指定することもできます。

schemaType

VARCHAR2(100)

出力された文書のスキーマ生成の型です。有効な値は、'NO_SCHEMA'および'USE_GIVEN_SCHEMA'です。デフォルトは、'NO_SCHEMA'です。

schemaName

VARCHAR2(4000)またはVARCHAR2(32767)脚注 1

schemaTypeの値が'USE_GIVEN_SCHEMA'のときに、Oracleが使用するターゲット・スキーマの名前です。schemaNameを指定すると、囲みタグが要素名として使用されます。

targetNameSpace

VARCHAR2(4000)またはVARCHAR2(32767)脚注 1

スキーマが指定されている場合(つまり、schemaTypeがGEN_SCHEMA_*またはUSE_GIVEN_SCHEMAの場合)のターゲット・ネームスペースです。

dburlPrefix

VARCHAR2(4000)またはVARCHAR2(32767)脚注 1

WITH_SCHEMAが指定されている場合に使用するデータベースのURLです。この属性が指定されていない場合、Oracleはその型へのURLを相対URL参照として宣言します。

processingIns

VARCHAR2(4000)またはVARCHAR2(32767)脚注 1

ファンクションの出力の最上位に、要素の前に追加されるユーザーの処理命令です。

脚注 1

この属性のデータ型は、初期化パラメータがMAX_STRING_SIZE = STANDARDの場合はVARCHAR2(4000)、MAX_STRING_SIZE = EXTENDEDの場合はVARCHAR2(32767)です。詳細は、拡張データ型を参照してください。

XMLFormatオブジェクトを実装するファンクションは、次のとおりです。 

STATIC FUNCTION createFormat(
     enclTag IN varchar2 := 'ROWSET',
     schemaType IN varchar2 := 'NO_SCHEMA',
     schemaName IN varchar2 := null,
     targetNameSpace IN varchar2 := null,
     dburlPrefix IN varchar2 := null, 
     processingIns IN varchar2 := null) RETURN XMLGenFormatType
       deterministic parallel_enable,
  MEMBER PROCEDURE genSchema (spec IN varchar2),
  MEMBER PROCEDURE setSchemaName(schemaName IN varchar2),
  MEMBER PROCEDURE setTargetNameSpace(targetNameSpace IN varchar2),
  MEMBER PROCEDURE setEnclosingElementName(enclTag IN varchar2), 
  MEMBER PROCEDURE setDbUrlPrefix(prefix IN varchar2),
  MEMBER PROCEDURE setProcessingIns(pi IN varchar2),
  CONSTRUCTOR FUNCTION XMLGenFormatType (
     enclTag IN varchar2 := 'ROWSET',
     schemaType IN varchar2 := 'NO_SCHEMA',
     schemaName IN varchar2 := null,
     targetNameSpace IN varchar2 := null,
     dbUrlPrefix IN varchar2 := null, 
     processingIns IN varchar2 := null) RETURN SELF AS RESULT
      deterministic parallel_enable,
  STATIC function createFormat2(
      enclTag in varchar2 := 'ROWSET',
      flags in raw) return sys.xmlgenformattype 
      deterministic parallel_enable
);