Агрегатные и аналитические функции. Создание других объектов базы данных (Лабораторная работа № 4), страница 5

end;

Попробуйте вставить запись с несуществующем в таблице department отделом:

begin

new_employee('Кира','Ёлкина','Дизайнер','Конь Виктор',to_date('15.01.2014','dd.mm.yyyy'),35000,'Разработка');

end;

Попробуйте вставить запись с несуществующей в таблице job должностью:

begin

new_employee('Кира','Ёлкина','Продавец','Конь Виктор',to_date('15.01.2014','dd.mm.yyyy'),35000,'Разработка ПО');

end;

Попробуйте вставить запись с несуществующей в таблице employee начальником:

begin

new_employee('Кира','Ёлкина','Дизайнер','Конь Анна',to_date('15.01.2014','dd.mm.yyyy'),35000,'Разработка ПО');

end;

9.  Для создания пакета, нужно сначала создаётся спецификация пакета,  в которой могут описываться типы данных, курсоры, процедуры, функции и т.д. Создадим спецификацию пакета с описанием, созданных нами ранее, процедурой и функцией: 

create or replace package manage_emloyee is

function manager_name(p_employee_id in number) return varchar2;

procedure new_employee(p_first_name varchar2,p_last_name varchar2,p_job varchar2,p_manager varchar2, p_hiredate date,p_salary number,p_department varchar2);

end;

Теперь создадим тело пакета:

create or replace package body manage_emloyee is

function manager_name(p_employee_id in number) return varchar2

as

cursor cur_manager is select e1.first_name||' '||e1.last_name name from employee e1, employee e2  where e1.employee_id=e2.manager_id and e2.employee_id=p_employee_id;

v_manager cur_manager%rowtype;

begin

v_manager.name:='Нет';

open cur_manager;

fetch cur_manager into v_manager;

close cur_manager;

return v_manager.name;

end;

procedure new_employee(p_first_name varchar2,p_last_name varchar2,p_job varchar2,p_manager varchar2, p_hiredate date,p_salary number,p_department varchar2)

as

cursor cur_manager is select employee_id from employee where last_name||' '||first_name=p_manager;

v_manager number;

cursor cur_dept is select department_id from department where name=p_department;

v_dept number;

cursor cur_job is select job_id from job1 where job_name =p_job;

v_job_id number;

e_nodept EXCEPTION;

e_nomanager  EXCEPTION;

e_nojob EXCEPTION;

begin

open cur_job;

fetch cur_job into v_job_id;

if cur_job%notfound then raise e_nojob;

end if;

v_dept:=0;

close cur_job;

open cur_dept;

fetch cur_dept into v_dept;

if cur_dept%notfound then raise e_nodept;

end if;

close cur_dept;

v_manager:=0;

open cur_manager;

fetch cur_manager into v_manager;

if cur_manager%notfound then raise e_nomanager ;

end if;

close cur_manager;

insert into employee(employee_id,first_name,last_name,job_id,manager_id ,

hire_date,salary,department_id) values(emplayeeseq.nextval,p_first_name,p_last_name ,v_job_id,

v_manager,sysdate, p_salary,v_dept);

commit;

exception

when e_nojob then

DBMS_OUTPUT.put_line('Нет такой специальности!');

when e_nomanager  then

DBMS_OUTPUT.put_line('Нет такого начальника!');

when e_nodept then

DBMS_OUTPUT.put_line('Нет такого отдела!');

when others then

DBMS_OUTPUT.put_line('Сотрудник с таким именем уже существует!');

end;

end manage_emloyee;

Чтобы использовать функцию и процедуру из нашего пакета, нужно перед именем функции и процедуры поставить название функции. Запросим  данные о начальниках сотрудника при помощи функции пакета manage_employee:

select manage_emloyee.manager_name(employee_id) "Начальник",employee.* from employee;

Вставьте данные о новом сотруднике при помощи процедуры, созданного нами пакета:

begin

manage_emloyee.new_employee('Борис','Никулин','Программист','Конь Виктор',to_date('20.01.2014','dd.mm.yyyy'),35000,'Разработка ПО');

end;

Запросите данные об этом сотруднике:

select * from employee where last_name='Никулин'