如何使用 REF 從兩個表中選擇列

我是 Oracle PL/SQL 的新手，我正在使用 11g。我有 2 個類型對象，每個對像都有一個表和第三個表，我在其中保留這 2 個對象的引用，如何使用第三個表中的引用從對象表中檢索值。

第一個類型對像看起來像：

CREATE OR REPLACE TYPE Teacher_T AS OBJECT
(
 teacher_id VARCHAR2(10),
 teacher_name VARCHAR2(20),
 teacher_address VARCHAR2(10),
 MEMBER PROCEDURE Insert_Teacher(new_teacher_id IN VARCHAR2,
                                 new_teacher_name IN VARCHAR2,
                                 new_teacher_address IN VARCHAR2), 
 MEMBER PROCEDURE Delete_Teacher
)
/

及其表：

CREATE TABLE Teacher OF Teacher_T
(
 teacher_id NOT NULL,
 PRIMARY KEY (teacher_id)
);

程序定義：

CREATE OR REPLACE TYPE BODY Teacher_T AS
 MEMBER PROCEDURE Insert_Teacher(new_teacher_id IN VARCHAR2,
                                 new_teacher_name IN VARCHAR2,
                                 new_teacher_address IN VARCHAR2) IS
 BEGIN
   INSERT INTO Teacher
    VALUES (new_teacher_id, new_teacher_name,new_teacher_address);
 END Insert_Teacher; 

 MEMBER PROCEDURE Delete_Teacher IS
 BEGIN
   DELETE FROM Teacher
    WHERE Teacher.teacher_id = self.teacher_id;
 END Delete_Teacher;
END; 
/

插入數據範例：

declare
 tea Teacher_T;
begin
 tea := new Teacher_T('','','');
 tea.Insert_Teacher('1','Jhon tea1','Cty');
end;

第二類對像看起來像：

CREATE OR REPLACE TYPE School_T AS OBJECT
(
 sch_id VARCHAR2(10),
 sch_name VARCHAR2(20),
 sch_address VARCHAR2(30),
 sch_type VARCHAR2(15),

 MEMBER PROCEDURE Insert_Sch(new_sch_id IN VARCHAR2,
                             new_sch_name IN VARCHAR2,
                             new_sch_address IN VARCHAR2,
                             new_sch_type IN VARCHAR2),
 MEMBER PROCEDURE Delete_Sch
)
/

及其表：

CREATE TABLE School OF School_T(
 sch_id NOT NULL,
 sch_type CHECK (sch_type IN ('Primary', 'Secondary', 'TechC')),
 PRIMARY KEY (sch_id)
);

程序定義：

CREATE OR REPLACE TYPE BODY School_T AS
 MEMBER PROCEDURE Insert_Sch(new_sch_id IN VARCHAR2,
                             new_sch_name IN VARCHAR2,
                             new_sch_address IN VARCHAR2,
                             new_sch_type IN VARCHAR2) IS
 BEGIN
   INSERT INTO School
   VALUES (new_sch_id, new_sch_name, new_sch_address, new_sch_type);
 END Insert_Sch;

 MEMBER PROCEDURE Delete_Sch IS
 BEGIN
   DELETE FROM Teach_In
   WHERE Teach_In.school IN (SELECT REF(a)
                               FROM School a
                              WHERE a.sch_id = self.sch_id);
   DELETE FROM School
   WHERE School.sch_id = self.sch_id;
 END Delete_Sch;
END;
/

插入數據範例：

declare
 sch School_T;
begin
 sch := new School_T('','','','');
 sch.Insert_Sch('2','schName','NYC','Secondary');
end;

最後參考表：

CREATE TABLE Teach_In
(
 teacher REF Teacher_T,
 school REF School_T
);

程序定義：

CREATE OR REPLACE PROCEDURE Insert_Teach_In(new_teacher_id IN VARCHAR2,
                                           new_sch_id IN VARCHAR2) 
AS
 teacher_temp REF Teacher_T;
 school_temp REF School_T;
BEGIN
 SELECT REF(a) INTO teacher_temp
   FROM Teacher a
  WHERE a.teacher_id = new_teacher_id;

 SELECT REF(b) INTO school_temp
   FROM School b
  WHERE b.sch_id = new_sch_id;

 INSERT INTO Teach_In
 VALUES (teacher_temp, school_temp); 
END Insert_Teach_In;
/

插入數據範例：

begin
 Insert_Teach_In('1','2');
end;

所以我從這個例子中的目標是使用 Teach_In 表（參考表）檢索教師姓名、教師地址、sch_name、sch_type、sch_address。

你可以做一個簡單的加入

select t.*,s.*
from Teacher t
join Teach_In r on r.teacher = ref(t)
join School s on r.school = ref(s)
;

引用自：https://dba.stackexchange.com/questions/239283