Oracle数据库之 PL SQL 学习笔记
1、定义基本变量：
2、引用型的变量：
set serveroutput on
declare
pename emp.ename%type;
psal emp.sal%type;
begin
select ename,sal into pename,psal from emp where empno='7521';
dbms_output.put_line(pename||'的薪水是'||psal);
end;
/
3、记录型变量：
set serveroutput on
declare
emp_rec emp%rowtype;
begin
select * into emp_rec from emp where empno='7698';
dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);
end;
/
4、if语句的使用
/*
判断用户从键盘的输入
*/
set serveroutput on
--接受一个键盘输入
--num:地址值，含义是在该地址上保存了输入的值
accept num prompt'请输入一个数字';
declare
--定义变量从键盘的输入
pnum number:=#
begin
if pnum=0 then dbms_output.put_line('您输入的是0');
elsif pnum=1 then dbms_output.put_line('您输入的是1');
elsif pnum=2 then dbms_output.put_line('你输入的是2');
else dbms_output.put_line('其他');
end if;
end;
/
5、while循环：
set serveroutput on
declare
pnum number := 1;
begin
while pnum <= 10 loop
dbms_output.put_line(pnum);
pnum := pnum + 1;
end loop;
end;
/
6、loop循环
set serveroutput on
declare
pnum number:=1;
begin
loop
exit when pnum>10;
dbms_output.put_line(pnum);
pnum:=pnum+1;
end loop;
end;
/
7、for循环
set serveroutput on
declare
pnum number:=1;
begin
for pnum in 1..10 loop
dbms_output.put_line(pnum);
end loop;
end;
/
（推荐使用loop循环）
8、光标的使用
--查询并打印员工的姓名和薪水
set serveroutput on
/*
光标的属性
%found:光标找到记录 %notfound：光标找不到记录
*/
declare
--定义一个光标
cursor cemp is select ename,sal from emp;
--为光标定义对应的变量
pename emp.ename%type;
psal emp.sal%type;
begin
--打开光标
open cemp;
loop
--取一条记录
fetch cemp into pename,psal;
exit when cemp %notfound;
dbms_output.put_line(pename||'的薪水是'||psal);
end loop;
--关闭光标
close cemp;
end;
/
9、实例---给员工涨工资：
set serveroutput on
declare
cursor cemp is select empno,perjob from emp;
pempno emp.empno%type;
pjob emp.perjob%type;
begin
open cemp;
loop
fetch cemp into pempno,pjob;
exit when cemp %notfound;
if pjob='PRESIDENT' then update emp set sal=sal+1500 where empno=pempno;
elsif pjob='ANALYST' then update emp set sal = sal+1000 where empno=pempno;
elsif pjob='SALESMAN' then update emp set sal = sal+500 where empno=pempno;
else update emp set sal = sal+300 where empno=pempno;
end if;
end loop;
close cemp;
commit;（如果update了数据，需在后面加上commit）
end;
/
10、光标的其他属性及其使用实例
①%isopen:
if cemp%isopen then dbms_output.put_line('光标一打开');
②%rowcount: （总共影响的行数）
dbms_output.put_line('行数：'||cemp %rowcount);
11、光标数的限制：
默认情况下oracle数据库只允许在同一个会话中打开300个光标
查看光标属性：切换到sys管理员用户下，conn sys/root as sysdba 就可以切换到sys用户
修改默认的光标数：
alter system set open_cursors=400 scope=both;
(scope的取值有三个参数：
memory：只更改当前实例，不更改系统参数文件
spfile :只更改参数文件，不更改当前实例，需重启数据库才能生效
both ：是以上两者
如果设置错了，可以使用rollback命令回滚
11、带参数的光标
set serveroutput on
declare
cursor cemp(dno number) is select ename from emp where deptno=dno;
pename emp.ename%type;
begin
open cemp(10);
loop
fetch cemp into pename;
exit when cemp %notfound;
dbms_output.put_line(pename);
end loop;
close cemp;
end;
/
12、系统例外：
no_data_found （没有找到数据）
too_many_rows （select ... into 语句匹配多个行）
zero_divide (被零除）
value_error （算术或转换错误）
timeout_on_resource (在等待资源时发生超时）
13、自定义例外：
set serveroutput on
declare
pename emp.ename%type;
no_emp_found exception;
cursor cemp is select ename from emp where empno=12;
begin
open cemp;
fetch cemp into pename;
if cemp%notfound then
raise no_emp_found;
end if;
close cemp;
exception
when no_emp_found then dbms_output.put_line('找不到员工');
when others then dbms_output.put_line('其他');
end;
/
14、统计每年入职的员工数
set serveroutput on
declare
cursor cemp is select to_char(hiredate,'yyyy') from emp;
pdate varchar2(4);
count80 number := 0;
count81 number := 0;
count82 number := 0;
count87 number := 0;
begin
open cemp;
loop
fetch cemp into pdate;
exit when cemp%notfound;
if pdate='1980' then count80:=count80+1;
elsif pdate='1981' then count81:=count81+1;
elsif pdate='1982' then count82:=count82+1;
else count87:=count87+1;
end if;
end loop;
dbms_output.put_line('总共：'||(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);
close cemp;
end;
/
15、案例二：为员工涨工资
/*
做之前先分析
SQL语句
select empno,sal from emp order by sal asc;
-->光标-->循环-->退出条件：1.工资总额>5w 2.%notfound
变量：1.初始值 2.如何得到
涨工资的人数：
countEmp number :=0;
涨后的工资总额：
salTotal number;
select sum(sal) into salTal from emp;
张后的工资总额=涨前的工资总额 + sal * 0.1
*/
set serveroutput on
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;
stop_sal exception;
begin
select sum(sal) into salTotal from emp;
open cemp;
loop
if salTotal < 50000 then （加入限制只有工资总额在5000以内才执行下面的代码）
exit when salTotal>50000;
fetch cemp into pempno,psal;
exit when cemp%notfound;
countEmp := countEmp+1;
update emp set sal=sal*1.1 where empno=pempno;
salTotal := salTotal + psal*0.1;
else raise stop_sal;
end if;
end loop;
close cemp;
commit;
dbms_output.put_line('涨工资人数：'||countEmp||'工资总额：'||salTotal);
exception
when stop_sal then dbms_output.put_line('涨工资结束');
when others then dbms_output.put_line('其他');
end;
/
16、综合案例四
1、由于最后的结果也是一张表，所以先创建一张表
create table msg1(
coursename varchar2(20),
dname varchar2(20),
count1 number,
count2 number,
count3 number,
avggrade number
);
SQL语句
1、得到有哪些系
select dno,dname from dep; -->光标 -->循环-->退出条件：notfound
2、得到系中选修了“大学物理“的学生的成绩
select grade fromm sc where cno=(select cno from course where cname=??) and sno in (select sno from student where dno =??);
-->带参数的光标 -->循环 -->退出条件 -->notfound
变量：1、初始值 2、如何得到
每个分数段的人数
count1 number,count2 number,count3 number
每个系选修了大学物理的学生的平均成绩
avggrade number
1、算术运算
2、sql语句查询
set serveroutput on
declare
--系的光标
cursor cdept is select dno,dname from dep;
pdno dep.dno%type;
pdname dep.dname%type;
--成绩光标
cursor cgrade(coursename varchar2,depno number) is select grade from sc where cno=(select cno from course where cname=coursename)
and sno in (select sno from student where dno=depno);
pgrade sc.grade%type;
--每个分数段的人数
count1 number;count2 number;count3 number;
--每个系选修了大学物理的学生的平均成绩
avggrade number;
--课程名称
pcourseName varchar2(20) := '大学物理';
begin
--打开系的光标
open cdept;
loop
--取一个系的信息
fetch cdept into pdno,pdname;
exit when cdept %notfound;
--初始化工作
count1:=0;count2:=0;count3:=0;
--系的平均成绩
select avg(grade) into avggrade from sc where cno=(select cno from course where cname=pcourseName)
and sno in (select sno from student where dno=pdno);
--取系中选修了大学物理的学生成绩
open cgrade(pcourseName,pdno);
loop
--取一个学生的成绩
fetch cgrade into pgrade;
exit when cgrade%notfound;
--判断成绩的范围
if pgrade<60 then count1:=count1+1;
elsif pgrade>=60 and pgrade<85 then count2:=count2+1;
else count3:=count3+1;
end if;
end loop;
close cgrade;
--保存当前的结构
insert into msg1 values(pcourseName,pdname,count1,count2,count3,avggrade);
end loop;
close cdept;
dbms_output.put_line('数据查询成功！');
end;
/
