Oracle 游标的练习

--1、什么是游标？使用游标的基本步骤是什么？ 

/*

挡在PL/SQL块中执行查询语句（SELECT）和数据操纵语句（DML）时，

Oracle会在内存中分配一个缓冲区，缓冲区中包含了处理过程的必需信息，

包括已经处理完的行数、指向被分析行的指针和查询情况下的活动集，即查询语句返回的数据行集。

该缓冲区域称为上下文区，游标是指向该缓冲区的句柄或指针。

*/

--2、游标有哪几种类型？分别在什么情况下使用？ 

/*

（1）显式游标：由用户定义、操作，用于处理返回多行数据的SELECT查询。

（2）隐式游标：由系统自动进行操作，用于处理DML语句和返回单行数据的SELECT查询。

*/

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

--方法一

declare

  cursor c_emp is select * from emp;

  cursor c_dept is select * from dept;

  v_count number;

begin

  for v_dept in c_dept loop

    v_count := 0;

    for v_emp in c_emp loop

      if v_emp.deptno = v_dept.deptno then

        v_count := v_count + 1;

      end if;

    end loop;

    dbms_output.put_line('编号：' || v_dept.deptno || '是：' || 

                        v_dept.dname || '部门，共有员工' || v_count || '人。');

  end loop;

end;

--方法二：（PS：缺失没有员工的部门）

declare

  cursor c_temp is (select deptno, dname, count(*) count_p

  from (select d.deptno, d.dname

          from dept d

          join emp e on d.deptno = e.deptno) t

 group by deptno, dname);

begin

  for v_temp in c_temp loop

    dbms_output.put_line(v_temp.deptno || '-' || v_temp.dname || '-' || v_temp.count_p);

  end loop;

end;

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

declare

  cursor c is (select * from emp);

begin

  for v in c loop

    if c%rowcount<=10 then

      dbms_output.put_line(c%rowcount || '-' || v.empno || '-' || v.ename || '-' || v.sal);

    else exit;

    end if;

  end loop;

end;

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

declare

  cursor c_emp is select * from emp;

  cursor c_dept is select * from dept;

begin

  for v_dept in c_dept loop

    dbms_output.put_line( v_dept.dname || ' 部门人员列表：');

    for v_emp in c_emp loop

      if v_emp.deptno = v_dept.deptno then

        dbms_output.put_line('    ' || v_emp.ename);

      end if;

    end loop;

    dbms_output.put_line('');

  end loop;

end;

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

declare

  cursor c_emp is select * from emp where deptno = &please_enter_deptno;

  v_dept dept%rowtype;

begin

  select * into v_dept from dept where  deptno = &please_enter_deptno;

  dbms_output.put_line(&please_enter_deptno || ' :: ' || v_dept.dname);

  dbms_output.put_line('');

  dbms_output.put_line('ename :: job :: sal');

  for v_emp in c_emp loop

    dbms_output.put_line(v_emp.ename || ' :: ' || v_emp.job || ' :: ' || v_emp.sal);

  end loop;

end;

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

begin

  for v_emp in (select e.ename, s.grade

  from emp e

  join salgrade s on e.sal between s.losal and s.hisal where rownum < =5) loop

    dbms_output.put_line(v_emp.ename || ' :: grade ' || v_emp.grade);

  end loop;

end;

--8、用带参数的游标输出部门编号为10， 30的员工信息。

declare

  cursor c_emp(v_deptno dept.deptno%type) is select * from emp where deptno = v_deptno;

begin

  dbms_output.put_line('部门编号为：10 的员工列表');

  for v_emp in c_emp(10) loop

    dbms_output.put_line(v_emp.ename);

  end loop;

  dbms_output.put_line('部门编号为：30 的员工列表');

  for v_emp in c_emp(30) loop

    dbms_output.put_line(v_emp.ename);

  end loop;

end;

--9、使用带参数的游标，实现接受一个部门名称，从emp表中显示该部门的所有雇员的姓名，工作和薪水。

declare

  cursor c_emp(v_dname dept.dname%type) is select * from emp where deptno = (select deptno from dept where dname = v_dname);

begin

  dbms_output.put_line('SALES 部门员工名单：');

  for v_emp in c_emp('SALES') loop

    dbms_output.put_line(v_emp.ename || ' :: ' || v_emp.job || ' :: ' || v_emp.sal);

  end loop;

end;

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

declare

  cursor c_emp is select * from emp;

begin

  dbms_output.put_line('收入超过1500的SALESMAN列表：');

  --收入超过2000的salesman没有数据

  for v_emp in c_emp loop

    if v_emp.job = 'SALESMAN' and v_emp.sal > 1500 then

      dbms_output.put_line(v_emp.ename || ' :: ' || v_emp.sal);

    end if;

  end loop;

end;

--11、编写一个PL/SQL程序块，从emp表中对名字以"A"或"S"开始的所有雇员按他们基本薪水的10%给他们加薪。

declare

  cursor c_emp is select * from emp2 for update;

begin

  for v_emp in c_emp loop

    if substr(v_emp.ename,1,1) = 'A' or substr(v_emp.ename,1,1) = 'S' then

      dbms_output.put_line('加薪员工：' || v_emp.ename || '加薪：' || v_emp.sal*0.1);

      update emp2 set sal = sal*1.1 where ename = v_emp.ename;

    end if;

  end loop;

  commit;

end;

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

begin

  for v_emp in (select ename,sal,empno from emp2) loop

    if v_emp.sal*1.1 < 5000 then

      dbms_output.put_line('待加薪的员工：' || v_emp.ename);

      update emp2 set sal = sal * 1.1 where empno = v_emp.empno;

    end if;

  end loop;

  commit;

end;

--13、按照salgrade表中的标准，给员工加薪，1：5%，2：4%，3：3%，4：2%，5：1%， 

--并打印输出每个人，加薪前后的工资。

--方法一

declare

  cursor c_emp is(

    select ename, sal, grade

      from emp2 e

      join salgrade s on e.sal between s.losal and s.hisal) order by ename;

begin

  for v_emp in c_emp loop

    update emp2 set sal = sal * (1+((6 - v_emp.grade)*0.01)) where emp2.ename = v_emp.ename;

    dbms_output.put_line(v_emp.ename || '，加薪前：' || v_emp.sal || '，等级：' || 

                         v_emp.grade || '，加薪后：' || v_emp.sal * (1+((6 - v_emp.grade)*0.01)));

  end loop;

  commit;

end;

--方法二

declare

  cursor c_emp is(

    select ename, sal, grade

      from emp2 e

      join salgrade s on e.sal between s.losal and s.hisal) order by ename;

begin

  for v_emp in c_emp loop

    case v_emp.grade

    when 1 then

      update emp2 set sal = sal * (1+0.05) where emp2.ename = v_emp.ename;

    when 2 then

      update emp2 set sal = sal * (1+0.04) where emp2.ename = v_emp.ename;

    when 3 then

      update emp2 set sal = sal * (1+0.03) where emp2.ename = v_emp.ename;

    when 4 then

      update emp2 set sal = sal * (1+0.02) where emp2.ename = v_emp.ename;

    when 5 then

      update emp2 set sal = sal * (1+0.01) where emp2.ename = v_emp.ename;

    else null;

    end case;

    

    /*dbms_output.put_line(v_emp.ename || '，加薪前：' || v_emp.sal || '，等级：' || 

                         v_emp.grade || '，加薪后：' || v_emp.sal * (1+((6 - v_emp.grade)*0.01)));*/

  end loop;

  commit;

end;