Oracle之PL/SQL游标练习题（三）

1、定义游标：列出每个员工的姓名部门名称并编程显示第10个到第20个记录

法一：

declare
  cursor ed_cursor is select ename,dname from
  (select ename,dname,rownum r from emp e left join dept d on e.deptno=d.deptno)t 
  where t.r between 10 and 20;
  v_ename varchar2(10);
  v_dname varchar2(10);
begin
  open ed_cursor;
  fetch ed_cursor into v_ename,v_dname;
  while ed_cursor%found loop
    dbms_output.put_line(v_ename||'  '||v_dname);
    fetch ed_cursor into v_ename,v_dname;
  end loop;
  close ed_cursor;
end;

法二：

declare
  cursor e_d is select * from
  (select e.ename,d.dname,rownum r from emp e,dept d where e.deptno=d.deptno)
  where r between 10 and 20;
begin
  for e in e_d loop
    dbms_output.put_line(e.ename||','||e.dname);
  end loop;
end;

法三：

declare
  cursor e_d  is select e.ename,d.dname from emp e,dept d where e.deptno=d.deptno;
begin
  for e in e_d loop
    if e_d%rowcount>=10 and e_d%rowcount<=20 then
      dbms_output.put_line(e.ename||','||e.dname);
    end if;
  end loop;
end;

2、定义游标：从雇员表中显示工资大于3000的记录，只要姓名、部门编号和工资，编程显示其中的奇数记录

法一：

declare
  cursor emp_cursor is select ename,deptno,sal from emp where sal>3000;
  v_ename varchar2(10);
  v_deptno number(10);
  v_sal number(10);
begin
  open emp_cursor;
  fetch emp_cursor into v_ename,v_deptno,v_sal;
  while emp_cursor%found loop
    if emp_cursor%rowcount mod 2=1 then
      dbms_output.put_line(v_ename||'  '||v_deptno||'  '||v_sal);
      fetch emp_cursor into v_ename,v_deptno,v_sal;
    end if;
  end loop;
  close emp_cursor;
end;

法二：

declare
  cursor c_emp is select ename,deptno,sal from emp where sal>3000;
begin
  for e in c_emp loop
    if mod(c_emp%rowcount,2)=1 then
      dbms_output.put_line(e.ename||','||e.deptno||','||e.sal);
    end if;
  end loop;
end;

3、用游标显示所有部门编号与名称，以及其所拥有的员工人数

法一：

declare
  cursor dept_cursor is select d.deptno,dname,count(*) from dept d
  left join emp e on d.deptno=e.deptno group by d.deptno,dname;
  v_deptno number(20);
  v_dname varchar2(10);
  v_sum number(10);
begin
  open dept_cursor;
  fetch dept_cursor into v_deptno,v_dname,v_sum;
  while dept_cursor%found loop
    dbms_output.put_line(v_deptno||' '||v_dname||' '||v_sum);
    fetch dept_cursor into v_deptno,v_dname,v_sum;
  end loop;
  close dept_cursor;
end;

法二：

declare
  cursor c_dept is select * from dept;
  v_n number:=0;
begin
  for d in c_dept loop
    select count(*) into v_n from emp where deptno=d.deptno;
    dbms_output.put_line(d.deptno||','||d.dname||',人数：'||v_n);
  end loop;
end;

4、用游标属性%rowcount实现输出前十个员工的信息

declare
  cursor c_emp is select * from emp;
  v_emp emp%rowtype;
begin
  open c_emp;
  loop
    fetch c_emp into v_emp;
    exit when c_emp%notfound or c_emp%rowcount>10;
    dbms_output.put_line(v_emp.empno||','||v_emp.ename);
  end loop;   
  close c_emp;
end;

5、通过使用游标来显示dept表中的部门名称及其相应的员工列表（提示：可以使用双重循环）

法一：

declare
  cursor dept_cursor is 
  select dname,e.* from dept d left join emp e on e.deptno=d.deptno;
  v_dname varchar2(10);
  v_emp emp%rowtype;
