oracle package and trigger

Question

Create a database that contains four tables department, employee, address and contact info, then Store 5 Records in each table. Write the PL/SQL Statement for the following:

Store a record into employee, address and contact using trigger.
Retrieve departments and their employees using package

..........................

this is my code , what am i doing wrong ?

-- Question 2  :
-- Create Tables :
CREATE TABLE address (
code int primary key,
city varchar2(30),
street varchar2(30)
);
create table Department (
DepId int primary key ,
Dep_Name varchar2(30) ,
Dep_adress varchar(30));
create table Employee (
Emp_Id int primary key ,
firstName varchar2(30),
lastName varchar2(30),
salary int,
Dep_Id int references Department (DepId),
AdCode int references address (code));
CREATE TABLE contact_info (
email varchar2(30) primary key ,
phone int ,
EmpId int references Employee (Emp_Id));
-- insert   :
insert into Department values (1,'IT','Amman');
insert into Department values (2,'CS','Jerash');
insert into Department values (3,'accounting','Amman');
insert into Department values (4,'managment','Amman');
insert into Department values (5,'employment','Amman');
insert into address values (50,'Amman','AAA');
insert into address values (60,'Amman','AAB');
insert into address values (70,'Amman','AAC');
insert into address values (80,'Jerash','AAD');
insert into address values (90,'Irbid','AAE');
insert into Employee (Emp_Id,firstName,lastName,salary,Dep_Id,AdCode) values (1,'john' , 'samo' , 1000 , (select DepId from Department where Dep_Name = 'IT'),(select code from address where street = 'AAA'));
insert into Employee (Emp_Id,firstName,lastName,salary,Dep_Id,AdCode) values (2,'mark' , 'wol' , 2000 , (select DepId from Department where Dep_Name = 'IT'),(select code from address where street = 'AAB'));
insert into Employee (Emp_Id,firstName,lastName,salary,Dep_Id,AdCode) values (3,'ahmad' , 'moh' , 1100 , (select DepId from Department where Dep_Name = 'IT'),(select code from address where street = 'AAC'));
insert into Employee (Emp_Id,firstName,lastName,salary,Dep_Id,AdCode) values (4,'maher' , 'imk' , 1700 , (select DepId from Department where Dep_Name = 'CS'),(select code from address where street = 'AAD'));
insert into Employee (Emp_Id,firstName,lastName,salary,Dep_Id,AdCode) values (5,'ali' , 'geh' , 1200 , (select DepId from Department where Dep_Name = 'CS'),(select code from address where street = 'AAE'));
insert into contact_info values ('john@gmail.com',0785602200, (select Emp_Id from Employee where salary = 1000));
insert into contact_info values ('mark@gmail.com',0785602201, (select Emp_Id from Employee where salary = 2000));
insert into contact_info values ('ahmad@gmail.com',0785602202, (select Emp_Id from Employee where salary = 1100));
insert into contact_info values ('maher@gmail.com',0785602203, (select Emp_Id from Employee where salary = 1700));
insert into contact_info values ('ali@gmail.com',0785602204, (select Emp_Id from Employee where salary = 1200));
-- trigger :
CREATE OR REPLACE TRIGGER add_rec
AFTER INSERT
ON Department
FOR EACH ROW
DECLARE
Empl_id NUMBER;
BEGIN
INSERT INTO address(code,city,street)
VALUES(:NEW.code, :NEW.city, :NEW.street)
INSERT INTO contact_info(email, phone, EmpId)
VALUES(:NEW.email, :NEW.phone, :NEW.EmpId)
INSERT INTO Employee(Emp_Id,firstName,lastName,salary,Dep_Id,AdCode)
VALUES(:NEW.Emp_Id, :NEW.firstName, :NEW.lastName, :NEW.salary, :NEW.lastName, :NEW.Dep_Id, :NEW.AdCode)
END;
-- Package :
set serveroutput on;
CREATE OR REPLACE PACKAGE emp_dept AS
TYPE EmpDept IS RECORD (employee_id number , last_name varchar2(25) , department_id number);
CURSOR dept_employees RETURN EmpDept;
PROCEDURE department_employee;
END emp_dept;
/
CREATE OR REPLACE PACKAGE BODY emp_dept AS
CURSOR dept_employees RETURN EmpDept is (select employee_id , last_name , d.department_id from Employee e join Department d on e.department_id = d.department_id);
rec EmpDept; 
PROCEDURE department_employee
is 
begin
open dept_employees;
loop
fetch dept_employees into rec;
exit when dept_employees%notfound;
dbms_output.put_line(rpad(rec.employee_id,5,' ') ||rpad(rec.last_name,12,' ') || rec.department_id);
end loop;
end;
END emp_dept;
/
execute EMP_DEPT.DEPARTMENT_EMPLOYEE;

score 0 · Answer 1 · answered Dec 07 '21 at 21:14

As of package, when you fix errors (wrong column names), it compiles and returns result:

SQL> CREATE OR REPLACE PACKAGE emp_dept AS
  2      TYPE empdept IS RECORD (
  3          employee_id   NUMBER,
  4          last_name     VARCHAR2(25),
  5          department_id NUMBER
  6      );
  7      CURSOR dept_employees RETURN empdept;
  8      PROCEDURE department_employee;
  9
 10  END emp_dept;
 11  /

Package created.

SQL> CREATE OR REPLACE PACKAGE BODY emp_dept AS
  2
  3      CURSOR dept_employees RETURN empdept IS
  4      ( SELECT
  5          emp_id,
  6          lastname,
  7          d.depid
  8      FROM
  9               employee e
 10          JOIN department d ON e.dep_id = d.depid
 11      );
 12
 13      rec empdept;
 14
 15      PROCEDURE department_employee IS
 16      BEGIN
 17          OPEN dept_employees;
 18          LOOP
 19              FETCH dept_employees INTO rec;
 20              EXIT WHEN dept_employees%notfound;
 21              dbms_output.put_line(rpad(rec.employee_id, 5, ' ')
 22                                   || rpad(rec.last_name, 12, ' ')
 23                                   || rec.department_id);
 24
 25          END LOOP;
 26
 27      END;
 28
 29  END emp_dept;
 30  /

Package body created.

Testing:

SQL> EXECUTE emp_dept.department_employee;
1    samo        1
2    wol         1
3    moh         1
4    imk         2
5    geh         2

PL/SQL procedure successfully completed.

SQL>

As of trigger: that question/requirement doesn't make any sense. How would you enter rows into all those tables, based on 3 columns you enter into the department table? You just don't have any values to insert ... From my point of view, either you misinterpreted the problem, or it can't be done as you described it.

oracle package and trigger

1 Answers1