如何在oracle中创建继承表

Question

有一个名为event的表充当父级，子级继承的event表是特殊事件和酒店事件。我已经创建了以下类型，但我不知道如何在oracle中为这些表创建表。我已经参考了Stack溢出、git hub等中目前可用的大多数解决方案。然而，这些解决方案都没有成功实现。

表格类型：

Event_t (
EventID:char(5),
EventType:varchar(20),
VenueName:varchar(50),
NoOfGuest:number(10)
) NOT FINAL
HotelEvent_t (
Date:date,
Price:numbr(8,2)
) UNDER Event_t
SpecialEvent_t (
BookingDate:date,
EndDate:date,
MenuNumber:number(2), 
Reservation ref Reservation_t
) UNDER event_t

非常感谢，如有任何建议，我们将不胜感激。

Answer 1

使用正确的语法创建类型：

CREATE TYPE Event_t AS OBJECT(
EventID   char(5),
EventType varchar(20),
VenueName varchar(50),
NoOfGuest number(10)
) NOT FINAL;
CREATE TYPE HotelEvent_t UNDER Event_t (
datetime date,                -- Date is a keyword, try to use a different name.
Price  number(8,2)
);
CREATE TYPE SpecialEvent_t UNDER event_t (
BookingDate date,
EndDate     date,
MenuNumbers NUMBER(2),
Reservation ref Reservation_t
);

然后您可以创建一个对象表：

CREATE TABLE Events OF Event_T(
eventid CONSTRAINT Events__EventID__PK PRIMARY KEY
);

然后你可以在其中插入不同的类型：

INSERT INTO EVENTS VALUES(
HotelEvent_T(
'H1',
'HOTEL',
'Venue1',
42,
DATE '0001-02-03' + INTERVAL '04:05:06' HOUR TO SECOND,
123456.78
)
);
INSERT INTO EVENTS VALUES(
SpecialEvent_T(
'SE1',
'SPECIAL',
'Time Travel Convention',
-1,
SYSDATE,
TRUNC(SYSDATE),
0,
NULL
)
);

并从中获取数据：

SELECT e.*,
TREAT( VALUE(e) AS HotelEvent_T ).datetime AS datetime,
TREAT( VALUE(e) AS HotelEvent_T ).price AS price,
TREAT( VALUE(e) AS SpecialEvent_T ).bookingdate AS bookingdate,
TREAT( VALUE(e) AS SpecialEvent_T ).enddate AS enddate,
TREAT( VALUE(e) AS SpecialEvent_T ).menunumbers AS menunumbers
FROM   Events e;

哪个输出：

EVENTID|EVENTTYPE|VENUENAME|NOOFGUEST|DATETIME|PRICE|BOOKINGDATE|ENDDATE|MENUNUMBERS：--------------------------------------------H1|HOTEL|Venue1|42|0001-02-03 04:05:06|12346.78|null|nullSE1|SPECIAL |时间旅行公约|-1|2ull|1ull| 2020-03-30 21:11:22|2020-03-3000:00:00

db<>小提琴这里

Answer 2

在Oracle中创建这些表的典型方法是：

create table event_t (
event_id char(5) primary key not null,
event_type varchar2(20),
venue_mame varchar2(50),
no_of_guest number(10)
);
create table hotel_event_t (
event_date date,
price number(8,2),
event_id char(5),
constraint fk1 foreign key (event_id) references event_t (event_id)
);
create table special_event_t (
booking_date date,
end_date date,
menu_number number(2), 
reservation_id char(5),
constraint fk2 foreign key (reservation_id) 
references reservation_t (reservation_id),
event_id char(5),
constraint fk3 foreign key (event_id) references event_t (event_id)
);