八、Oracle PL/SQL

• 一、Oracle 操作集
• 二、Oracle 数据类型
• 三、Oracle 函数
• 四、Oracle 视图
• 五、Oracle 序列
• 六、Oracle 同义词
• 七、Oracle 索引
• 八、Oracle PL/SQL
• 九、Oracle 存储函数
• 十、Oracle 存储过程
• 十一、Oracle 触发器
• 十二、Oracle 查询

---

一、PL/SQL

• PL/SQL（Procedure Language/SQL）是 Oracle 对 SQL 语言的过程化扩展。

1. 指在 SQL 命令语言中增加了 过程处理语句（如：分支、循环等），使 SQL 语言具有 过程处理能力。
2. 把 SQL 语言的 数据操纵能力 与过程语言的 数据处理能力 结合起来。
3. 使得 PL/SQL 面向过程，但比过程语言简单、高效、灵活和实用。

---

1. 基本语法结构

-- 声明变量
[declare]
begin

-- ...代码逻辑

-- 异常处理
[exception]
end;

---

2. 变量声明赋值

• 声明变量语法。

'变量名' '类型'('长度');

---

• 变量赋值语法。

-- `=`等号是判断两值是否相等
'变量名' := '变量值'

---

• 需求：声明变量（水费单价、字数、吨数、金额）。

1. 对变量（水费单价、字数）进行赋值。
2. 计算吨数：根据 字数 换算。
   规则为：水费字数 除以 1000，并且四舍五入，保留两位小数。
3. 计算金额：金额 = 单价 * 吨数。

• 输出：水费单价、数量和金额。

-- 变量的用法 --
declare
	-- 水费单价
    v_price number(10,2);
    -- 字数
    v_usenum number; 
    -- 吨数
    v_usenum2 number(10,2);
    -- 金额
    v_money number(10,2);
begin
	-- 水费单价
    v_price:=2.45;
    -- 字数
    v_usenum:=8012;
    -- 字数 换算为 吨数
    v_usenum2:=round( v_usenum/1000,2 );
    -- 计算金额
    v_money:=round( v_price*v_usenum2,2 );
    -- 输出：单价、数量和金额
    dbms_output.put_line('单价：'||v_price||'，吨数：'||v_usenum2||'，金额：'||v_money);
end;
-- 单价：2.45，吨数：8.01，金额：19.62

---

• Select into 方式赋值语法结构。

select '列名' into '变量名' from '表名' where '条件';

---

• 注意：结果必须是一条记录，有多条记录 和 没有记录 都会报错。

declare
	-- 单价
    v_price number(10,2);
    -- 水费字数
    v_usenum number;
    -- 上月字数
    v_num0 number;
    -- 本月字数
    v_num1 number;
    -- 使用吨数
    v_usenum2 number(10,2);
    -- 水费金额
    v_money number(10,2);
begin
    -- 对单价进行赋值
    v_price:=3.45;
    -- 变量赋值
    select usenum,num0,num1 into v_usenum,v_num0,v_num1 from T_ACCOUNT where year='2012' and month='01' and owneruuid=1;
    -- 水费字数 / 1000
    v_usenum2:=round( v_usenum/1000,2 );
    -- 单价 * 使用吨数
    v_money:=v_price*v_usenum2;
    -- 输出
    dbms_output.put_line('单价：'||v_price||'，吨数：'||v_usenum2||'，金额：'||v_money||'，上月字数：'||v_num0||'，本月字数：'||v_num1);
end;
-- 单价：3.45，吨数：19.92，金额：68.72，上月字数：30203，本月字数：50123

---

3. 属性类型

• 表名.列名%TYPE 引用型。
  作用：引用 某个表某列 的字段类型。

declare
	-- 单价
	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
	-- 对单价进行赋值
	v_price:=3.45;
	-- v_usenum:=8090;
	select usenum,num0,num1 into v_usenum,v_num0,v_num1 from T_ACCOUNT where year='2012' and month='01' and owneruuid=1;
	-- 使用吨数
	v_usenum2:=round( v_usenum/1000,2 );
	-- 计算金额
	v_money:=v_price*v_usenum2;
	-- 输出
	DBMS_OUTPUT.put_line('单价：'||v_price||'，吨数：'||v_usenum2||'，金额：'||v_money||'，上月字数：'||v_num0||'，本月字数：'||v_num1);
end;
-- 单价：3.45，吨数：19.92，金额：68.72，上月字数：30203，本月字数：50123

---

• 表名%ROWTYPE 记录型。
  作用：标识 某个表的一行记录 类型（一行数据）。

