PL/SQL
PL/SQL(Procedure Language/SQL)是 Oracle 对 sql 语言的过程化扩展,指在 SQL 命令语言中增加了过程处理语句(如分支、循环等),使 SQL 语言具有过程处理能力。把 SQL 语言的数据操纵能力与过程语言的数据处理能力结合起来,使得 PLSQL 面向过程但比过程语言简单、高效、灵活和实用。
基本语法
[declare
--声明变量
]
begin
--代码逻辑
[exception
--异常处理
]
end;
变量
- 声明变量
变量名 类型(长度);
- 变量赋值
-- 方式一:直接赋值
变量名:=变量值;
-- 方式二:select ··· into ··· 赋值
select 列名1,··· into 变量名1,··· from 表名 where 条件
属性类型
- 引用型 %TYPE:引用某表某列的字段类型
- 记录型 %ROWTYPE:标识某个表的行记录类型
异常
- 预定义异常 - 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发
- 用户定义异常 - 用户可以在 PL/SQL 块的声明部分定义异常,自定义的异常通过 RAISE 语句显式引发
exception
when 异常类型 then
异常处理逻辑
条件判断
if 条件 then
业务逻辑
elsif 条件 then
业务逻辑
else
业务逻辑
end if;
循环
loop
--循环语句
end loop;
while 条件
loop
-- 循环语句
end loop;
for 变量 in 起始值..终止值
loop
-- 循环语句
end loop;
游标
游标是系统为用户开设的一个数据缓冲区,存放 SQL 语句的执行结果。 我们可以把游标理解为 PL/SQL 中的结果集。
- 游标的使用
cursor 游标名称 is SQL 语句;
open 游标名称
loop
fetch 游标名称 into 变量
exit when 游标名称%notfound
-- 循环语句
end loop;
close 游标名称;
- 带参数的游标
cursor 游标名称(变量名 类型(长度)) is SQL 语句(变量名);
- for 循环提取游标值
declare cursor ······; --定义游标
begin
for 变量 in 游标名(参数)
loop
-- 循环语句
end loop;
end ;
存储函数
存储函数又称为自定义函数。可以接收一个或多个参数,返回一个结果。在函数中我们可以使用 P/SQL 进行逻辑的处理。
创建
CREATE [ OR REPLACE ] FUNCTION 函数名称
(参数名称 参数类型, 参数名称 参数类型, ...)
RETURN 结果变量数据类型
IS
变量声明部分;
BEGIN
逻辑部分;
RETURN 结果变量;
[EXCEPTION 异常处理部分]
END;
存储过程
存储过程是被命名的 PL/SQL 块,存储于数据库中,是数据库对象的一种。应用程序可以调用存储过程,执行相应的逻辑。
存储过程与存储函数都可以封装一定的业务逻辑并返回结果,存在区别如下:
- 存储函数中有返回值,且必须返回;而存储过程没有返回值,可以通过传出参数返回多个值。
- 存储函数可以在 select 语句中直接使用,而存储过程不能。过程多数是被应用程序所调用。
- 存储函数一般都是封装一个查询结果,而存储过程一般都封装一段事务代码。
创建
CREATE [ OR REPLACE ] PROCEDURE 存储过程名称
(参数名 类型, 参数名 类型, 参数名 类型 ······)
IS|AS
变量声明部分;
BEGIN
逻辑部分
[EXCEPTION 异常处理部分]
END;
注意:
参数只指定类型,不指定长度
过程参数的三种模式:
IN 传入参数(默认)
OUT 传出参数 ,主要用于返回程序运行结果
IN OUT 传入传出参数
使用
call 存储过程(参数 ······);
触发器
数据库触发器是一个与表相关联的、存储的 PL/SQL 程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时,Oracle 自动地执行触发器中定义的语句序列。
使用场景
- 数据确认
- 实施复杂的安全性检查
- 做审计,跟踪表上所做的数据操作等
- 数据的备份和同步
分类
- 前置触发器(BEFORE)
- 后置触发器(AFTER)
创建
CREATE [or REPLACE] TRIGGER 触发器名
BEFORE | AFTER
[DELETE ][[or] INSERT] [[or]UPDATE [OF 列名]]
ON 表名
[FOR EACH ROW ][WHEN(条件) ]
declare
……
begin
PLSQL 块
END ;
-- FOR EACH ROW 作用是标注此触发器是行级触发器,不是语句级触发器
Oracle 预定义异常
命名的系统异常 | 产生原因 |
---|---|
ACCESS_INTO_NULL | 未定义对象 |
CASE_NOT_FOUND | CASE 中若未包含相应的 WHEN ,并且没有设置 ELSE 时 |
COLLECTION_IS_NULL | 集合元素未初始化 |
CURSER_ALREADY_OPEN | 游标已经打开 |
DUP_VAL_ON_INDEX | 唯一索引对应的列上有重复的值 |
INVALID_CURSOR | 在不合法的游标上进行操作 |
INVALID_NUMBER | 内嵌的 SQL 语句不能将字符转换为数字 |
NO_DATA_FOUND | 使用 select into 未返回行 |
TOO_MANY_ROWS | 执行 select into 时,结果集超过一行 |
ZERO_DIVIDE | 除数为 0 |
SUBSCRIPT_BEYOND_COUNT | 元素下标超过嵌套表或 VARRAY 的最大值 |
SUBSCRIPT_OUTSIDE_LIMIT | 使用嵌套表或 VARRAY 时,将下标指定为负数 |
VALUE_ERROR | 赋值时,变量长度不足以容纳实际数据 |
LOGIN_DENIED | PL/SQL 应用程序连接到 oracle 数据库时,提供了不正确的用户名或密码 |
NOT_LOGGED_ON | PL/SQL 应用程序在没有连接 oralce 数据库的情况下访问数据 |
PROGRAM_ERROR | PL/SQL 内部问题,可能需要重装数据字典& pl./SQL 系统包 |
ROWTYPE_MISMATCH | 宿主游标变量与 PL/SQL 游标变量的返回类型不兼容 |
SELF_IS_NULL | 使用对象类型时,在 null 对象上调用对象方法 |
STORAGE_ERROR | 运行 PL/SQL 时,超出内存空间 |
SYS_INVALID_ID | 无效的 ROWID 字符串 |
TIMEOUT_ON_RESOURCE | Oracle 在等待资源时超时 |