Oracle-day04 下

最新推荐文章于 2021-04-10 20:33:16 发布

weixin_34268310

最新推荐文章于 2021-04-10 20:33:16 发布

阅读量288

点赞数 2

文章标签： java 数据库

原文链接：http://blog.51cto.com/13587708/2125561

版权

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


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

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

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

-增加业主信息序列[/align]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创建带传出参数的存储过程
需求：添加业主信息，传出参数为新增业主的 ID

-增加业主信息存储过程[/align]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;

执行成功后输出结果：
Oracle-day04 下
JDBC 调用存储过程


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

四、触发器
（一）什么是触发器
数据库触发器是一个与表相关联的、存储的 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 作用是标注此触发器是行级触发器
语句级触发器
在触发器中触发语句与伪记录变量的值
Oracle-day04 下

（三）案例
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.后置触发器
需求：当用户修改了业主信息表的数据时记录修改前与修改后的值

-创建业主名称修改日志表:用于记录业主更改前后的名称[/align]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 id=3;
commit;
--查询日志表
select * from t_owners_log;

五、综合案例
1.编写 PL/SQL，用水吨数 12吨，业主类型为 1，计算阶梯水费。
思路分析：
水费是实行阶梯计算的，我们查询价格表中业主类型为 1 的水费价格记录
Oracle-day04 下
minnum 为下限值，maxnum 为上限值。上边的记录的含义是
5 吨以下的价格为 2.45
超过 5 吨不足 10 吨的价格为 3.45
超过 10 吨以上的价格为 4.45

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

金额=0
循环价格表{
if( 上限值为空 或者 总吨数<上限值
) -- 最高阶梯
{
//此为最后阶梯 ，数量为超过上限值部分的吨数
金额=金额+ 价格*（总吨数- 上限值）
退出循环
}
else
{
//此为非最后阶梯 ，数量为区间内的吨数
金额=金额+ 价格*（上限值- 下限值）
}
}

语句：

declare
v_ownertypeid number;--业主类型 ID

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<=v_pricetable.maxnum then
--最后阶梯
（总吨数-下限值）*价格
v_money:=v_money+
v_pricetable.price*(v_usenum2-v_pricetable.minnum);
exit;
else
--非最后阶梯 (上限值-下限值)* 价格
v_money:=v_money+
v_pricetable.price*(v_pricetable.maxnum-v_pricetable.minn
um);
end if;
end loop;
DBMS_OUTPUT.put_line('阶梯水费金额：'||v_money);
end;

2.存储函数综合案例：创建计算阶梯水费的函数，参数为业主类型、吨数。

create or replace function fn_calmoney(v_ownertypeid

number,v_usenum2 number)
return number
is
v_pricetable t_pricetable%rowtype;--价格行对象
v_money number(10,2);--金额
cursor cur_pricetable(v_type number) is select * from
t_pricetable where ownertypeid=v_type order by minnum;--定
义游标
begin
v_money:=0;--金额
for v_pricetable in cur_pricetable(v_ownertypeid)
loop
--计算阶梯水费
--如果水费小于最大值，或最大值为 null 表示此阶梯为最后一个阶梯，
--价格*（总吨数-此阶梯下限值）
if v_usenum2<= v_pricetable.maxnum or
v_pricetable.maxnum is null then
v_money:=v_money+ v_pricetable.price* ( v_usenum2 -
v_pricetable.minnum);
exit;
else -- 价格*（此阶梯上限值-此阶梯下限值）
v_money:=v_money+ v_pricetable.price*
(v_pricetable.maxnum-v_pricetable.minnum );
end if;
end loop;
return v_money;
end;
测试此函数：
select fn_calmoney(1,12) from dual;

3.触发器综合案例：当用户输入本月累计数后，自动计算阶梯水费。

create or replace trigger tri_account_updatenum1
before
update of num1
on t_account
for each row
declare
v_usenum2 number(10,2);--吨数
begin
--使用数赋值
:new.usenum:=:new.num1-:new.num0;
v_usenum2:= round( :new.usenum/1000,3);--计算吨数
:new.money:=fn_calmoney(:new.ownertype,v_usenum2);--对金
额列赋值
end ;

修改某记录，观察结果。
4.存储过程综合案例。
需求：增加业主信息时，同时在账务表（account）增加一条记录，年份与月份为当前日期的年月，初始值（num0）为 0,其它字段信息（区域）与 t_owners表一致
难点分析：

如何取得年和月用 to_char()函数
如何取得区域 ID 参数中没有直接提供区域 ID，我们可以通过 addressid
到 address 表查询
创建存储过程语句：

create or replace procedure pro_owners_add

(
)
v_name varchar2,
v_addressid number,
v_housenumber varchar2,
v_watermeter varchar2,
v_type number,
v_ownersuuid out number
is
v_area number;--区域编号
v_year char(4);--年份
v_month char(2);--月份
begin
--提取序列值到变量
select seq_owners.nextval into v_ownersuuid from dual;
--根据地址编号查询区域编号
select areaid into v_area from t_address where
id=v_addressid;
--年份
v_year:=to_char(sysdate ,'yyyy');
--月份
v_month:=to_char(sysdate,'mm');
--增加业主信息
insert into t_owners
values( v_ownersuuid,v_name,v_addressid,v_housenumber,v_w
atermeter,sysdate,v_type );
--增加账务表信息
insert into t_account
(id,owneruuid,ownertype,areaid,year,month,num0)
values
(seq_account.nextval,v_ownersuuid,v_type,v_area,v_year,
v_month,0 );
commit;

exception
when NO_DATA_FOUND then
v_ownersuuid:=-1;
rollback;
end;

六、总结
（一）知识点总结
（二）上机任务布置

转载于:https://blog.51cto.com/13587708/2125561