第2章 SQL基本元素：字面值

原文地址：http://docs.oracle.com/cd/B19306_01/server.102/b14200/sql_elements003.htm

字面值是常量值的同义词，它们都是指一个固定的数据值。例如，‘JACK’、‘BLUE ISLAND’以及‘101’都是字符字面值；5001是数字字面值。字符字面值使用单引号包含，以便与模式对象的名称区分。

关于日期时间和时间间隔数据类型的字面值参考“第2章 SQL基本元素：数据类型”。

文本字面值

文本字面值、字符字面值和字符串可以交换使用。它们总是使用单引号进行包含。如果语法中使用了词语char，可以指定文本字面值或者其他能够转换为字符数据的表达式，例如表hr.employees的last_name列。

文本字面值的语法如下：

text::=

其中，N或者n说明字面值使用国家字符集（NCHAR或者NVARCHAR2数据）。默认，这种方式输入的文本被服务器先转换为数据库字符集，经过数据库字符集转换为国家字符集。为了避免转换为数据库字符集时可能的数据丢失，可以将环境变量ORA_NCHAR_LITERAL_REPLACE设置为TRUE。这样可以不经过数据库字符集转换。

在语法的上分支中：

• c可以是用户字符集中的任何字符。字面值中的单引号必须前置一个转义字符。要在字面值中包含一个单引号，输入两个单引号。
• ''是标记文本字面值开始和结束的两个单引号。
  

在语法的下分支中：

• Q或者q表示使用替代的引用机制。这种机制运行使用各种不同的分隔符。
• 最外面的' '是两个单引号，分别为开始和结束quote_delimiter的前置和后置引号。
• c可以是用户字符集中的任何字符。可以包含引号（''）。也可以包含quote_delimiter，只要在其后不是一个单引号。
• quote_delimiter可以是任何单字节或者多字节字符，除了空格、制表符以及回车之外。它可以是一个单引号。但是，如果在文本字面值中代表它自身，需要确认其后不是一个单引号。
• 如果开始的quote_delimiter为[、{、(，结束的quote_delimiter必须对应为]、}、>或者)。其他情况下开始和结束的quote_delimiter必须相同。
  

文本字面值同时具有CHAR和VARCHAR2数据类型的属性：

• 在表达式和条件中，文本字面值被看作CHAR类型，比较时使用空格填充的比较语义。参见“第2章 SQL基本元素：数据类型比较规则”。
• 文本字面值最大长度为4000字节。
  

以下是一些有效的文本字面值：

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

以下是使用替代引用机制时的一些有效文本字面值：

q'!name LIKE '%DBMS_%%'!'
q''
q'{SELECT * FROM employees WHERE last_name = 'Smith';}'
nq'ï Ÿ1234 ï'
q'"name like '['"'

数字字面值

整数字面值

语法如下：

integer::=

其中，digit是0，1，2，3，4，5，6，7，8，9之一。

整数最多存储38位精度的数字。以下是一些有效的整数：

7
+255

NUMBER与浮点型字面值

语法如下：

number::=

其中，

• +或者-代表正数或者负数。如果忽略该符号，代表正数。
• digit为0，1，2，3，4，5，6，7，8，9之一。
• e或者E代表科学计数法。E之后的数字代表指数，从-130到125。
• f或者F代表32位二进制浮点数（BINARY_FLOAT）。
• d或者D代表64位二进制浮点数（BINARY_DOUBLE）。
  

如果忽略f或者F以及d或者D，数字类型为NUMBER。

后缀f（F）以及d（D）仅支持浮点型数字字面值，不能用于转换为NUMBER的字符串。也就是说，如果需要一个NUMBER而输入的是字符串'9'，则将字符串转换为数字9。但是如果输入的是'9f'，则转换失败并返回一个错误。

NUMBER类型的数字最多支持38位精度。如果字面值需要高于NUMBER、BINARY_FLOAT或者BINARY_DOUBLE提供的精度，Oracle将会截断该值。如果字面值的范围超过NUMBER、BINARY_FLOAT或者BINARY_DOUBLE支持的范围，Oracle将会产生一个错误。

如果使用初始化参数NLS_NUMERIC_CHARACTERS将小数点分隔符设置为非句点号（.），必须使用'text'标记指定数字字面值。Oracle自动将这种文本字面值转换为数字值。

注释：这种标记法不能用于浮点数字面值。

例如，如果NLS_NUMERIC_CHARACTERS指定逗号作为小数点分隔符，使用以下方式指定数字5.123：

'5,123'

以下是一些有效的NUMBER字面值：

25
+6.34
0.5
25e-03
-1

以下是一些有效的浮点型数字字面值：

25f
+6.34F
0.5d
-1D

也可以在无法使用数字字面值表示一个值时使用以下补充的浮点型字面值：

日期时间字面值

Oracle数据库支持4种日期时间数据类型：DATE、TIMESTAMP、TIMESTAMP WITH TIME ZONE以及TIMESTAMP WITH LOCAL TIME ZONE。

DATE字面值

可以使用字符串字面值指定一个DATE值，或者使用TO_DATE函数将字符或者数字值转换为日期值。DATE字面值是唯一可以使用TO_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指定。

当字符值用于日期表达式时，自动使用默认的日期格式进行转换。

如果日期值不包含时间部分，默认的时间为午夜（24小时制的00:00:00或者12小时制的12:00:00）。如果日期值不包含日期部分，默认的日期为当前月的第一天。

Oracle DATE列总是包含日期域和时间域。因此，如果查询DATE列，必须指定时间域，或者确认DATE列的时间域被设置为午夜。否则，可能不能返回希望的查询结果。可以使用TRUNC(date)函数将时间域设置为午夜，或者使用大于或小于条件替代等于或不等于条件。

以下是一些示例，假设表my_table拥有一个数字列row_num以及一个DATE列datecol：

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('03-OCT-02','DD-MON-YY');

   ROW_NUM DATECOL
---------- ---------
         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

如果确认DATE列的时间域被设置为午夜，可以按照上例的方法查询DATE列，或者使用DATE字面值：

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

但是，如果DATE列包含了其他时间值，必须在查询中过滤掉时间域。例如：

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

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));
  

