sql - 尝试将值插入表时出错,但不确定原因
问题描述
我正在使用 Oracle,我正在尝试将值插入一个名为 bug 的表中,但我不确定它的含义以及如何修复它。
我的错误如下所示:
从第 78 行开始的错误命令 - INSERT INTO Bug(Bug_ID, Bug_Type, Bug_Desc, Bug_Time) VALUES (00, 'Crash', '软件停止正常运行并退出', DATE '1980-03-20 09:26:50 ')
命令行错误:78 列:133 错误报告 - SQL 错误:ORA-01861:文字与格式字符串 01861 不匹配。00000 -“文字与格式字符串不匹配”
*原因:输入中的文字必须与格式字符串中的文字长度相同(前导空格除外)。如果启用了“FX”修饰符,则文字必须完全匹配,没有多余的空格。
*操作:更正格式字符串以匹配文字。
任何帮助将不胜感激。
到目前为止,我的代码如下所示:
--CREATE SCRIPTS
/*put your create scripts here – your script should not commented out*/
CREATE TABLE Project
(
Proj_ID integer,
Proj_Name varchar(10),
Proj_Start_Date date,
primary key (Proj_ID)
);
CREATE TABLE Bug
(
Bug_ID integer,
Bug_Type varchar(40),
Bug_Desc varchar(20),
Bug_Time date,
primary key(Bug_ID)
);
CREATE TABLE Engineer
(
Engineer_ID integer,
Engineer_Name varchar(10),
Engineer_Type varchar(20),
primary key (Engineer_ID)
);
CREATE TABLE Bug_Project
(
Bug_ID integer,
Proj_ID integer,
primary key(Bug_ID, Proj_ID),
foreign key(Bug_ID) references Bug (Bug_ID),
foreign key(Proj_ID) references Project (Proj_ID)
);
CREATE TABLE Fix_Allocation
(
Engineer_ID integer,
Bug_ID integer,
primary key(Engineer_ID, Bug_ID),
foreign key(Engineer_ID) references Engineer (Engineer_ID),
foreign key(Bug_ID) references Bug (Bug_ID)
);
CREATE TABLE Test_Allocation
(
Engineer_ID integer,
Bug_ID integer,
primary key(Engineer_ID, Bug_ID),
foreign key(Engineer_ID) references Engineer (Engineer_ID),
foreign key(Bug_ID) references Bug (Bug_ID)
);
CREATE TABLE Note
(
Engineer_ID integer,
Bug_ID integer,
Note_author varchar(10),
Note_contents varchar(20),
primary key(Engineer_ID, Bug_ID),
foreign key(Engineer_ID) references Engineer (Engineer_ID),
foreign key(Bug_ID) references Bug (Bug_ID)
);
COMMIT;
--INSERT SCRIPTS
/*put your insert scripts here – your script should not commented out */
INSERT INTO Project(Proj_ID, Proj_Name, Proj_Start_Date) VALUES (00, 'Project 1', DATE '1980-02-14');
INSERT INTO Project(Proj_ID, Proj_Name, Proj_Start_Date) VALUES (01, 'Project 2', DATE '1985-12-11');
INSERT INTO Project(Proj_ID, Proj_Name, Proj_Start_Date) VALUES (02, 'Project 3', DATE '1992-06-03');
INSERT INTO Project(Proj_ID, Proj_Name, Proj_Start_Date) VALUES (03, 'Project 4', DATE '2000-07-22');
INSERT INTO Project(Proj_ID, Proj_Name, Proj_Start_Date) VALUES (04, 'Project 5', DATE '2012-03-19');
INSERT INTO Project(Proj_ID, Proj_Name, Proj_Start_Date) VALUES (05, 'Project 6', DATE '2015-10-21');
INSERT INTO Bug(Bug_ID, Bug_Type, Bug_Desc, Bug_Time) VALUES (00, 'Crash', 'Software stopped functioning properly and exited', DATE '1980-03-20 09:26:50');
INSERT INTO Bug(Bug_ID, Bug_Type, Bug_Desc, Bug_Time) VALUES (01, 'Run Time Error', 'Wrong output due to a logical error', DATE '1982-06-31 11:36:32');
INSERT INTO Bug(Bug_ID, Bug_Type, Bug_Desc, Bug_Time) VALUES (02, 'Compilation Error', 'Problems with the compiler, failed complication of source code', DATE '1987-07-12 14:11:15');
INSERT INTO Bug(Bug_ID, Bug_Type, Bug_Desc, Bug_Time) VALUES (03, 'Crash', 'Software stopped functioning properly and exited', DATE '1993-01-31 03:21:17');
INSERT INTO Bug(Bug_ID, Bug_Type, Bug_Desc, Bug_Time) VALUES (04, 'Logical Error', 'Unexpected behavior due to problem in source code', DATE '1997-04-01 10:46:18');
INSERT INTO Bug(Bug_ID, Bug_Type, Bug_Desc, Bug_Time) VALUES (05, 'Run Time Error', 'Wrong output due to a logical error', DATE '2001-12-24 12:12:37');
INSERT INTO Bug(Bug_ID, Bug_Type, Bug_Desc, Bug_Time) VALUES (06, 'GUI Error', 'Glitchy interface ', DATE '2005-09-02 17:11:55');
INSERT INTO Engineer(Engineer_ID, Engineer_Name, Engineer_Type) VALUES (00, 'Ava', 'Tester');
INSERT INTO Engineer(Engineer_ID, Engineer_Name, Engineer_Type) VALUES (01, 'Alexander', 'Fixer');
INSERT INTO Engineer(Engineer_ID, Engineer_Name, Engineer_Type) VALUES (02, 'Aiden', 'Fixer');
INSERT INTO Engineer(Engineer_ID, Engineer_Name, Engineer_Type) VALUES (03, 'Anthony', 'Tester');
INSERT INTO Engineer(Engineer_ID, Engineer_Name, Engineer_Type) VALUES (04, 'Adam', 'Fixer');
INSERT INTO Engineer(Engineer_ID, Engineer_Name, Engineer_Type) VALUES (05, 'Alex', 'Tester');
INSERT INTO Engineer(Engineer_ID, Engineer_Name, Engineer_Type) VALUES (06, 'John', 'Fixer');
INSERT INTO Bug_Project VALUES ();
INSERT INTO Bug_Project VALUES ();
INSERT INTO Bug_Project VALUES ();
INSERT INTO Fix_Allocation VALUES ();
INSERT INTO Fix_Allocation VALUES ();
INSERT INTO Fix_Allocation VALUES ();
INSERT INTO Test_Allocation VALUES ();
INSERT INTO Test_Allocation VALUES ();
INSERT INTO Test_Allocation VALUES ();
INSERT INTO Note VALUES ();
INSERT INTO Note VALUES ();
INSERT INTO Note VALUES ();
COMMIT;
--SELECT SCRIPT
/*put your select scripts here (with indication of which query is answered) – your script should not commented out
-- Query 1: List of all the bugs, and their details.
SELECT * FROM Bug;
-- Query 2: List of all bugs, and their notes.
-- Query 3: List of all bugs, with their notes, and the engineers who have written them; sorted by name of engineer.
-- Query 4: List the bugs and how much cumulative time (in hours) they have taken; ordered by time taken.
-- Query 5: The bug that has taken most time to fix, and the projects it is connected to.
COMMIT;
--DROP SCRIPT
/*put your drop scripts here (in the correct order)– your script should not commented out
DROP TABLE Note;
DROP TABLE Test_Allocation;
DROP TABLE Fix_Allocation;
DROP TABLE Engineer;
DROP TABLE Bug_Project;
DROP TABLE Bug;
DROP TABLE Project;
COMMIT;
编辑:
INSERT INTO Bug(Bug_ID, Bug_Type, Bug_Desc, Bug_Time) VALUES (01, 'Run Time Error', 'Wrong output due to a logical error', To_DATE ('1982-06-31 11:36:32', 'YYYY-MM-DD HH24:MO:SS'));
这是我收到的错误消息:
从第 79 行开始的错误命令 - INSERT INTO Bug(Bug_ID, Bug_Type, Bug_Desc, Bug_Time) VALUES (01, 'Run Time Error', 'Wrong output due to a logic error', To_DATE ('1982-06-31 11 :36:32', 'YYYY-MM-DD HH24:MO:SS')) 错误报告 - ORA-01821: 日期格式无法识别
解决方案
日期函数只能取'YYYY-MM-DD'
格式。
DATE '1982-06-31 11:36:32'
是错的。
您可以使用 to_date 如下。
To_DATE ('1982-06-31 11:36:32,'YYYY-MM-DD HH24:MI:SS')
-- hour in 24 hour format as can be seen in other dates in your example.
-- if your date contains AM or PM then you can use, To_DATE ('1982-06-31 11:36:32 AM' ,'YYYY-MM-DD HH:MI:SS AM')
干杯!!
推荐阅读
- amazon-web-services - Jenskins 无法使用 Shell 脚本在另一个 EC2 实例上部署 War 文件
- python - dtype float32(lambda 输入)的张量转换请求的 dtype 字符串
- java - 在构建 xml (ant) 中包含 spotbugs 目标
- marklogic - 为 CorbTask 设置堆大小
- javascript - 当数字输入字段中有点或逗号时,jQuery val() 返回空字符串
- android - 如何获取 textView 的字体系列
- java - 我需要知道如何在关联数组中设置值并在屏幕上显示
- c - While 循环以 Char 中断
- google-apps-script - 如何创建包含下拉列表的 HTML 对话框?
- jmockit - JMockit - 模拟 REST 客户端对象