第四階段
q.編寫一過程以接受用戶輸入的三個部門編號并顯示其中兩個部門編號的部門名稱。
a.
create or replace procedure deptname(no1 dept.deptno%type,no2 dept.deptno%type,no3 dept.deptno%type) as
vflag number;
vdeptno1 dept.deptno%type;
vdeptno2 dept.deptno%type;
vdname1 dept.dname%type;
vdname2 dept.dname%type;
begin
vflag:=to_number(to_char(sysdate,'ss'));
if (vflag>=1 and vflag<=10) or (vflag>=50 and vflag<60) then
select deptno,dname into vdeptno1,vdname1 from dept where deptno=no1;
select deptno,dname into vdeptno2,vdname2 from dept where deptno=no2;
elsif (vflag>=11 and vflag<=20) or (vflag>=40 and vflag<50) then
select deptno,dname into vdeptno1,vdname1 from dept where deptno=no1;
select deptno,dname into vdeptno2,vdname2 from dept where deptno=no3;
else
select deptno,dname into vdeptno1,vdname1 from dept where deptno=no2;
select deptno,dname into vdeptno2,vdname2 from dept where deptno=no3;
end if;
dbms_output.put_line('部門編號:'||vdeptno1 ||' '||'部門名稱:' ||vdname1);
dbms_output.put_line('部門編號:'||vdeptno2 ||' '||'部門名稱:' ||vdname2);
end;
/
execute deptname(10,20,30);
q.編寫一過程以顯示所指定雇員名的雇員部門名和位置。
a.
create or replace procedure deptmesg(pename emp.ename%type,pdname out dept.dname%type,ploc out dept.loc%type) as
begin
select dname,loc into pdname,ploc
from emp,dept
where emp.deptno=dept.deptno and emp.ename=pename;
end;
/
variable vdname varchar2(14)
variable vloc varchar2(13)
execute deptmesg('smith',:vdname,:vloc);
print vdname vloc;
q.編寫一個給特殊雇員加薪10%的過程，這之后，檢查如果已經雇傭該雇員超過60個月，則給他額外加薪3000.
a.
create or replace procedure raise_sal(no in number) as
vhiredate date;
vsal emp.sal%type;
begin
select hiredate,sal into vhiredate,vsal from emp where empno=no;
if months_between(sysdate,vhiredate)>60 then
vsal:=nvl(vsal,0)*1.1+3000;
else
vsal:=nvl(vsal,0)*1.1;
end if;
update emp set sal=vsal where empno=no;
end;
/
variable no number
begin
:no:=7369;
end;
/
execute raise_sal(:no)
select empno,ename,sal,comm,hiredate from emp where empno=:no;
q.編寫一個函數以檢查所指定雇員的薪水是否有效范圍內。不同職位的薪水范圍為：
designation raise
clerk 1500-2500
salesman 2501-3500
analyst 3501-4500
others 4501 and above.
如果薪水在此范圍內，則顯示消息“salary is ok”，否則，更新薪水為該范圍內的最水值。
a.
create or replace function sal_level(no emp.empno%type) return char as
vjob emp.job%type;
vsal emp.sal%type;
vmesg char(50);
begin
select job,sal into vjob,vsal from emp where empno=no;
if vjob='clerk' then
if vsal>=1500 and vsal<=2500 then
vmesg:='salary is ok.';
else
vsal:=1500;
vmesg:='have updated your salary to '||to_char(vsal);
end if;
elsif vjob='salesman' then
if vsal>=2501 and vsal<=3500 then
vmesg:='salary is ok.';
else
vsal:=2501;
vmesg:='have updated your salary to '||to_char(vsal);
end if;
elsif vjob='analyst' then
if vsal>=3501 and vsal<=4500 then
vmesg:='salary is ok.';
else
vsal:=3501;
vmesg:='have updated your salary to '||to_char(vsal);
end if;
else
if vsal>=4501 then
vmesg:='salary is ok.';
else
vsal:=4501;
vmesg:='have updated your salary to '||to_char(vsal);
end if;
end if;
update emp set sal=vsal where empno=no;
return vmesg;
end;
/
declare
vmesg char(50);
vempno emp.empno%type;
begin
vempno:=&empno;
vmesg:=sal_level(vempno);
dbms_output.put_line(vmesg);
end;
/
--select empno,ename,sal,comm,hiredate from emp where empno=:no;
q.編寫一個函數以顯示該雇員在此組織中的工作天數。
a.
create or replace function hire_day(no emp.empno%type) return number as
vhiredate emp.hiredate%type;
vday number;
begin
select hiredate into vhiredate from emp where empno=no;
vday:=ceil(sysdate-vhiredate);
return vday;
end;
/
declare
vday number;
vempno emp.empno%type;
begin
vempno:=&empno;
vday:=hire_day(vempno);
dbms_output.put_line(vday);
end;
/
--select empno,ename,sal,comm,hiredate from emp where empno=:no;
