第五階段
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;
/
(全文完)
