Блог

Создайте базу данных, содержащую четыре таблицы отдел, сотрудник, адрес и контактную информацию, затем сохраните по 5 записей в каждой таблице. Напишите инструкцию PL/SQL для следующего:

1. Сохраните запись в сотруднике, адресе и контакте с помощью триггера.
2. Извлеките отделы и их сотрудников с помощью пакета

……………………..

это мой код , что я делаю не так ?

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

Начиная с пакета, когда вы исправляете ошибки (неправильные имена столбцов), он компилируется и возвращает результат:

 SQLgt; 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.  SQLgt; 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.  

Тестирование:

 SQLgt; 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.  SQLgt;  

По состоянию на триггер: этот вопрос/требование не имеет никакого смысла. Как бы вы ввели строки во все эти таблицы, основываясь на 3 столбцах, которые вы вводите в department таблицу? У вас просто нет никаких значений для вставки … С моей точки зрения, либо вы неправильно истолковали проблему, либо это невозможно сделать так, как вы ее описали.