PL/SQL实例

1. 统计每年入职的员工人数

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;
count83 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  count83:=count83+1;
end if;
end loop;
close cemp;
dbms_output.put_line('total:'||(count80+count81+count82+count83));
end;
/

 2. 员工涨工资问题，问题点;多次执行涨工资后，总工资会超过6000，需在更新前判断总工资是否有超过6000，有则不更新

declare 
cursor cemp is select empno,sal from emp order by sal;
countEmp number:=0;
salTotal number;
pempno emp.empno%TYPE;
psal emp.sal%TYPE;

begin
select sum(sal) into salTotal from emp;
open cemp;
loop
exit when saltotal>6000;
fetch cemp into pempno,psal ;
exit when cemp%notfound;
update emp set sal=sal+sal*0.1 where empno=pempno;
 countEmp:=countEmp+1;
 salTotal:=salTotal+psal*0.1;
end loop;
close cemp;
commit;
dbms_output.put_line('涨工资人数：'||countEmp||','||'涨薪总额'||salTotal);
end;

 3. 多张表涨工资问题：实现按部门分段（6000以上，6000-3000,3000以下）统计各工资段的职工人数，以及各部门的工资总额。

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;
saltotal:=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>6000 then count3:=count3+1;
else count2:=count2+1;
end if;

end loop;
close cemp;

insert into msg values(pdeptno,count1,count2,count3,saltotal);
end loop;
close cdept;

dbms_output.put_line('统计完成');
end;

 4. 带输入参数和输出参数的执行

create or replace PROCEDURE p_hello(p_empno in emp.empno%type,
                                    p_sal out emp.sal%type) 
as

begin
select sal into p_sal from emp where empno=p_empno;
end;

执行时，需要定义一个参数，用来存储输出参数的值：

declare
p_sal emp.sal%type;
begin
p_hello(8003,p_sal);
end;