Return to Question

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

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:

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:

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

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

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

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