PL/SQL
什么是 PL/SQL
PL/SQL（Procedure Language/SQL）是 Oracle 对 sql 语言的过程化扩展，指在 SQL 命令语言中增加了过程处理语句（如分支、循环等），使 SQL 语言具有过程处理能力。把SQL语言的数据操纵能力与过程语言的数据处理能力结合起来，使得 PLSQL 面向过程但比过程语言简单、高效、灵活和实用。
基本语法结构
[declare
-- 声明变量
]
begin
-- 代码逻辑
[exception
-- 异常处理
]
end;入门案例
/*PL/SQL的快速入门
*/
declarea varchar2(32);
begina := 'hello,world';DBMS_OUTPUT_LINE("年龄"||a);
end;首次执行可以发现控制台并没有输出内容，此时需要将Datagrip的DBMSOUTPUT功能开启。
再次执行可以发现控制台已经输出内容了
变量
一个变量只不过是在程序中可以操纵的存储区域的名称。 PL/SQL中的每个变量都有一个指定的数据类型，它决定了变量内存的大小和布局.
PL/SQL变量的名称由可选的字母，数字，美元($)符号，下划线和数字符号组成，不能超过30个字符。 默认情况下，变量名不区分大小写。不能将保留的PL/SQL关键字用作变量名称。
声明变量的语法：
变量名 类型 （长度）[default 默认值]; 变量赋值的语法：
变量名:=变量值变量的声明案例
-- todo 目标: 掌握变量的用法
-- todo 1 声明变量水费单价 v_price、水费字数 v_usenum、吨数 v_usenum2、金额 v_money。
-- todo 2 对水费单价=2.24、字数=8012、进行赋值
-- todo 3 吨数根据水费字数换算，规则为水费字数除以1000，并且四舍五入，保留两位小数。
-- todo 4 计算金额，金额=单价*吨数。
-- todo 5 输出单价 、数量和金额。实现
declare-- todo 1 声明变量水费单价 v_price、水费字数 v_usenum、吨数 v_usenum2、金额 v_money。v_price number(10, 2);v_usenum number;v_usenum2 number(10, 2);v_money number(10, 2);
begin-- todo 2 对水费单价=2.24、字数=8012、进行赋值v_price := 2.24;v_usenum := 8012;-- todo 3 吨数根据水费字数换算，规则为水费字数除以1000，并且四舍五入，保留两位小数。v_usenum2 := round(v_usenum / 1000, 2);-- todo 4 计算金额，金额=单价*吨数。v_money := v_usenum2 * v_price;-- todo 5 输出单价 、数量和金额。DBMS_OUTPUT.PUT_LINE('单价: ' || v_price || ', 数量: ' || v_usenum2 || ', 金额: ' || v_money);
end;结果如下：
select into 方式 赋值
语法结构：
select 列名 into 变量名 from 表名 where 条件
select 列名1,列名2 into 变量1,变量2 变量名 from 表名 where 条件注意：结果必须是一条记录 ，有多条记录和没有记录都会报错
-- todo 目标: select into 方式 赋值
-- todo 需求: 计算 业主编号为 1, 2012年12月 应付水费.
-- todo 1 声明变量: v_price 单价, v_usenum 水费字数, v_num0 上月字数, v_num1 本月字数, v_usenum2 使用吨数, v_money 水费金额
-- todo 2 单价赋值=3.45
-- todo 3 使用 select into 给 水费字数, 上月字数, 本月字数 赋值
-- todo 4 求吨数
-- todo 5 计算应付金额 = 吨数 * 单价
-- todo 6 输出 单价 吨数 应付金额 上月字数 本月字数实现
declare-- todo 1 声明变量水费单价 v_price、水费字数 v_usenum、吨数 v_usenum2、金额 v_money。v_price number(10, 2);v_usenum number;v_usenum2 number(10, 2);v_money number(10, 2);
begin-- todo 2 对水费单价=2.24、字数=8012、进行赋值v_price := 2.24;v_usenum := 8012;-- todo 3 吨数根据水费字数换算，规则为水费字数除以1000，并且四舍五入，保留两位小数。v_usenum2 := round(v_usenum / 1000, 2);-- todo 4 计算金额，金额=单价*吨数。v_money := v_usenum2 * v_price;-- todo 5 输出单价 、数量和金额。DBMS_OUTPUT.PUT_LINE('单价: ' || v_price || ', 数量: ' || v_usenum2 || ', 金额: ' || v_money);
end;结果如下：
引用变量
语法
变量名 表名.列名%TYPE;作用：引用某表某列的字段类型, 跟指定列的类型保持一致.
-- todo 目标: 引用变量 改造上面的代码
-- todo 1 声明变量: v_price 单价, v_usenum 水费字数, v_num0 上月字数, v_num1 本月字数, v_usenum2 使用吨数, v_money 水费金额
-- todo 2 单价赋值=3.45
-- todo 3 使用 select into 给 水费字数, 上月字数, 本月字数 赋值
-- todo 4 求吨数
-- todo 5 计算应付金额 = 吨数 * 单价
-- todo 6 输出 单价 吨数 应付金额 上月字数 本月字数实现
declare-- todo 目标: select into 方式 赋值-- todo 1 声明变量: v_price 单价, v_usenum 水费字数, v_num0 上月字数, v_num1 本月字数, v_usenum2 使用吨数, v_money 水费金额v_price number(10, 2);v_usenum T_ACCOUNT.USENUM%type;v_num0 T_ACCOUNT.NUM0%type;v_num1 T_ACCOUNT.NUM1%type;v_usenum2 number(10, 2);v_money number(10, 2);
begin-- todo 2 单价赋值=3.45v_price := 3.45;-- todo 3 使用 select into 给 水费字数, 上月字数, 本月字数 赋值select USENUM, NUM0, NUM1 into v_usenum,v_num0,v_num1from WATERUSER.T_ACCOUNTwhere YEAR='2012' and month='01' and OWNERUUID=1;-- todo 4 求吨数v_usenum2 := round(v_usenum / 1000, 2);-- todo 5 计算应付金额 = 吨数 * 单价v_money := v_price * v_usenum2;-- todo 6 输出 单价 吨数 应付金额 上月字数 本月字数DBMS_OUTPUT.PUT_LINE('单价: ' || v_price || ', 吨数: ' || v_usenum2 || ', 应付金额: ' || v_money);
end;结果如下：
记录型变量(行变量)
定义语法
行变量名 表名%rowtype;作用: 标识某个表的行记录类型
使用记录的列值的语法
行变量名.列名-- todo 目标: 记录型变量
-- todo 1 声明变量: v_price 单价, v_account 行变量, v_usenum2 使用吨数, v_money 水费金额
-- todo 2 单价赋值=3.45
-- todo 3 使用 select into 给 行变量 赋值
-- todo 4 求吨数
-- todo 5 计算应付金额 = 吨数 * 单价
-- todo 6 输出 单价 吨数 应付金额 上月字数 本月字数实现
-- todo 目标: 记录型变量
declare-- todo 1 声明变量: v_price 单价, v_account 行变量, v_usenum2 使用吨数, v_money 水费金额v_price number(10, 2);v_account t_account%rowtype;v_usenum2 number(10, 2);v_money number(10, 2);
begin-- todo 2 单价赋值=3.45v_price := 3.45;-- todo 3 使用 select into 给 行变量 赋值select * into v_accountfrom WATERUSER.T_ACCOUNTwhere YEAR='2012' and month='01' and OWNERUUID=1;-- todo 4 求吨数v_usenum2 := round(v_account.usenum / 1000, 2);-- todo 5 计算应付金额 = 吨数 * 单价v_money := v_price * v_usenum2;-- todo 6 输出 单价 吨数 应付金额 上月字数 本月字数DBMS_OUTPUT.PUT_LINE('单价: ' || v_price || ', 吨数: ' || v_usenum2 || ', 应付金额: ' || v_money);
end;结果如下：
异常
默认 开启事务
执行 sql1(增删改)
执行 sql2(增删改)
.... ...
如果没有问题, 提交生效(commit);
如果出现问题, 可以使用官方提示异常, 也可以自定义异常.
在运行程序时出现的错误叫做异常
发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分
异常有两种类型：
预定义异常 - 当 PL/SQL 程序违反 Oracle 规则或超越系统限制时隐式引发
用户定义异常 - 用户可以在 PL/SQL 块的声明部分定义异常，自定义的异常通过 RAISE 语句显式引发
预定义异常
Oracle 预定义异常 21 个
| 命名的系统异常 | 产生原因 |
| 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 在等待资源时超时 |
语法结构：
exception
when 异常类型 then
异常处理逻辑根据上例中的代码，添加异常处理部分
-- todo 目标: 异常处理
-- todo 1 声明变量: v_price 单价, v_account 行变量, v_usenum2 使用吨数, v_money 水费金额
-- todo 2 单价赋值=3.45
-- todo 3 使用 select into 给 水费字数, 上月字数, 本月字数 赋值
-- todo 4 求吨数
-- todo 5 计算应付金额 = 吨数 * 单价
-- todo 6 输出 单价 吨数 应付金额 上月字数 本月字数
-- todo 7 处理异常
-- todo 7.1 处理 未找到数据异常 no_data_found
-- todo 7.2 处理 查询条件有误, 返回多条数据异常 too_many_rows实现
declare-- todo 1 声明变量: v_price 单价, v_account 行变量, v_usenum2 使用吨数, v_money 水费金额v_price number(10, 2);v_account t_account%rowtype;v_usenum2 number(10, 2);v_money number(10, 2);
begin-- todo 2 单价赋值=3.45v_price := 3.45;-- todo 3 使用 select into 给 水费字数, 上月字数, 本月字数 赋值select * into v_accountfrom WATERUSER.T_ACCOUNTwhere YEAR='2012' and month='01' and OWNERUUID=1; -- 正常
-- where YEAR='2012' and OWNERUUID=1; -- 模拟异常1: 数据多了
-- where YEAR='2030' and month='01' and OWNERUUID=1; --模拟异常2: 数据没找到-- todo 4 求吨数v_usenum2 := round(v_account.usenum / 1000, 2);-- todo 5 计算应付金额 = 吨数 * 单价v_money := v_price * v_usenum2;-- todo 6 输出 单价 吨数 应付金额 上月字数 本月字数DBMS_OUTPUT.PUT_LINE('单价: ' || v_price || ', 吨数: ' || v_usenum2 || ', 应付金额: ' || v_money);-- todo 7 处理异常exception-- todo 7.1 处理 未找到数据异常 no_data_foundwhen no_data_found thenDBMS_OUTPUT.PUT_LINE('没有找到对应数据, 请核实!');-- todo 7.2 处理 查询条件有误, 返回多条数据异常 too_many_rowswhen too_many_rows thenDBMS_OUTPUT.PUT_LINE('查询条件有误, 返回多条数据异常, 请核实!');
end;结果如下：
条件判断
基本语法 1
单分支 只有if 特点：一个条件一个分支 满足就执行 不满足就跳过。
if 条件 then业务逻辑
end if;基本语法 2
双分支 有if有else 特点：一个条件两个分支，满足走then后面的分支1 ，不满足走else的分支。
if 条件 then业务逻辑
else业务逻辑
end if;基本语法 3
多分支 if elsif….. else 特点：N个条件 N+1个分支
if 条件1 then业务逻辑
elsif 条件2 then业务逻辑
else业务逻辑
end if;业务：设置三个等级的水费 5 吨以下 2.45 元/吨、 5 吨到 10 吨部分 3.45 元/吨，
超过 10 吨部分 4.45，根据使用水费的量来计算阶梯水费。
-- todo 目标: 使用判断if计算阶梯水费
-- todo 业务: 设置三个等级的水费 5 吨以下 2.45 元/吨、 5 吨到 10 吨部分 3.45 元/吨，超过 10 吨部分 4.45，根据使用水费的量来计算阶梯水费。
-- todo 1 声明变量: v_price1 <=5吨单价, v_price2 (5,10]吨单价, v_price3 >10吨单价, v_account 行变量, v_usenum2 使用吨数, v_money 水费金额
-- todo 2 单价赋值: <5吨单价=2.45, [5,10)吨单价=3.45, >=10吨单价=4.45
-- todo 3 使用 select into 给 行变量 赋值
-- todo 4 求吨数
-- todo 5 计算阶梯水费
-- todo 6 输出 吨数 应付金额
-- todo 7 处理异常
-- todo 7.1 处理 未找到数据异常 no_data_found
-- todo 7.2 处理 查询条件有误, 返回多条数据异常 too_many_rows实现
declare-- todo 1 声明变量: v_price1 <=5吨单价, v_price2 (5,10]吨单价, v_price3 >10吨单价, v_account 行变量, v_usenum2 使用吨数, v_money 水费金额v_price1 number(10, 2);v_price2 number(10, 2);v_price3 number(10, 2);v_account T_ACCOUNT%rowtype;v_usenum2 number(10, 2);v_money number(10, 2);
begin-- todo 2 单价赋值: <5吨单价=2.45, [5,10)吨单价=3.45, >=10吨单价=4.45v_price1 := 2.45;v_price2 := 3.45;v_price3 := 4.45;-- todo 3 使用 select into 给 行变量 赋值select * into v_account from T_ACCOUNTwhere YEAR='2012' and MONTH='01' and OWNERUUID=1;-- todo 4 求吨数v_usenum2 := round(v_account.USENUM / 1000, 2);-- todo 5 计算阶梯水费if v_usenum2<=5 thenv_money := v_usenum2 * v_price1;elsif v_usenum2<=10 thenv_money := 5 * v_price1 + (10 - v_usenum2) * v_price2;elsev_money := 5 * v_price1 + (10 - 5) * v_price2 + (v_usenum2 - 10) * v_price3;end if;-- todo 6 输出 吨数 应付金额DBMS_OUTPUT.PUT_LINE('吨数: ' || v_usenum2 || ', 应付金额: ' || v_money);-- todo 7 处理异常-- todo 7.1 处理 未找到数据异常 no_data_found-- todo 7.2 处理 查询条件有误, 返回多条数据异常 too_many_rowsexceptionwhen no_data_found thenDBMS_OUTPUT.PUT_LINE('没有找到数据, 请核实!');when too_many_rows thenDBMS_OUTPUT.PUT_LINE('返回多条数据, 请核实!');
end;结果:
循环
无条件循环
语法结构
loop
--循环语句
--exit when 条件
end loop;-- todo 目标: 使用 loop 输出 1 ~ 100
-- todo 1 定义变量 v_num
-- todo 2 初始化变量 v_num等于1
-- todo 3 编写 loop 循环
-- todo 3.1 进入循环, 输出 v_num变量的值
-- todo 3.2 变量 v_num 加 1
-- todo 3.3 当 v_num>100时退出实现
declare-- todo 目标: 使用 loop 输出 1 ~ 100-- todo 1 定义变量 v_numv_num number;
begin-- todo 2 初始化变量 v_num等于1v_num := 1;-- todo 3 编写 loop 循环loop-- todo 3.1 进入循环, 输出 v_num变量的值DBMS_OUTPUT.PUT_LINE(v_num);-- todo 3.2 变量 v_num 加 1v_num := v_num + 1;-- todo 3.3 当 v_num>100时退出exit when v_num>100;end loop;
end;结果:
条件循环
语法结构
while 条件
loop
end loop;-- todo 目标: 使用 while 输出 1 ~ 100
-- todo 1 初始化变量 v_num等于1
-- todo 2 编写 while 循环, 指定继续执行条件 v_num<=100
-- todo 2.1 进入循环, 输出 v_num变量的值
-- todo 2.2 变量 v_num 加 1实现
declare-- todo 目标: 使用 while 输出 1 ~ 100-- todo 1 初始化变量 v_num等于1v_num number := 1;
begin-- todo 2 编写 while 循环, 指定继续执行条件 v_num<=100while v_num<=100 loop-- todo 2.1 进入循环, 输出 v_num变量的值DBMS_OUTPUT.PUT_LINE(v_num);-- todo 2.2 变量 v_num 加 1v_num := v_num + 1;end loop;
end;结果:
for循环
基本语法
for 变量 in 起始值..终止值
loop
end loop;目标: 使用 for 输出 1 ~ 100
beginfor v_num in 1 .. 100 loopdbms_output.put_line(v_num);end loop;
end;结果:
游标
什么是游标
游标是系统为用户开设的一个数据缓冲区,存放 SQL 语句的执行结果。
我们可以把游标理解为 PL/SQL 中的结果集。
这么说其实是不严谨的,准确来说游标（Cursor）是一种用于查询结果集的指针，它允许你逐行处理查询结果。
不带参数的游标
语法结构及示例
在声明区声明游标，语法如下：
cursor 游标名称 is SQL语句;使用游标语法
open 游标名称
loop
fetch 游标名称 into 变量
exit when 游标名称%notfound
end loop;
close 游标名称案例
-- todo 目标: 不带参数的游标
-- todo 需求：打印业主类型为 1 的价格表
-- todo 1 声明行变量 v_pricetable
-- todo 2 声明游标 cur_pricetable 保存业主类型为 1 的价格表
-- todo 3 打开游标
-- todo 5 loop 循环
-- todo 6 fetch 提取游标到变量
-- todo 7 当游标到最后一行下面退出循环
-- todo 8 输出 价格: XX, 吨位: YY-ZZ
-- todo 4 关闭游标实现
declare-- todo 1 声明行变量 v_pricetablev_pricetable T_PRICETABLE%rowtype;-- todo 2 声明游标 cur_pricetable 保存业主类型为 1 的价格表cursor cur_pricetable isselect * from T_PRICETABLE where OWNERTYPEID=1;
begin-- todo 3 打开游标open cur_pricetable;-- todo 5 loop 循环loop-- todo 6 fetch 提取游标到变量fetch cur_pricetable into v_pricetable;-- todo 7 当游标到最后一行下面退出循环exit when cur_pricetable%notfound;-- todo 8 输出 价格: XX, 吨位: YY-ZZDBMS_OUTPUT.PUT_LINE('价格: ' || v_pricetable.PRICE || ', 吨位: ' || v_pricetable.MINNUM ||'-' || v_pricetable.MAXNUM);end loop;-- todo 4 关闭游标close cur_pricetable;
end;运行结果如下：
带参数的游标
语法1: 创建带参数的游标
cursor cur_pricetable(参数名 参数类型) is SQL;语法2: 使用带参数的游标
open cur_pricetable(2);我们的查询语句的条件值有可能是在运行时才能决定的，比如性业主类型， 可能是运行时才可以决定，那如何实现呢？我们接下来学习带参数的游标，修改上述案例
案例:
-- todo 目标: 带参数的游标
-- todo 需求：打印根据参数值 业主类型 显示 价格表
-- todo 1 声明行变量 v_pricetable
-- todo 2 声明带参数游标 cur_pricetable 根据参数值获取指定类型的价格表
-- todo 3 打开游标
-- todo 5 loop 循环
-- todo 6 fetch 提取游标到变量
-- todo 7 当游标到最后一行下面退出循环 notfound
-- todo 8 输出 价格: XX, 吨位: YY-ZZ
-- todo 4 关闭游标实现
declare-- todo 1 声明行变量 v_pricetablev_pricetable T_PRICETABLE%rowtype;-- todo 2 声明带参数游标 cur_pricetable 根据参数值获取指定类型的价格表cursor cur_pricetable(v_ownertypeid number) isselect * from T_PRICETABLE where ownertypeid=v_ownertypeid;
begin-- todo 3 打开游标open cur_pricetable(2);-- todo 5 loop 循环loop-- todo 6 fetch 提取游标到变量fetch cur_pricetable into v_pricetable;-- todo 7 当游标到最后一行下面退出循环 notfoundexit when cur_pricetable%notfound;-- todo 8 输出 价格: XX, 吨位: YY-ZZDBMS_OUTPUT.PUT_LINE('价格: ' || v_pricetable.PRICE || ', 吨位: ' || v_pricetable.MINNUM || '-' || v_pricetable.MAXNUM);end loop;-- todo 4 关闭游标close cur_pricetable;
end;运行结果如下：
for 循环提取游标值
我们每次提取游标，需要打开游标 关闭游标 循环游标 提取游标 控制循环的 退出等等，好麻烦！有没有更简单的写法呢？有！用 for 循环一切都那么简单 ，
上例的代码可以改造为下列形式
案例
-- todo 目标: 带参数的游标
-- todo 需求：使用for 打印根据参数值显示指定业主类型 的 价格表
-- todo 1 声明带参数游标 cur_pricetable 根据参数值获取指定类型的价格表
-- todo 2 使用 for 循环遍历
-- todo 2.1 打印 价格: XX, 吨位: YY-ZZ实现
declare-- todo 1 声明带参数游标 cur_pricetable 根据参数值获取指定类型的价格表cursor cur_pricetable(v_ownertypeid number) isselect * from t_pricetable where ownertypeid=v_ownertypeid;
begin-- todo 2 使用 for 循环遍历for v_pricetable in cur_pricetable(1) loop-- todo 2.1 打印 价格: XX, 吨位: YY-ZZDBMS_OUTPUT.PUT_LINE('价格: ' || v_pricetable.PRICE ||', 吨位: ' || v_pricetable.MINNUM || '-' || v_pricetable.MAXNUM);end loop;
end;结果:
存储函数
什么是存储函数
存储函数又称为自定义函数。可以接收一个或多个参数，返回一个结果。
在函数中我们可以使用 PL/SQL 进行逻辑的处理。
select max(xx), 函数(xxx), substr(列1, 2, 3) from 表
存储函数语法结构
创建或修改存储过程的语法如下:
CREATE [ OR REPLACE ] FUNCTION 函数名称
（参数名称 参数类型, 参数名称 参数类型, ... ）
RETURN 结果变量数据类型
IS变量声明部分;
BEGIN逻辑部分;RETURN 结果变量;[EXCEPTION异常处理部分]
END;案例
需求：创建存储函数，根据地址 ID 查询地址名称。
语句:
create function fn_getaddress(v_id number)
return varchar2
isv_name varchar2(30);
beginselect name into v_name from t_address where id=v_id;
return v_name;
end;测试此函数：
select fn_getaddress(3) from dual;
select name from t_address where id = 3;输出内容
需求：查询业主 ID ，业主名称，业主地址，业主地址使用刚才我们创建的函数
来实现。
select id 编号,name 业主名称,fn_getaddress(addressid) 地址
from t_owners;查询结果如下：
存储过程
什么是存储过程
存储过程是被命名的 PL/SQL 块，存储于数据库中，是数据库对象的一种。
应用程序可以调用存储过程，执行相应的逻辑。
存储过程与存储函数都可以封装一定的业务逻辑并返回结果，存在区别如下：
1、存储函数中有返回值，且必须返回；而存储过程没有返回值，可以通过传出参数返回多个值。
2、存储函数可以在select 语句中直接使用，而存储过程不能。过程多数是被应用程序所调用。
3、存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务代码。
存储过程语法结构
创建或修改存储过程的语法如下: [prəˈsidʒər]
CREATE [ OR REPLACE ] PROCEDURE 存储过程名称
（参数名 in 类型, 参数名 out类型, 参数名 inout 类型）
IS|AS
变量声明部分;
BEGIN
逻辑部分
[EXCEPTION
异常处理部分]
END;参数只指定类型，不指定长度
过程参数的三种模式：
IN 传入参数（默认）
OUT 传出参数，主要用于返回程序运行结果
INOUT 传入传出参数
创建不带传出参数的存储过程：添加业主信息
--增加业主信息序列
create sequence seq_owners start with 11;
--增加业主信息存储过程
create or replace procedure pro_owners_add
(v_name varchar2,v_addressid number,v_housenumber varchar2,v_watermeter varchar2,v_type number
)
is
begininsert into t_ownersvalues( seq_owners.nextval, v_name, v_addressid, v_housenumber, v_watermeter, sysdate, v_type );commit;
end;PL/SQL 中调用存储过程
call pro_owners_add('赵伟',1,'999-3','132-7',1);结果:
案例
-- todo 目标: 创建存储过程 和 使用存储过程
-- todo 准备工作 创建业主信息序列
-- todo 需求1: 创建不带传出参数的存储过程 pro_owners_add：添加业主信息
-- todo 1 声明参数: v_name, v_addressid, v_housenumber, v_watermeter, v_type
-- todo 2 插入 insert into
-- todo 3 提交事务
-- todo 需求2: 调用存储过程 添加业主信息 call 存储过程(... ...);创建带传出参数的存储过程
需求：添加业主信息，传出参数为新增业主的 ID
--增加业主信息存储过程
create or replace procedure pro_owners_add
(v_name varchar2,v_addressid number,v_housenumber varchar2,v_watermeter varchar2,v_type number,v_id out number
)
is
beginselect seq_owners.nextval into v_id from dual;insert into t_ownersvalues( v_id, v_name, v_addressid, v_housenumber, v_watermeter, sysdate, v_type );commit;
end;PL/SQL 调用该存储过程
declarev_id number;--定义传出参数的变量
beginpro_owners_add('王旺旺',1,'922-3','133-7',1,v_id);DBMS_OUTPUT.put_line('增加成功,ID:'||v_id);
end;执行成功后输出结果：
案例
-- todo 需求3: 创建带传出参数的存储过程：添加业主信息
-- todo 1 调用序列下一个值 赋值给 v_id
-- todo 2 插入 insert into
-- todo 3 提交事务
-- todo 需求4: 调用存储过程 添加业主信息 且 打印返回的结果
-- todo 1 声明变量 v_id
-- todo 2 调用存储过程 注意: 不需要 call
-- todo 3 打印返回 v_id触发器
什么是触发器
数据库触发器是一个与表相关联的、存储的 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
……
beginPLSQL 块
End ；FOR EACH ROW 作用是标注此触发器是行级触发器 语句级触发器
在触发器中触发语句与伪记录变量的值
| 触发语句 | :old | :new |
| Insert | 所有字段都是空(null) | 将要插入的数据 |
| Update | 更新以前该行的值 | 更新后的值 |
| delete | 删除以前该行的值 | 所有字段都是空(null) |
案例
后置触发器
需求：当用户修改了业主信息表的数据时记录修改前与修改后的值
--创建业主名称修改日志表:用于记录业主更改前后的名称
create table t_owners_log
(updatetime date,ownerid number,oldname varchar2(30),newname varchar2(30)
);
--创建后置触发器，自动记录业主更改前后日志
create or replace trigger tri_owners_log
after
update of name
on t_owners
for each row
declare
begininsert into t_owners_logvalues(sysdate, :old.id, :old.name, :new.name);
end;查询结果如下：
测试：
--测试
--更新数据
update t_owners set name='杨小花' where id=3;
commit;
--查询日志表
select * from t_owners_log;查询结果如下：
注意: 触发器中不能使用 提交 commit
定时器 job
why
定时器通过自动执行任务，例如在夜间备份数据库，每周生成销售报表，或定期清理无效数据，从而提高数据库管理和应用程序开发的效率，并确保数据的完整性和可靠性。
通用语法
提交job语法
DBMS_JOB.SUBMIT (job OUT BINARY_INTEGER,what IN VARCHAR2,next_date IN DATE DEFAULT SYSDATE,interval IN VARCHAR2 DEFAULT NULL
);参数含义如下:
- job：输出参数，JOB的ID，当提交任务后，系统会返回一个JOB的ID，会写到此参数变量中。用来唯一地标示一个任务。该参数既可由用户指定也可由系统自动赋予。一般定义一个变量接收，可以去user_jobs视图查询job值
- 作业号 通常由系统自建的 jobseq 序列来取值
- select jobseq.nextval from dual; ---DBA
- what：输入参数，是一个将被执行的 PL/SQL 代码块，或者是存储过程名，如果是存储过程名，则要在后面添加“;”号
- SQL 语句要用单引号括起来
- 语句中的单引号要用两个单引号书写 单引号结合
- 例如 ename=‘王翦’ 写在作业里
- ename=''王翦''
- next_date：输入参数，指定何时将运行这个工作。一个时间类型变量而不是一个字符串，所以需要注意不要把它当成字符串，不需要对该参数加引号。用来调度任务队列 中该任务下一次运行的时间。
- 开始时间
- 指定日期时间 启动作业
- interval：输入参数， interval 是一个字符串类型，注意添加引号。next_date 是建立这个 job时 希望第一次运行的时间，interval 是 next_date 之后下一次 运行的时间间隔
- 周期性间隔时间
job设定interval 的例子
| 运行频率 | 时间表达式 |
| 每天运行一次 | SYSDATE + 1 |
| 每小时运行一次 | SYSDATE + 1/24 |
| 每10分钟运行一次 | SYSDATE + 10/(60*24) |
| 每30秒运行一次 | SYSDATE + 30/(60*24*60) |
| 每隔一星期运行一次 | SYSDATE + 7 |
| 每个月最后一天运行一次 | TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,1))) + 23/24 |
| 每年1月1号零时 | TRUNC(LAST_DAY(TO_DATE(EXTRACT(YEAR FROM SYSDATE) |
| 每天午夜12点 | TRUNC(SYSDATE + 1) |
| 每天早上8点30分 | TRUNC(SYSDATE + 1) + (8*60+30)/(24*60) |
| 每星期二中午12点 | NEXT_DAY(TRUNC(SYSDATE ), 'TUESDAY' ) + 12/24 |
| 每个月第一天的午夜12点 | TRUNC(LAST_DAY(SYSDATE ) + 1) |
| 每个月最后一天的23点 | TRUNC (LAST_DAY (SYSDATE)) + 23 / 24 |
| 每个季度最后一天的晚上11点 | TRUNC(ADD_MONTHS(SYSDATE + 2/24, 3 ), 'Q' ) -1/24 |
| 每星期六和日早上6点10分 | TRUNC(LEAST(NEXT_DAY(SYSDATE, "SATURDAY"), NEXT_DAY(SYSDATE, "SUNDAY"))) + (6*60+10)/(24*60) |
SYSDATE + 30/(60*24*60) 每30秒运行一次
TRUNC(LAST_DAY(ADD_MONTHS(SYSDATE,1))) + 23/24 每个月最后一天运行一次
NEXT_DAY(TRUNC(SYSDATE ), 'TUESDAY' ) + 12/24 每星期二中午12点
查看正在运行的 job
selectjob,log_user,last_date,next_date,interval,what
from user_jobs;修改作业
修改作业的sql语句
- 不想修改的信息直接写 NULL
- 填写的信息会将原值覆盖
begindbms_job.change(21,'update tb_emp set salary=salary-10 where name=''李斯'';', NULL, NULL);commit;
end;修改下次运行时间
begindbms_job.next_date(21, sysdate+5/(24*60));commit;
end;修改时间间隔 interval
- 将间隔时间改成 NULL 既是下次运行后就不在运行
begindbms_job.INTERVAL(21,null);commit;
end;删除 job
declarejob_id number := 18;
begindbms_job.remove(job_id);commit;
end;案例1: 通过 job 定时执行存储过程
准备工作: 建表 和 创建存储过程
-- 1.1 建表
create table tb_date(dt date);
-- 1.2 创建存储过程
create or replace procedure pro_date
as
begininsert into tb_date values(sysdate);commit;
end;
-- 测试
call pro_date();
select * from tb_date;
truncate table tb_date;查询结果如下：
提交 job
DECLAREjob_id NUMBER;
BEGIN-- 提交 job 并执行存储过程 pro_datedbms_job.submit(job_id,'pro_date;',SYSDATE,'SYSDATE+(10)/(24*60*60)');-- 运行 jobdbms_job.run(job_id);dbms_output.put_line(job_id);
end;查询结果如下：
查看 job
-- 3 查看 job
selectjob,log_user,last_date,next_date,interval,what
from user_jobs;查询结果如下：
检查
-- 4 检查
select * from tb_date order by dt desc;查询结果如下：
停止 job
-- 5 修改job
---停止定时器
declarejob_id number := 23;
begindbms_job.remove(job_id);commit;
end;查询结果如下：
检查
-- 6 检查
selectjob,log_user,last_date,next_date,interval,what
from user_jobs;查询结果如下：
目标2: 通过 job 定时执行 SQL语句
准备工作
-- 1 准备工作: 建表 和 插入数据
-- 创建雇员表 tb_emp
CREATE TABLE tb_emp (id NUMBER PRIMARY KEY,name VARCHAR2(100),salary NUMBER
);
-- 插入数据
INSERT INTO tb_emp (id, name, salary) VALUES (1, '秦始皇', 100000);
INSERT INTO tb_emp (id, name, salary) VALUES (2, '李斯', 80000);
INSERT INTO tb_emp (id, name, salary) VALUES (3, '王翦', 70000);
-- 提交事务
COMMIT;
select * from tb_emp;
update tb_emp set salary=salary+1000 where name='王翦';查询结果如下：
提交 job
-- 2 提交 job
DECLAREjob_id NUMBER;
BEGIN-- 提交 job 并执行存储过程 pro_datedbms_job.submit(job_id,'update tb_emp set salary=salary+1000 where name=''王翦'';',SYSDATE,'SYSDATE+(10)/(24*60*60)');-- 运行 jobdbms_job.run(job_id);dbms_output.put_line(job_id);
end;- 语句中的单引号要用两个单引号书写 单引号结合
- 例如 ename=‘王翦’ 写在作业里
- ename=''王翦''
查看 job
-- 3 查看 job
selectjob,log_user,last_date,next_date,interval,what
from user_jobs;查询结果如下：
检查
-- 4 检查
select * from tb_emp;查询结果如下：
修改作业的sql语句
-- 不想修改的信息直接写 NULL
-- 填写的信息会将原值覆盖
begindbms_job.change(24,'update tb_emp set salary=salary-10 where name=''李斯'';', NULL, NULL);commit;
end;查询结果如下：
修改下次运行时间
--修改下次运行时间
begindbms_job.next_date(24, sysdate+5/(24*60));commit;
end;查询结果如下：
将间隔时间改成 NULL 既是下次运行后就不在运行
--将间隔时间改为null，下次运行后就不再运行
begindbms_job.INTERVAL(24,null);commit;
end;查询结果如下：
删除定时器
-- 5 停止定时器
declarejob_id number := 24;
begindbms_job.remove(job_id);commit;
end;查询结果如下：
