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

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;

Для нахождения имени менеджера в функции используется явный курсор - cursor cur_manager is далее идёт запрос данных. Открытие явного курсора выполняется командой open cur_manager. Заполняем переменную v_manager данными fetch cur_manager into v_manager. Далее закрываем курсор  close cur_manager;

и возвращаем значение имени менеджера return v_manager.name. Если у сотрудника нет менеджера, то вернётся слово “Нет”.

Запросим данные из таблицы employee, и добавим поле с именем начальника каждого подчинённого при помощи обращения к созданной нами функции:

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

В запросе мы обращаемся к функции, и передаём ей в параметре ID сотрудника.

8.  Создадим процедуру new_employee для вставки данных в таблицу employee:

create or replace 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;

В блоке 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('Сотрудник с таким именем уже существует!');

Часть исключений пользовательские, e_nomanager  EXCEPTION;

они вызываются при помощи команды raise e_nomanager . Выражение if cur_dept%notfound используя атрибут курсора notfound, проверяет, вернул ли курсор данные, и если нет, то вызывается исключение.  В when others then

обрабатываются остальные стандартный оракловые ошибки.

Вставьте данные в таблицу employee при помощи созданной процедуры:

begin

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

end;

Выведите вставленную запись:

select * from employee where last_name='Ёлкина'

Попробуйте вставить эту запись ещё раз:

begin

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