begin
  open dept_cursor;
  fetch dept_cursor into v_dname,v_emp.empno,v_emp.ename,v_emp.job,
    v_emp.mgr,v_emp.hiredate,v_emp.sal,v_emp.comm,v_emp.deptno;
  while dept_cursor%found loop
    dbms_output.put_line(v_dname||','||v_emp.empno||','||v_emp.ename
      ||','||v_emp.job||','||v_emp.mgr||','||v_emp.hiredate||','||
      v_emp.sal||','||v_emp.comm||','||v_emp.deptno); 
    fetch dept_cursor into v_dname,v_emp.empno,v_emp.ename,v_emp.job,
      v_emp.mgr,v_emp.hiredate,v_emp.sal,v_emp.comm,v_emp.deptno;
  end loop;
  close dept_cursor;
end;

法二：

declare
  cursor c_dept is select * from dept;
  --定义带参数的游标，根据提供的部门编号，去查询对应的员工列表
  cursor c_emp(dno emp.deptno%type) is select * from emp where deptno=dno;
  e emp%rowtype;
begin
  for d in c_dept loop
    dbms_output.put_line('------部门名称：'||d.dname);   
    open c_emp(d.deptno);--根据外层循环得到的部门编号去查询对应员工信息
    loop
      fetch c_emp into e;
      exit when c_emp%notfound;
      dbms_output.put_line(e.empno||','||e.ename);
    end loop;  
    close c_emp;
  end loop;  
end;

6、接受一个部门号，使用For循环，从emp表中显示该部门的所有雇员的姓名，工作和薪水

declare
  cursor emp_cursor is select * from emp where deptno=&deptno;
begin
  for e in emp_cursor loop
    dbms_output.put_line(e.ename||','||e.job||','||e.sal);
  end loop;
end;

7、编写一个程序块，将emp表中前5人的名字及其出的工资等级（salgrade）显示出来

法一：

declare
  cursor es_cursor is select ename,sal,grade
                      from(select ename,sal,rownum r from emp)e,salgrade s
                      where r<=5 and sal between losal and hisal;
  v_ename varchar2(10);
  v_sal number(10,2);
  v_grade number(2);
begin
  open es_cursor;
  fetch es_cursor into v_ename,v_sal,v_grade;
  while es_cursor%found loop
    dbms_output.put_line(v_ename||', '||v_sal||', '||v_grade);
    fetch es_cursor into v_ename,v_sal,v_grade;
  end loop;
  close es_cursor;
end;

法二：

declare
  cursor c_e is select e.ename,s.grade,rownum from emp e,salgrade s
                where e.sal between s.losal and s.hisal and rownum<=5;
begin
  for e in c_e loop
    dbms_output.put_line(e.ename||','||e.grade);   
  end loop;
end;

8、emp表中对所有雇员按他们基本薪水的10%给他们加薪，如果所增加后的薪水大于5000，则取消加薪

declare
  cursor cemp is select * from emp;
begin
  for e in cemp loop
    if e.sal*1.1<=5000 then
      update emp set sal=sal*1.1 where empno=e.empno;
    end if;
  end loop;
end;

9、按照salgrade表中的标准，给员工加薪，1：5%，2：4%，3：3%，4：2%，5：1%，并打印输出每个人加薪前后的工资

法一：

declare
  cursor es_cursor is select ename,sal,grade from emp e,salgrade s 
                      where sal between losal and hisal;
  v_ename varchar2(10);
  v_sal number(10,2);
  v_grade number(2);
