Oracle-day04 下

dopost

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

　　
CREATE [ OR REPLACE ] PROCEDURE
　　
存储过程名称
　　
（参数名 类型, 参数名 类型, 参数名 类型）
　　
IS|AS
　　
变量声明部分;
　　

　　
BEGIN
　　
逻辑部分
　　
[EXCEPTION
　　
异常处理部分]
　　
END;
　　
参数只指定类型，不指定长度
　　
过程参数的三种模式：
　　
IN 传入参数（默认）
　　
OUT 传出参数 ，主要用于返回程序运行结果
　　
IN OUT 传入传出参数
　　

（三）案例
　　1.创建不带传出参数的存储过程：添加业主信息
　　

-增加业主信息序列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
　　
begin
　　

　　
insert into T_OWNERS
　　
values( seq_owners.nextval,v_name,v_addressid,v_housenumb
　　
er,v_watermeter,sysdate,v_type );
　　
commit;
　　
end;
　　

　　PL/SQL 中调用存储过程
　　call pro_owners_add('赵伟',1,'999-3','132-7',1);
　　JDBC 调用存储过程
　　

　　
/**
　　
* 增加
　　
* @param owners
　　
*/
　　
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);
　　
}
　　
}
　　

2创建带传出参数的存储过程

　　需求：添加业主信息，传出参数为新增业主的>　　

-增加业主信息存储过程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_watermete
　　
r,sysdate,v_type );
　　
commit;
　　
end;
　　

　　PL/SQL 调用该存储过程
　　

declare　　
v_id number;--定义传出参数的变量
　　
begin
　　
pro_owners_add('王旺旺',1,'922-3','133-7',1,v_id);
　　
DBMS_OUTPUT.put_line('增加成功,ID:'||v_id);
　　
end;
　　

　　执行成功后输出结果：

　　JDBC 调用存储过程
　　

　　
/**
　　
* 增加
　　
* @param owners
　　
*/
　　
public static long add(Owners owners){

　　
long>　　
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>　　
}
　　

　　四、触发器
　　（一）什么是触发器
　　数据库触发器是一个与表相关联的、存储的 PL/SQL 程序。每当一个特定的数据操作语句(Insert,update,delete)在指定的表上发出时，Oracle 自动地执行触发器中定义的语句序列。
　　触发器可用于
　　l 数据确认
　　l 实施复杂的安全性检查
　　l 做审计，跟踪表上所做的数据操作等
　　l 数据的备份和同步
　　触发器分类
　　l 前置触发器（BEFORE）
　　l 后置触发器（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 作用是标注此触发器是行级触发器
　　语句级触发器
　　在触发器中触发语句与伪记录变量的值

　　（三）案例
　　1.前置触发器
　　需求：当用户输入本月累计表数后，自动计算出本月使用数 。
　　代码：
　　

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;
　　

2.后置触发器
　　需求：当用户修改了业主信息表的数据时记录修改前与修改后的值
　　

-创建业主名称修改日志表:用于记录业主更改前后的名称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;
　　

　　测试：
　　

--更新数据
　　
update t_owners set name='杨小花' where>　　
commit;
　　
--查询日志表
　　
select * from t_owners_log;
　　

　　五、综合案例
　　1.编写 PL/SQL，用水吨数 12吨，业主类型为 1，计算阶梯水费。
　　思路分析：
　　水费是实行阶梯计算的，我们查询价格表中业主类型为 1 的水费价格记录

　　minnum 为下限值 ，maxnum 为上限值。上边的记录的含义是
　　5 吨以下的价格为 2.45
　　超过 5 吨不足 10 吨的价格为 3.45
　　超过 10 吨以上的价格为 4.45
　　如果吨数为 12。计算如下：

　　考虑到阶梯的层次可能是不确定的，所以我们需要通过游标查询出阶梯价格记录，然后计算每一阶梯的水费，然后相加。伪代码如下：
　　

金额=0　　
循环价格表{
　　
if( 上限值为空 或者 总吨数　　

　　
v_usenum2 number(10,2);--总吨数
　　
v_money number(10,2);--总金额
　　
cursor cur_pricetable(v_type number) is select * from
　　
t_pricetable where ownertypeid=v_type;--价格游标
　　
v_pricetable t_pricetable%rowtype;--每阶梯价格对象
　　
begin
　　
v_ownertypeid:=1;
　　
v_usenum2:=12;
　　
v_money:=0;
　　
for v_pricetable in cur_pricetable(v_ownertypeid)
　　
loop
　　
if v_pricetable.maxnum is null or
　　
v_usenum2