为什么 Oracle 的 SQL 中的文字'01-01-07'是错误的..？

CUST_TRANS -
Name Null? Type
-------------- ----------------- ------------------
CUSTNO NOT NULL CHAR (2)
TRANSDATE DATE
TRANSAMT NUMBER (6, 2)
CUSTNO TRANSDATE TRANSAMT
------------- ----------------------- -----------------------
11 01-JAN-07 1000
22 01-FEB-07 2000
33 01-MAR-07 3000
Dates are stored in the default date format dd-mm-rr in the CUST_TRANS table.
Which three SQL statements would execute successfully? (Choose three.)
A. SELECT transdate + '10' FROM cust_trans;
B. SELECT * FROM cust_trans WHERE transdate = '01-01-07';
C. SELECT transamt FROM cust_trans WHERE custno > '11';
D. SELECT * FROM cust_trans WHERE transdate='01-JANUARY-07';
E. SELECT custno + 'A' FROM cust_trans WHERE transamt > 2000;

我正在研究这个问题，我在sqldeveloper中创建了表，我尝试在B中运行相同的代码，它很有效。所以我想知道为什么答案B被认为是错误的。

INSERT ALL 
INTO CUST_TRANS VALUES (11, '01-jAN-07', 1000)
INTO CUST_TRANS VALUES (22, '01-FEB-07', 2000)
INTO CUST_TRANS VALUES (33, '01-MAR-07', 3000)
SELECT * FROM DUAL;

我做的桌子。我试着运行的脚本

SELECT * FROM cust_trans WHERE transdate = '01-01-07';

它在sqldeveloper中成功运行，我正在运行最新的oracle版本

NLS_LANGUAGE    ENGLISH
NLS_TERRITORY   CANADA
NLS_CURRENCY    $
NLS_ISO_CURRENCY    CANADA
NLS_NUMERIC_CHARACTERS  .,
NLS_CALENDAR    GREGORIAN
NLS_DATE_FORMAT RR-MM-DD
NLS_DATE_LANGUAGE   ENGLISH
NLS_SORT    BINARY
NLS_TIME_FORMAT HH24:MI:SSXFF
NLS_TIMESTAMP_FORMAT    RR-MM-DD HH24:MI:SSXFF
NLS_TIME_TZ_FORMAT  HH24:MI:SSXFF TZR
NLS_TIMESTAMP_TZ_FORMAT RR-MM-DD HH24:MI:SSXFF TZR
NLS_DUAL_CURRENCY   $
NLS_COMP    BINARY
NLS_LENGTH_SEMANTICS    BYTE
NLS_NCHAR_CONV_EXCP FALSE

我的配置

日期以默认日期格式dd-mm-rr存储在CUST_TRANS表中。

这句话错了！DATE是一种二进制数据类型，由7个字节组成，表示：世纪、世纪年、月、日、小时、分钟和秒。它是与所有这些组件一起存储的，并且是以任何特定(人类可读)格式存储的，因为它存储为二进制值。

如果以dd-mm-rr格式存储类似日期的数据，则将它们存储为字符串，而不是DATE。如果将日期存储为DATE，则不能将其存储为可读格式。

如果您想了解Oracle如何存储DATE，请使用：

SELECT DUMP(transdate) FROM cust_trans;

设置问题的人可能的意思是，dd-mm-rr是Oracle用于字符串到日期和日期到字符串转换的默认格式(使用NLS_DATE_FORMAT会话参数)，并且一些客户端应用程序(即SQL*Plus和SQLDeveloper)在显示日期时用作默认显示格式。然而，仅仅因为某些会话参数允许字符串的隐式转换，并不意味着这是一种好的做法。

指出dd-mm-rr是默认日期格式也是错误的，因为它只是某些地区(阿尔及利亚、巴林、印度、摩洛哥、荷兰和突尼斯)的NLS_DATE_FORMAT会话参数的默认格式；在世界其他地方，它不是默认的。

B。SELECT * FROM cust_trans WHERE transdate = '01-01-07';
我试着在B中运行相同的代码，它很有效。所以我想知道为什么答案B被认为是错误的。

B。只要Oracle可以执行从字符串到DATE的隐式强制转换，并且它使用NLS_DATE_FORMAT会话来执行该转换，就可以工作。

代码实际上与相同

SELECT *
FROM   cust_trans
WHERE  transdate = TO_DATE(
'01-01-07',
( SELECT value
FROM   NLS_SESSION_PARAMETERS
WHERE  PARAMETER = 'NLS_DATE_FORMAT' )
);

如果NLS_DATE_FORMAT与dd-mm-rr(或字符串到日期转换规则的另一种格式)匹配，则它将对其进行解析

如果你这样做：

ALTER SESSION SET NLS_DATE_FORMAT = 'dd-mm-rr';
SELECT * FROM cust_trans WHERE transdate = '01-01-07'

然后，日期将被解析为2007-01-01 00:00:00，但如果您使用：

ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
SELECT * FROM cust_trans WHERE transdate = '01-01-07'

然后，日期将被解析为0001-01-07 00:00:00，它有一个意想不到的世纪，年份和日期被交换。

如果您使用：

ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-RR';
SELECT * FROM cust_trans WHERE transdate = '01-01-07'

然后，它不解析该值，并引发异常ORA-01843: not a valid month。

db<gt；小提琴这里

您应该NEVER依赖字符串到日期的隐式强制转换。

更好的解决方案是：

使用日期文字

SELECT * FROM cust_trans WHERE transdate = DATE '2007-01-01'

使用时间戳文字

SELECT * FROM cust_trans WHERE transdate = TIMESTAMP '2007-01-01 00:00:00'

或者，使用显式字符串进行日期转换

SELECT * FROM cust_trans WHERE transdate = TO_DATE('01-01-07', 'DD-MM-RR')

因为这些都不依赖于数据类型之间的隐式强制转换。

关于这个问题的答案。

如果NLS_DATE_FORMAT是DD-MM-RR，那么四个(而不是三个)选项是将成功解析和执行的查询。

给定设置：

CREATE TABLE cust_trans (
custno    CHAR(2),
transdate DATE,
transamt  NUMBER
);
INSERT INTO cust_trans (custno, transdate, transamt) VALUES ('25', DATE '2007-01-01', 5000);
ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MM-RR';

然后：

A。SELECT transdate + '10' FROM cust_trans;
将执行从字符串文字'10'到数字10的隐式转换，然后将向transdate添加10天并输出：
TRANSDATE+'10'
11-07
您有一个名为nls_date_format的东西。您可以通过更改会话来设置它。
当你把它设置成这样的问题格式时：
```
ALTER SESSION SET NLS_DATE_FORMAT = 'dd-mm-rr';
```
B的答案很好。
当您将其设置为，例如：
```
ALTER SESSION SET NLS_DATE_FORMAT = 'DD MON YYYY';
```
它不会起作用。
演示
您可以通过以下查询检查此参数在会话中的设置方式：
```
select *
from nls_session_parameters
where parameter = 'NLS_DATE_FORMAT';
```

相关内容

最新更新

热门标签：