日本語PDF
  1. SQL言語リファレンス
  2. Oracle SQLの基本要素
  3. リテラル

リテラル

「リテラル」と「定数値」という用語は同義語であり、固定されたデータ値を表します。たとえば、'JACK'、'BLUE ISLAND'および'101'はすべて文字リテラルです。5001は数値リテラルです。文字リテラルは、一重引用符で囲みます。一重引用符を付けることで、Oracleは文字リテラルとスキーマ・オブジェクト名を区別します。

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

多くのSQL文とファンクションでは、文字リテラルと数値リテラルを指定する必要があります。式と条件の一部として、リテラルを指定できます。文字リテラルは'text'の表記法を、各国文字リテラルはN'text'の表記法を、数値リテラルはリテラルのコンテキストに応じてintegerまたはnumberの表記法を使用して指定できます。これらの表記法の構文については、次の項で説明します。

Datetime(日時)またはInterval(期間)のデータ型をリテラルとして指定する場合は、データ型に含まれているオプションの精度を考慮する必要があります。Datetime (日時)およびInterval (期間)のデータ型をリテラルとして指定する場合の例は、データ型の関連する項を参照してください。

テキスト・リテラル

このマニュアルの他の箇所で、式、条件、SQLファンクションおよびSQL文の各構文に示されているstringに値を指定するときには、必ずこのテキスト・リテラルの表記法を使用してください。このマニュアルでは、 テキスト・リテラル、文字リテラルおよび文字列は同じ用語として使用しています。テキスト、文字、文字列リテラルは必ず一重引用符で囲まれています。構文にcharが使用されている場合、テキスト・リテラルを指定するか、または文字データに変換する他の式(たとえば、hr.employees表のlast_name列)を指定します。構文にcharがある場合、一重引用符で囲む必要はありません。

テキスト・リテラルまたは文字列の構文は次のとおりです。

string::=

string.epsの説明が続きます
図string.epsの説明

ここで、Nまたはnは、各国語文字セット(NCHARまたはNVARCHAR2データ)を使用してリテラルを指定します。デフォルトでは、この表記法を使用して入力したテキストは、サーバーで使用するときにデータベースの文字セットによって各国語文字セットに変換されます。テキスト・リテラルをデータベースの文字セットに変換しているときにデータの消失を避けるためには、環境変数ORA_NCHAR_LITERAL_REPLACEにTRUEを設定してください。このように設定することで、n'を透過的に内部で置き換え、SQLの処理中にテキスト・リテラルを保持します。

関連項目:

N付き引用符で表されたリテラルの詳細は、『Oracle Databaseグローバリゼーション・サポート・ガイド』を参照してください。

