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.总结: 一个返回值,用存储函数,否则,一般用存储过程