------------------------------------------------------------------------
--功能说明：根据一个表中的两行的主键，把这两行的信息插入另一个表中形成一条记录
--创建人：peer
--创建时间：2012/5/16 17:33:09
------------------------------------------------------------------------
--修改说明：
--修改人：
--修改时间：
------------------------------------------------------------------------
if exists(select * from sysobjects where name='InsertOneSection' and [type]='P')
drop proc InsertOneSection
go
CREATE PROCEDURE [dbo].[InsertOneSection]
@SerialId1 int,
@serialId2 int
AS
BEGIN
declare @MaxSerial int
select @MaxSerial=max(serial) FROM section
declare @serial1 int
declare @name1 nvarchar(60)
declare @x1 int
declare @y1 int
declare @next1 int
declare @DateTime1 datetime
select @serial1=serial,@name1=[name],@x1=x,@y1=y,@next1=next,@DateTime1=[datetime] from dot whereserial=@SerialId1
declare @serial2 int
declare @name2 nvarchar(60)
declare @x2 int
declare @y2 int
declare @next2 int
declare @DateTime2 datetime
select @serial2=serial,@name2=[name],@x2=x,@y2=y,@next2=next,@DateTime2=[datetime] from dot whereserial=@SerialId2
declare @Length float
declare @LengthinFact int
select @length=sqrt(abs((@x1-@x2)*(@x1-@x2)+(@y1-@y2)*(@y1-@y2))),@LengthinFact=sqrt(abs((@x1-@x2)*(@x1-@x2)+(@y1-@y2)*(@y1-@y2)))
INSERT INTO [lcj].[dbo].[Section]
([Serial]
,[Serial1]
,[Name1]
,[X1]
,[Y1]
,[Next1]
,[DateTime1]
,[Serial2]
,[Name2]
,[X2]
,[Y2]
,[Next2]
,[DateTime2]
,[Length]
,[DateTime]
,[LengthinFact])
VALUES
(@MaxSerial+1
,@Serial1
,@Name1
,@X1
,@Y1
,@Next1
,@DateTime1
,@Serial2
,@Name2
,@X2
,@Y2
,@Next2
,@DateTime2
,@Length
,CONVERT(varchar(100), GETDATE(), 20)
,@LengthinFact)
END
-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
//点表添加一列‘时间’，并赋值
alter table dot add [datetime] datetime null
update dot set [datetime]='2008-01-01'
//更新点表的点名称
update dot set [name]=[name] + cast(serial as nvarchar(3))
//根据点表中的点名修改线表中起始点的名称
update section set name1 = (select [name] from dot where dot.x=section.x1 and dot.y=section.y1)
//根据点表中的点名修改线表中终点的名称
update section set name2 = (select [name] from dot where dot.x=section.x2 and dot.y=section.y2)
//删除线表中的异常点信息
delete section where name1 is NULL or name2 is NULL
//删除孤立路段
delete from section where serial2 not in (select serial from dot)
//为section添加一列表示路段在图形中占用的像素长度
alter table section add LengthinFact int
//修改像素长度存储过程
create procedure UpadateSectionLenghinFact
as
begin
declare mycursor cursor for
select sqrt(abs(x1-x2)*abs(x1-x2)+abs(y1-y2)*abs(y1-y2)) as inFact,serial from section
open mycursor
declare @inFact int
declare @serial int
fetch next from mycursor into @inFact,@serial
while(@@fetch_status=0)
begin
update section set LengthinFact=@inFact whereserial=@serial
fetch next from mycursor into @inFact,@serial
end
close mycursor
deallocate mycursor
end
//
exec UpadateSectionLenghinFact
//根据点表中的点名修改线表中起始点的名称
create procedure UpadateSectionName
as
begin
declare mycursor cursor for
select x,y,[name] from dot
open mycursor
declare @x int
declare @y int
declare @name nvarchar(60)
fetch next from mycursor into @x,@y,@name
while(@@fetch_status=0)
begin
update section set name1=@name where x1=@x and y1=@y
update section set name2=@name where x2=@x and y2=@y
fetch next from mycursor into @x,@y,@name
end
close mycursor
deallocate mycursor
end
//
exec UpadateSectionName
//修改section表的serial1 和serial2
create procedure [dbo].[UpdateSectionserial]
as
begin
declare mycursor cursor for
select x,y,serial from dot
open mycursor
declare @x int
declare @y int
declare @serial int
fetch next from mycursor into @x,@y,@serial
while(@@fetch_status=0)
begin
update section set serial1=@serial wherex1=@x andy1=@y
update section set serial2=@serial wherex2=@x andy2=@y
fetch next from mycursor into @x,@y,@serial
end
close mycursor
deallocate mycursor
end
//
exec [dbo].[UpdateSectionserial]
//在点表Dot上创建一个删除一个点的触发器
drop trigger delonedot
Create trigger DelOneDot
On Dot
for Delete
As
Delete section
From section br , Deleted d
Where br.serial1=d.serial or br.serial2=d.serial
//在点表Dot上创建一个修改点名称的触发器
Create Trigger UpdateDotName
On Dot
for Update
As
if Update([Name])
begin
Update section
Set Name1=i.[Name]
From section br , Deleted d ,Inserted i
Where br.x1=d.x and br.y1=d.y
Update section
Set Name2=i.[Name]
From section br , Deleted d ,Inserted i
Where br.x2=d.x and br.y2=d.y
end
//插入一个新路段
if exists(select * from sysobjects where name='InsertOneSection' and [type]='P')
drop proc InsertOneSection
go
CREATE PROCEDURE [dbo].[InsertOneSection]
---新点作为起点
@SerialId1 int,
---原来的终点作为终点
@serialId2 int
AS
BEGIN
declare @MaxSerial int
select @MaxSerial=max(serial) FROM section
declare @serial1 int
declare @name1 nvarchar(60)
declare @x1 int
declare @y1 int
declare @next1 int
declare @DateTime1 datetime
select @serial1=serial,@name1=[name],@x1=x,@y1=y,@next1=next,@DateTime1=[datetime] from dot whereserial=@SerialId1
declare @serial2 int
declare @name2 nvarchar(60)
declare @x2 int
declare @y2 int
declare @next2 int
declare @DateTime2 datetime
select @serial2=serial,@name2=[name],@x2=x,@y2=y,@next2=next,@DateTime2=[datetime] from dot whereserial=@SerialId2
declare @Length float
declare @LengthinFact int
select @length=sqrt(abs((@x1-@x2)*(@x1-@x2)+(@y1-@y2)*(@y1-@y2))),@LengthinFact=sqrt(abs((@x1-@x2)*(@x1-@x2)+(@y1-@y2)*(@y1-@y2)))
INSERT INTO [lcj].[dbo].[Section]
([Serial]
,[Serial1]
,[Name1]
,[X1]
,[Y1]
,[Next1]
,[DateTime1]
,[Serial2]
,[Name2]
,[X2]
,[Y2]
,[Next2]
,[DateTime2]
,[Length]
,[DateTime]
,[LengthinFact])
VALUES
(@MaxSerial+1
,@Serial1
,@Name1
,@X1
,@Y1
,@Next1
,@DateTime1
,@Serial2
,@Name2
,@X2
,@Y2
,@Next2
,@DateTime2
,@Length
,CONVERT(varchar(100), GETDATE(), 20)
,@LengthinFact)
END
--------------------------------------------------------------------------------------------------------------------
//添加一个新点后更新原来的
if exists(select * from sysobjects where name='UpdateOneSection' and [type]='P')
drop proc UpdateOneSection
go
CREATE PROCEDURE [dbo].[UpdateOneSection]
---原先的线的起点和终点的Serial
@SerialId1 int,
@serialId2 int,
---新点的Serial
@serialId3 int
AS
BEGIN
declare @CurSerial int
select @CurSerial=serial FROM section where serial1=@SerialId1 andserial2=@serialId2
declare @x1 int
declare @y1 int
---原先起点的坐标
select @x1=x,@y1=y from dot where serial=@SerialId1
declare @serial2 int
declare @name2 nvarchar(60)
declare @x2 int
declare @y2 int
declare @next2 int
declare @DateTime2 datetime
---新点的信息，将作为这条更新后线的终点
select @serial2=serial,@name2=[name],@x2=x,@y2=y,@next2=next,@DateTime2=[datetime] from dot whereserial=@SerialId3
declare @Length float
declare @LengthinFact int
select @length=sqrt(abs((@x1-@x2)*(@x1-@x2)+(@y1-@y2)*(@y1-@y2))),@LengthinFact=sqrt(abs((@x1-@x2)*(@x1-@x2)+(@y1-@y2)*(@y1-@y2)))
UPDATE [lcj].[dbo].[Section]
SET [Serial2] = @Serial2
,[Name2] = @Name2
,[X2] = @X2
,[Y2] = @Y2
,[Next2] = @Next2
,[DateTime2] = @DateTime2
,[Length] = @Length
,[DateTime] = CONVERT(varchar(100), GETDATE(), 20)
,[LengthinFact] = @LengthinFact
WHERE serial=@CurSerial
END
//添加一个新点到点表
if exists(select * from sysobjects where name='InsertOneDot' and [type]='P')
drop proc InsertOneDot
go
CREATE PROCEDURE [dbo].[InsertOneDot]
@X int,
@Y int,
@NewName nvarchar(60),
@MaxSerial int output
AS
BEGIN
select @MaxSerial=max(serial) FROM dot
INSERT INTO [lcj].[dbo].[Dot]
([Serial]
,[Name]
,[X]
,[Y]
,[Next]
,[datetime])
VALUES
(@MaxSerial+1
,@NewName
,@X
,@Y
,-1
,CONVERT(varchar(100), GETDATE(), 20))
END
'+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
数据库对于查询所得NULL值的处理
select isnull(max(id),0) as id from testfile
参看博客：http://www.dushuwu.net/a/shujuku/sql/17/3532.html
‘+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++