pl/sql异常处理和事务

异常处理

21个预定义异常

异常 错误 何时出现
ACCESS_INTO_NULL ORA-06530 视图访问未初始化对象的时候出现
CASE_NOT_FOUND ORA-06592 如果定义了一个没有ELSE子句的CASE语句，而且没有CASE语句满足运行时条件是出现该异常
CIKKECTION_IS_NULL ORA-06531 当程序去访问一个没有进行初始化的NESRED RABLE或者是VARRAY的时候，会出现该异常
COLLECTION_ALREADY_OPEN ORA-06511 游标已经被OPEN，如果再次尝试打开该游标的时候，会出现该异常
DUP_VAL_ON_INDEX ORA-00001 如果插入一列被唯一索引约束的重复值的时候，就会引发该异常（该值被INDEX认定为冲突的）
INVALID_CURSOR ORA-01001 不允许的游标操作，比如关闭一个游标
INVALID_NUMBER ORA-01722 给数字值赋非数字值的时候，该异常就会发生，这个异常也会发生在批读取时候LIMIT子句返回非正数的时候
LOGIN_DENIED LOGIN_DENIED 程序中，使用错误的用户名和密码登录的时候，就会抛出这个异常
NO_DATA_FOUND ORA_06548 在使用SELECT INTO 结构，并且语句返回NULL值的时候；访问嵌套表中 已经删除的表或者是访问INDEX BY表（联合数组）中的未初始化元素就会出现该异常
NOT_LOGGED_ON ORA-01012 当程序发出数据库调用，但是没有连接的时候（通常，在实际与会话断开连接之后）
PROGRAM_ERROR ORA-06501 当Oracle还未正式捕获的错误发生时常会发生，这是因为数据库大量的 Object功能而发生
ROWTYPE_MISMATCH ORA-06504 如果游标结构不适合PL/SQL游标变量或者是实际的游标参数不同于游标形 参的时候发生该异常
SELF_IS_NULL ORA-30625 调用一个对象类型非静态成员方法（其中没有初始化对象类型实例）的时候 发生该异常
STORAGE_ERROR ORA-06500 当内存不够分配SGA的足够配额或者是被破坏的时候，引发该异常
SUBSCRIPT_BEYOND_COUNT ORA-06533 当分配给NESTED TABLE或者VARRAY的空间小于使用的下标的时候，发生 该异常（类似于java的ArrayIndexOutOfBoundsException）
SUBSCRIPT_OUTSIDE_LIMIT ORA-06532 使用非法的索引值来访问NESTED TABLE或者VARRAY的时候引发
TIMEOUT_ON_RESOURCE ORA-00051 当数据库不能安全锁定资源的时候引发
TOO_MANY_ROWS ORA-01422 常见错误，在使用SELECT INTO 并且查询返回多个行时引发。如果子查询 返回多行，而比较运算符为相等的时候也会引发该异常。
USERENV_COMMITSCN_ERROR ORA-01725 只可使用函数USERENV(‘COMMITSCN’)作为INSERT语句的VALUES子句中 的顶级表达式或者作为UPDATE语句的SET子句中的右操作数
VALUE_ERROR ORA-06502 将一个变量赋给另一个不能容纳该变量的变量时引发
ZERO_DIVIDE ORA-01476 将某个数字除以0的时候，会发生该异常

declare 
begin
   exception
     when 异常名称 then 
     输出 
end;

自定义异常

根据业务规则，编程需要可以自定义异常

1.定义异常类型的变量

2.抛出异常raise异常名字

3.处理异常

declare 
   --异常类型的变量
   myexp exception;
begin
   delete from emp where empno=7369;
   -- sql:前面执行的一个sql语句 ：%notfound：没有找到数据
   if sql%notfound then
     raise myexp; --抛出异常
     
   end if;
   exception
     when myexp then 
       dbms_output.put_line('自定义异常');
       
   end;

事务

