存储过程三个例子

/*
实例1：统计每年入职的员工个数。

可能SQL：
select to_char(hiredate,’yyyy’) from emp;
*/
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);