1 PL/SQL

1.1 概述

PL/SQL( Procedure Language/SQL)是Oracle 对sql语言的过程化扩展，指在SQL命令语言中增加了
过程处理语句(如分支、循环等)，使SQL语言具有过程处理能力。把SQL 语言的数据操纵能力与过
程语言的数据处理能力结合起来，使得 PLSQL面向过程但比过程语言简单、高效、灵活和实用。

1.2 语法结构

[declare-- 声明变量
]
begin-- 代码逻辑
[exception-- 异常处理
] 
end;

2 变量

2.1 变量声明

2.1.1 语法

declare 变量名 类型(长度);

2.1.2 变量类型

变量的类型除了可以是Oracle常规的数据类型，如（char、varchar2、number），还可以进行引用型或记录型
引用型
语法：

表名.列明%type

示例：

declare
v_num0 t_account.num0%type; -- 上月水表数
v_num1 t_account.num1%type; -- 本月水表数

记录型
语法：

表名%rowtype

示例：

declare
v_account t_account%rowtype; --台账行记录类型

2.2 变量赋值

2.2.1 变量赋值语句

语法：

变量名 := 变量值

示例：

v_price := 2.45;

2.2.1 select into 赋值语句

语法：

select 列明 into 变量名 from 表名 where 条件

示例：

select usenum,num0,num1 into v_usenum,v_num0,v_num1 from t_accountwhere year = '2012' and month = '01' and id = 1;

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

3 异常处理

3.1 概述

在设计或运行一个程序时，常出现这样或那样的错误。针对这类错误，采取相应的措施。这就是异常处理(EXCEPTION)
异常处理就是针对系统中发生的各种错误所采取的处理措施,也叫例外

3.2 异常分类

3.3 语法

BEGIN
…;
EXCEPTIONWHEN 异常情况 1[ OR 异常情况 2 …] THEN…；WHEN 异常情况 3[ OR 异常情况 4 …] THEN…；WHEN OTHERS THEN
…；
END；

3.4 示例

3.4.1 系统预定义的异常示例

declarev_price number(10,2); --单价v_usenum2 number(10,2); -- 吨数v_money number(10,2); -- 金额v_account t_account%rowtype; --台账行记录类型
beginv_price := 2.45; --单价赋值-- v_usenum := 9213; --水费字数select * into v_account from t_accountwhere year = '2012' and month = '01' ;v_usenum2 := round(v_account.usenum/1000,2); --吨数v_money := v_price * v_usenum2; --金额DBMS_OUTPUT.PUT_LINE('水费字数：'|| v_account.usenum ||' 金额：' || v_money);exceptionwhen too_many_rows thenDBMS_OUTPUT.PUT_LINE('返回多行账单数据');when no_data_found thenDBMS_OUTPUT.PUT_LINE('没有找到账单数据');
end;

3.4.2 非系统预定义的异常示例

要使用非预定义的Oracle异常，则用户必须首先进行定义。步骤如下：
(1)在PL/SQL块的declare定义部分定义异常名：
<exception_name> EXCEPTION;
(2)使用pragma exception_init语句，将其定义好的异常名与标准的Oracle错误编号联系起来：
pragma exception_init(<exception_name >, <handle_code>)；
(3)在PL/SQL块异常的处理部分对异常情况做出相应的处理。

DECLAREFk_delete  EXCEPTION;PRAGMA EXCEPTION_INIT (Fk_delete ,-2292);
BEGINDELETE products WHERE productid=1;              EXCEPTION
WHEN Fk_delete   THENdbms_output.put_line(‘在另一个表中引用了该记录’);
WHEN others  THENdbms_output.put_line(‘其它错误’||sqlcode);END;

3.4.3 用户自定义的异常

步骤：
1、定义异常处理：declare 异常名 exception
2、触发异常：raise 异常名
3、处理异常：exception when 异常名1 then 异常处理语句段1; when 异常名2 then 异常处理语句段2

DECLAREprod_excep EXCEPTION;
BEGINUPDATE products SET Discontinued=‘1’WHERE unitprice<=3;IF sql%notfound THEN RAISE prod_excep;ELSEdbms_output.put_line(‘更新了’||sql%rowcount||’条记录’);END IF;
EXCEPTIONWHEN prod_excep THENdbms_output.put_line(‘未找到相应产品’);
END;  

