如何在Oracle中添加时间查询? [英] How to add time query in Oracle?
问题描述
CREATE TABLE Appointment
(
appointID INTEGER,
appoint_date DATE,
appoint_time TIME,
appoint_type VARCHAR(5),
primary key (appointID)
);
INSERT INTO Appointment VALUES(1, '15-Apr-2017', '10:00', 'long');
INSERT INTO Appointment VALUES(2, '15-Apr-2017', '10:30', 'short');
INSERT INTO Appointment VALUES(3, '28-May-2017', '14:00', 'long');
INSERT INTO Appointment VALUES(4, '20-May-2017', '15:00', 'short');
INSERT INTO Appointment VALUES(5, '11-May-2017', '10:30', 'long');
INSERT INTO Appointment VALUES(6, '26-Jun-2017', '9:30', 'short');
INSERT INTO Appointment VALUES(7, '30-Jun-2017', '14:00', 'long');
INSERT INTO Appointment VALUES(8, '30-Jun-2017', '15:30', 'short');
INSERT INTO Appointment VALUES(9, '28-Apr-2017', '16:00', 'short');
INSERT INTO Appointment VALUES(10,'30-Apr-2017', '13:00', 'short');
尝试添加时间时,我一直收到此错误:
I keep getting this error when I try to add TIME:
Error starting at line : 24 in command -
CREATE TABLE Appointment(
appointID INTEGER,
appoint_date DATE,
appoint_time TIME,
appoint_type VARCHAR(5),
primary key (appointID)
)
Error report -
ORA-00902: invalid datatype
00902. 00000 - "invalid datatype"
*Cause:
*Action:
我也在尝试添加我的DOCTOR TABLE,但是我一直收到错误报告
I'm also trying to add my DOCTOR TABLE, but I keep getting a Error report
ORA-00907: missing right parenthesis
00907. 00000 - "missing right parenthesis"
*Cause:
*Action:**
create table Doctor
(
appointID INTEGER not null,
regnum CHAR(6),
doc_name VARCHAR(40),
doc_gender CHAR(1),
qual VARCHAR(80),
foreign key (appointID) references Appointment
primary key (appointID, regnum)
);
推荐答案
这是我的建议:避免使用CHAR
数据类型,除非它有意义(例如您在 gender 中使用),作为VARCHAR
>>>的替代品,请使用VARCHAR2
(个人而言,我从不使用CHAR
,也从未使用过VARCHAR
).
Here's my suggestion: avoid CHAR
datatype unless it makes sense (such as in gender, as you did), as well as VARCHAR
>>> use VARCHAR2
instead (personally, I never use CHAR
, and have never ever used VARCHAR
).
DATE
数据类型同时包含日期和时间部分,因此使用它是安全的.
DATE
datatype contains both date and time component, so you're safe if you use it.
构成主键约束的列不必指定NOT NULL
约束,因为主键无论如何都不允许为空.
Columns that make the primary key constraint don't have to have the NOT NULL
constraint specified, because primary keys don't allow nulls anyway.
因此,这是一个可行的示例:
So, here it is, a working example:
SQL> create table appointment
2 (appointid integer constraint pk_app primary key,
3 appoint_date date,
4 appoint_type varchar2(5)
5 );
Table created.
SQL>
SQL> insert into appointment values
2 (1, to_date('15.04.2017 10:00', 'dd.mm.yyyy hh24:mi'), 'long');
1 row created.
SQL>
SQL> create table doctor
2 (appointid integer constraint fk_doc_app references appointment (appointid),
3 regnum varchar2(6),
4 doc_name varchar2(40),
5 doc_gender char(1),
6 qual varchar2(80),
7 --
8 constraint pk_doc primary key (appointid, regnum)
9 );
Table created.
SQL>
这篇关于如何在Oracle中添加时间查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!