存储过程与触发器实验

第一章存储过程与触发器实验

实验目的

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

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

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．将触发器添加到数据库服务器上