运用瀑布模型完成PL/SQL程序设计
12).变量:(1).初始值是多少
统计每年入职的员工人数(2).SQL语句:
-->光标-->循环-->退出条件:notfound
每年入职的员工人数:
count81 number := 0;
count87 number := 0;
set serveroutput on
cursor cemp is select to_char(hiredate,'yyyy') from emp;
count80 number := 0;
count82 number := 0;
begin
loop
exit when cemp%notfound;
elsif phiredate = '1981' then count81 := count81 + 1;
else count87 := count87 + 1;
end loop;
dbms_output.put_line('Total:'||(count80+count81+count82+count87));
dbms_output.put_line('count81:'||count81);
dbms_output.put_line('count87:'||count87);
/
(1).问题描述:为员工涨工资。从最低工资涨起,每人涨10%,但工资总额不能超过5万元,请计算涨工资的人数和涨工资后的工资总额,并输出涨工资人数及工资总额。
select empno,sal from emp order by sal;
(3).变量:(1).初始值(2).如何得到
countEmp number := 0;
SalTotal number;
2.涨后的工资总额=涨前的工资总额+sal*0.1;
set serveroutput on
cursor cemp is select empno,sal from emp order by sal;
psal emp.sal%type;
salTotal number;
select sum(sal) into salTotal from emp;
loop
fetch cemp into pempno,psal;
exit when salTotal + 0.1*psal > 50000;
countEmp := countEmp + 1;
end loop;
commit;
end;
4.涉及两张表的员工涨工资问题(2).SQL语句:
有哪些部门
-->光标-->循环-->退出条件:notfound
select sal from emp where deptno=?-->带一个参数的光标-->循环-->退出条件:notfound
每个段的员工人数
count2 number;
每个部门的工资总额:
方法1.select sum(sal) into saltotal from emp where deptno=???
(4).代码:
-- cursor cdept is select deptno from dept;
--员工光标
csal emp.sal%type;
count3 number;
count6 number;
totalsal number;
open cdept;
fetch cdept into cdeptno;
count3:=0;
count6:=0;
open cemp(cdeptno);
fetch cemp into csal;
if csal<3000 then
elsif csal>=3000 and csal<6000 then
else
end if;
end loop;
close cemp;
close cdept;
dbms_output.put_line('统计完成');
/
:在sqlplus中用@引入sql文件路径,然后回车执行。