如何在日期字段中插入 NULL Oracle SQL Developer
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/36095250/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert NULL in to date field Oracle SQL Developer
提问by meeshi
CREATE TABLE pledge
(
pledge_ID NUMBER NOT NULL ,
pledge_endDate DATE NULL ,
pledge_startDate DATE NULL ,
pledge_amount DECIMAL(9,2) NULL CONSTRAINT Currency_1322638346 CHECK (pledge_amount >= 0),
artist_userID NUMBER NOT NULL,
follower_userID NUMBER NOT NULL,
CONSTRAINT XPKPledge PRIMARY KEY (pledge_ID),
CONSTRAINT gets FOREIGN KEY (artist_userID) REFERENCES ArtistMember (user_ID),
CONSTRAINT makes FOREIGN KEY (follower_userID) REFERENCES FollowerMember (user_ID)
);
When I try to insert a null value I get the error below.
当我尝试插入空值时，出现以下错误。
INSERT INTO pledge VALUES(559, 'null','1-FEB-2016', 3850, 85275, 88128);
Error report -
SQL Error: ORA-00904: : invalid identifier
00904. 00000 - "%s: invalid identifier"
*Cause:
*Action:
Error starting at line : 209 in command -
INSERT INTO pledge VALUES(559, 'NULL','1-FEB-2016', 3850, 85275, 88128)
Error at Command Line : 209 Column : 13
Error report -
SQL Error: ORA-00942: table or view does not exist
00942. 00000 - "table or view does not exist"
*Cause:
*Action:
采纳答案by MT0
The SQL Error: ORA-00904: : invalid identifieris probably being caused because your FOREIGN KEYs are referencing a column that does not exist - check that the column names are spelt correctly and that should solve it (and then your CREATE TABLEstatement will work).
这SQL Error: ORA-00904: : invalid identifier可能是因为您的FOREIGN KEYs 引用了一个不存在的列 - 检查列名是否拼写正确并且应该解决它（然后您的CREATE TABLE语句将起作用）。
CREATE TABLE ArtistMember (
user_ID INT PRIMARY KEY
);
CREATE TABLE FollowerMember (
user_ID INT PRIMARY KEY
);
CREATE TABLE pledge (
pledge_ID INT CONSTRAINT XPKPledge PRIMARY KEY,
pledge_endDate DATE NULL,
pledge_startDate DATE NULL,
pledge_amount DECIMAL(9,2) NULL CONSTRAINT Currency_1322638346 CHECK (pledge_amount >= 0),
artist_userID INT NOT NULL CONSTRAINT gets REFERENCES ArtistMember (user_ID),
follower_userID INT NOT NULL CONSTRAINT makes REFERENCES FollowerMember (user_ID)
);
INSERT INTO ArtistMember VALUES ( 85275 );
INSERT INTO FollowerMember VALUES( 88128 );
INSERT INTO pledge VALUES(
559,
NULL, -- Use NULL and not 'NULL'
DATE '2016-02-01', -- Use a Date literal and not a string literal
3850,
85275,
88128
);
If you just use the string in '1-FEB-2016'then Oracle will implicitly try to convert the string literal using the TO_DATE()function with the NLS_DATE_FORMATsession parameter as the format mask. If they match then it will work but this is a client variable so can be changed and then the query will break without the code having changed (and be a pain to debug). The simple answer is to ensure that you compare date value by either using TO_DATE()and specifying the format mask (as per the query above) or to use an ANSI date literal DATE '2016-02-01'(which is independent of the NLS settings).
如果您只使用 in 中的字符串，'1-FEB-2016'那么 Oracle 将隐式地尝试使用TO_DATE()以NLS_DATE_FORMAT会话参数作为格式掩码的函数来转换字符串文字。如果它们匹配，那么它将起作用，但这是一个客户端变量，因此可以更改，然后查询将在代码未更改的情况下中断（并且调试起来很痛苦）。简单的答案是确保您通过使用TO_DATE()和指定格式掩码（根据上面的查询）或使用 ANSI 日期文字DATE '2016-02-01'（独立于 NLS 设置）来比较日期值。
回答by Do Nhu Vy
Insert an empty string ''to a NUMBER column, Oracle will insert NULL.
''向 NUMBER 列插入一个空字符串，Oracle 将插入NULL.
For example, col_2has data type is date field or number(12,0)(any datatype what not date field), SQL statement set NULLvalue for col_2like this:
例如，col_2有数据类型是日期字段或number(12,0)（不是日期字段的任何数据类型），SQL语句设置NULL值col_2如下：
insert into foo (col_1, col_2) values ('abc', '');
or
或者
insert into foo (col_1, col_2) values ('abc', NULL);
(it is NULL, not 'NULL'or "NULL")
（是NULL，不是'NULL'或"NULL"）