構文の上の方のブランチでは、次のようになります。

  • cは、データベース文字セットの任意の要素です。リテラル内の一重引用符(')の前には、エスケープ文字を付ける必要があります。リテラル内で一重引用符を表すには、一重引用符を2つ使用します。

  • ' 'は、テキスト・リテラルの始まりと終わりを示す2つの一重引用符です。

構文の下の方のブランチでは、次のようになります。

  • Qまたはqは、代替引用メカニズムが使用されることを示します。このメカニズムを使用すると、様々なデリミタをテキスト文字列に使用できます。

  • 一番外側の' 'は、開始と終了のquote_delimiterの前後に付ける2つの一重引用符です。

  • cは、データベース文字セットの任意の要素です。cで構成されるテキスト・リテラル内に引用符(")を含めることができます。また、一重引用符が直後に付かないquote_delimiterも含めることができます。

  • quote_delimiterは、空白、タブおよび改行文字を除く、任意のシングルバイト文字またはマルチバイト文字です。quote_delimiterには一重引用符も使用できます。ただし、quote_delimiterがテキスト・リテラル自体に使用されている場合は、一重引用符を直後に付けないようにしてください。

    開始のquote_delimiterが[、{、<または(のいずれかである場合、終了のquote_delimiterも対応する]、}、>または)である必要があります。それ以外の場合は常に、開始および終了のquote_delimiterは同じ文字である必要があります。

テキスト・リテラルは、次のようにCHARデータ型とVARCHAR2データ型の両方のプロパティを持ちます。

  • 式と条件の中のテキスト・リテラルは、OracleによってCHARデータ型として扱われ、空白埋め比較セマンティクスで比較されます。

  • テキスト・リテラルの最大長は、初期化パラメータがMAX_STRING_SIZE = STANDARDの場合は4,000バイトになり、MAX_STRING_SIZE = EXTENDEDの場合は32,767になります。詳細は、拡張データ型を参照してください。

有効なテキスト・リテラルの例を次に示します。

'Hello'
'ORACLE.dbs'
'Jackie''s raincoat'
'09-MAR-98'
N'nchar literal'

代替引用メカニズムを使用した場合の、有効なテキスト・リテラルの例を次に示します。

q'!name LIKE '%DBMS_%%'!'
q'<'So,' she said, 'It's finished.'>'
q'{SELECT * FROM employees WHERE last_name = 'Smith';}'
nq'ï Ÿ1234 ï'
q'"name like '['"'

関連項目:

空白埋め比較セマンティクスおよび非空白埋め比較セマンティクス

数値リテラル

固定長の数値または浮動小数点数値を指定する場合は、数値リテラルの表記を使用します。

整数リテラル

このマニュアルの他の箇所で、式、条件、SQLファンクションおよびSQL文に示されているinteger(整数)に値を指定するときには、必ずこの表記法を使用してください。

integerの構文は次のとおりです。

integer::=

integer.epsの説明が続きます
図integer.epsの説明

digitは0、1、2、3、4、5、6、7、8、9のいずれかです。

整数は最大38桁の精度を記憶できます。

次に、有効な整数を示します。

7
+255

NUMBERおよび浮動小数点リテラル

このマニュアルの他の箇所で、式、条件、SQLファンクションおよびSQL文に示されているnumberまたはn(数)に値を指定するときには、必ずこれらの表記法を使用してください。

numberの構文は次のとおりです。

number::=

number.epsの説明が続きます
図number.epsの説明

説明:

  • +は正、-は負の値を示します。符号を省略すると、デフォルトで正の値になります。

  • digitは0、1、2、3、4、5、6、7、8、9のいずれかです。

  • eまたはEは、数が科学表記法で指定されることを示します。Eの後の数字が指数を示します。指数は-130から125の範囲で指定します。

  • fまたはFは、数がBINARY_FLOAT型の32ビットの2進浮動小数点数であることを示します。

  • dまたはDは、数がBINARY_DOUBLE型の64ビットの2進浮動小数点数であることを示します。

    f(F)およびd(D)を省略すると、数はNUMBER型になります。

    接尾辞f(F)およびd(D)は、浮動小数点数リテラルでのみサポートされます。文字列ではNUMBERに変換されるため、サポートされません。たとえば、OracleがNUMBERを想定している場合に、文字列'9'を使用すると、文字列は数字の9に変換されます。ただし、文字列'9f'を使用すると、変換は行われず、エラーが戻されます。

NUMBER型の数値は、最大38桁の精度を記憶できます。NUMBER、BINARY_FLOATまたはBINARY_DOUBLEで提供される精度以上の精度がリテラルに必要な場合、値は切り捨てられます。リテラルの範囲がNUMBER、BINARY_FLOATまたはBINARY_DOUBLEでサポートされる範囲を超える場合、エラーが発生します。

数値リテラルはSQL構文の要素であり、NLS設定には従いません。数値リテラルの小数点区切り文字は、常にピリオド(.)です。ただし、数値が必要な箇所でテキスト・リテラルが指定されている場合、テキスト・リテラルはNLSに従って暗黙的に数値に変換されます。テキスト・リテラルに含まれる小数点区切りは、初期化パラメータNLS_NUMERIC_CHARACTERSで設定されたものである必要があります。NLS環境に関係なく動作するように、SQLスクリプトでは数値リテラルを使用することが推奨されます。

次の例は、数値リテラルとテキスト・リテラルでの小数点区切りの動作を示しています。これらの例では、次の文で現在のセッションに、カンマ(,)がNLS小数点区切りとして設定されていると想定しています。

ALTER SESSION SET NLS_NUMERIC_CHARACTERS=',.';

前の文では、ピリオド(.)もNLSグループ・セパレータとして設定されていますが、それはこれらの例とは関係ありません。

この例では、数値リテラル1.23では必要な小数点区切り(.)が使用されており、テキスト・リテラル'2,34'では設定されたNLS小数点区切り(,)が使用されています。テキスト・リテラルは数値2.34に変換され、出力は小数点区切りのカンマを使用して表示されます。 

SELECT 2 * 1.23, 3 * '2,34' FROM DUAL;

    2*1.23   3*'2,34'
---------- ----------
      2,46       7,02

次の例では、カンマは数値リテラルの一部として扱われていません。かわりに、カンマは2つの数式2*1と23のリストのデリミタとして扱われています。 

SELECT 2 * 1,23 FROM DUAL;

       2*1         23
---------- ----------
         2         23

次の例は、テキストから数値への暗黙的な変換を成功させるには、テキスト・リテラルの小数点区切りとNLS小数点区切りが一致する必要があることを示しています。　次の文は、小数点区切り(.)が設定されたNLS小数点区切り(,)と一致しないため失敗します。

SELECT 3 * '2.34' FROM DUAL;
           *
ERROR at line 1:
ORA-01722: invalid number

関連項目:

ALTER SESSIONおよび『Oracle Databaseリファレンス』を参照してください。

次に、有効なNUMBERリテラルを示します。 

25
+6.34
0.5
25e-03
-1

次に、有効な浮動小数点数リテラルを示します。

25f
+6.34F
0.5d
-1D

値を数値リテラルとして表現できない場合は、次の浮動小数点リテラルを使用することもできます。

表2-11 浮動小数点リテラル

リテラル 意味 例

binary_float_nan

IS NAN条件がtrueであるBINARY_FLOAT型の値 

SELECT COUNT(*) 
  FROM employees 
  WHERE TO_BINARY_FLOAT(commission_pct)
     != BINARY_FLOAT_NAN;

binary_float_infinity

単精度の正の無限大

 
SELECT COUNT(*) 
  FROM employees 
  WHERE salary < BINARY_FLOAT_INFINITY;

binary_double_nan

IS NAN条件がtrueであるBINARY_DOUBLE型の値 

SELECT COUNT(*) 
  FROM employees 
  WHERE TO_BINARY_FLOAT(commission_pct)
     != BINARY_FLOAT_NAN;

binary_double_infinity

倍精度の正の無限大

 
SELECT COUNT(*) 
  FROM employees 
  WHERE salary < BINARY_DOUBLE_INFINITY;

日時リテラル

Oracle Databaseは、DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONEおよびTIMESTAMP WITH LOCAL TIME ZONEの4つの日時データ型をサポートしています。

日付リテラル

DATE値を文字列リテラルに指定するか、文字値や数値をTO_DATEファンクションによって日付値に変換できます。Oracle Databaseが文字列リテラルのかわりにTO_DATE式を受け入れるのは、DATEリテラルの場合だけです。

DATE値をリテラルに指定する場合は、グレゴリオ暦を使用する必要があります。次の例に示すように、ANSIのリテラルを指定できます。 

DATE '1998-12-25'

ANSIの日付リテラルには、時刻部分を含めず、書式'YYYY-MM-DD'で指定する必要があります。また、次のように、Oracleの日付値を指定できます。 

TO_DATE('98-DEC-25 17:30','YY-MON-DD HH24:MI')

OracleのDATE値のデフォルトの日付書式は、初期化パラメータNLS_DATE_FORMATで指定します。この例は、日付としての2桁の数、月の名前の省略形、年の下2桁および24時間表記の時刻を含む日付書式です。

デフォルト日付書式の文字値が日付式で使用されると、Oracleは自動的にそれらを日付値に変換します。

日付値を指定する場合に時刻コンポーネントを指定しないと、デフォルト時刻の真夜中(24時間表記では00:00:00、12時間表記では12:00:00)が採用されます。日付値を指定する場合に日付を指定しないと、デフォルト日付である現在の月の最初の日が採用されます。

OracleのDATE列には、常に、日付フィールドと時刻フィールドが含まれます。したがって、DATE列を問い合せる場合は、問合せで時刻フィールドを指定するか、またはDATE列の時刻フィールドが真夜中に設定されていることを確認する必要があります。そうでない場合、Oracleは、正しい結果を戻さない場合があります。時刻フィールドを真夜中に設定するには、TRUNC日付ファンクションを使用します。また、問合せに、等価性や非等価性の条件のかわりに大/小条件を含めることもできます。

次の例では、数値列row_numおよびDATE列datecolを持つ表my_tableがあると想定します。 

INSERT INTO my_table VALUES (1, SYSDATE);
INSERT INTO my_table VALUES (2, TRUNC(SYSDATE));

SELECT *
  FROM my_table;

   ROW_NUM DATECOL
---------- ---------
         1 03-OCT-02
         2 03-OCT-02

SELECT *
  FROM my_table
  WHERE datecol > TO_DATE('02-OCT-02', 'DD-MON-YY');

   ROW_NUM DATECOL
---------- ---------
         1 03-OCT-02
         2 03-OCT-02

SELECT *
  FROM my_table
  WHERE datecol = TO_DATE('03-OCT-02','DD-MON-YY');

   ROW_NUM DATECOL
---------- ---------
         2 03-OCT-02

DATE列の時刻フィールドが真夜中に設定されている場合は、前述の例に示すように、DATE列に対して問い合せるか、DATEリテラルを使用して問い合せることができます。 

SELECT *
  FROM my_table
  WHERE datecol = DATE '2002-10-03';


   ROW_NUM DATECOL
---------- ---------
         2 03-OCT-02

ただし、DATE列が真夜中以外の値を含む場合、正しい結果を得るためには、問合せで時刻フィールドを排除する必要があります。次に例を示します。 

SELECT *
  FROM my_table
  WHERE TRUNC(datecol) = DATE '2002-10-03';


   ROW_NUM DATECOL
---------- ---------
         1 03-OCT-02
         2 03-OCT-02

Oracleは、問合せの各行にTRUNCファンクションを適用します。これによって、データの時刻フィールドが真夜中である場合、パフォーマンスが向上します。時刻フィールドを真夜中に設定するには、挿入および更新時に次のいずれかの操作を行います。

  • TO_DATEファンクションを使用して、時刻フィールドをマスクします。 

    INSERT INTO my_table
  VALUES (3, TO_DATE('3-OCT-2002','DD-MON-YYYY'));

  • DATEリテラルを使用します。 

    INSERT INTO my_table
  VALUES (4, '03-OCT-02');

  • TRUNCファンクションを使用します。 

    INSERT INTO my_table
  VALUES (5, TRUNC(SYSDATE));

SYSDATE日付ファンクションは、現在のシステムの日付および時刻を返します。CURRENT_DATEファンクションは、現行のセッションの日付を返します。SYSDATE、TO_*日時ファンクションおよびデフォルト日付書式の詳細は、日時ファンクションを参照してください。

TIMESTAMPリテラル

TIMESTAMPデータ型には、年、月、日、時、分、秒および小数秒の値が格納されます。TIMESTAMPをリテラルに指定する場合、fractional_seconds_precision値には最大9桁を指定できます。次に例を示します。 

TIMESTAMP '1997-01-31 09:26:50.124'

TIMESTAMP WITH TIME ZONEリテラル

TIMESTAMP WITH TIME ZONEデータ型はTIMESTAMPの変形で、値にはタイムゾーン地域名またはタイムゾーン・オフセットが含まれます。TIMESTAMP WITH TIME ZONEをリテラルに指定する場合、fractional_seconds_precision値には最大9桁を指定できます。次に例を示します。 

TIMESTAMP '1997-01-31 09:26:56.66 +02:00'

2つのTIMESTAMP WITH TIME ZONE値がUTCで同じ時刻を表す場合は、データに格納されたTIME ZONEオフセットにかかわらず、同一であるとみなされます。たとえば、次のようになります。 

TIMESTAMP '1999-04-15 8:00:00 -8:00'

前述の例文は次の例文と同じです。

TIMESTAMP '1999-04-15 11:00:00 -5:00'

太平洋標準時の午前8時は、東部標準時の午前11時と同じです。

UTCオフセットをTZR(タイムゾーン地域名)書式要素に置換できます。次の例では、前述の例と同じ値を持ちます。 

TIMESTAMP '1999-04-15 8:00:00 US/Pacific'

夏時間に切り替えられる境界のあいまいさを排除するには、TZRおよび対応するTZD書式要素の両方を使用します。次の例では、前述の例が確実に夏時間の値を戻します。 

TIMESTAMP '1999-10-29 01:30:00 US/Pacific PDT'

タイムゾーン・オフセットは日時式を使用して表記することもできます。

SELECT TIMESTAMP '2009-10-29 01:30:00' AT TIME ZONE 'US/Pacific'
  FROM DUAL;

関連項目:

詳細は、日時式を参照してください。

ERROR_ON_OVERLAP_TIMEセッション・パラメータをTRUEに設定しておくと、TZD書式要素を追加しなかったために日時値があいまいな場合、Oracleはエラーを戻します。そのパラメータがFALSEに設定されている場合、不明確な日時は、指定されている地域の標準時間として解釈されます。

TIMESTAMP WITH LOCAL TIME ZONEリテラル

TIMESTAMP WITH LOCAL TIME ZONEデータ型は、TIMESTAMP WITH TIME ZONEとは異なり、データベースに格納されるデータはデータベースのタイムゾーンに対して正規化されます。タイムゾーン・オフセットは、列データの一部として格納されません。TIMESTAMP WITH LOCAL TIME ZONEにはリテラルはありません。他の有効な日時リテラルを使用してこのデータ型の値を示してください。次の表には、TIMESTAMP WITH LOCAL TIME ZONE列に値を挿入するときに使用できる一部の書式と、問合せによって戻される対応する値を示します。

表2-12 TIMESTAMP WITH LOCAL TIME ZONEリテラル

INSERT文の中で指定する値 問合せによって戻される値

'19-FEB-2004'

19-FEB-2004.00.00.000000 AM

SYSTIMESTAMP

19-FEB-04 02.54.36.497659 PM

TO_TIMESTAMP('19-FEB-2004', 'DD-MON-YYYY')

19-FEB-04 12.00.00.000000 AM

SYSDATE

19-FEB-04 02.55.29.000000 PM

TO_DATE('19-FEB-2004', 'DD-MON-YYYY')

19-FEB-04 12.00.00.000000 AM

TIMESTAMP'2004-02-19 8:00:00 US/Pacific'

19-FEB-04 08.00.00.000000 AM

指定した値に時刻コンポーネントがない場合(明示的または暗黙的に)、デフォルトの午前0時の値が戻されます。

期間リテラル

期間リテラルは期間を指定します。年および月、または日付、時間、分および秒の違いを指定できます。Oracle Databaseは、YEAR TO MONTHおよびDAY TO SECONDの2種類の期間リテラルをサポートします。各リテラルは先行フィールドを含み、後続フィールドを含むこともあります。先頭フィールドは、測定する日付または時間の基本単位を定義します。末尾フィールドは、該当する基本単位の最小の増分を定義します。たとえば、YEAR TO MONTH期間では、最も近い月に対する年との期間が考慮されます。DAY TO MINUTE期間では、最も近い分に対する日との期間が考慮されます。

数値形式の日付データがある場合、NUMTOYMINTERVALまたはNUMTODSINTERVAL変換ファンクションを使用して、数値データを期間値へ変換できます。

期間リテラルは、主に分析ファンクションとともに使用します。

関連項目:

分析ファンクション、NUMTODSINTERVAL、NUMTOYMINTERVAL

INTERVALYEARTOMONTH

次の構文を使用して、YEAR TO MONTH期間リテラルを指定します。

interval_year_to_month::=

interval_year_to_month.epsの説明が続きます
図interval_year_to_month.epsの説明

説明:

  • 'integer [-integer]'には、リテラルの先行フィールドおよびオプションの後続フィールドの整数値を指定します。先行フィールドがYEARで、後続フィールドがMONTHの場合、MONTHフィールドの整数値の範囲は0から11です。

  • precisionは、先行フィールドの桁数です。先行フィールド精度の有効範囲は0から9で、デフォルトは2です。

先行フィールドに対する制限

後続のフィールドを指定する場合、先行のフィールドよりも下位の単位を指定する必要があります。たとえば、INTERVAL '0-1' MONTH TO YEARは無効です。

次のINTERVAL YEAR TO MONTHリテラルは、intervalが123年2か月であることを示しています。 

INTERVAL '123-2' YEAR(3) TO MONTH

このリテラルの他の書式の例を次に示します。省略バージョンも含みます。

表2-13 INTERVAL YEAR TO MONTHリテラルの書式

期間リテラルの形式 解釈

INTERVAL '123-2' YEAR(3) TO MONTH

123年2か月のintervalを示します。先行フィールド精度がデフォルトの2桁より大きい場合、その精度を指定してください。

INTERVAL '123' YEAR(3)

123年0か月のintervalを示します。

INTERVAL '300' MONTH(3)

300か月のintervalを示します。

INTERVAL '4' YEAR

INTERVAL '4-0' YEAR TO MONTHへマップし、4年を示します。

INTERVAL '50' MONTH

INTERVAL '4-2' YEAR TO MONTHへマップし、50か月(4年2か月)を示します。

INTERVAL '123' YEAR

デフォルト精度は2ですが、123が3桁のため、エラーを戻します。

あるINTERVAL YEAR TO MONTHリテラルを別のリテラルに加算または減算して、新しいINTERVAL YEAR TO MONTHリテラルを作成できます。次に例を示します。 

INTERVAL '5-3' YEAR TO MONTH + INTERVAL'20' MONTH = 
INTERVAL '6-11' YEAR TO MONTH

INTERVALDAYTOSECOND

次の構文を使用して、DAY TO SECOND期間リテラルを指定します。

interval_day_to_second::=

interval_day_to_second.epsの説明が続きます
図interval_day_to_second.epsの説明

説明:

  • integerは日数を指定します。ここで指定した値の桁数が先行精度で指定した桁数より大きい場合、Oracleはエラーを戻します。

  • time_exprには、HH[:MI[:SS[.n]]]、MI[:SS[.n]]またはSS[.n]の形式で時間を指定します(ここで、nは秒の小数部を指定します)。nの桁数が、fractional_seconds_precisionで指定した数より多い場合、nはfractional_seconds_precision値で指定した数に丸められます。先行フィールドがDAYの場合にのみ、1桁の整数および1つの空白の後にtime_exprを指定できます。

  • leading_precisionは、先行フィールドの桁数です。0から9までの値を使用できます。デフォルトは2です。

  • fractional_seconds_precisionは、SECOND日時フィールドの小数部の桁数です。有効範囲は1から9です。デフォルトは6です。

先行フィールドに対する制限:

後続のフィールドを指定する場合、先行のフィールドよりも下位の単位を指定する必要があります。たとえば、INTERVAL MINUTE TO DAYは無効です。このため、SECONDが先行フィールドの場合、期間リテラルは後続フィールドを持つことができません。

後続フィールドの有効範囲は次のとおりです。

  • HOUR: 0から23

  • MINUTE: 0から59

  • SECOND: 0から59.999999999

様々なINTERVAL DAY TO SECONDリテラルの書式の例を次に示します。省略バージョンも含みます。

表2-14 INTERVAL DAY TO SECONDリテラルの書式

期間リテラルの形式 解釈

INTERVAL '4 5:12:10.222' DAY TO SECOND(3)

4日、5時間、12分、10秒、222ミリ秒。

INTERVAL '4 5:12' DAY TO MINUTE

4日5時間12分を示します。

INTERVAL '400 5' DAY(3) TO HOUR

400日5時間を示します。

INTERVAL '400' DAY(3)

400日を示します。

INTERVAL '11:12:10.2222222' HOUR TO SECOND(7)

11時間12分10.2222222秒を示します。

INTERVAL '11:20' HOUR TO MINUTE

11時間20分を示します。

INTERVAL '10' HOUR

10時間を示します。

INTERVAL '10:22' MINUTE TO SECOND

10分22秒を示します。

INTERVAL '10' MINUTE

10分を示します。

INTERVAL '4' DAY

4日を示します。

INTERVAL '25' HOUR

25時間を示します。

INTERVAL '40' MINUTE

40分を示します。

INTERVAL '120' HOUR(3)

120時間を示します。

INTERVAL '30.12345' SECOND(2,4)

30.1235秒を示します。精度は4のため、秒の小数部'12345'は'1235'に丸められます。

DAY TO SECOND期間リテラルを別のDAY TO SECOND期間リテラルに加算または減算できます。次に例を示します。 

INTERVAL'20' DAY - INTERVAL'240' HOUR = INTERVAL'10-0' DAY TO SECOND