plsql : procedure language sql 是Oracle特有的数据库编程语言,普通的sql可以实现对数据的增删改查,一条sql很难做到 即查询,又根据不同的条件筛选数据,修改数据,而PLSQL可以帮我们实现
基本语法#
分成三部分:
- 声明部分 (声明一些临时变量,后续使用)
- 可执行部分 (我们的业务逻辑)
- 异常处理部分(可执行部分出现的异常信息,并处理)
declare -- 声明部分 自定义的变量名 变量类型; -- 变量可以边声明变初始化 v_name varchar(2) :='zhangsan'; begin -- 执行业务逻辑 exception -- 异常处理部分 end;
Oracle 常用的 变量类型#
- 定义数据表时使用的变量类型(varchar2 number) 都能用
- 列级变量-- 表名.列名%type
- 行级变量-- 表名%rowtype
PLSQL的 分支 选择 判断#
分支判断#
if 条件1 then -- 处理逻辑 elsif 条件2 then -- 处理逻辑 else -- 处理逻辑 end if;
while循环:#
while 条件 loop -- 处理逻辑 end loop;
例: 输出1-10
declare i number:=1; begin while i<=10 loop dbms_output.put_line(i); i:= i+1; end loop; end;
while 循环,不满足条件自动退出
for 循环#
for 自定义的变量X in [reverse反转终止值和初始值] 初始值..终止值 loop -- 业务逻辑 end loop;
输出10 - 1:
declare begin for i in reverse 1 ..10 loop -- 起始的条件不需要声明 dbms_output.put_line(i); -- i:=i+1; 循环遍历的下标 不能作为 赋值目标 end loop; end;
for循环中循环遍历的下标不能作为赋值的对象
for循环中,自定义的变量X 不用提前在 declare和begin之间声明
for循环的退出条件是 自定义的变量X 从起始值到终止值为止
loop 循环#
loop -- 业务逻辑 exit when 退出条件 end loop;
输出 1-10
declare i number := 1; begin loop i := i+1; dbms_output.put_line(i); exit when i=10; end loop; end;
loop退出循环的条件是 exit when 我们手动控制
- 例: 查询7369的工资, 并且打印出来
-- 知道什么是 引用类型(单列的值) 表名.列名%type
-- 如何赋值 into
declare sval emp.sal%type; begin select sal into sval from EMP where EMPNO=7369; -- 打印 dbms_output.put_line(sval); end;
- 例 查询7369 的员工信息, 并且打印出来
-- 关键是 找个变量把结果存起来 行级变量 %rowtype
--sql select * from EMP where EMPNO=7369; declare vrow emp%rowtype; -- 行级变量 表名%rowtype begin select * into vrow from EMP where EMPNO=7369; -- dbms_output.put_line(vrow); 不能直接打印行!!! 而是 行.列 打印出来 dbms_output.put_line('员工名称'||vrow.ENAME||'员工编号'||vrow.EMPNO); end;
注意点: -- dbms_output.put_line(vrow); 不能直接打印行!!! 而是 行.列 打印出来
游标#
什么是游标? 能给我们带来什么?#
我们可以把游标理解成一个指针,或者一个箭头,他可以指向我们给定的结果集(这个结果集其实就是我们的sql select的查询结果),然后我们可以用plsql的代码,控制游标遍历这个结果集,在遍历的时候我们可以取出当前行,也能取出当前行的具体一列的值,然后我们根据取出的值进行判断,下一步是该 update表数据,还是delete表数据
最开始当游标指向这个结果集的时候,它指向的是结果集的表头部分,每当我们fetch一下,他就把下一行的数据(也就是第一行)给我们,再fetch 就给我们第二行
游标的分类#
- 静态游标
- 隐式游标
- 没参数的,显示游标
- 动态游标
- 带参数的显示游标（ref）
- 系统游标
静态游标和动态游标的最大区别是啥？#
- 静态游标在编译的时候就知道了结果集是什么, 因为它没有任何需要我们传递进去的参数
- 动态游标带参数,参数具体是几,只有真正运行的时候才知道,它的结果集是运行时才知道的,称它是动态游标
语法#
cursor 游标名 in 查询结果集 -- 不带参数 cursor 游标名[参数名, 参数类型] in 查询结果集 -- 带参数
隐式游标的使用:#
begin update t_student set sname='张三丰' where sid='10001'; if sql%found then dbms_output.put_line('表已经更新'); end if; end;
每条sql默认自带一个隐式游标,游标的名字是 sql, 自动打开/关闭
没参数的静态游标#
开发步骤; 1. 声明游标 -- cursor 游标名 is 查询结果集 2. 打开游标 -- open 游标名 3. 取出游标中的数据( 游标相当于鼠标,每次都是从表头开始的, fetch一下,往下移动一行,正好从第一行开始) -- fetch 游标名 into 变量 -- 游标名%find 找到数据 -- 游标名%notfound 没有找到数据 4. 关闭游标 -- close 游标名`
- 例 输出员工表中的员工姓名和工资(不带参数游标)
-- select ename,SAL from emp ; -- 游标 -- 结果集: 所有员工 -- 声明一个变量,用来记录一行数据 %rowtype declare -- 声明游标 cursor vrows is select * from emp ; -- 声明一个中间标量, 中转上面游标中获取的每一行 vrow emp%rowtype; begin -- 开启游标 open vrows; -- 遍历打印 loop fetch vrows into vrow; -- 取一条数据 exit when vrows%notfound; -- 判断数据是否为空 --- 再次强调一下,不能直接打印行 -- dbms_output.out_line(vrow); --- dbms_output.put_line('姓名'||vrow.ename||'员工号'||vrow.EMPNO); -- 这里输出的 内容,一定要存在于 上面的游标中 end loop; -- 关闭游标 close vrows; end;
如果没有判断下一次获取的是否为空,当游标循环遍历到结果集的最后一行的时候,再次fetch,依然是最后一行的数据,还可能是内存溢出,所以判断条件紧跟在fetch之后,然后再是我的业务逻辑
系统游标#
--- 系统 引用游标
1. 声明游标 -- 游标名 游标类型 sys_refcursor (后面的 refcursor是个合成词) 2. 打开游标,关联结果集 3. 从游标中取数据 4. 关闭游标
例:
-- 例 输出员工表中的员工姓名和工资(不带参数游标)
declare -- 声明系统游标 vrows sys_refcursor; -- 声明行级变量 vrow emp%rowtype; begin -- 打开游标 open vrows for select * from EMP ; -- 系统游标在打开时,关联结果集 -- 循环 loop fetch vrows into vrow; -- 获取值 exit when vrows%notfound; dbms_output.put_line('姓名'||vrow.ename); -- 结束循环 end loop; -- 关闭游标 close vrows; end;
带参数的动态游标#
--- 输出指定部门下的员工的姓名和工资( 带参数的游标 )
/* 游标: (操作的结果集) 指定部门的所有员工 声明 一个变量记录一行数据 */ declare -- 声明带有参数的游标 cursor vrows(dno number) is select * from EMP where DEPTNO = dno; -- 声明 行级 中转变量 vrow emp%rowtype; begin -- 打开游标 (传入参数) open vrows(10); -- 循环 loop -- 取出数据 fetch vrows into vrow ; -- 判断 结束循环 exit when vrows%notfound; -- 输出结果 dbms_output.put_line('姓名'||vrow.ENAME||'工资'||vrow.sal); end loop; -- 关闭游标 close vrows; end;
系统游标 和 不带参的静态游标#
- 相同点: 都是指向了某个结果集,可以一条一条的遍历结果集,获取出每一条数据
- 不同点: 获取结果集的位置不同
- 普通游标获取操作集合的位置是 declare 和 begin 之间
- 系统游标 在begin 和 end 之间 的打开游标位置 ,使用的关键字不再是 is 而是 for
- 声明方式不同!
- 普通游标可带参数
- 系统游标的声明固定写法; 名 sys_refcursor;
for 循环遍历游标#
上面的例子都是使用loop循环遍历游标,控制退出的条件是 游标%notfound
下面使用for循环遍历游标
declare cursor v_rows is select * from emp; begin for v_row in v_rows loop dbms_output.put_line(v_row.JOB); end loop; end;
不用提前声明 需要中转每一行的变量
不用手动控制循环退出的条件
小案例#
- 游标的小案例:
-- .1 按照员工工作 给员工涨工资, 总裁涨1000 经理涨 800 其他人 400
/* -- 查询经理信息 select mgr from emp where mgr is not null; -- 不查询不为空的员工信息 */ declare -- 声明游标 cursor vrows is select * from emp; -- 声明变量 vrow emp%rowtype; begin -- 打开游标 open vrows; -- 遍历游标 loop -- 获取游标的当前值 fetch vrows into vrow; -- 判断是否为空 exit when vrows%notfound; -- 不为空,就加工资 if vrow.job = 'MANAGER' then -- 注意点: set的字段是 不能是 中专变量名.列名 而直接是类名 , 因为我们是在更新表中的字段,而不是在更新中转的那个变量 update emp set sal = sal+400 where empno = vrow.EMPNO; -- plsql里面的 等于 := 和 sql里面的 等于 = 区分开 elsif vrow.job = 'CLERK' then update emp set sal = sal+800 where empno = vrow.EMPNO; end if; end loop; close vrows; end;
注意点 : plsql和sql混在一起写的时候,注意点就是 sql中的赋值运算符是 = 但是plsql中的赋值运算符是 := 别整混了
异常#
有啥用?#
通常异常用来处理我们在业务逻辑阶段可能出现的逻辑错误,我们捕获了相应的异常之后自己处理掉,不至于影响后续的操作
语法#
declare -- 声明异常 begin -- 业务逻辑 exception -- 声明下面是处理异常的部分 -- 处理异常 when 异常1 then -- 处理逻辑 when 异常2 then -- 处理逻辑 end;
Oracle中异常的分类#
- 系统异常
- zero_divide : 除零异常
- value_error : 类型转换异常
- too_many_row: 查询查了多行的记录,但是却把它赋值给一个行级的变量
- no_data_found: 没有找到记录
- raise_application_error(状态码,'异常信息') : 运行时,系统异常
- 用户自定义异常
用户自定义异常,分三步
- 声明异常
- raise 抛出异常
- 在exception后面捕获异常
- 我们操作的结果集只能来自游标的结果集,(下面解释)
坑#
- 看下面的代码,查询一个不存在的人的信息,看着貌似会捕获处理我们自定义的异常,但是其实并不会! 因为执行到 select的时候就会出现异常 no_data_found, 直接停止往下执行,我们的逻辑根本不会触发
declare vrow emp%rowtype; -- 声明行级变量 no_emp exception; -- 自定义异常 begin select * into vrow from emp where EMPNO = 8888; -- 如果这是 888 没有这个员工,执行到这句就会 抛出异常,而不会往下执行抛出 自定义的异常 if vrow.SAL is not null then -- vrow一直是有的,我们通过里面的一个字段判断他是否为空 dbms_output.put_line('员工信息'||vrow.sal); -- 注意这里的,依然不能使用 dbms 输出一整行 else raise no_emp; -- 抛出自定义的异常 (自己抛出,自己捕获) end if; exception when no_emp then dbms_output.put_line('没有该员工信息'); when others then dbms_output.put_line('其他异常'||sqlerrm); -- sqlerrm 输出其他异常 end;
- 简单的select 查询没办法完成我们的需求, 因为它碰到异常会报错,但是游标不会!!! %found %notfound
- 解决
- 声明 行级变量
- 声明游标(带参数), 获取要操作的结果集
- 遍历游标, 判断,如果为空,抛出我们自定义的异常
declare vrow emp%rowtype; -- 声明行级变量 --- 注意点: 游标的查询语句中去 * 再赋值给行级变量 (不然报错)) cursor emp_Info(empno NUMBER) is select * from EMP; -- 声明游标,并获取结果集 no_emp exception ; -- 自定义异常 begin -- 打开=游标 传入参数 open emp_Info(1); loop fetch emp_Info into vrow; -- 获取元素 if emp_Info%notfound then -- 如果找不到 raise no_emp; -- ,退出循环 exit ; -- 退出循环 end if; end loop; -- 关闭游标 close emp_Info; -- 捕获异常 exception when no_emp then dbms_output.put_line('没找到信息'); when others then dbms_output.put_line('其他异常'||sqlerrm); end;
用户自定义异常的例子#
declare begin end;
捕获系统异常的例子:#
declare vi number; vrow emp%rowtype; -- 行级变量 begin vi:=vi/0; -- 除零异常 vi:='abc'; -- 类型转换异常 select * into vrow from EMP; -- 多行数据赋给 行级变量 select * into vrow from DEPT where DEPTNO=123; -- 没找到异常信息异常 exception when zero_divide then dbms_output.put_line('发生了除零异常'); when value_error then dnms_output.put_line('类型转换异常'); when to_many_rows then dbms_output.putline('多对 一 异常'); when no_data_found then dbms_output.put_line('没找到数据'); when others then dbms_output.put_line('发生了其他异常'); end;