事务：确保一系列数据操作（多条sql命令）作为整体执行，要么都执行（commit），要么都不执行（rollback）

delete 
update 
insert ... 
delete

事务的四大特点（ACID）

A 原子性（Atomic）：一些了操作作为整体执行，要么都执行，要么都回滚

C 一致性（Consistency）：事务完成前、后，数据要保持一致性的状态

I 隔离性（ISOLATION）：多个事务在并发执行时，事务之间是相互隔离的；一个事务无法去操作另一个事务正在操作（未提交）的数据

D 持久性：（Durability）事务一旦完成后（提交，回滚），对数据库的影响都是永久性的

隔离级别不同会引发不同的问题

脏读：A事务读取了B事务正在修改单位提交的数据。读了脏数据

转账：

B事务在转账 (未提交，回滚)

A事务在读取数据

不可重复读：A事务正在读取数据，读取了第一次的结果；B事务进来修改了部分数据；A事务再次读取 数据，读取了第二次的结果。结果不一样。

幻读：A事务正在读取数据，读取了第一次的结果；B事务执行录入操作；A事务再次读取数据，读取了 第二次的结果。结果不一样。

设置不同的隔离级别解决1上述问题

1.未提交读：Read_uncommitted ：引发脏读

2.已提交读：Read_Committed；解决脏读（oracle默认的隔离级别）

3.重复读：Repeatable；可以解决脏读，不可重复读（mysql的默认隔离级别）

4.串行化：Serializable：可以解决脏读，不可重复读。幻读

oracle中的事物，默认是在自动开启的，需要显示提交或回滚

--属于事物的一部分
insert into dept(deptno,dname,loc) values(82,'研发部','郑州');
insert into dept(deptno,dname,loc) values(83,'学术部','郑州');
insert into dept(deptno,dname,loc) values(84,'客服部','郑州');
commit;

sql server中 begin tran：需要显示的开启事务

begin tran 
insert
update
delete 
commit /rollback;

事务的操作命令

commit ：提交
rollback ：回滚
rollback to  回滚到指定的点
savepoint 名称：设置保存点

事务控制语句

设置事务的自动提交方式

set autocommit on|off|immediate|n

事务演示

create table bank(
   id int,
   name varchar(20), --名字
   balance number(20),--余额
   constraint ck_balance check(balance>1)  --余额不能小于1
)

没有进行事务控制是转账超过10000会出现问题

使用事务进项控制后的效果

begin
   update bank set balance=balance-5000 where id=120;
   update bank set balance=balance+5000 where id=121;
   -- 余额不能小于1扣除不掉回滚  
   -- 不发生就提交事务 
   commit;
   exception
     -- 发生异常就回滚事务
     when others then 
       dbms_output.put_line('事务回滚');
       rollback;
end;

还可以才事务操作过程中添加保存点

declare
     n number(2);
begin 
     insert into dept(deptno,dname) values(91,'一部');
     savepoint a; --设置保存点（记号）
     insert into dept(deptno,dname) values(92,'二部');
     savepoint b;
     insert into dept(deptno,dname) values(93,'三部');
     savepoint c;
     n:=#
     if n=1 then 
       rollback to a;-- 回滚到指定的保存点
     elsif n=2 then 
       rollback to b;
     elsif n=3 then
       rollback to c;
     end if;
     commit; --提交事务
end;

事务锁

分类

DDL锁：执行（create，alter，drop）操作时，系统自动枷锁；执行create，alter，drop命令时，自动提交事务所

DML锁：执行（insert，update，delete）操作是，自动枷锁

数据库锁：执行系统及的事件是（登录、登出、加载）

DML锁

排他锁（Exclusive lock）：当事务正在执行操作是，其他事务无法进行数据的读和修改操作

共享锁：（Share lock） 当事务正在执行操作时，其他事务可以进行读取操作，无法执行修改操作

死锁：指两个事务或者多个事务在同一资源上相互占用，并请求对方所占用的资源，从而造成恶性循环的现象。