第一階段
q.編寫一個pl/sql程序塊以顯示所給出雇員編號的雇員的詳細信息。
a.
declare
erec emp%rowtype;
begin
select * into erec from emp where empno=&雇員編號;
dbms_output.put_line('empno' || ' ' || 'ename' || ' '|| 'job' || ' ' || 'manager' || ' ' || 'hiredate' || ' ' || 'salary' || ' ' || 'commision' || ' ' || 'deptno');
dbms_output.put_line(erec.ename || ' ' || erec.job || ' ' || erec.mgr || ' ' ||erec.hiredate || ' ' || erec.sal || ' ' || erec.comm || ' ' || erec.deptno);
end;
/
q.編寫一個pl/sql程序塊以計算某個雇員的年度薪水總額。
a.
declare
esal number;
eename emp.ename%type;
begin
select (nvl(sal,0)+nvl(comm,0))*12,ename into esal,eename from emp where empno=&雇員編號;
dbms_output.put_line(eename || '''s years salary is ' || esal);
end;
/
q.按下列加薪比執行：
deptno raise(%age)
10 5%
20 10%
30 15%
40 20%
加薪的百分比是以他們現有的薪水為根據的。寫一pl/sql以對指定雇員加薪。
a.
declare
vcounter number:=10;
vraise number;
begin
loop
exit when vcounter>40;
update emp set sal=nvl(sal,0)+nvl(sal,0)*0.05 where deptno=vcounter;
vcounter:=vcounter+10;
end loop;
end;
/
q.編寫一pl/sql以向"emp"表添加10個新雇員編號。
（提示：如果當前最大的雇員編號為7900，則新雇員編號將為7901到7910）
a.
declare
vcounter number;
begin
select max(empno) into vcounter from emp;
for i in 1..10
loop
vcounter:=vcounter+1;
insert into emp(empno) values(vcounter);
end loop;
end;
/
q.只使用一個變量來解決實驗課作業4。
a
declare
erec emp%rowtype;
-- vraise number;
begin
select * into erec
from emp
where ename='&ename';
if erec.job='clerk' then
update emp set sal=sal+500 where empno=erec.empno;
elsif erec.job='salesman' then
update emp set sal=sal+1000 where empno=erec.empno;
elsif erec.job='analyst' then
update emp set sal=sal+1500 where empno=erec.empno;
else
update emp set sal=sal+2000 where empno=erec.empno;
end if;
-- update emp set sal=sal+vraise where empno=erec.empno;
-- dbms_output.put_line(vraise);
end;
/
q.接受兩個數相除并且顯示結果。如果第二個數為0，則顯示消息"divide by zero"。
a.
declare
num1 number;
num2 number;
begin
num1:=#
num2:=#
dbms_output.put_line(num1 || '/' || num2 || ' is ' || num1/num2);
exception
when zero_divide then
dbms_output.put_line('didn''t your teacher tell you not to divide by zero?');
end;
/
第二階段
q.編寫一個pl/sql程序塊，對名字以"a"或"s"開始的所有雇員按他們的基本薪水的10%加薪。
a.
declare
cursor c1 is
select * from emp where substr(ename,1,1)='a' or substr(ename,1,1)='s' for update of sal;
begin
for i in c1
loop
update emp set sal=nvl(sal,0)+nvl(sal,0)*0.1 where current of c1;
end loop;
end;
/
q.編寫一pl/sql，對所有的"銷售員"(salesman)增加傭金500.
a.
declare
cursor c1 is
select * from emp where job='salesman' for update of sal;
begin
for i in c1
loop
update emp set sal=nvl(sal,0)+500 where current of c1;
end loop;
end;
/
q.編寫一pl/sql，以提升兩個資格最老的"職員"為"高級職員"。（工作時間越長，優先級越高）
a.
declare
cursor c1 is
select * from emp where job='clerk' order by hiredate for update of job;
--升序排列，工齡長的在前面
begin
for i in c1
loop
exit when c1%rowcount>2;
dbms_output.put_line(i.ename);
update emp set job='highclerk' where current of c1;
end loop;
end;
/
q.編寫一pl/sql，對所有雇員按他們基本薪水的10%加薪，如果所增加的薪水大于5000，則取消加薪。
a.
declare
cursor c1 is select * from emp for update of sal;
begin
for i in c1
loop
if (i.sal+i.sal*0.1)<=5000 then
update emp set sal=sal+sal*0.1 where current of c1;
dbms_output.put_line(i.sal);
end if;
end loop;
end;
/
q.顯示emp中的第四條記錄。
a.
declare
cursor c1 is select * from emp;
begin
for i in c1
loop
if c1%rowcount=4 then
dbms_output.put_line(i. empno || ' ' ||i.ename || ' ' || i.job || ' ' || i.mgr || ' ' || i.hiredate || ' ' || i.sal || ' ' || i.comm || ' ' || i.deptno);
exit;
end if;
end loop;
end;
/
第三階段
q.使用ref游標顯示"emp"表中的值。
a.
declare
type emprectyp is record
(
empno emp.empno%type,
ename emp.ename%type,
job emp.job%type,
mgr emp.mgr%type,
hiredate emp.hiredate%type,
sal emp.sal%type,
comm emp.comm%type,
deptno emp.deptno%type
);
type emp_cursor is ref cursor return emp%rowtype;
vemp_cur emp_cursor;
vemp_rec emprectyp;
begin
open vemp_cur for select * from emp;
loop
fetch vemp_cur into vemp_rec;
exit when vemp_cur%notfound;
dbms_output.put(vemp_rec.empno||' '||vemp_rec.ename||' '||vemp_rec.job);
dbms_output.put(vemp_rec.mgr||' '||vemp_rec.hiredate||' '||vemp_rec.sal);
dbms_output.put_line(vemp_rec.comm||' '||vemp_rec.deptno);
end loop;
close vemp_cur;
end;
/
q.從"emp"中獲得值送到pl/sql表，將pl/sql表中的薪水值增加500，并向用戶顯示增加的薪水及其他詳細信息。
a.
declare
type emprec is record
(
empno emp.empno%type,
ename emp.ename%type,
job emp.job%type,
mgr emp.mgr%type,
hiredate emp.hiredate%type,
sal emp.sal%type,
comm emp.comm%type,
deptno emp.deptno%type
);
i binary_integer:=1;
type emp_tab is table of emprec index by binary_integer;
vemp emp_tab;
cursor c1 is select * from emp;
begin
for x in c1
loop
vemp(i).empno:=x.empno;
vemp(i).ename:=x.ename;
vemp(i).job:=x.job;
vemp(i).mgr:=x.mgr;
vemp(i).hiredate:=x.hiredate;
vemp(i).sal:=x.sal+500;
vemp(i).comm:=x.comm;
vemp(i).deptno:=x.deptno;
i:=i+1;
end loop;
for j in 1..i-1
loop
dbms_output.put(vemp(j).empno||' '||vemp(j).ename||' '||vemp(j).job);
dbms_output.put(vemp(j).mgr||' '||vemp(j).hiredate||' '||vemp(j).sal);
dbms_output.put_line(vemp(j).comm||' '||vemp(j).deptno);
end loop;
end;
/
q.一旦將值送到pl/sql表后，嘗試在pl/sql表中插入新記錄并且刪除某些現有的記錄。
a.
declare
type emprec is record
(
empno emp.empno%type,
ename emp.ename%type,
job emp.job%type,
mgr emp.mgr%type,
hiredate emp.hiredate%type,
sal emp.sal%type,
comm emp.comm%type,
deptno emp.deptno%type
);
i binary_integer:=1;
type emp_tab is table of emprec index by binary_integer;
vemp emp_tab;
cursor c1 is select * from emp;
begin
for x in c1
loop
vemp(i).empno:=x.empno;
vemp(i).ename:=x.ename;
vemp(i).job:=x.job;
vemp(i).mgr:=x.mgr;
vemp(i).hiredate:=x.hiredate;
vemp(i).sal:=x.sal;
vemp(i).comm:=x.comm;
vemp(i).deptno:=x.deptno;
i:=i+1;
end loop;
-- for j in 1..i-1
-- loop
-- dbms_output.put(vemp(j).empno||' '||vemp(j).ename||' '||vemp(j).job);
-- dbms_output.put(vemp(j).mgr||' '||vemp(j).hiredate||' '||vemp(j).sal);
-- dbms_output.put_line(vemp(j).comm||' '||vemp(j).deptno);
-- end loop;
--插入記錄
dbms_output.put_line('插入記錄:');
vemp(i).empno:=1000;
vemp(i).ename:='goldens';
vemp(i).job:='software';
vemp(i).mgr:=null;
vemp(i).hiredate:='2003-01-04';
vemp(i).sal:=8888;
vemp(i).comm:=10;
vemp(i).deptno:=10;
for j in 1..i
loop
dbms_output.put(vemp(j).empno||' '||vemp(j).ename||' '||vemp(j).job);
dbms_output.put(vemp(j).mgr||' '||vemp(j).hiredate||' '||vemp(j).sal);
dbms_output.put_line(vemp(j).comm||' '||vemp(j).deptno);
end loop;
--刪除第5、6條記錄
dbms_output.put_line('刪除第5、6條記錄:');
for j in 5..i-2
loop
vemp(j).empno:=vemp(j+2).empno;
vemp(j).ename:=vemp(j+2).ename;
vemp(j).job:=vemp(j+2).job;
vemp(j).mgr:=vemp(j+2).mgr;
vemp(j).hiredate:=vemp(j+1).hiredate;
vemp(j).sal:=vemp(j+2).sal;
vemp(j).comm:=vemp(j+2).comm;
vemp(j).deptno:=vemp(j+2).deptno;
end loop;
vemp(i-1).empno:=null;
vemp(i-1).ename:=null;
vemp(i-1).job:=null;
vemp(i-1).mgr:=null;
vemp(i-1).hiredate:=null;
vemp(i-1).sal:=null;
vemp(i-1).comm:=null;
vemp(i-1).deptno:=null;
vemp(i).empno:=null;
vemp(i).ename:=null;
vemp(i).job:=null;
vemp(i).mgr:=null;
vemp(i).hiredate:=null;
vemp(i).sal:=null;
vemp(i).comm:=null;
vemp(i).deptno:=null;
for j in 1..i-2
loop
dbms_output.put(vemp(j).empno||' '||vemp(j).ename||' '||vemp(j).job);
dbms_output.put(vemp(j).mgr||' '||vemp(j).hiredate||' '||vemp(j).sal);
dbms_output.put_line(vemp(j).comm||' '||vemp(j).deptno);
end loop;
end;
/
第四階段
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;
第五階段
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;
/
----8i下通過。資料來自accp
