存储过程与触发器实验

第一章 存储过程与触发器实验
实验目的
1．理解存储过程的工作原理和作用。
2．掌握存储过程设置和程序设计过程。
3．理解触发器的工作原理和作用。
4．掌握触发器编写方法。

实验环境
采用IBM DB2或Sybase数据库管理系统作为实验平台。其中，DB2可以采用DB2
Express-C或DB2 V8 Enterprise。
实验完成人：李肇臻，谢锦

实验内容

一、存储过程实验
1．针对下面2个完整性约束条件，建立存储过程，实现当数据导入或更新时，可以自动修
改拥塞率、半速率话务量比例
(1) 拥塞率 ＝ 拥塞数量／呼叫数量
(2) 半速率话务量比例 ＝ 半速率话务量／全速率话务量
在DB2CMD中运行，win7用管理员权限。
create procedure M()
language SQL
begin
update CALLDATA
set "callcongs"="congsnum"/"callnum"
where "congsnum"<>0 and "callnum"<>0;
update CALLDATA
set "rate"="thtraff"/"traff"
where "thtraff"<>0 and "traff"<>0;
end @
2．将存储过程添加到数据库服务器上
3．在客户端编写调用存储过程的主程序
4．运行客户端程序，调用存储过程，观察存储过程执行过程和数据更新情况；调用就用db2
CALL M()

二、触发器实验
1. 针对下列约束条件，分别建立1个触发器：
1）每个小区/扇区最多占用14个TCH频点，合法频点范围在[1,60]之间。当向小区中新加
入频点时，如果小区中现有频点数目已达到14个，则用新加入的频点替换现有频点中的最
小频点；当修改或新加入频点时，如果发现频点不在合法范围内，则输出提示信息，并拒
绝该操作。

create trigger first_1 after insert on FREQUENCY
referencing new row as nrow
for each row
when(nrow."CellID" in(select "CellID"
from FREQUENCY
group by "CellID"
having
count("Freq")=15))
delete from FREQUENCY
where(("CellID","Freq") in(select "CellID",min("Freq")
from FREQUENCY
where("CellID"=nrow."CellID" and "Freq" <>nrow."Freq")
group by "CellID"))

create trigger first_2 before insert on FREQUENCY
referencing new row as nrow
for each row
when
(
nrow."Freq"<1 or nrow."Freq">60
)
signal sqlstate '80001' set message_text='Freq不合法';

create trigger first_3 before update on FREQUENCY
referencing new row as nrow
for each row
when
(
nrow."Freq"<1 or nrow."Freq">60
)
signal sqlstate '80001' set message_text='FREQ不合法';
2）每个小区有且只能有一个BCCH频点，合法范围在[70-90]之间。当修改或新加入BCCH
频点时，如果发现频点不在合法范围内，则输出提示信息，并拒绝该操作。

create trigger second_1 before insert on CELL
referencing new row as nrow
for each row
when((select count("Bcch")
from CELL
where "CellID"=nrow."CellID")=1
or (nrow."Bcch">90 or nrow."Bcch"<70))
signal sqlstate '80001' set message_text='Bcch不合法';

create trigger second_2 before insert on CELL
referencing new row as nrow
for each row
when( nrow."Bcch">90 or nrow."Bcch"<70 )
signal sqlstate '80001' set message_text='Bcch不合法';

create trigger second_3 before update on CELL
referencing new row as nrow
for each row
when( nrow."Bcch">90 or nrow."Bcch"<70 )
signal sqlstate '80001' set message_text='Bcch不合法'
3）每个小区与其邻小区的BCCH不允许相同。当修改某小区的BCCH频点值时，如果发现
修改后与其它邻区的BCCH频点相同，则则输出提示信息，并拒绝该操作。
create trigger three_1 after update on CELL
referencing new row as nrow
for each row
when(nrow."Bcch"
in
(select "Bcch"
from CELL,NEIGHBOR
where nrow."CellID"="AdjcellID")
)
signal sqlstate '80001' set message_text='Bcch修改不合法';
2．将触发器添加到数据库服务器上
3．向数据库添加新的TCH、BCCH频点数据，或修改已有TCH、BCCH频点数据，观察当
违反上述3条约束时，触发器的执行情况。
第一题：
插入一个大于60的频点：
insert into FREQUENCY
values(9012,89);

第二题：
update CELL
set "Bcch"=99
where "CellID"=9011
第三题：
9152有个相邻小区9031,9031的Bcch是70.
update CELL
set "Bcch"=70
where "CellID"=9152

实验总结
初步了解了存储过程的作用，并尝试编写一个存储过程，了解触发器的工作原理，更深
入的掌握触发器的编写方法。