前言
无论是写PL/SQL还是在写程序的时候,最忌讳的就是不通过构思,直接写程序。本篇文章主要是认识到写PL/SQL的思路,以及思考方式是如何的。
思路
模板如下:
SQL语句
变量:1. 初始值 2.最终得到
优化:
1.在选择方法的时候,当出现累加和操作数据库2种方式都可以实现,那么优先选择累加,避免多次操作数据库。
实例
- 题目:在scott账号下,统计每年入职的员工个数。
- SQL语句是什么?
- 是否需要光标=》循环》退出条件:notfound
- 变量:1.初始值 2.最终得到的值
/*
SQL语句
select to_char(hiredate,'yyyy') from emp;
--->光标 --> 循环 --> 退出条件:notfound
变量:1. 初始值 2.最终得到
count80 number := 0;
count81 number := 0;
count82 number := 0;
count87 number := 0;
*/
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);
dbms_output.put_line('1981:'||count81);
dbms_output.put_line('1982:'||count82);
dbms_output.put_line('1987:'||count87);
end;
- 题目:在scott用户下的em表。为员工涨工资,从最低工资调起每人长10%,但工资总额不能超过5万元,请计算涨工资的人数和涨工资后的工资总额,并输出涨工资人数及工资总额。
/*
SQL语句
select empno,sal from emp order by sal
---> 光标 --> 循环 --> 退出条件:1. 总额>5w 2. notfound
变量:1. 初始值 2.最终得到
涨工资的人数: countEmp number := 0;
涨后的工资总额:salTotal number;
1.select sum(sal) into salTotal from emp;
2.涨后=涨前+sal*0.1
练习:涨后的工资总额:50205.325 人数:8
*/
declare
cursor cemp is select empno,sal from emp order by sal;
pempno emp.empno%type;
psal emp.sal%type;
--涨工资的人数:
countEmp number := 0;
--涨后的工资总额:
salTotal number;
begin
-- 得到初始的工资总额
select sum(sal) into salTotal from emp;
open cemp;
loop
--1. 总额>5w
exit when salTotal > 50000;
--取一个员工涨工资
fetch cemp into pempno,psal;
--2. notfound
exit when cemp%notfound;
--涨工资
update emp set sal=sal*1.1 where empno=pempno;
--人数+1
countEmp:= countEmp+ 1;
--2.涨后=涨前+sal*0.1
salTotal := salTotal + psal*0.1;
end loop;
close cemp;
commit;
dbms_output.put_line('涨后的工资总额:'||salTotal||' 人数:'||countEmp);
end;
- 题目:实现按部门分段(6000以上,(6000,3000),3000以下)统计各工资段的职工人数,以及各部门的工资总额(工资总额中不包括奖金)。
rem PL/SQL Developer Test Script
set feedback off
set autoprint off
rem Execute PL/SQL Block
/*
SQL语句
部门:select deptno from dept;
查部门中的员工薪水: select sal from emp where deptno=??
变量:1. 初始值 2.最终得到
每个段的人数
count1 number; count2 number; count3 number;
部门的工资总额
salTotal number;
1. select sum(sal) into salTotal from emp where deptno=??
2. 累加
*/
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;
--部门的工资总额
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>=3000 and psal<6000 then count2:=count2+1;
else count3:=count3+1;
end if;
end loop;
close cemp;
--保存结果
insert into msg values(pdeptno,count1,count2,count3,nvl(salTotal,0));
end loop;
close cdept;
commit;
dbms_output.put_line('完成');
end;
/