第五阶段
q.编写一个数据包，它有两个函数和两个过程以操作“emp”表。
该数据包要执行的任务为：
插入一个新雇员；删除一个现有雇员；显示指定雇员的整体薪水（薪水+佣金）；显示指定雇员所在部门名称。
a.
create or replace package emppack as
procedure insrec(pempno emp.empno%type,pename emp.ename%type,
pjob emp.job%type,pmgr emp.mgr%type,
phiredate emp.hiredate%type,psal emp.sal%type,
pcomm emp.comm%type,pdeptno emp.deptno%type);
procedure delrec(pempno in number);
function selsal(pempno number) return number;
function seldname(pempno number) return varchar2;
end;
/
create or replace package body emppack as
procedure insrec(pempno emp.empno%type,pename emp.ename%type,
pjob emp.job%type,pmgr emp.mgr%type,
phiredate emp.hiredate%type,psal emp.sal%type,
pcomm emp.comm%type,pdeptno emp.deptno%type)
is
begin
insert into emp values(pempno,pename,pjob,pmgr,phiredate,
psal,pcomm,pdeptno);
dbms_output.put_line('1 record is created.');
end insrec;
procedure delrec(pempno in number)
is
begin
delete from emp where empno=pempno;
dbms_output.put_line('1 record is deleted.');
end delrec;
function selsal(pempno number) return number
is
vtotalsal number;
begin
select nvl(sal,0)+nvl(comm,0) into vtotalsal
from emp
where empno=pempno;
return vtotalsal;
end selsal;
function seldname(pempno number) return varchar2
is
vdname dept.dname%type;
begin
select dname into vdname
from emp,dept
where empno=pempno and emp.deptno=dept.deptno;
return vdname;
end seldname;
end;
/
--执行包中的过程和函数
execute emppack.insrec(1111,'goldens','manager',7698,'2003-01-18',2000,400,30);
execute emppack.delrec(1111);
declare
salary number;
begin
salary:=emppack.selsal(7369);
dbms_output.put_line('total salary is '||salary);
end;
/
declare
department varchar2(30);
begin
department:=emppack.seldname(7369);
dbms_output.put_line('department name is '||department);
end;
/
q.编写一个数据库触发器以显示当任何时候雇员加薪时的加薪情况。
a.
create or replace trigger emp_salup
after update of sal on emp
for each row
declare
vsal number;
begin
vsal:=nvl(:new.sal,0)-nvl(:old.sal,0);
if vsal<=0 then
raise_application_error(-20001,'increased salary is not zero and littler than zero');
end if;
end;
/
q.编写一个数据库触发器，它允许用户只在上午9.00到下午5.00之间执行dml任务。
a.
create or replace trigger operate_time_limited
before insert or update or delete on emp
--for each row
declare
vtime number;
begin
vtime:=to_number(to_char(sysdate,'hh24'));
if vtime not between 9 and 17 then
raise_application_error(-20444,'sorry!not except 9am and 5pm.');
end if;
end;
/
q.编写一个数据为触发器以检查某个组织中不能有两个总裁。
a.
create or replace trigger check_president
before insert or update on emp
for each row
when (upper(new.job)='president')
declare
vcount number;
begin
select count(job) into vcount
from emp
where upper(job)='president'; --把总统的个数统计出来，当为0时，变量值为0
if vcount>0 then
raise_application_error(-20444,'sorry!can''t have two president.');
end if;
end;
/
q.编写一个数据库触发器，当任何时候某个部门从”dept”中删除时，该触发器将从”emp”表中删除该部门的所有雇员。
a.
create or replace trigger del_emp_deptno
before delete on dept
for each row
begin
delete from emp where deptno=:old.deptno;
end;
/
(全文完)
