如何在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屋！