Oracle游标练习题

按工号从小到大的顺序输出雇员名字、工资以及工资与平均工资的差。

declare
v_ename emp.ename%type;
v_sal emp.sal%type;
v_avgsal number;
cursor cur1 is select ename,sal,round(sal-avgsal) from emp,(select avg(sal) avgsal from emp) a order by empno;
begin
open cur1;
loop
fetch cur1 into v_ename,v_sal,v_avgsal;
exit when cur1%notfound;
dbms_output.put_line(v_ename||''||v_sal||''||v_avgsal);
end loop;
close cur1;
end;

为所有雇员增加工资，工资在1000以内的增加30%，在1000-2000增加20%，2000以上增加10%。

declare
cursor cur2 is select empno,sal from emp;
v_empno emp.empno%type;
v_sal emp.sal%type;
begin
open cur2;
 loop
fetch cur2 into v_empno,v_sal;
exit when cur2%notfound;
 if v_sal<1000 then
  update emp set sal=sal*1.3 where empno=v_empno;
 dbms_output.put_line('雇员名：'||v_empno||'原工资'||v_sal||'新工资'||v_sal*1.3);
elsif v_sal>2000 then
 update emp set sal=sal*1.1 where empno=v_empno;
 dbms_output.put_line('雇员名：'||v_empno||'原工资'||v_sal||'新工资'||v_sal*1.1);
 else
update emp set sal=sal*1.2 where empno=v_empno;
 dbms_output.put_line('雇员名：'||v_empno||'原工资'||v_sal||'新工资'||v_sal*1.2);
 end if;
end loop;
close cur2;
 end;

统计输出部门名称、部门总人数、总工资及部门经理。

declare
v_deptno number;
v_dept_name varchar2(20);
v_count number;
v_count_sal number;
v_dept_manager varchar2(20);
cursor c_dept is select deptno,count(*),sum(sal) from emp group by deptno;
begin
open c_dept;
loop
fetch c_dept into v_deptno,v_count,v_count_sal;
exit when c_dept%notfound;
select dname into v_dept_name from dept where deptno = v_deptno;
select ename into v_dept_manager from emp where deptno = v_deptno and job = 'MANAGER';
dbms_output.put_line(rpad(v_dept_name,15)||rpad(v_count,6)||rpad(v_count_sal,10)||rpad(v_dept_manager,6));
end loop;
close c_dept;
end;

显示工资最高的前3名雇员的名称和工资。

declare
cursor cur4 is
select sal,ename from
(select sal,ename from emp order by sal desc) where rownum<=3;
v_sal emp.sal%type;
v_ename emp.ename%type;
begin
open cur4;
loop
fetch cur4 into v_sal,v_ename;
exit when cur4%notfound;
dbms_output.put_line(rpad(v_Sal,5)||rpad(v_ename,8));
end loop;
close cur4;
end;