循环、光标、异常
oracle学习笔记：循环、光标、异常
打印Hello World
- 第一案例：打印Hello World
declare
--(没有声明可以不写)
--说明变量(变量声明，光标)
begin
--程序体
dbms_output.put_line('Hello World');
end;
--（/表示执行）
/
--查询输出函数
desc dbms_output
- PLSQL是对SQL的扩展
定义基本变量
类型：char，varchr2,date,number,boolean,long
举例：
var1 char(15) --（名字+类型）
married boolean:=true -- ":="表示赋值
psal number(7,2)
DECLARE
--定义基本变量类型
--基本变量类型
pnumber NUMBER(7,2);
--字符串变量
pname VARCHAR2(20);
--日期变量
pdate DATE;
BEGIN
pnumber:=1;
pname:='TOM';
pdate:=SYSDATE;
DBMS_OUTPUT.put_line(pnumber);
END;
/
引用型变量(单行)my_name emp.ename%type
- 案例
declare
--定义引用型变量
--pename varchar2(20)
--psal number(20)
pename emp.ename%type;
psal emp.sal%type;
begin
--得到7782的姓名和薪水
select ename,sal into pename,psal from emp
where empno=7839;
--打印
dbms_output.put_line(pename);
dbms_output.put_line(psal);
end;
/
记录型变量(多行) emp_rec emp%rowtype
记录型变量分量的赋值，emp_rec.ename:='ADAMS'；
declare
--定义记录型变量
emp_rec emp%rowtype;
begin
--7839
select * into emp_rec from emp where empno=7839;
--打印
dbms_output.put_line(emp_rec.ename||'的薪水是'||emp_rec.sal);
end;
/
面向过程语句
IF语句
- IF 条件 then 语句1；语句2；end if;
- IF 条件 then 语句序列1；else 语句序列2；end if;
- IF 条件 then 语句序列1；elsif 语句 then 语句；else 语句end if;
案例
案例：判断用户从键盘输入的数字
1.如何使用if 语句
2.接收一个键盘输入(都是字符串)
注：
在PLSQL中出现ORA_0131错误是：grant debug connect session to scott;
--打开输入开关
set serveroutput on
--接受键盘输入
--num是一个地址值，含义是在该地址上保存了输入的值
--prompt表示提示
accept num prompt'请输入一个数字';
declare
-- 定义变量保存用户从键盘输入的数字
-- 地址
pnum number:= #
BEGIN
--执行if语句
dbms_output.put_line(pnum);
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;
/
循环语句
语法：while total <=25000 LOOP 循环体 end LOOP;
案例：
--使用while循环打印数字1~10
declare
pnum number:=1;
begin
while pnum<=10 loop
dbms_output.put_line(pnum);
--使变量+1
pnum:=pnum+1;
end loop;
end;
/
LOOP循环
语法：LOOP exit [when 条件]； .... End LOOP;
案例
--使用loop循环
declare
pnum number:=1;
begin
loop
--退出条件：循环变量大于10
exit when pnum>10;
--打印该变量
dbms_output.put_line(pnum);
--循环变量+1
pnum:=pnum+1;
end loop;
end;
/
For循环
语法：..表示连续区间 For I in 1..3 LOOP end LOOP;
案例：
--使用for循环打印1-10
declare
--定义循环变量
pnum number:=1;
begin
for pnum in 1..10 loop
dbms_output.put_line(pnum);
end loop;
end;
/
推荐使用第二种循环(LOOP)，便于控制光标！！！
光标
光标(游标)：产生一个结果集(result set)
基础语法
语法cursor 光标名【（参数名 数据类型）】(可以带参数，也可以不带参数) Is select 语句；
简单案例：
cursor c1 is select ename from emp;(集合)
--打开光标
-open c1;（打开光标执行查询）
-close c1;(关闭游标释放资源)
fetch c1 into pename；(取一行到变量中)
***案例：使用光标查询员工姓名和工资，并打印
光标的属性
| 标记 | 表示 |
|---|---|
| %found | fetch可以取到为True,否则False; |
| %notfound | 与上面刚好相反 |
| %isopen; | 判断光标是否打开 |
| %rowcount; | 影响的行数 |
光标数的限制
默认情dao况下只允许在同一个会话中，打开300个光标
--切换到管理员
show parameter cursor
--修改光标数
alter system set open_cursors=400 scope=both;
scope取值：both、memory(值更改实例)，spflie(只更改当前参数文件)
案例
--查询打印员工姓名和工资
set serveroutput on
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;
--思考：1循环什么时候退出？2.fetch语句不一定能取到？没有取到记录
exit when cemp%notfound;
--打印
dbms_output.put_line(pename||'--------------的薪水是------------>>>'||psal);
end loop;
--关闭光标
close cemp;
end;
/
案例：给员工涨工资
--给员工涨工资，总裁1000，经理800，其它400
set serveroutput on
declare
--定义光标代表哪些员工涨工资
cursor cemp is select empno,job from emp;
pempno emp.empno%type;
pjob emp.job%type;
begin
rollback;
--打开光标
open cemp;
loop
--取出一个员工
fetch cemp into pempno,pjob;
exit when cemp%notfound;
--判断员工职位
if pjob='PRESIDENT' then update emp set sal=sal+1000 where empno=pempno;
elsif pjob='MANAGER' then update emp set sal=sal+800 where empno=pempno;
else update emp set sal=sal+400 where empno=pempno;
end if;
end loop;
close cemp;
--对于oracle,默认的事务隔离级别是read committed
--事务的ACID,性质：原子性、一致性、隔离性、持久性
--提交
commit;
dbms_output.put_line('涨工资完成!');
end;
/
--再次查询
select ename,sal from emp;
案例：判断光标是否打开
declare
--定义光表
cursor cemp is select empno,job from emp;
pempno emp.empno%type;
pjob emp.job%type;
begin
--打开光标
open cemp;
if cemp%isopen then
dbms_output.put_line('光标已经打开');
else
dbms_output.put_line('光标没有打开');
end if;
close cemp;
end;
/
案例：光标的属性%rowcount
declare
--定义光标代表哪些员工涨工资
cursor cemp is select empno,job from emp;
pempno emp.empno%type;
pjob emp.job%type;
begin
--打开光标
open cemp;
loop
--取出一行记录
fetch cemp into pempno,pjob;
exit when cemp%notfound;
--打印
dbms_output.put_line('rowcount'||cemp%rowcount);
end loop;
close cemp;
end;
/
带参数的光标
--查询某一个部门中员工的姓名
declare
--定义带参数的光标
cursor cemp(dno number) is select ename from emp where deptno=dno;
pename emp.ename%type;
begin
--打开光标
open cemp(20);
loop
--取员工姓名
fetch cemp into pename;
exit when cemp%notfound;
dbms_output.put_line(pename);
end loop;
close cemp;
end;
/
例外
定义：程序设计语言提供的一种功能，用来增强程序的容错性。
系统例外
| 符号 | 描述 |
|---|---|
| No_data_found | 没有找到数据 |
| Too_many_rows | select...into语句匹配到多个行 |
| Zero_Divide | 被零除 |
| Value_error | 算术或转换错误 |
| Timeout_on_resource | 在等待资源时发生超时 |
案例：No_data_found
declare
pename emp.ename%type;
begin
--查询员工号为123456的员工姓名
select ename into pename from emp where empno=123456;
exception
when no_data_found then dbms_output.put_line('没有找到该员工');
when others then dbms_output.put_line('其它例外');
end;
/
案例：too_many_rows
set serveroutput on
declare
--定义变量
pename emp.ename%type;
begin
--查询所有10号部门的员工姓名
select ename into pename from emp where deptno=10;
exception
when too_many_rows then dbms_output.put_line('select into 匹配了多个行');
when others then dbms_output.put_line('其它问题');
end;
/
案例：zero_divide
declare
--定义一个变量
pnum number;
begin
pnum:=1/0;
exception
when zero_divide then dbms_output.put_line('1被零除');
dbms_output.put_line('2被零除');
when others then dbms_output.put_line('其它问题');
end;
/
案例：value_error
declare
--定义一个变量
pnum number;
begin
pnum:='abc';
exception
when value_error then dbms_output.put_line('算数或数字转换错误');
when others then dbms_output.put_line('其他例外');
end;
/
自定义例外
定义变量。类型是exception，使用raise抛出自定义例外
自定义例外；查询50号部门的员工姓名
declare
--定义光标
cursor cemp is select ename from emp where deptno=50;
pename emp.ename%type;
--自定义例外
no_emp_found exception;
begin
open cemp;
--直接去一个员工的姓名
fetch cemp into pename;
if cemp%notfound then
--抛出意外
raise no_emp_found;
end if;
--关闭光标
--oracle 自动启动pmon(process monitor )
close cemp;
exception
when no_emp_found then dbms_output.put_line('没有找到员工');
when others then dbms_output.put('其它例外');
end;
/
程序设计
瀑布模型需求分析-->设计(概要、详细)-->编码-->测试-->测试
SQL语句
变量：
初始值；如何得到
案例：统计每一年入职的员工人数
/*
select to_char(hiredate,'yyyy') from emp;
-->光标-->循环--》退出条件：notfound
count80 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;
/
案例：为员工涨工资
案例：为员工涨工资。从最低工资涨起每人涨10%，但工资
总额不能超过5万元，请计算装工资的人数和涨工资后的工资
总额，并输出涨工资的人数及工资总额。
/*
分析：
select empno，sal from emp order by sal ; 排序
从工资最低的涨起
-->光标-->循环-->退出条件：1.工资总额>5W;2.%notfound
变量：
1.初始值；2.如何得到
涨工资的人数
countEmp number:=0
涨后的工资总额
1.salTotal number;(select sum(sal) from emp)
2.涨后的工资总额=涨前的工资总额+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;
begin
--得到工资总额的初始值
select sum(sal) into salTotal from emp;
--打开光标
open cemp;
loop
--1.工资总额大于50000
exit when salTotal>50000;
fetch cemp into pempno,psal;
exit when cemp%notfound;
--取一个员工的涨工资
--涨工资
update emp set sal=psal*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('涨工资的人数'||countEmp||'涨后的工资总额：'||salTotal);
end;
/
分部门统计工资
案例3：
实现按部门分段(6000以上、(6000,3000)，3000元以下)统计各工资段的员工人数；以及各部门的工资总额(不包括奖金)
--创建空表
create table msg(
deptno number，
count1 number，
count2 number，
count3 number，
saltotal number
);
/*
分析：
SQL语句：
有哪些部门？
select deptno from dept;-->光标-->循环-->推出条件;notfpound
2.
统计部门中员工的薪水
select sal from where deptno=? --》光标(参数)--循环--退出条件：1
变量：
1.初始值；2 如何得到？
每个段的员工人数
count1 number;
count number;
count3 number;
每个部门的工资总额：
salTotal number;
1 . select sum(sal) into saltotal from emp where deptno=???;
2.累加 #第2种的效果要好于第1种
*/
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;
--得到部门的工资总和
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;
--保存当前部门的结果
--nvl 的作用是当salTotal为空时，赋值为0
insert into msg values(pdeptno,count1,count2,count3,nvl(salTotal,0));
end loop;
--提交
dbms_output.put_line('统计完成！');
close cdept;
end;
/