-- 变量的用法 --
declare
	-- 单价
	v_price number(10,2);
	-- 使用吨数
	v_usenum2 number(10,2);
	-- 水费金额
	v_money number(10,2);
	-- 台账行记录型
	v_account T_ACCOUNT%ROWTYPE;
begin
	-- 对单价进行赋值
	v_price:=3.45;
	-- 赋值 = 一行数据
	select * into v_account from T_ACCOUNT where year='2012' and month='01' and owneruuid=1;
	-- 使用吨数
	v_usenum2:=round( v_account.usenum/1000,2 );
	-- 计算金额
	v_money:=v_price*v_usenum2;
	-- 输出
	DBMS_OUTPUT.put_line('单价：'||v_price||'，吨数：'||v_usenum2||'，金额：'||v_money||'，上月字数：'||v_account.num0||'，本月字数：'||v_account.num1);
end;
-- 单价：3.45，吨数：19.92，金额：68.72，上月字数：30203，本月字数：50123

---

4. 异常

• 在运行程序时，出现的 错误 叫做 异常。
• 发生异常后，语句将停止执行，控制权转移到 PL/SQL 块的异常处理部分。

---

• 异常有两种类型。

1. 预定义异常：
   当 PL/SQL 程序违反 Oracle规则 或 超越系统限制 时 隐式引发。
2. 用户定义异常：
   用户可以在 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 '异常处理逻辑';

---

 -- 变量的用法
declare
	-- 水费单价
	v_price number(10,2);
	-- 水费字数
	v_usenum T_ACCOUNT.USENUM%type; 
	-- 吨数
	v_usenum2 number(10,3);
	-- 金额
	v_money number(10,2);
begin
	-- 水费单价
	v_price:=2.45;
-- 	select usenum into v_usenum from T_ACCOUNT where owneruuid=1 and year='2012' and month='01';
	select usenum into v_usenum from T_ACCOUNT where owneruuid=1 and year='2022' and month='01';
	-- 字数换算为吨数
	v_usenum2:=round( v_usenum/1000,3 );
	-- 计算金额
	v_money:=round( v_price*v_usenum2,2 );
	-- 输入
	dbms_output.put_line('单价：'||v_price||'，吨数：'||v_usenum2||'，金额：'||v_money);
-- 异常
exception
	when NO_DATA_FOUND 
	then dbms_output.put_line('未找到数据，请核实');
	when TOO_MANY_ROWS 
	then dbms_output.put_line('查询条件有误，返回多条信息，请核实');
end;
-- 单价：2.45，吨数：19.92，金额：48.8
-- 未找到数据，请核实

---

5. 条件判断

• 基本语法一。

if '条件' 
then '业务逻辑'
end if;

---

• 基本语法二。

if '条件' 
then '业务逻辑'
else '业务逻辑2'
end if;

---

• 基本语法三。

if '条件' 
then '业务逻辑'
elsif '条件' 
then '业务逻辑2'
else '业务逻辑3'
end if;

---

• 需求：设置三个等级的水费。

1. 5吨 以下 2.45 元/吨。
2. 5吨 到 10吨 部分 3.45 元/吨。
3. 超过 10吨 部分 4.45 元/吨。

• 根据使用水费的量，来计算阶梯水费。

declare
	-- 不足`5`吨的单价
	v_price1 number(10,2);
	-- 超过`5`吨不足`10`吨单价
	v_price2 number(10,2);
	-- 超过`10`吨单价
	v_price3 number(10,2);
	-- 记录型
	v_account T_ACCOUNT%ROWTYPE;
	-- 使用吨数
	v_usenum2 number(10,2);
	-- 水费金额 
	v_money number(10,2);
begin
	-- 对单价进行赋值
	v_price1:=2.45;
	v_price2:=3.45;
	v_price3:=4.45;    
	-- 赋值
	select * into v_account from T_ACCOUNT where year='2012' and month='01' and owneruuid=1;
	-- 使用吨数
	v_usenum2:=round( v_account.usenum/1000,2 );
	-- 计算金额（阶梯水费）
	-- 第一个阶梯
	if v_usenum2 <= 5 
	then v_money:=v_price1*v_usenum2;
	-- 第二个阶梯
	elsif v_usenum2 > 5 and v_usenum2 <= 10 
	then v_money:=v_price1*5 + v_price2*( v_usenum2-5 );
	-- 第三个阶梯
	else v_money:=v_price1*5 + v_price2*5 + v_price3*( v_usenum2-10 );
	end if;
	-- 输出
	DBMS_OUTPUT.put_line('吨数：'||v_usenum2||'，金额：'||v_money||'，上月字数：'||v_account.num0||'，本月字数：'||v_account.num1);
exception
	when NO_DATA_FOUND 
	then DBMS_OUTPUT.put_line('没有找到数据');
	when TOO_MANY_ROWS 
	then DBMS_OUTPUT.put_line('返回的数据有多行');
