使用ORACLE-11g在数据库中插入timestamp类型和包含撇号的数据

Question

我已经成功地创建了表，如下所示：

CREATE TABLE TOY_STORE
(
  TOY_STORE_ID NUMBER(3) PRIMARY KEY,
  TOY_STORE_NAME VARCHAR2(30) NOT NULL,
  CITY VARCHAR2(30) DEFAULT 'Delhi',
  PHONENUMBER NUMBER(10) NOT NULL UNIQUE,
  STORE_OPENING_TIME TIMESTAMP,
  STORE_CLOSING_TIME TIMESTAMP
);

ALTER TABLE TOY_STORE ADD CHECK (EXTRACT(HOUR FROM CAST (TO_CHAR (STORE_OPENING_TIME, 'YYYY-MON-DD HH24:MI:SS') AS TIMESTAMP)) > 8 || NULL);

ALTER TABLE TOY_STORE ADD CHECK (EXTRACT(HOUR FROM CAST(TO_CHAR(STORE_CLOSING_TIME, 'YYYY-MON-DD HH24:MI:SS') AS TIMESTAMP)) < 22 || NULL);

现在我想在表中输入数据。我执行了以下命令(这里的第二个数据是"Kid's Cave")，

INSERT INTO TOY_STORE VALUES(1, 'Kid''s Cave', 'Delhi', 9912312312, 2014-04-01 09:10:12, 2014-04-01 21:42:05); 

但是它显示了以下错误..

ORA-00917: missing comma

请解释一下

Answer 1

您需要将日期放入''中。试试这个：

INSERT INTO TOY_STORE 
VALUES(1, 'Kid''s Cave', 'Delhi', 9912312312, '2014-04-01 09:10:12', '2014-04-01 21:42:05'); 

旁注：

我将建议您使用varchar()而不是Number数据类型来存储PhoneNumbers

Answer 2

日期在查询中没有单引号-使用如下所示：

'2014-04-01 09:10:12', '2014-04-01 21:42:05'

Answer 3

尝试使用to_date将字符串转换为date

INSERT INTO TOY_STORE 
VALUES (1, 'Kid''s Cave', 'Delhi', 9912312312, 
        to_date('2014-04-01 09:10:12', 'yyyy-mm-dd hh24:mi:ss'),
        to_date('2014-04-01 21:42:05', 'yyyy-mm-dd hh24:mi:ss'));

您也可以使用TIMESTAMP literal

INSERT INTO TOY_STORE 
VALUES (1, 'Kid''s Cave', 'Delhi', 9912312312, 
        TIMESTAMP '2014-04-01 09:10:12',
        TIMESTAMP '2014-04-01 21:42:05');