4 条件判断

4.1 单分支

if 条件 then业务逻辑
end if;

4.2 两个条件分支

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

4.3 多条件分支

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

4.4条件判断综合应用

declare-- 定义变量v_price1 number(10,2); -- 不超过5吨的价格v_price2 number(10,2); -- 不超过10吨的价格v_price3 number(10,2); -- 超过10吨的价格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_accountwhere year = '2012' and month = '01' and id = 1;v_usenum2 := round(v_account.usenum/1000,2); --吨数-- 阶梯水费计算if v_usenum2 <= 5 thenv_money := v_price1 * v_usenum2;elsif v_usenum2 >5 and v_usenum2 <= 10  thenv_money := v_price1*5 + v_price2*(v_usenum2-5);elsev_money := v_price1*5 + v_price2*5 + v_price3*(v_usenum2-10);end if;-- 输出展示DBMS_OUTPUT.put_line('水的吨数是：' || v_usenum2 || ' 水费金额：' || v_money);exceptionwhen too_many_rows thenDBMS_OUTPUT.PUT_LINE('返回多行账单数据');when no_data_found thenDBMS_OUTPUT.PUT_LINE('没有找到账单数据');end;

5 循环

5.1 无条件循环

语法：

loop循环语句
end loop

示例：

declarev_num number;
beginv_num := 1;-- 循环loopdbms_output.put_line(v_num);v_num := v_num + 1;/* if v_num > 100 then exit;  -- 循环100次退出end if;*/exit when v_num > 100;  -- 与上面等效end loop;end;

5.2 有条件循环

语法：

  while 条件loop循环语句end loop;

示例：

declare v_num number;beginv_num := 1;while v_num <=100loopdbms_output.put_line(v_num);v_num := v_num + 1;end loop;
end;

5.3 for 循环

语法：

for 循环名 in （循环体） 
loop 
执行循环内容;
end loop;

示例

beginfor v_num in 1 .. 100loopdbms_output.put_line(v_num);end loop;
end;

6 游标

6.1 概述

游标是系统为用户开设的一个数据缓冲区存放SQL 语的执行结果，我们可以把游标理解为 PVSQL中的结果集。

6.2 语法

-- 声明区声明游标，语法如下：cursor 游标名称 is SQL语句;-- 使用游标open 游标名称  -- 打开游标loop -- 循环fetch 游标名称 into 变量  -- 取出游标赋值exit when 游标名称%notfound  --关闭游标end loop;close 游标名称

6.3 游标案例

6.3.1 不带参数，输出结果集

declare cursor cur_pricetable is select * from t_pricetable where ownertypeid = 1;  -- 声明游标v_pricetable t_pricetable%rowtype;
beginopen cur_pricetable;  -- 打开游标loopfetch 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;

6.3.2 带参数，输出结果集

declare cursor cur_pricetable(v_ownertype number) is select * from t_pricetable where ownertypeid = v_ownertype;  -- 声明游标v_pricetable t_pricetable%rowtype;
beginopen cur_pricetable(2);  -- 打开游标loopfetch 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;

6.3.2 for 循环 带参数的游标

declare cursor cur_pricetable(v_ownertype number) is select * from t_pricetable where ownertypeid = v_ownertype;  -- 声明游标-- v_pricetable t_pricetable%rowtype;
beginfor v_pricetable in cur_pricetable(2)  -- for循环自动打开和关闭游标，节省代码loopDBMS_OUTPUT.PUT_LINE('价格：' || v_pricetable.price || ' 吨位：' ||v_pricetable.minnum || '-' || v_pricetable.maxnum); end loop;end;

7 存储函数

7.1 概述

存储函数：又称为自定义函数。可以接收一个或多个参数，返回一个结果在函数中我们可以使用 P/SQL进行逻辑的处理

7.2 语法

   create [or replace] function 函数名(参数名称 参数类型,参数名称 参数类型,...)return 结果变量数据类型isdeclare-- 声明变量   begin逻辑部分;return 结果变量;[exception异常处理部分]end；

7.3 示例

创建存储函数

create or replace function fn_getaddress
(v_id number)
return varchar2
isv_name varchar2(30);  
begin-- 查询地址表select name into v_name from t_address where id = v_id;return v_name;
end;

8 存储过程

8.1 概述