end;
-- 吨数：19.92，金额：73.64，上月字数：30203，本月字数：50123

---

6. 循环

---

6.1 无条件循环

loop
	-- 循环语句
end loop;

---

• 范例：输出从 1 开始的 100 个数字。

declare
	v_num number:=1;
begin
	loop
		DBMS_OUTPUT.put_line(v_num);
		v_num:=v_num + 1;
		-- 判断条件退出
		exit when v_num > 100; 
	end loop;
end;
-- 1 
-- ... 
-- 100

---

6.2 有条件循环

while '条件'
loop
	-- 循环语句
end loop;

---

• 范例：输出从 1 开始的 100 个数字。

declare
	v_num number:=1;
begin
	-- 判断条件
	while v_num <= 100 
	loop
		DBMS_OUTPUT.put_line(v_num);
		v_num:=v_num + 1;
	end loop;
end;
-- 1
-- ...
-- 100

---

6.3 for 循环

for '变量' in '起始值'..'终止值'
loop
	-- 循环语句
end loop;  

---

• 范例：输出从 1 开始的 100 个数。

begin
    for v_num in 1..100
    loop
        DBMS_OUTPUT.put_line(v_num);
    end loop;
end;
-- 1
-- ...
-- 100

---

7. 游标

• 游标 是系统为用户开设的一个 数据缓冲区。

1. 存放 SQL 语句的执行结果。
2. 可以把 游标 理解为 PL/SQL 中的结果集。
   

---

• 在声明区声明游标。

cursor '游标名称' is SQL '语句';

---

• 使用游标语法。

-- 打开游标
open '游标名称' 
	loop
		-- 提取游标到变量
  		fetch '游标名称' into '变量' 
  		-- 当游标到最后一行下面退出循环
        exit when '游标名称'%notfound 
    end loop;
-- 关闭游标
close '游标名称';    

---

• 需求：打印 业主类型 为1 的价格表代码。

declare
	-- 价格行对象
	v_pricetable T_PRICETABLE%rowtype;
	-- 定义游标
	cursor cur_pricetable is select * from 'T_PRICETABLE' where ownertypeid=1;
begin    
	-- 打开游标
	open cur_pricetable;
			loop
				-- 提取游标到变量
				fetch cur_pricetable into v_pricetable;
				-- 当游标到最后一行下面退出循环
				exit when cur_pricetable%notfound;
				-- 输出
				dbms_output.put_line( '价格：'||v_pricetable.price ||'，吨位：'||v_pricetable.minnum||'-'||v_pricetable.maxnum ); 
			end loop;
	-- 关闭游标
	close cur_pricetable;
end;   
-- 价格：2.45，吨位：0-5
-- 价格：3.45，吨位：5-10
-- 价格：4.45，吨位：10-

---

7.1 带参数的游标

• 查询语句的条件值，有可能是在运行时才能决定的。
  比如：指定 业主类型，可能是运行时才可以决定，使用 带参数的游标。

declare
	-- 价格行对象
	v_pricetable T_PRICETABLE%rowtype;
	-- 定义游标
	cursor cur_pricetable(v_ownertypeid number) is select * from 'T_PRICETABLE' where ownertypeid=v_ownertypeid;
begin
	-- 打开游标
	open cur_pricetable(2);
		loop
			-- 提取游标到变量         	
			fetch cur_pricetable into v_pricetable;
			-- 当游标到最后一行下面退出循环
			exit when cur_pricetable%notfound;
			-- 输出
			dbms_output.put_line( '价格：'||v_pricetable.price ||'，吨位：'||v_pricetable.minnum||'-'||v_pricetable.maxnum );
		end loop;
	-- 关闭游标
	close cur_pricetable;
end;
-- 价格：3.87，吨位：0-5
-- 价格：4.87，吨位：5-10
-- 价格：5.87，吨位：10-

---

7.2 for 循环提取游标值

• 每次提取游标，需要 打开游标、关闭游标、循环游标、提取游标、控制循环的退出 等等。
• 很麻烦，用 for循环 一切都那么简单。

declare
	-- 定义游标
	cursor cur_pricetable(v_ownertypeid number) is select * from 'T_PRICETABLE' where ownertypeid=v_ownertypeid;
begin
	for v_pricetable in cur_pricetable(3)
	loop
		-- 输出
		dbms_output.put_line( '价格：'||v_pricetable.price ||'，吨位：'||v_pricetable.minnum||'-'||v_pricetable.maxnum );
	end loop;
end;
-- 价格：4.36，吨位：0-5
-- 价格：5.36，吨位：5-10
-- 价格：6.36，吨位：10-

---