这篇博客我们主要是介绍 Oracle 编程方面的内容，其实主要就是 PL/SQL 以及与之相关的存储函数、存储过程和触发器，掌握这些内容，我们就可以直接使用 Oracle 数据库实现业务逻辑了，功能还是很强大的。
1.PL/SQL
1.1 什么是PL/SQL
PL/SQL(Procedure Language/SQL) 是 Oracle 对 SQL 语言的过程化扩展，使得可以在 SQL 中增加过程处理语句，比如分支和循环，这样的话我们就可以在 SQL 中进行逻辑处理，很好地将 SQL 语言操作数据的能力和过程语言操作业务逻辑的能力结合起来了，因此 PL/SQL 面向过程但比过程语言更加灵活实用。
基本语法结构：
[declare
--声明变量
]
begin
--代码逻辑
[exception
--异常处理
]
end;
1.2 变量
下面看 PL/SQL 中变量的使用，声明变量的语法：
变量名 类型(长度);
变量赋值的语法：
变量名:=变量值
我们可以写一个计算水费的具体例子：
--变量的用法--
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;
上面例子的水费字数是直接赋值的，正常开发中这个值应该是需要从数据库中查出来的，然后赋值给我们创建的变量的，这时候我们就可以使用 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;
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;
1.3 属性类型
一共有两种属性类型，第一种属性类型为引用型，引用数据表中某个字段的字段类型，第二种属性类型则是记录型，标识数据表中某行记录的记录类型，类似于 Java 中的类对象。
1.引用类，表名.列名%type
2.记录型，表名%rowtype
我们还是通过具体的例子分别进行说明，先看第一种引用类型，我们在 PL/SQL 中声明变量时，如果确认某个变量的数据类型就是和数据表中的某个字段数据类型一样，那我们声明变量时就可以直接引用数据表中的数据类型。
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;
再看第二种记录型，当我们需要使用数据表中某行记录的多个字段时，就可以使用单个记录型的变量，然后需要使用具体字段时，再使用 . 号引用具体字段，下面看具体例子。
declare
v_price number(10,2);--单价
v_account T_ACCOUNT%ROWTYPE;--记录型
v_usenum2 number(10,2);--使用吨数
v_money number(10,2);--水费金额
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;
1.4 异常
当程序运行出现错误时就叫异常，PL/SQL 中也有对异常的处理机制，异常一共分为如下两种：
预定义异常：当PL/SQL程序违反Oracle规则或超越系统限制时隐式引发
用户定义异常：用户可以在PL/SQL块的声明部分定义异常，自定义的异常通过RAISE语句显式引发
Oracle 中预定义的异常有 21 种，这里我们选两种进行说明。
NO_DATA_FOUND：使用select into方式查询赋值，期待一行记录但未返回记录
TOO_MANY_ROWS：执行select into方式查询赋值，期待一行记录但返回多行记录
异常处理的语法结构如下：
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';
--字数换算为吨数
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;
1.5 条件判断
PL/SQL 中同样还提供了条件判断的功能，供我们来实现分支的逻辑，一共有下面三种形式。
语法一：
if 条件 then
业务逻辑
end if;
语法二：
if 条件 then
业务逻辑
else
业务逻辑
end if;
语法三：
if 条件 then
业务逻辑
elsif 条件 then
业务逻辑
else
业务逻辑
end if;
我们通过下面阶梯水费的例子来学习具体使用。
declare
v_price1 number(10,2);--不足 5 吨的单价
v_price2 number(10,2);--超过 5 吨不足 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_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;
1.6 循环
PL/SQL 中同样也提供了循环的功能，分别有无条件循环、条件循环和 for 循环三种，下面我们分别给出这三种形式，并以输出 1 到 100 的数字作为例子进行说明。
无条件循环：
loop
--循环语句
end loop;
无条件循环例子：
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;
条件循环：
while 条件
loop
end loop;
条件循环例子：
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;
for 循环：
for 变量 in 起始值..终止值
loop
end loop;
for 循环例子：
begin
for v_num in 1..100
loop
dbms_output.put_line(v_num);
end loop;
end;
1.7 游标
下面我们再来看游标，游标是 Oracle 为用户开设的一个数据缓冲区，用于存放 SQL 语句的执行结果，可以理解为 PL/SQL 中的结果集，当然概念也就和 Java 中的结果集类似了。
游标声明语法如下：
cursor 游标名称 is SQL语句;
游标使用语法如下：
open 游标名称
loop
fetch 游标名称 into 变量
exit when 游标名称%notfound
end loop;
close 游标名称
具体例子我们可以看对于价格表的打印。
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;
有时游标定义时的 SQL 语句中的条件不是固定的，而是需要传入具体的参数进去的，那这种就是带参数的游标，需要在游标定义时的游标名称后面增加参数定义，然后就可以在游标定义的 SQL 中使用该参数了，使用游标时传入该参数就可以了，我们还是以具体例子来进行说明。
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;
最后我们还可以使用 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;
2.存储函数
2.1 什么是存储函数
存储函数是 Oracle 数据库中的自定义函数，接受一个或多个参数，返回一个结果，在存储函数中我们可以使用 PL/SQL 进行逻辑的处理。
2.2 存储函数语法结构
存储函数创建和修改的语法如下：
CREATE [ OR REPLACE ] FUNCTION 函数名称
(参数名称 参数类型, 参数名称 参数类型, ...)
RETURN 结果变量数据类型
IS
变量声明部分;
BEGIN
逻辑部分;
RETURN 结果变量;
[EXCEPTION
异常处理部分]
END;
2.3 案例
我们通过具体的例子来学习存储函数：
create function fn_getaddress(v_id number)
return varchar2
is
v_name varchar2(30);
begin
select name into v_name from t_address where id=v_id;
return v_name;
end;
定义好了之后，我们可以使用下列语句进行测试：
select fn_getaddress(3) from dual
有了存储函数之后，我们就可以在查询语句中进行使用了，之前需要多表关联查询的现在使用存储函数查询单表就可以了。
select id 编号,name 业主名称,fn_getaddress(addressid) 地址 from t_owners
3.存储过程
3.1 什么是存储过程
存储过程是被命名的 PL/SQL 块，存储于数据库当中，是数据库对象的一种，可以被应用程序调用，执行相应的业务逻辑。
存储过程和存储函数都可以封装一定的业务逻辑并返回结果，两者的区别如下：
1.存储函数有返回值，且必须返回；而存储过程没有返回值，可以通过传出参数返回多个值；
2.存储函数可以在select语句中直接使用，而存储过程不行，存储过程一般是被应用程序所调用；
3.存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务代码。
3.2 存储过程语法结构
存储过程创建或修改的语法如下：
CREATE [ OR REPLACE ] PROCEDURE 存储过程名称
(参数名 类型, 参数名 类型, 参数名 类型)
IS|AS
变量声明部分;
BEGIN
逻辑部分
[EXCEPTION
异常处理部分]
END;
其中参数只指定类型，不要指定长度。同时参数名和类型之间还可以指定参数模式，用于指定是传入参数还是传出参数，参数模式一共有下面三种。
IN：传入参数（默认）
OUT：传出参数，主要用于返回程序运行结果
IN OUT：传入传出参数
3.3 案例
我们通过具体的案例来学习存储过程，主要是分两类，一类是不带参数的存储过程，另一类则是带参数的存储过程，下面具体来看。
不带传出参数的存储过程
定义：
--增加业主信息序列
create sequence seq_owners;
--增加业主信息存储过程
create or replace procedure pro_owners_add
(
v_name varchar2,
v_addressid number,
v_housenumber varchar2,
v_watermeter varchar2,
v_type number
)
is
begin
insert into T_OWNERS values( 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);
或者
begin
pro_owners_add('赵伟',1,'999-3','132-7',1);
end;
而且我们还可以使用 Java 来调用存储过程，如下：
public static void add(Owners owners){
java.sql.Connection conn=null;
java.sql.CallableStatement stmt=null;
try {
conn=BaseDao.getConnection();
stmt=conn.prepareCall("{call pro_owners_add(?,?,?,?,?)}");
stmt.setString(1, owners.getName());
stmt.setLong(2, owners.getAddressid());
stmt.setString(3, owners.getHousenumber());
stmt.setString(4, owners.getWatermeter());
stmt.setLong(5, owners.getOwnertypeid());
stmt.execute();
} catch (SQLException e) {
e.printStackTrace();
}finally {
BaseDao.closeAll(null, stmt, conn);
}
}
带传出参数的存储过程
带传出参数的存储过程则是需要在定义存储过程时，对于传出参数增加 OUT 来说明是传出参数，定义可以如下：
--增加业主信息存储过程
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
begin
select seq_owners.nextval into v_id from dual;
insert into T_OWNERS values( v_id,v_name,v_addressid,v_housenumber,v_watermeter,sysdate,v_type );
commit;
end;
同样的调用我们可以如下：
declare
v_id number;--定义传出参数的变量
begin
pro_owners_add('王旺旺',1,'922-3','133-7',1,v_id);
DBMS_OUTPUT.put_line('增加成功,ID:'||v_id);
end;
也可以使用 Java 来进行调用：
public static long add(Owners owners){
long id=0;
java.sql.Connection conn=null;
java.sql.CallableStatement stmt=null;
try {
conn=BaseDao.getConnection();
stmt=conn.prepareCall("{call pro_owners_add(?,?,?,?,?,?)}");
stmt.setString(1, owners.getName());
stmt.setLong(2, owners.getAddressid());
stmt.setString(3, owners.getHousenumber());
stmt.setString(4, owners.getWatermeter());
stmt.setLong(5, owners.getOwnertypeid());
stmt.registerOutParameter(6, OracleTypes.NUMBER);//注册传出参数类型
stmt.execute();
id=stmt.getLong(6);//提取传出参数
} catch (SQLException e) {
e.printStackTrace();
}finally {
BaseDao.closeAll(null, stmt, conn);
}
return id;
}
4.触发器
4.1 什么是触发器
数据库触发器是一个与表关联的、存储的 PL/SQL 程序，每当有特定的数据操作语句（增加、删除和修改）在指定的表上执行时，Oracle 都会自动触发先前已经定义好的触发器。
触发器可用于下列场景：
数据确认
实施复杂的安全性检查
做审计，跟踪表上所做的数据操作等
数据的备份和同步
触发器的分类：
前置触发器（BEFORE）：语句执行之前触发
后置触发器（AFTER）：语句执行之后触发
4.2 创建触发器的语法
创建触发器的语法如下：
CREATE [or REPLACE] TRIGGER 触发器名
BEFORE | AFTER
[DELETE ][[or] INSERT] [[or]UPDATE [OF 列名]]
ON 表名
[FOR EACH ROW ][WHEN(条件) ]
declare
……
begin
PLSQL块
end;
需要注意的一点是，关于定义语句中的 FOR EACH ROW 选项，指的是该触发器是一个行级触发器，还是一个语句级触发器。行级触发器是指执行语句涉及到多少条数据，那么该触发器就会被触发多少次，而语句级触发器则是只会触发一次。
还有需要注意的一个概念，就是伪记录变量，它一共有 :new 和 :old 两个值，分别表示语句修改前后的记录，增加、修改和删除所对应的伪记录变量的值如下：
触发语句 :old :new
insert 所有字段都是空(null) 将要插入的数据
update 更新以前该行的值 更新后的值
delete 删除以前该行的值 所有字段都是空(null)
4.3 案例
同样的，我们还是通过具体的例子来进行学习，对于前置触发器和后置触发器分别来看。
前置触发器：
create or replace trigger tri_account_updatenum1
before
update of num1
on t_account
for each row
declare
begin
:new.usenum:=:new.num1-:new.num0;
end;
如果我们想在用户执行 SQL 的同时，根据我们的意愿修改对应行中字段的值，就需要使用前置触发器，不能使用后置触发器，因为语句执行完之后，是不允许再在触发器中修改对应记录的值的。
后置触发器：
后置触发器我们是想使用一张表来记录字段修改前后的值，首先创建一张日志表：
--创建业主名称修改日志表:用于记录业主更改前后的名称
create table t_owners_log
(
updatetime date,
ownerid number,
oldname varchar2(30),
newname varchar2(30)
);
然后再定义触发器来记录字段修改前后的值：
--创建后置触发器，自动记录业主更改前后日志
create trigger tri_owners_log
after
update of name
on t_owners
for each row
declare
begin
insert into t_owners_log values(sysdate,:old.id,:old.name,:new.name);
end;
当我们对某行记录进行修改操作之后，就能在日志表中看到修改前后的值了。
5.总结
这篇博客主要就是介绍的 PL/SQL 以及与之相关的存储函数、存储过程和触发器，学完之后感觉其实在数据库中也能做很多事情，不一定非要在应用程序中实现。Oracle 的学习就先告一段落了，整理完之后自己也感觉很踏实，以前一直觉得 Oracle 好难，连安装都不简单，现在整理完就有一种突破的感觉，看来事情越困难，完成之后成就感越大，是积累才有这样的突破，慢慢积累吧。
