Oracle 游标、异常、存储过程、触发器

1、游标

• 什么是游标

游标是一段私有的SQL工作区，也就是一段内存区域，用于暂时存放SQL语句影响到的数据

• 游标分类

分为两种类型：

• 隐性游标
  不用主动创建游标，PL/SQL中出现的SQL语句,oracle都会为其分配一段私有的内存空间,也就是游标区域所有的DML语句或PL/SQL SELECT语句都有隐式游标
• 显性游标
  显式游标由开发人员通过程序显式控制,用于从表中取出多行数据,并将多行数据一行一行的单独进行处理

两种游标具有相同的属性，可以使用游标属性取得SQL语句的相关信息

• 游标属性

显示游标与隐性游标共同的游标属性：

• %ROWCOUNT 受SQL影响的行数
• %FOUND Boolean值，是否还有数据
• %NOTFOUND Boolean值，是否已无数据
• %ISOPEN Boolean值，游标是否打开

怎么使用这些属性（ 显示游标与隐性游标使用方法和含义却不相同，但语法都是游标名作为前缀）。
隐式游标没有名称，所以在使用隐式游标的时候采取统一 的一个名称SQL。

• SQL%ROWCOUNT 受SQL影响的行数
• SQL%FOUND Boolean值，是否还有数据
• SQL%NOTFOUND Boolean值，是否已无数据
• SQL%ISOPEN 总为FALSE

显性游标专门用于从数据库中查询多条数据，显性游标首先将查询出的多行数据暂存在游标区域中,然后在PL/SQL中借助于循环语句手动的控制游标的多行操作，每次取出一条进行处理，直到取出游标中所有的数据为止
在PL/SQL中执行SELECT语句的特殊要求:1. SELECT语句只能返回一条记录:2.必须要搭配使用INTO

PL/SQL中循环游标的方法：

• LOOP
  EXIT WITH 游标名%NOTFOUND;
  END LOOP;
• WHILE (游标名%FOUND) LOOP
  END LOOP;
• FOR 变量名 IN 游标名 LOOP
  END LOOP;

2、异常处理

在PL/SQL中，BEGIN中的异常可以被EXCEPTION捕获，如果没有被捕获，则会在出现异常地方中断执行
异常分类：

• 预定义异常
  预定义异常就是Oracle中已经预先定义好名称的异常，在异常处理部分中，通过异常的名称来捕获，预定义的一些异常名称：
  NO DATA FOUND 没找到数据
  TOO_ MANY_ ROWS 数据太多
  INVALID_ CURSOR 失效的游标
  ZERO_ DIVIDE 除数为0
  DUP_ VAL_ON_INDEX 唯一索引中插入 重复值
  VALUE_ ERROR 赋值异常
• 非预定义异常
  非预定义异常,也是因为违反Oracle的规则, Oracle会产生报错信息(有缮误編号和错误信息),但是orac1e并没有为这类错误定义好异常名称，那么我们可以自己定义一种异常名称，并将这种异常名称和错误编号进行绑定
  取得异常名称 EXCEPTION;
  PRAGMA EXCEPYION_INIT(取的异常名称,错误编号);//绑定错误编号与异常名称
• 用户定义异常
  用户定义异常,我们操作的的时候，并没有违反oracle的规则,而是违反了用户定义的规则，由于没有违反orac1e的规则，0racle不会自已主动的产生报错信息，而要我们自己手动的提出一个异常
  在需要抛出异常的地方使用RAISE 异常名称；而这个异常名称是自己定义的EXCEPTION

--对于非预期的异常：在WHEN OTHERS THEN 中收集异常名称(SQLERRM)、错误编号(SQLCODE)写入数据库的日志表中
CREATE TABLE ERR_LOG(
	ID NUMBER(4) Primary key,
	CODE NUMBER(10),
	MESSAGE VARCHAR2(255),
	ERRTIME DATE
);

3、存储过程与函数

--存储过程是存储在数据库中提供所有用户程序调用的子程序，定义存储过程的关键字为PROCEDURE 。
CREATE OR REPLACE PROCEDURE NAME(参数列表,不带参数类型) IS
BEGIN
END;

--存储过程是存储在数据库中提供所有用户程序调用的子程序，定义存储过程的关键字为FUNCTION 。
CREATE OR REPLACE FUNCTION NAME(参数列表,带参数类型) IS
BEGIN
END;

存储过程与函数的区别：

• 存储过程用于完成某些特定的任何或工作；函数用于完成复杂的计算
• 存储过程作为一个独立的PL/SQL语句来执行（匿名）；函数不能独立执行;必须作为表达式的一部分来阅用
• 存储过程程序头部使用PROCEDURE声明；函数头部使用FUNCTION声明
• 存储过程的程序头不用描述返回类型；函数的程序头部必须描述返回值的类型
• 存储过程不需要RETURN语句；函数必须在程序执行主体中包含RETURN语句至少有-一个有效的RETUR语句
• 存储过程可以使用IN/OUTAIN OUT三种模式的参数；函数可以使用IN/OUT/IN OUT三种模式的参数(但是out/in out没有意义)
• 存储过程可以不返回值,也可以返回多个值,通过OUT/IN OUT类型参数来返回；函数必须返回一个值通过RETURN语句返回.并且返回的值要与声明部分一致,也可以通 过out类型的参数带出变量(但是通常情况下不会这么做)

4、触发器

触发器分类：

• DML触发器
  ✧ 包含DELETE、INSERT、UPDATE三种触发器
  ✧操作对象为表和视图
  ✧ 触发时机：表是before和after，视图有instead of
  ✧ 触发范围：行级触发(FOR EACH ROW)和表级触发
  ✧设置WHEN，就会满足WHEN后触发；不然直接触发
• 系统触发器
  ✧用户触发事件：CREATE, ALTER,或者DROP命令；登录或者退出数据库连接
  ✧系统触发事件：启动、关闭数据库；特殊错误发生
• 触发器作用：
  ✧安全性方面，确定用户的操作是否可以继续执行
  ✧产生对数据值修改的审计，将修改的信息记录下来，产生数据改动记录
  ✧提供更灵活的完整性校验规则，能根据复杂的规则校验数据
  ✧提供表数据的同步复制，使多个表的数据同步
  ✧事件日志记录，记录数据库的重要操作

5、总结

1. 查询结构：匿名块、触发器、存储过程、函数、包
2. 匿名块：declare声明
3. 存储过程：完成某些操作任务，类似于java中的方法；命令行中EXEC执行或者匿名块的begin中直接调用创建的存储过程名称
4.总结： 一个返回值，用存储函数，否则，一般用存储过程