Задание:
I. Создать таблицы DEPT (deptno, dname, location) и EMP (empno, ename, job, sal, comm, deptno, data) и связать их по полю deptno связью «один ко многим». Заполнить их.
II. Создать подпрограммы для реализации следующих операций:
1) прием на работу нового сотрудника;
2) увольнение с работы сотрудника по empno;
3) изменение зарплаты на заданную величину для конкретного сотрудника;
4) в каждом отделе определить число сотрудников, получающих более 5000 в год;
5) определить название отдела по его номеру;
6) подсчитать общее жалование сотрудников для заданного отдела;
7) подсчитать количество сотрудников для заданного отдела;
8) создание нового отдела;
9) реструктуризация фирмы: все отделы с названием sales перевести в Dallas, остальные в New York. Определить количество отделов в каждом городе.
Решение:
I
create
table DEPT (
deptno number(2) not null,
dname varchar2(40),
location varchar2(40),
constraint pk_d primary key (deptno)
);
create
table EMP (
empno number(3) not null,
ename varchar2(40),
job varchar2(40),
sal number(5),
comm number(2),
deptno number(2),
data date,
constraint pk_e primary key (empno),
constraint fk_ed foreign key
(deptno) references DEPT (deptno)
);
II
1.
create or
replace procedure add_emp (
p_empno IN number,
p_ename IN varchar2,
p_job IN varchar2,
p_sal IN number,
p_comm IN number,
p_deptno IN number)
as
begin
insert into EMP values
(p_empno,p_ename,p_job,p_sal,p_comm,p_deptno,sysdate);
end
add_emp;
begin
add_emp (1,'asd','zxc',2000,9,1); end;
2.
create or
replace procedure del_emp (p_empno IN number) as
begin
delete from EMP where empno=p_empno;
end
del_emp;
begin
del_emp (1); end;
3.
create or
replace procedure change_sal (p_empno IN number, p_nadbavka IN number) as
begin
update EMP set sal=sal+p_nadbavka
where empno=p_empno;
end
change_sal;
begin
change_sal (1,1000); end;
4.
create or
replace procedure num_of_emp
as
cursor c is
select dname from DEPT;
v_c
varchar2(40);
v_count
number(2);
begin
open c;
loop
fetch c into v_c;
exit when c%notfound;
select count(sal) INTO
v_count
from EMP
where deptno=(select
deptno from DEPT where dname=v_c) and sal>5000;
dbms_output.put_line(v_c
|| ' ' || to_char(v_count));
end loop;
close c;
end
num_of_emp;
begin num_of_emp;
end;
5.
create or
replace function name_dept (p_deptno IN number)
return
varchar2
as
v_dname
varchar2(40);
begin
select dname INTO v_dname from DEPT
where deptno=p_deptno;
return
v_dname;
end
name_dept;
select
name_dept (1) from dual;
6.
create or
replace function all_sal (p_dname IN varchar2)
return number
as
v_allsal number(6);
begin
select sum(sal) INTO v_allsal from
EMP where deptno=(select deptno from DEPT where dname=p_dname);
return
v_allsal;
end
all_sal;
select
all_sal ('Buh') from dual;
7.
create or
replace function num_emp (p_dname IN varchar2)
return
number
as
v_emp
number(6);
begin
select count(empno) INTO v_emp from
EMP where deptno=(select deptno from DEPT where dname=p_dname);
return
v_emp;
end
num_emp;
select
num_emp ('Buh') from dual;
8.
create or
replace procedure add_dept (
p_deptno IN number,
p_dname IN varchar2,
p_location IN varchar2)
as
begin
insert into DEPT values
(p_deptno,p_dname,p_location);
end
add_dept;
begin
add_dept (2,'Kadri','USM'); end;
9.
create or
replace procedure dept_restruct
as
cursor c is
select location from DEPT group by location;
v_c
varchar2(40);
v_count
number(2);
begin
update DEPT set location='
update DEPT set location='
open c;
loop
fetch c into v_c;
exit when c%notfound;
select count(dname) INTO
v_count from DEPT where location=v_c;
dbms_output.put_line(v_c
|| ' ' || to_char(v_count));
end loop;
close c;
end
dept_restruct;
begin dept_restruct; end;