IDC资讯中心
第二阶段
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;
/
