2

I'm new to Oracle's PL/SQL i'm using 11g. I have 2 Type Objects each one of them with has a table and third table where i keep references of this 2 objects, how can i retrieve values from objects tables using references in the third table.

First Type Object looks like:

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
)
/

And its table:

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

Procedures definition:

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; 
/

Example to insert data:

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

Second Type Object looks like:

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
)
/

And its table:

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

Procedures definition:

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;
/

Example to insert data:

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

Finally references table:

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

Procedure def:

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;
/

Example to insert data:

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

So my goal from this example is to retrieve teacher_name,teacher_address,sch_name,sch_type,sch_address using Teach_In table(references table).

  • did you checked the REF function? could you also add samle data and examples what you a trying to do please – hotfix May 29 at 7:59
  • @hotfix thanks, i've added sample data also i explained more what i want. – blabla May 29 at 16:59
0

you can do a simple join

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.