PL/SQL 三个小例子

/*
SQL语句
员工集合：select to_char(hiredate,'yyyy') from emp --> 光标  --> 循环--> 退出条件：notfound

变量
count80 number := 0;
count81 number := 0;
count82 number := 0;
count87 number := 0;
*/
set serveroutput on
declare
  cursor cemp is select to_char(hiredate,'yyyy') from emp;
  phiredate varchar2(4);
  count80 number := 0;
  count81 number := 0;
  count82 number := 0;
  count87 number := 0;
begin
  open cemp;
  loop
    --取一个员工
    fetch cemp into phiredate;
    exit when cemp%notfound;
    --判断年份
    if phiredate = '1980' then count80:=count80+1;
      elsif phiredate = '1981' then count81:=count81+1;
      elsif phiredate = '1982' then count82:=count82+1;
      else count87:=count87+1;
    end if;
  
  end loop;
  close cemp;
  --输出
  dbms_output.put_line('Total:'||(count80+count81+count82+count87));
  dbms_output.put_line('1980:'||count80);
  dbms_output.put_line('1981:'||count81);
  dbms_output.put_line('1982:'||count82);
  dbms_output.put_line('1987:'||count87);
end;
/
  

/*
SQL语句
员工集合: select empno,sal from emp order by sal
--> 光标 --> 循环 --> 退出条件：1. 总额>5w  2. notfound

变量
涨工资的人数: countEmp number := 0;
涨后工资总额： salTotal number;
1. select sum(sal) into salTotal from emp;
2. 涨后 = 涨前 + sal *0.1

练习:把程序改对
*/
set serveroutput on
declare
  cursor cemp is select empno,sal from emp order by sal;
  pempno emp.empno%type;
  psal   emp.sal%type;
  
  --涨工资的人数: 
  countEmp number := 0;
  --涨后工资总额： 
  salTotal number;
begin
  --得到初始的工资总额
  select sum(sal) into salTotal from emp;
  
  open cemp;
  loop
    --1. 总额>5w
    exit when salTotal > 50000;
    --取一个员工
    fetch cemp into pempno,psal;
    --2. notfound
    exit when cemp%notfound;
    
    update emp set sal=sal*1.1 where empno=pempno;
    --人数加一
    countEmp:=countEmp+1;
    
    --涨后 = 涨前 + sal *0.1
    salTotal := salTotal + psal*0.1;
  end loop;
  close cemp;

  commit;
  dbms_output.put_line('人数:'||countEmp||'   总额:'||salTotal);

end;
/

  
  
  
  

set serveroutput on
declare
  cursor cdept is select deptno from dept;
  pdeptno dept.deptno%type;
  
  cursor cemp(dno number) is select sal from emp where deptno=dno;
  psal emp.sal%type;
  --每个段的人数
  count1 number; count2 number; count3 number;
  --部门的工资的工资: 
  saltotal number;
begin
  open cdept;
  loop
    --取一个部门
    fetch cdept into pdeptno;
    exit when cdept%notfound;
    
    --初始化
    count1:=0; count2:=0; count3:=0;
    select sum(sal) into saltotal from emp where deptno=pdeptno;
  
    --得到部门中员工的薪水
    open cemp(pdeptno);
    loop
      --取一个员工
      fetch cemp into psal;
      exit when cemp%notfound;
      --判断薪水所在的区间
      if psal < 3000 then count1:=count1+1;
        elsif psal>=3000 and psal<6000 then count2:=count2+1;
        else count3:=count3+1;
      end if;
    end loop;
    close cemp;
    
    --保存当前部门的结果
    insert into msg values(pdeptno,count1,count2,count3,nvl(SALTOTAL,0));
  
  end loop;
  close cdept;
  commit;
end;
/