ORACLE第六章习题及答案

1:使用游标和loop循环来显示所有部门的名称
declare
cursor empcur is select job from emp;
a emp.job%type;
begin
open empcur;
loop
fetch empcur into a;
exit when empcur%notfound;
dbms_output.put_line(a);
end loop;
close empcur;
end;
2：使用游标和loop循环来显示所有部门的的地理位置（用%found属性）
declare
cursor empcur is select loc from dept;
a dept.loc%type;
begin
open empcur;
loop
fetch empcur into a;
exit when not empcur%found;
dbms_output.put_line(a);
end loop;
close empcur;
end;
3:接收用户输入的部门编号，用for循环和游标，打印出此部门的所有雇员的所有信息declare
b emp.deptno%type;
cursor empcur(d emp.deptno%type)is select*from emp where deptno=d;
begin
b:=&部门编号;
for a in empcur(b)
loop
dbms_output.put_line(a.empno||','||a.ename||','||a.job||','||a.mgr||','||a.hiredate||','||a.sa l||','||m||','||a.deptno);
end loop;
end;
4：向游标传递一个工种，显示此工种的所有雇员的所有信息
5：用更新游标来为雇员加佣金：
clerk+300
salesman+400
analyst+500
others null
declare
cursor empcur is select*from emp for update;
begin
for a in empcur
loop
if a.job='CLERK'then
update emp set sal=sal+300where current of empcur;
elsif a.job='SALESMAN'then
update emp set sal=sal+400where current of empcur;
elsif a.job='ANALYST'then
update emp set sal=sal+500where current of empcur;
end if;
end loop;
end;
6:编写一个PL/SQL程序块，对名字以‘A’或‘S’开始的所有雇员按他们的基本薪水的10%给他们加薪
declare
cursor empcur is select*from emp where ename like'A%'or ename like'S%'for update; begin
for a in empcur loop
dbms_output.put_line(a.ename||'原来的工资:'||a.sal);
update emp set sal=sal*1.1where current of empcur;
end loop;
end;
7：编写一个PL/SQL程序块，对所有的salesman增加佣金500
declare
cursor empcur is select*from emp for update;
begin
for a in empcur
loop
if a.job='SALESMAN'then
update emp set sal=sal+500where current of empcur;
end if;
end loop;
end;
8：编写一个PL/SQL程序块，以提升2个资格最老的职员为高级职员（工作时间越长，资格越老）
9：编写一个PL/SQL程序块，对所有雇员按他们的基本薪水的20%为他们加薪，如果增加的薪水大于300就取消加薪
declare
cursor empcur is select*from emp for update;
begin
for a in empcur
loop
if a.sal*0.2<=300then
update emp set sal=sal*1.2where current of empcur;
end if;
end loop;
end;
10：使用ref游标显示表dept中的记录
declare
type curemptype is ref cursor;
a curemptype;
b dept%rowtype;
begin
open a for select*from dept;
loop
fetch a into b;
exit when a%notfound;
dbms_output.put_line(b.deptno||','||b.dname||','||b.loc);
end loop;
close a;
end;
11：使用ref游标显示表emp中的记录
declare
type curemptype is ref cursor;
a curemptype;
b emp%rowtype;
begin
open a for select*from emp;
loop
fetch a into b;
exit when a%notfound;
dbms_output.put_line(b.empno||','||b.ename||','||b.job||','||b.mgr||','||b.hiredate||','||b.s al||','||m||','||b.deptno);
end loop;
close a; end;。