sqlserver根据输入的两个点表中的ID号,插入一条线的记录到线表的存储过程

时间:2022-12-09 05:57:25

------------------------------------------------------------------------
--功能说明:根据一个表中的两行的主键,把这两行的信息插入另一个表中形成一条记录
--创建人: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

‘+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++