一、PL/SQL
1.1什么是 PL/SQL
pl/sql说白了就是数据库中的一段脚本代码。
PL/SQL
（
Procedure Language/SQL
）是
Oracle
对
sql
语言的过程化扩展，指
在
SQL
命令语言中增加了过程处理语句（如分支、循环等），使
SQL
语言具有
过程处理能力。把
SQL
语言的数据操纵能力与过程语言的数据处理能力结合起
来，使得
PLSQL
面向过程但比过程语言简单、高效、灵活和实用。
基本语法结构
[
declare
--
声明变量
]
begin
--
代码逻辑
[
exception
--
异常处理
]
end
;
1.2变量
变量是存储数据的，数据库的声明变量和java的声明变量是相反的。赋值是：=的。
声明变量的语法：
变量名 类型（长度）；
变量赋值的语法：
变量名
:=
变量值
变量的声明
需求：
声明变量水费单价、水费字数、吨数、金额。
对水费单价、字数、进行赋值 。吨数根据水费字数换算，规则为水费字数除以
1000
，并且四舍五入，保留两位小数。计算金额，金额
=
单价
*
吨数。
输出单价 、数量和金额。
--
变量的用法
--
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了。
语法结构：
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属性类型--2种(推荐记录型)
属性类型有2种，引用型和记录型。
%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
;
%ROWTYPE
记录型
，上例中的例子可以用下面的代码代替
作用
:
标识某个表的行记录类型
查询结果：
记录型就是代表某个表某一行的类型。把这个表中整个一行的记录（列）赋值给一个变量。
引用型就是提取几个列就要写几个变量。
--
变量的用法
--
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 的时候，就是没有返回行，就会报
NO_DATA_FOUND的错误，如果返回的行多处一行，就会报
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条件判断--3种写法
基本语法
1
if
条件
then
业务逻辑
end if
;
基本语法
2
if
条件
then
业务逻辑
else
业务逻辑
end if
;
基本语法
3
if
条件
then
业务逻辑
elsif
条件
then
业务逻辑
else
业务逻辑
end if
;
需求：设置三个等级的水费
5
吨以下
2.45
元
/
吨
5
吨到
10
吨部分
3.45
元
/
吨
，
超过
10
吨部分
4.45
，根据使用水费的量来计算阶梯水费。
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循环--3种写法
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
;
2
、条件循环
有条件循环就是多了一个while条件，其他的都是一样的。就是这个while条件成立的时候才循环。跟无条件循环刚好相反，它是条件不成立才循环。
语法结构
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
;
3
、
for
循环
for循环很简单，变量都省了，我们一般用for循环和无条件loop循环较多。
基本语法
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.7游标
1.什么是游标
游标就是一个结果集，用来展示你的查询结果的，这个结果集可以用来循环，游标相当于jdbc中的结果集，这个结果集通过next就可以读取下一行的数据，读取一行就取出一行。有时候我们需要把查询的结果作为一个变量，变量的时候我们讲过select。。into要求你的每一行必须有一条记录，如果查询的多条记录的话则需要游标了。
游标是系统为用户开设的一个数据缓冲区
,
存放
SQL
语句的执行结果。我们
可以把游标理解为
PL/SQL
中的结果集。
打开游标，逐行提取每一条记录，打开游标的时候有个小指针指向第一条记录的上面，提取的时候就会指向第一条，每提一行就往下走，最后需要关闭游标。
2.语法结构及示例
在声明区声明游标，语法如下：
cursor
游标名称
is
SQL 语句;
使用游标语法
open
游标名称
loop
fetch
游标名称
into
变量
exit
when
游标名称%notfound
end loop;
close
游标名称
先用cursor声明游标，然后再使用open使用游标，然后进行循环，通过fetch提取游标into给变量。然后游标走到（游标名称%notfound）就退出。
还不如用for循环，都不用打开游标。
需求：打印业主类型为
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
;
运行结果如下：
3.带参数的游标---就是形参和实参
我们的查询语句的条件值有可能是在运行时才能决定的，比如性业主类型，
可能是运行时才可以决定，那如何实现呢？我们接下来学习带参数的游标，修改
上述案例
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
;
4. 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
;
二、存储函数
2.1什么是存储函数
存储函数又称为自定义函数。可以接收一个或多个参数，返回一个结果。
在函数中我们可以使用 P/SQL 进行逻辑的处理。
2.2存储函数语法结构
创建或修改存储过程的语法如下
:
CREATE [ OR REPLACE ] FUNCTION 函数名称
（参数名称 参数类型, 参数名称 参数类型, ...）
RETURN 结果变量数据类型
IS
变量声明部分;
BEGIN
逻辑部分;
RETURN 结果变量;
[EXCEPTION
异常处理部分]
END;
注意：参数类型只写类型，不写长度的。
下面is就是类似于pl/sql，其实后面的存储函数，储存过程，触发器都是以pl/sql为基础的。
2.3案例
需求：
创建存储函数，根据地址
ID
查询地址名称。
语句
:
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
输出内容
需求：查询业主
ID
，业主名称，业主地址，业主地址使用刚才我们创建的函数
来实现。
select id
编号
,
name
业主名称
,fn_getaddress(addressid)
地址
from
t_owners
查询结果如下：
三、存储过程
3.1什么是存储过程
存储函数类似于java中的方法，接收参数，返回结果。存储函数一般都用在查询中，存储函数一般返回一个值，存储过程可以返回多个值，它是通过传出参数返回的，存储过程本身是没有返回值的，没有return的写法，它可以通过传出参数像调用端传出多个参数。一般返回多个值的时候就用存储过程。有个局限性，存储函数可以直接在select语句中直接使用，存储过程通常是被应用程序（比如php，java，.net之类的）所调用。存储过程其实是对一段业务的封装，存储过程和我们三大框架是互斥的，他们是不能一起使用的，三大框架数据持久层hibernate已经帮我们解决了，我们根本不需要写存储过程。它的逻辑是要写到业务逻辑层中的，它是分层的。存储过程是吧逻辑写到数据库里，你的编程语言不写逻辑，直接去调用存储过程得到结果。
存储过程执行效率比三大框架的高，不需要打开链接，关闭链接啥的，当要求你的执行效率很高的时候，我们就用存储过程。
存储过程是被命名的 PL/SQL 块，存储于数据库中，是数据库对象的一种。
应用程序可以调用存储过程，执行相应的逻辑。
存储过程与存储函数都可以封装一定的业务逻辑并返回结果，存在区别如
下：
1
、存储函数中有返回值，且必须返回；而存储过程没有返回值，可以通过
传出参数返回多个值。
2
、存储函数可以在
select 语句
中直接使用，而存储过程不能。过程多数是
被应用程序所调用。
3
、存储函数一般都是封装一个查询结果，而存储过程一般都封装一段事务
代码。
3.2存储过程语法结构
存储过程语法结构和存储函数语法结构很相似，它里面没有return，声明部分is或者as，注意参数部分它可以传入和传出，传入参数的话，就在类型前面加上in，传出参数的话，就在类型前面加上out，加上in out的话就可以传入传出了。
创建或修改存储过程的语法如下
:
CREATE [ OR REPLACE ] PROCEDURE 存储过程名称
（参数名 类型, 参数名 类型, 参数名 类型）
IS|AS
变量声明部分;
BEGIN
逻辑部分
[EXCEPTION
异常处理部分]
END;
参数只指定类型，不指定长度
过程参数的三种模式：
IN 传入参数（默认）
OUT 传出参数 ，主要用于返回程序运行结果
IN OUT 传入传出参数
3.3案例
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
创建带传出参数的存储过程
带传出参数的存储过程调用的时候只能用begin ...end，不能用call。
需求：添加业主信息，传出参数为新增业主的
ID
--
增加业主信息存储过程
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
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.1什么是触发器
现实中的触发器，空调开关，遥控器，灯的开关，枪，特点就是触发某一操作，会被动触发另外操作。数据库的触发器是自动执行的存储过程，之前的存储过程需要通过应用程序去调用它。
数据库触发器是一个与表相关联的、存储的
PL/SQL
程序。每当一个特定的
数据操作语句
(Insert,update,delete)
在指定的表上发出时，
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
作用是标注此触发器是行级触发器
语句级触发器
注意：行级触发器就是加了这个
FOR EACH ROW这个关键字，语句及触发器就是没有加。
在触发器中触发语句与
伪记录变量
的值
伪记录变量主要有2种:old和：new，:old就是你修改记录之前的数据，它是代表一行数据，修改之前的数据可以通过:old.列名获取修改之前的数据，：new就是你修改之后的数据。
4.3案例
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 id
=
3
;
commit
;
--
查询日志表
select
*
from
t_owners_log;
五、综合案例
5.1编写 PL/SQL ，用水吨数 12 吨，业主类型为 1，计算阶梯水费。
思路分析：
水费是实行阶梯计算的，我们查询价格表中业主类型为
1
的水费价格记录
minnum
为下限值 ，
maxnum
为上限值。上边的记录的含义是
5
吨以下的价格为
2.45
超过
5
吨不足
10
吨的价格为
3.45
超过
10
吨以上的价格为
4.45
如果吨数为
12
。计算如下：
考虑到阶梯的层次可能是不确定的，所以我们需要通过游标查询出阶梯价格记
录，然后计算每一阶梯的水费，然后相加。伪代码如下：
金额
=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
;
5.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;
5.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
;
修改某记录，观察结果。
5.4存储过程综合案例。
需求：增加业主信息时，同时在账务表（account）增加一条记录，年份与月份
为当前日期的年月，初始值（num0）为 0,其它字段信息（区域）与 t_owners
表一致
难点分析：
1. 如何取得年和月 用 to_char()函数
2. 如何取得区域 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
;
1468
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