begin
  open es_cursor;
  fetch es_cursor into v_ename,v_sal,v_grade;
  while es_cursor%found loop
    dbms_output.put_line('加薪前: '||v_ename||', '||v_sal||', '||v_grade);
    case v_grade when 1 then v_sal:=v_sal*1.05;
             when 2 then v_sal:=v_sal*1.04;
             when 3 then v_sal:=v_sal*1.03;
             when 4 then v_sal:=v_sal*1.02;
             when 5 then v_sal:=v_sal*1.01;
    end case;
    dbms_output.put_line('加薪后: '||v_ename||', '||v_sal);
    update emp set sal=v_sal where ename=v_ename;
    fetch es_cursor into v_ename,v_sal,v_grade;
  end loop;
  close es_cursor;
end;

法二：

declare
  cursor emp_sal_cursor is select sal,empno from emp;
  v_sal emp.sal%type;
  v_id emp.empno%type;
begin
  open emp_sal_cursor;
  fetch emp_sal_cursor into v_sal,v_id;
  while emp_sal_cursor%found loop
    dbms_output.put_line('加薪前: '||v_id || ': ' || v_sal);
    if v_sal between 700 and 1200 then v_sal:=v_sal+v_sal*0.05;
    elsif v_sal between 1201 and 1400 then v_sal:=v_sal+v_sal*0.04;
    elsif v_sal between 1401 and 2000 then v_sal:=v_sal+v_sal*0.03;
    elsif v_sal between 2001 and 3000 then v_sal:=v_sal+v_sal*0.02;
    else v_sal:=v_sal+v_sal*0.01;
    end if;
    update emp set sal=v_sal where empno=v_id;
    dbms_output.put_line('加薪后: '||v_id || ': ' || v_sal);
    fetch emp_sal_cursor into v_sal,v_id;
  end loop;
  close emp_sal_cursor;
end;

法三：

declare
  cursor emp_sal_cursor is select sal,empno from emp;
  temp number(4,2);
begin
  for c in emp_sal_cursor loop
    dbms_output.put_line(c.empno||': '||c.sal);
    if c.sal between 700 and 1200 then c.sal:=c.sal+c.sal*0.05;
    elsif c.sal between 1201 and 1400 then c.sal:=c.sal+c.sal*0.04;
    elsif c.sal between 1401 and 2000 then c.sal:=c.sal+c.sal*0.03;
    elsif c.sal between 2001 and 3000 then c.sal:=c.sal+c.sal*0.02;
    else c.sal:=c.sal+c.sal*0.01;
    end if;
    dbms_output.put_line(c.empno||': '||c.sal);
    update emp set sal=c.sal where empno=c.empno;
    end loop; 
end;

法四：

declare
  cursor c_e is select e.empno,e.ename,e.sal,s.grade from emp e,salgrade s 
                where e.sal between s.losal and s.hisal;
  sal_1 emp.sal%type;--加薪前的工资
  sal_2 emp.sal%type;--加薪后的工资     
begin
  for e in c_e loop
    sal_1:=e.sal;
    case e.grade
      when 1 then
        sal_2:=e.sal*1.05;
      when 2 then
        sal_2:=e.sal*1.04;
      when 3 then
        sal_2:=e.sal*1.03;
      when 4 then
        sal_2:=e.sal*1.02;
      when 5 then
        sal_2:=e.sal*1.01;
    end case;
    update emp set sal=sal_2 where empno=e.empno;
    dbms_output.put_line(e.empno||','||e.ename||','||sal_1||','||sal_2);
  end loop;
end;

10、用游标获取所有收入超过2000的 salesman

法一：

declare
  cursor emp_cursor is select * from emp where (sal+nvl(comm,0))>2000 and job='SALESMAN';
  v_emp emp%rowtype;
begin
  open emp_cursor;
  fetch emp_cursor into v_emp;
  while emp_cursor%found loop
    dbms_output.put_line(v_emp.empno||','||v_emp.ename);
    fetch emp_cursor into v_emp;
  end loop;
  close emp_cursor;
end;

法二：

declare
  cursor c_emp is select * from emp where (sal+nvl(comm,0))>2000 and job=upper('salesman');
begin
  for e in c_emp loop
    dbms_output.put_line(e.ename);
  end loop;
end;