Oracle 游标使用2

 转载链接。http://www.cnblogs.com/sc-xx/archive/2011/12/03/2275084.html

--7:编写一个PL/SQL程序块，对名字以‘A’或‘S’开始的所有雇员按他们的基本薪水(sal)的10%给他们加薪(对emp1表进行修改操作)
declare
  cursor csr_AddSal is
    select *
      from emp1
     where ENAME LIKE 'A%'
        OR ENAME LIKE 'S%'
       for update OF SAL;
  r_AddSal csr_AddSal%rowtype;
  saleInfo emp1.SAL%TYPE;
begin
  for r_AddSal in csr_AddSal loop
    dbms_output.put_line(r_AddSal.ENAME || '原来的工资:' || r_AddSal.SAL);
    saleInfo := r_AddSal.SAL * 1.1;
    UPDATE emp1 SET SAL = saleInfo WHERE CURRENT OF csr_AddSal;
  end loop;
end;
--8：编写一个PL/SQL程序块，对所有的salesman增加佣金(comm)500
declare
  cursor csr_AddComm(p_job nvarchar2) is
    select * from emp1 where JOB = p_job FOR UPDATE OF COMM;
  r_AddComm emp1%rowtype;
  commInfo  emp1.comm%type;
begin
  for r_AddComm in csr_AddComm('SALESMAN') LOOP
    commInfo := r_AddComm.COMM + 500;
    UPDATE EMP1 SET COMM = commInfo where CURRENT OF csr_AddComm;
  END LOOP;
END;

--9：编写一个PL/SQL程序块，以提升2个资格最老的职员为MANAGER（工作时间越长，资格越老）
--（提示：可以定义一个变量作为计数器控制游标只提取两条数据；也可以在声明游标的时候把雇员中资格最老的两个人查出来放到游标中。）
declare
  cursor crs_testComput is
    select * from emp1 order by HIREDATE asc;
  --计数器
  top_two      number := 2;
  r_testComput crs_testComput%rowtype;
begin
  open crs_testComput;
  FETCH crs_testComput
    INTO r_testComput;
  while top_two > 0 loop
    dbms_output.put_line('员工姓名：' || r_testComput.ENAME || ' 工作时间：' ||
                         r_testComput.HIREDATE);
    --计速器减一
    top_two := top_two - 1;
    FETCH crs_testComput
      INTO r_testComput;
  end loop;
  close crs_testComput;
end;

--10：编写一个PL/SQL程序块，对所有雇员按他们的基本薪水(sal)的20%为他们加薪，
--如果增加的薪水大于300就取消加薪(对emp1表进行修改操作，并将更新前后的数据输出出来)
declare
  cursor crs_UpadateSal is
    select * from emp1 for update of SAL;
  r_UpdateSal crs_UpadateSal%rowtype;
  salAdd      emp1.sal%type;
  salInfo     emp1.sal%type;
begin
  for r_UpdateSal in crs_UpadateSal loop
    salAdd := r_UpdateSal.SAL * 0.2;
    if salAdd > 300 then
      salInfo := r_UpdateSal.SAL;
      dbms_output.put_line(r_UpdateSal.ENAME || ':  加薪失败。' || '薪水维持在：' ||
                           r_UpdateSal.SAL);
    else
      salInfo := r_UpdateSal.SAL + salAdd;
      dbms_output.put_line(r_UpdateSal.ENAME || ':  加薪成功.' || '薪水变为：' ||
                           salInfo);
    end if;
    update emp1 set SAL = salInfo where current of crs_UpadateSal;
  end loop;
end;

--11:将每位员工工作了多少年零多少月零多少天输出出来   
--近似
--CEIL(n)函数：取大于等于数值n的最小整数
--FLOOR(n)函数：取小于等于数值n的最大整数
--truc的用法 http://publish.it168.com/2005/1028/20051028034101.shtml
declare
  cursor crs_WorkDay is
    select ENAME,
           HIREDATE,
           trunc(months_between(sysdate, hiredate) / 12) AS SPANDYEARS,
           trunc(mod(months_between(sysdate, hiredate), 12)) AS months,
           trunc(mod(mod(sysdate - hiredate, 365), 12)) as days
      from emp1;
  r_WorkDay crs_WorkDay%rowtype;
begin
  for r_WorkDay in crs_WorkDay loop
    dbms_output.put_line(r_WorkDay.ENAME || '已经工作了' ||
                         r_WorkDay.SPANDYEARS || '年,零' || r_WorkDay.months ||
                         '月,零' || r_WorkDay.days || '天');
  end loop;
end;

--12:输入部门编号，按照下列加薪比例执行(用CASE实现，创建一个emp1表，修改emp1表的数据),并将更新前后的数据输出出来
--  deptno  raise(%)
--  10      5%
--  20      10%
--  30      15%
--  40      20%
--  加薪比例以现有的sal为标准
--CASE expr WHEN comparison_expr THEN return_expr
--[, WHEN comparison_expr THEN return_expr]... [ELSE else_expr] END
declare
  cursor crs_caseTest is
    select * from emp1 for update of SAL;
  r_caseTest crs_caseTest%rowtype;
  salInfo    emp1.sal%type;
begin
  for r_caseTest in crs_caseTest loop
    case
      when r_caseTest.DEPNO = 10 THEN
        salInfo := r_caseTest.SAL * 1.05;
      when r_caseTest.DEPNO = 20 THEN
        salInfo := r_caseTest.SAL * 1.1;
      when r_caseTest.DEPNO = 30 THEN
        salInfo := r_caseTest.SAL * 1.15;
      when r_caseTest.DEPNO = 40 THEN
        salInfo := r_caseTest.SAL * 1.2;
    end case;
    update emp1 set SAL = salInfo where current of crs_caseTest;
  end loop;
end;

--13:对每位员工的薪水进行判断，如果该员工薪水高于其所在部门的平均薪水，则将其薪水减50元，输出更新前后的薪水，员工姓名，所在部门编号。
--AVG([distinct|all] expr) over (analytic_clause)
---作用：
--按照analytic_clause中的规则求分组平均值。
--分析函数语法:
--FUNCTION_NAME(<argument>,<argument>...)
--OVER
--(<Partition-Clause><Order-by-Clause><Windowing Clause>)
--PARTITION子句
--按照表达式分区(就是分组),如果省略了分区子句,则全部的结果集被看作是一个单一的组
select *
  from emp1 DECLARE CURSOR crs_testAvg IS
        select EMPNO,
               ENAME,
               JOB,
               SAL,
               DEPNO,
               AVG(SAL) OVER(PARTITION BY DEPNO) AS DEP_AVG
          FROM EMP1
           for update of SAL;

r_testAvg crs_testAvg%rowtype;
salInfo emp1.sal%type;
begin
  for r_testAvg in crs_testAvg loop
    if r_testAvg.SAL > r_testAvg.DEP_AVG then
      salInfo := r_testAvg.SAL - 50;
    end if;
    update emp1 set SAL = salInfo where current of crs_testAvg;
  end loop;
end;