TIMESTAMP字面值

TIMESTAMP数据类型存储年、月、日、小时、分以及秒和小数秒的值。指定TIMESTAMP字面值时，小数秒的精度可以为最大9位数字，如下所示：

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

TIMESTAMP WITH TIME ZONE字面值

TIMESTAMP WITH TIME ZONE数据类型是TIMESTAMP的变体，包含一个时区偏移量。例如：

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

在UTC中表示同一时刻的两个TIMESTAMP WITH TIME ZONE值是相等的，不考虑数据中的TIME ZONE偏移量。例如：

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

与以下时间相同：

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

也就是说，太平洋标准时间的早上8点与美国东部标准时间的早上11点相同。

可以使用TZR格式替代UTC偏移量。例如，以下示例与上例的时间相同：

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

为了消除夏令时切换时的边界歧义，可以同时使用TZR和对应的TZD格式。以下示例确保前面的示例能够返回一个夏令时的值：

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

还可以使用日期时间表达式表示时区偏移量：

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

TIMESTAMP WITH LOCAL TIME ZONE字面值

TIMESTAMP WITH LOCAL TIME ZONE数据类型与TIMESTAMP WITH TIME ZONE类型不同，它的数据在存储时规格化为数据库的时区。时区偏移量不作为数据的一部分存储。TIMESTAMP WITH LOCAL TIME ZONE不存在字面值。但是，可以使用任何其他有效的日期时间字面值表示这种数据类型。下表显示了一些可以用于插入时的格式，以及对应查询返回的值。

时间间隔字面值

时间间隔表示一个时间段。可以使用年和月（YEAR TO MONTH），或者日、小时、分和秒（DAY TO SECOND）表示这些时间差。

可以使用NUMTOYMINTERVAL或者NUMTODSINTERVAL转换函数将数字转换为时间间隔值。

INTERVAL TO MONTH

语法如下：

interval_year_to_month::=

其中，

• 'integer [-integer]'代表字面值的主域和可选的尾域。如果主域是YEAR，尾域是MONTH，月的整型值范围为0到11。
• precision是主域的最大位数。有效范围为0到9，默认为2。
  

主域的限制：如果指定了一个尾域，它必须位于主域之后。例如 INTERVAL '0-1' MONTH TO YEAR是无效的。

以下字面值代表123年2个月的时间段：

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

以下是一些其他形式的示例，包括缩写版本：

可以进行时间间隔字面值的加减，产生另一个字面值。例如：

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

INTERVAL DAY TO SECOND

语法如下：

interval_day_to_second::=

其中，

• integer表示天数。如果位数超过了精度指定的值，返回错误。
• time_expr表示时间，格式为HH[:MI[:SS[.n]]]或者MI[:SS[.n]]或者SS[.n]，其中n表示小数秒。如果n的位数超过了fractional_seconds_precision指定的位数，进行舍入。
• 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字面值的示例：

可以进行DAY TO SECOND时间间隔字面值的加减，产生另一个字面值。例如：

text.gif

integer.gif

number.gif

interval_year_to_month.gif

interval_day_to_second.gif

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/24945919/viewspace-747030/，如需转载，请注明出处，否则将追究法律责任。