gpt4 book ai didi

SQL Oracle 继承关系数据库

转载 作者:太空狗 更新时间:2023-10-30 01:57:57 26 4
gpt4 key购买 nike

这是一张图片:

http://i.stack.imgur.com/AjHwH.png

这是我在编码方面的进展:

CREATE TYPE appointment_list_type AS TABLE OF REF appointment_type;
/

CREATE OR REPLACE TYPE person_type AS OBJECT (
personID NUMBER,
Surname varchar2(10),
Forname varchar2(10),
dateOfBirth date,
AddressLine1 varchar2(30),
AddressLine2 varchar2(30),
Town varchar2(10),
contacTel1 varchar2(10),
contacTel2 varchar2(10)) NOT FINAL;
/

CREATE TYPE applicant_type UNDER person_type(
applicantID NUMBER,
maxPrice number(7,2),
desiredArea varchar2(10),
Attends appointment_list_type
);
/

CREATE TYPE salesperson_type UNDER person_type(
salespersonID NUMBER,
manager varchar2(10),
Makes appointment_list_type
);
/

这是创建人员的类型,将其分为继承的 Salesperson 和 Applicant。

CREATE TYPE appointment_type AS OBJECT(
appointmentID NUMBER,
Appdate date,
Apptime timestamp,
appointmentType varchar2(10),
levelOfInterest varchar2(10),
offerMade varchar2(10),
Made_by REF salesperson_type,
Attends_by REF applicant_type
);
/

这是约会类型,引用用于将它们关联在一起。

创建表:

CREATE TABLE person_table OF person_type (
personID PRIMARY KEY NOT NULL)
NESTED TABLE Attends STORE AS attend_meeting_table;

CREATE TABLE applicant_table OF applicant_type (
personID PRIMARY KEY NOT NULL)
NESTED TABLE Attends STORE AS attend_meeting_table;

CREATE TABLE salesperson_table OF salesperson_type (
personID PRIMARY KEY NOT NULL)
NESTED TABLE Makes STORE AS makes_meeting_table;

CREATE TABLE appointment_table OF appointment_type (
appointmentID PRIMARY KEY NOT NULL,
SCOPE FOR (Made_by) IS person_table,
SCOPE FOR (Attends_by) IS person_table);

这也是我所做的一些代码,现在我的问题是:

继承如何直接在 Appointment 中执行 1 到 many?

我真的很困惑。谁能帮我解决这个问题?

最佳答案

phuh,我想我终于明白是什么困扰着你了......

目前applicant_table和salesperson_table中的约会REFd是完全独立的。这意味着应聘者可能会与销售人员开会,而这些销售人员实际上正在与其他人开会:)

当然,您希望所有约会都存储在 appointment_table 中。

这是对象 View 的完美用例。您根本不需要这些对象表。关系表更易于管理。

只需创建普通表,然后创建对象 View ,就像 SALESPERSON 的这个一样:

create view ov_salesperson as
(select personID,
salespersonID,
SALESPERSON_TYPE
(personID
Surname,
Forname,
dateOfBirth,
AddressLine1,
AddressLine2,
Town,
contacTel1,
contacTel2,
salespersonID,
manager,
CAST
(MULTISET
(Select appointment_type
(appointmentID,
Appdate,
Apptime,
appointmentType,
levelOfInterest,
offerMade,
salesperson_id,
applicant_id
)
From appointment_table A
Where A.salesperson_id = S.salesperson_id
)
as appointment_list_type
)
) as salesperson_obj
from salesperson_table S
);

关于SQL Oracle 继承关系数据库,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/8270225/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com