oralce plsql案例练习
以下plsql程序用的scott用户的dept,emp表。
案例1
1 --查询80,81,82,87年员工入职人数 2 set serveroutput on 3 declare 4 5 cursor cemp is select to_char(hiredate,'yyyy') from emp; 6 count80 number :=0; 7 count81 number :=0; 8 count82 number :=0; 9 count87 number :=0; 10 phiredate varchar2(4) := ''; 11 12 begin 13 open cemp; 14 15 loop 16 fetch cemp into phiredate ; 17 exit when cemp%notfound; 18 19 if phiredate = '1980' then count80 := count80 +1; 20 elsif phiredate = '1981' then count81 := count81 +1; 21 elsif phiredate = '1982' then count82 := count82 +1; 22 elsif phiredate = '1987' then count87 := count87 +1; 23 end if; 24 25 end loop; 26 close cemp; 27 28 dbms_output.put_line('total:'||(count80+count81+count82+count87)); 29 dbms_output.put_line('1980:'||(count80 )); 30 dbms_output.put_line('1981:'||(count81)); 31 dbms_output.put_line('1982:'||(count82)); 32 dbms_output.put_line('1987:'||(count87)); 33 34 end; 35 /
案例2
1 --员工涨工资。从最低工资涨起每人涨10%，但是所有员工的工资总额不能超过5万元，请计算涨工资的人数和涨工资后的所有员工的工资总额 2 3 4 set serveroutput on 5 6 declare 7 8 --定义光标:查询所有员工的员工号和工资 9 cursor cemp is select empno,sal from emp order by sal; 10 pempno emp.empno%type; 11 psal emp.sal%type; 12 13 countEmp number := 0; 14 salTotal emp.sal%type; 15 16 begin 17 --查询所有员工涨前的工资总额 18 select sum(sal) into salTotal from emp; 19 20 open cemp; 21 loop 22 --涨前工资综合大于5000则退出循环 23 exit when salTotal > 50000 ; 24 25 fetch cemp into pempno,psal; 26 27 --若未从光标中取到值退出循环 28 exit when cemp%notfound; 29 30 --若当前的工资总额加上 当前员工涨后的工资大于5万则退出循环 31 exit when salTotal +psal*0.1 >50000; 32 33 update emp set sal = sal*1.1 where empno = pempno; 34 35 countEmp := countEmp +1; 36 salTotal :=salTotal +psal*0.1; 37 38 end loop; 39 close cemp; 40 commit; 41 42 dbms_output.put_line('涨工资的人数是:'||countEmp); 43 dbms_output.put_line('涨后的工资总额是:'||salTotal); 44 45 46 end; 47 /
案例3:
1 --实现按部门分段(6000以上、(6000，3000)、3000元以下)统计各个工资段的职工人数、以及各部门的工资总额 2 3 set serveroutput on 4 5 declare 6 7 cursor cdept is select deptno from dept; 8 pdeptno dept.deptno%type; 9 10 cursor cemp(dno number ) is select deptno,sal from emp where deptno = dno; 11 pdno emp.deptno%type; 12 psal emp.sal%type; 13 14 count1 number := 0; 15 count2 number := 0; 16 count3 number := 0; 17 saltotal number := 0; 18 19 begin 20 21 open cdept; 22 23 loop 24 fetch cdept into pdeptno ; 25 exit when cdept%notfound; 26 27 open cemp(pdeptno); 28 saltotal := 0; 29 count1 := 0; 30 count2 := 0; 31 count3 :=0; 32 loop 33 fetch cemp into pdno,psal; 34 exit when cemp%notfound; 35 36 if psal < 3000 then count1 :=count1+1; 37 elsif psal <= 6000 then count2:=count2+1; 38 elsif psal > 6000 then count3 := count3 +1; 39 end if; 40 saltotal := saltotal +psal; 41 end loop; 42 close cemp; 43 insert into msg values(pdno,count1,count2,count3,saltotal); 44 end loop; 45 46 47 close cdept; 48 49 commit; 50 end; 51 /
案例出处：http://www.imooc.com/learn/360
