如何在日期字段Oracle SQL Developer中插入NULL

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

当我尝试插入空值时,我得到以下错误.

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',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:

解决方法

SQL错误：ORA-00904 ::可能导致无效的标识符,因为您的FOREIGN KEY引用了一个不存在的列 – 检查列名是否拼写正确并且应该解决它(然后您的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,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,88128
);

如果您只使用’1-FEB-2016’中的字符串,那么Oracle将隐式尝试使用TO_DATE()函数转换字符串文字,并使用NLS_DATE_FORMAT会话参数作为格式掩码.如果它们匹配,那么它将工作,但这是一个客户端变量,因此可以更改,然后查询将中断而代码没有更改(并且很难调试).简单的答案是确保您使用TO_DATE()和指定格式掩码(根据上面的查询)比较日期值,或使用ANSI日期文字DATE’2016-02-01′(它独立于NLS设置).