数据库T-SQL上机实验及代码

实验5T-SQL程序、函数、存储过程与触发器

【实验目的与要求】

1、掌握T-SQL程序编写的基本方法；

2、掌握SQLSERVER中自定义函数、存储过程、触发器的使用。

【实验准备】

实验数据：实验2创建的数据表及录入的数据记录

【实验内容与步骤】

1.在数据库中创建函数Distance(@x1 float,@y1 float,@x2 float,@y2 float)用于计算输入两点（@x1,@y1）和(@x2,@y2)的距离；

create function Distance(@x1float,@y1float,@x2float,@y2float)

returns float

as

begin

declare@distance float

select@distance=sqrt((@x2-@x1)*(@x2-@x1)+(@y2-@y1)*(@y2-@y1))

return@distance

end

2.编写T-SQL程序，检查界点表是否存在距离工作区中心点大于1000m的点，如果有，则显示界址点及其距中心点的距离；

declare@x_center float,@y_center float

select@x_center=(min(x)+max(x))/2,

@y_center=(min(y)+max(y))/2 from dbo.界址点

declare@x float,@y float,@Distance float

if exists(select*from dbo.界址点where dbo.Distance(x,y,@x_center,@y_center)>10 )

begin

select点号,x,y,dbo.Distance(x,y,@x_center,@y_center)from dbo.界址点

end

else

print'不存在'

断宗地表中是否存在相同宗地号，若存在则返回错误；然后判断街坊号、宗地图号是否合法，若不合法则提示错误；判断完成后，实现数据插入操作。测试存储过程sp_InsertZD。

create proc sp_insertZD@宗地号int,@宗地图号int,@所属街坊号int,@Result varchar(50) output

AS

BEGIN

IF Exists(select*from dbo.宗地where宗地号=@宗地号)

set@Result='错误'

ELSE

begin

IF Exists(select*from dbo.宗地where宗地图号=@宗地图号and所属街坊号=@所属街坊号)

set@Result='错误'

else

insert into dbo.宗地(宗地号,宗地图号,所属街坊号)values(@宗地号,@宗地图号,@所属街坊号)

set@Result='添加成功'

end

End

4.创建存储过程sp_DeleteCLY，根据输入的测量员工号，判断数据库中是否存在该测量员测量的宗地图，若存在，则不允许删除，并给出提示，否则删除该测量员记录。测试存储过程sp_DeleteCLY。

create procedure sp_DeleteCLY@工号int,@result varchar(50)output

as

begin

if exists(select图号from dbo.宗地图纸where测量员_工号=@工号)

set@result='不允许删除'

else

begin

delete from dbo.测量员where工号=@工号

End

End

declare@test varchar(50)

exec sp_DeleteCLY43002,@test OUTPUT

PRINT@test

5.修改sp_DeleteCLY存储过程，在其中添加条件：若测量员为高级工程师，则不允许删除。测试存储过程sp_DeleteCLY。

alter proc dbo.sp_DeleteCLY@工号varchar(20),@result varchar(20)output

as

begin

declare@职称varchar (20)

select@职称=(select职称from测量员where@工号=工号)

if exists(select*from测量员where@职称='高级工程师')

set@result='不允许删除'

end

declare@test varchar(20)

exec sp_DeleteCLY430002,@test OUTPUT

PRINT@test

6.为界址点创建触发器TG_InsertJZD，对当前插入的记录进行有效检查，若当前界址点距离工作区中心超过1000m，则不允许插入，否则完成插入，并给出相应提示。

create trigger TG_InsertJZD

on界址点

after insert

as

begin

declare@x_min float,@x_max float,@y_min float,@y_max float

select@x_min=min(x),@x_max=max(x),@y_min=min(y),@y_max=max(y)

from界址点

declare@x_center float,@y_center float

select@x_center=(@x_min+@x_max)/2,@y_center=(@y_min+@y_max)/2

declare@x float,@y float,@点号int

if exists(select*from界址点where dbo.Distance(@x,@y,@x_center,@y_center)>1000)

print ('插入不允许')

else

insert into dbo.界址点(点号,x,y)

values(@点号,@x,@y)

End

7.修改TG_InsertJZD，在其中添加是否超出工作范围的判断，即利用实验四中创建的V_Envelope视图，从当前界址点中计算工作区范围（MBR），并判断当前插入点是否超出MBR，若超出则提示错误且不允许插入，否则完成插入，并给出提示。

USE[ff]

GO

/****** Object: Trigger [dbo].[TG_InsertJZD] Script Date: 04/08/2013 18:25:23 ******/ SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

ALTER trigger[dbo].[TG_InsertJZD]

on[dbo].[界址点]

after insert

as

begin

declare@x_min float,@x_max float,@y_min float,@y_max float

select@x_min=min(x),@x_max=max(x),@y_min=min(y),@y_max=max(y)

from界址点

declare@x_center float,@y_center float

select@x_center=(@x_min+@x_max)/2,@y_center=(@y_min+@y_max)/2

declare@x float,@y float,@点号int

if exists(select*from界址点where dbo.Distance(@x,@y,@x_center,@y_center)>1000)

print ('插入不允许')

else

if exists(select*from dbo.V_Envelope where (@xx2or@yy2))

print ('插入不允许')

else

insert into dbo.界址点(点号,x,y)

values(@点号,@x,@y)

end