存储过程是被命名的 PL/SQL块，存储于数据库中，是数据库对象的一种。应用程序可以调用存储过程，执行相应的逻辑
存储过程与存储函数都可以封装一定的业务逻辑并返回结果，存在区别如
1、存储函数中有返回值，且必须返回;而存储过程没有返回值，可以通过传出参数返回多个值。
2、存储函数可以在 select 语中直接使用，而存储过程不能。过程多数是被应用程序所调用。
3、存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务代码

8.2 语法

create or replace procedure 存储过程名称
(参数名称 类型,参数名称 类型,...)
is | as
变量声明部分;
begin逻辑部分[exception异常处理部分]
end;

注意：
1、参数只指定类型，不指定长度；
2、过程参数的三种模式：
IN 传入参数（默认）
OUT 传出参数，主要用于返回程序运行结果
IN OUT 传入传出参数

8.3 示例

8.3.1 案例1 不带传出参数的存储过程

create sequence seq_owners1 start with 1;  -- 业主序列create or replace procedure pro_owners_add 
(
v_name varchar2, --名称
v_addressid number, -- 地址编码
v_housenumber varchar2, -- 门牌号
v_watermeter varchar2, -- 水表号
v_ownertypeid number -- 业主类型
)isbegininsert into t_owners values(seq_owners.nextval,v_name,v_addressid,v_housenumber,v_watermeter,sysdate,v_ownertypeid);commit;  -- 别忘记写提交了
end;

8.3.1 案例1 带传出参数存储过程

create or replace procedure pro_owners_add 
(
v_name varchar2, --名称
v_addressid number, -- 地址编码
v_housenumber varchar2, -- 门牌号
v_watermeter varchar2, -- 水表号
v_ownertypeid number, -- 业主类型
v_id out number -- 传出参数
)isbegin-- 对传出参数赋值select seq_owners1.nextval into v_id from dual;-- 新增业主insert into t_owners values(v_id,v_name,v_addressid,v_housenumber,v_watermeter,sysdate,v_ownertypeid);commit;
end;

8.4 存储过程的调用

8.4.1 不带参数存储过程的调用

调用方式1：

call pro_owners_add('马大哈',2,'22333','6677',1);

调用方式2：

beginpro_owners_add('马小哈',2,'22333','6678',1);
end;

8.4.1 带参数存储过程的调用

只能使用方式2：

declare
v_id number;beginpro_owners_add('马二哈',2,'223323','664378',1,v_id);dbms_output.put_line(v_id);
end;

9 触发器

9.1 概述

  数据库触发器是一个与表相关联的、存储的 PL/SQL程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle 自动地执行触发器中定义的语句序列
作用
– 数据确认
– 实施复杂的安全性检查
– 做审计，跟踪表上所做的数据操作等
– 数据的备份和同步

触发器的分类
– 前置触发器（BEFORE）
– 后置触发器（AFTER）

9.2 语法

CREATE [OR REPLACE] TRIGGER 触发器名BEFORE | AFTER[DELETE][[OR] INSERT][[OR] UPDATE [OF 列明]]ON 表名[FOR EACH ROW][WHEN (条件)]
declare-- 变量声明beginPLSQL 块
end; 

**解释：**在触发器语句与伪记录变量的值
触发语句 :old :new
Insert 所有字段都是空(nulI) 将要插入的数据
Update 更新以前该行的值 更新后的值
delete 删除以前该行的值 所有字段都是空(null)

9.3 示例

9.3.1 前置触发器案例

常用于提交数据前的触发器操作

create or replace trigger tri_account_num1
before  -- 前置触发器
update of num1 
on t_account
for each rowdeclarebegin-- 通过伪记录变量修改usenum 字段的值:new.usenum := :new.num1 - :new.num0;  -- 后置触发器无法修改:new.usenum值
end;

9.3.2 后置触发器案例

后置触发器常用于存储修改后的日志记录

-- 创建日志表，记录业主名称修改前和修改后的名称
create table t_owners_log
(
ownerid number,
oldname varchar2(30),
newname varchar2(30),
updatetime date
)create or replace trigger tri_owners_log
after
update of name
on t_owners
for each rowdeclarebegin-- 向日志表插入日志就行insert into t_owners_log values(:new.id,:old.name,:new.name,sysdate);  -- 不许写commit
end;