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