CH03_游标练习

1. 使用游标 显示工资等级及该等级的最高工资

declare
  v_grade salgrade.grade%type;
  v_hisal salgrade.hisal%type;
  cursor cur_salgrade is 
  select grade,hisal from salgrade;

begin
  open cur_salgrade;
  fetch cur_salgrade into v_grade,v_hisal;
  while cur_salgrade%found loop
    dbms_output.put_line('工资等级是：'||v_grade||',最高工资是：'||v_hisal);  
    fetch cur_salgrade into v_grade,v_hisal;
    end loop;
  close cur_salgrade;
end;

2. 用户输入工资等级，使用游标 显示能拿到该等级工资的员工姓名

declare
  v_ename emp.ename%type;
  cursor cur_emp(v_grade salgrade.grade%type := &v_grade) is select e.ename from emp e,salgrade g where g.grade =  v_grade and e.sal between g.losal and g.hisal;
begin
  open cur_emp;
  fetch cur_emp into v_ename;
  while cur_emp%found loop
  dbms_output.put_line('能拿到该等级工资的员工：'||v_ename);
  fetch cur_emp into v_ename;
  end loop;
  close cur_emp;
end;

3. 用户输入工资等级，使用游标 显示能拿到该等级工资的员工姓名，所属部门

declare
  v_ename emp.ename%type;
  v_dno emp.deptno%type;
  cursor cur_emp(v_grade salgrade.grade%type := &v_grade) is select e.ename,e.deptno from emp e,salgrade g where g.grade =  v_grade and e.sal between g.losal and g.hisal;
begin
  open cur_emp;
  fetch cur_emp into v_ename,v_dno;
  while cur_emp%found loop
  dbms_output.put_line('能拿到该等级工资的员工：'||v_ename||',所属部门：'||v_dno);
  fetch cur_emp into v_ename,v_dno;
  end loop;
  close cur_emp;
end;

4. 用户输入工资等级，使用游标 显示能拿到该等级工资的员工姓名，所属部门及领导是谁

declare
  cursor cur_emp(v_grade salgrade.grade%type := &v_grade) is select e.ename,e.deptno,e.mgr from emp e,salgrade g where g.grade =  v_grade and e.sal between g.losal and g.hisal;
begin
  for emp_record in cur_emp
  loop
  dbms_output.put_line('能拿到该等级工资的员工：'||emp_record.ename||',所属部门：'||emp_record.deptno||',领导：'||emp_record.mgr);
  end loop;
  close cur_emp;
end;

5. 使用游标 显示前n名，员工编号，员工姓名，部门名称，职位名称，工资，工资等级，并按工资降序排列。要求：用参数游标实现

declare
  cursor cur_emp(eno number := &eno) is select e.empno,e.ename,d.dname,e.job,e.sal,g.grade 
  from emp e,dept d,salgrade g where e.deptno = d.deptno and e.sal between g.losal and g.hisal and rownum < eno order by sal desc;
begin
  for emp_record in cur_emp
  loop
  dbms_output.put_line('员工编号:'||emp_record.empno||',员工姓名：'||emp_record.ename||',职位名称：'||emp_record.job||'工资：'||emp_record.sal||',工资等级：'||emp_record.grade);
  end loop;
end;

6. 输入办公地址loc，使用游标 显示在该地址的所有部门的员工

declare
  v_loc dept.loc%type := '&loc';
  cursor cur_emp is select e.empno from emp e,dept d where e.deptno = d.deptno and d.loc = v_loc;
begin
  for emp_record in cur_emp
  loop
  dbms_output.put_line('员工编号:'||emp_record.empno);
  end loop;
end;

7. 使用游标 和loop循环来显示所有部门的名称

begin
  for emp_record in (select dname from dept)
  loop
  dbms_output.put_line('员工编号:'||emp_record.dname);
  end loop;
end;

8. 使用游标 和loop循环来显示所有部门的的地理位置（用%found属性）

declare
  v_loc dept.loc%type;
  cursor cur_emp is select loc from dept;
begin
  open cur_emp;
  fetch cur_emp into v_loc;
  while cur_emp %found loop
  dbms_output.put_line('地理位置:'||v_loc);
  fetch cur_emp into v_loc;
  end loop;
end;

9. 接收用户输入的部门编号，用for循环和游标，打印出此部门的所有雇员的所有信息

declare
  v_dno emp.empno%type := &dno;
begin
  for cur_emp in (select * from emp where deptno = v_dno)
  loop
    dbms_output.put_line('员工编号:'||cur_emp.empno||',员工姓名：'||cur_emp.ename||',工作：'||cur_emp.job||',上司：'||cur_emp.mgr||',日期：'||cur_emp.hiredate||',工资：'||cur_emp.sal);
  end loop;
end;  

10. 向游标传递一个工种JOB，显示此工种的所有雇员的所有信息

declare 
  cursor cur_emp(v_job in varchar2:='SALESMAN') is select empno,ename,sal from emp where job = v_job;
begin
  for emp_record in cur_emp('MANAGER')
  loop
    dbms_output.put_line('员工编号:'||emp_record.empno||',员工姓名：'||emp_record.ename||',工资：'||emp_record.sal);
  end loop;
end;

11. 用更新游标来为雇员加佣金，如果是销售人员，每人加200，如果是MANAGER，每人加500,其它，每人加100

declare
   cursor c_empupd is select sal,job from emp FOR UPDATE NOWAIT;
begin
    for v_emp in c_empupd
    loop
    if v_emp.job='SALESMAN'then update emp set sal=sal+200 where current of c_empupd;
      elsif v_emp.job='MANAGER'then update emp set sal=sal+500 where current of c_empupd;
      else update emp set sal=sal+100 where current of c_empupd; 
    end if;
  end loop;
end;

12. 编写一个PL/SQL程序块，对名字以‘A’或‘S’开始的所有雇员按他们的基本薪水的10%给他们加薪

begin
  update emp set sal = sal + sal*0.1 where ename like 'A%' or ename like 'S%';
  if sql%notfound then
    dbms_output.put_line('没有雇员需要上调工资');
  else
    dbms_output.put_line('已经上调了工资');
  end if;
end;

13. 编写一个PL/SQL程序块，对所有的salesman增加佣金500

begin
  update emp set comm=NVL(comm,0)+500 where job ='SALESMAN';
  if sql%notfound then
    dbms_output.put_line('salesman增加佣金500');
  else
    dbms_output.put_line('no_data_found');
  end if;
end;

14. 编写一个PL/SQL程序块，以提升2个资格最老的职员为高级职员（工作时间越长，资格越老）

declare
   cursor c_emp is select * from emp order by hiredate FOR UPDATE NOWAIT;
   v_emp emp%rowtype;
   begin
    --%rowcount当前提取到游标的第几行数据
    open c_emp;
    fetch c_emp into v_emp;
    while c_emp%rowcount<=2 
    loop
      update emp set job ='MANAGER' where current of c_emp;
      fetch c_emp into v_emp;
    end loop;
end;

15. 编写一个PL/SQL程序块，对所有雇员按他们的基本薪水的20%为他们加薪，如果增加的薪水大于300 就取消加薪

declare 
  cursor cur_emp is select sal from emp for update of sal;
  update_emp cur_emp%rowtype;
  sal_add emp.sal%type;
  sal_info emp.sal%type;
begin
  for update_emp in cur_emp loop
  sal_add := update_emp.sal * 0.2;
  if sal_add > 300 then
    sal_info := update_emp.sal;
  else
    sal_info := update_emp.sal + sal_add;
    end if;
    update emp set sal = sal_info where current of cur_emp;
  end loop;
end;