create
sequence inc_emp;
create or
replace package sotrudnik as
procedure
add_emp (p_ename IN varchar2,p_job IN varchar2,p_sal IN number,p_comm IN
number,p_deptno IN number);
procedure
del_emp (p_empno IN number);
procedure
change_sal (p_empno IN number, p_nadbavka IN number);
procedure
num_of_emp;
end sotrudnik;
create or
replace package body sotrudnik as
procedure
add_emp (p_ename IN varchar2,p_job IN varchar2,p_sal IN number,p_comm IN
number,p_deptno IN number)
as
v_dname
varchar2(40);
begin
insert into EMP values (inc_emp.nextval,p_ename,p_job,p_sal,p_comm,p_deptno,sysdate);
end
add_emp;
procedure
del_emp (p_empno IN number) as
v_ename
varchar2(40);
begin
select ename into v_ename from EMP where
empno=p_empno;
delete from EMP where empno=p_empno;
exception
when NO_DATA_FOUND then
DBMS_OUTPUT.PUT_LINE('Сотрудника с таким номером не существует!');
end
del_emp;
procedure
change_sal (p_empno IN number, p_nadbavka IN number) as
v_ename
varchar2(40);
begin
select ename into v_ename from EMP
where empno=p_empno;
update EMP set sal=sal+p_nadbavka
where empno=p_empno;
exception
when NO_DATA_FOUND then
DBMS_OUTPUT.PUT_LINE('Сотрудника с таким номером не существует!');
end
change_sal;
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*12>5000;
DBMS_OUTPUT.PUT_LINE(v_c
|| ' ' || to_char(v_count));
end loop;
close c;
end
num_of_emp;
end sotrudnik;
Вызов подпрограмм:
1. begin sotrudnik.add_emp
('Dima','bar',1500,9,2); end;
2. begin sotrudnik.del_emp
(4); end;
3. begin sotrudnik.change_sal
(3,789); end;
4. begin sotrudnik.num_of_emp;
end;
create
sequence inc_dep;
create or
replace package otdel as
function
name_dept (p_deptno IN number) return varchar2;
function
all_sal (p_dname IN varchar2) return number;
function
num_emp (p_dname IN varchar2) return number;
procedure
add_dept (p_dname IN varchar2,p_location IN varchar2);
procedure
dept_restruct;
end otdel;
create or
replace package body otdel as
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;
exception
when NO_DATA_FOUND then
DBMS_OUTPUT.PUT_LINE('Отдела с таким номером не существует!'); return v_dname;
end
name_dept;
function
all_sal (p_dname IN varchar2)
return
number
as
v_allsal
number(6);
v_deptno
number(2);
v_dname
varchar2(40);
begin
select deptno INTO v_deptno from
DEPT where dname=p_dname;
select sum(sal) INTO v_allsal from
EMP where deptno=v_deptno;
return v_allsal;
exception
when NO_DATA_FOUND then
DBMS_OUTPUT.PUT_LINE('Отдела с таким названием не существует!'); return v_allsal;
end
all_sal;
function
num_emp (p_dname IN varchar2)
return number
as
v_deptno
number(2);
v_emp
number(6);
begin
select deptno INTO v_deptno from
DEPT where dname=p_dname;
select count(empno) INTO v_emp from
EMP where deptno=v_deptno;
return v_emp;
exception
when NO_DATA_FOUND then
DBMS_OUTPUT.PUT_LINE('Отдела с таким названием не существует!'); return v_emp;
end num_emp;
procedure
add_dept (p_dname IN varchar2,p_location IN varchar2)
as
begin
insert into DEPT values (inc_dep.nextval,p_dname,p_location);
end add_dept;
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;
end otdel;
Вызов подпрограмм:
1. select otdel.name_dept
(2) from dual;
2. select otdel.all_sal
('pervii') from dual;
3. select otdel.num_emp
('pervii') from dual;
4. begin otdel.add_dept
('tretii','
5. begin otdel.dept_restruct;
end;