规范化的SQL数据修改语句总结

1 增加字段

if not exists (select * from syscolumns where id=object_id('querydatasource') and name = 'iscrosstable')
begin
alter table querydatasource add iscrosstable bit default(0) --插入字段
end

2 存储过程

if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[plsystem_insert]') and objectproperty(id, n'isprocedure') = 1)
drop procedure [dbo].[plsystem_insert]
go
create procedure dbo.plsystem_insert
(
@plsystemid smallint,
@plsystemname varchar(20),
@plsystemflag char(2)
)
as
insert into dbo.plsystem
(
plsystemid,
plsystemname,
plsystemflag
)
values
(
@plsystemid,
@plsystemname,
@plsystemflag
)
go

if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[plsystem_delete]') and objectproperty(id, n'isprocedure') = 1)
drop procedure [dbo].[plsystem_delete]
go
create procedure dbo.plsystem_delete
(
@plsystemid smallint
)
as
delete from dbo.plsystem
where
plsystemid = @plsystemid
go

if exists (select * from dbo.sysobjects where id = object_id(n'[dbo].[plsystem_update]') and objectproperty(id, n'isprocedure') = 1)
drop procedure [dbo].[plsystem_update]
go
create procedure dbo.plsystem_update
(
@plsystemid smallint,
@plsystemname varchar(20),
@plsystemflag char(2)
)
as
update dbo.plsystem set
plsystemname = @plsystemname,
plsystemflag = @plsystemflag
where
plsystemid = @plsystemid
go

3 创建表

if exists (select * from sys.objects where object_id = object_id(n'[dbo].[plsystem]') and type in (n'u'))
drop table [dbo].[plsystem]
go
if not exists (select * from sys.objects where object_id = object_id(n'[dbo].[plsystem]') and type in (n'u'))
begin
create table [dbo].[plsystem](
[plsystemid] [tinyint] not null,
[plsystemname] [varchar](20) null,
[plsystemflag] [char](2) null,
constraint [xpkplsystem] primary key clustered
(
[plsystemid] asc
)
) on [primary]
end
go

4 游标

begin
declare @pluserid varchar(8)
declare plusercurse cursor for select pluserid from pluser where pcanceljudge=0
open plusercurse --打开游标
fetch next from plusercurse into @pluserid
while @@fetch_status=0
begin
print (@pluserid)
insert into pluseractordepart(departid,plactorsystemid,pluserid)
select departmentid,plsystemid,pluserid from msuserright where msuserright.departmentid not in
(
select departmentid from pldepartment where pldepartment.dparent=(select departmentid from pluser where pluserid=@pluserid )
or pldepartment.departmentid=(select departmentid from pluser where pluserid =@pluserid)
) and msuserright.pluserid=@pluserid
fetch next from plusercurse into @pluserid
end
close plusercurse
deallocate plusercurse
end

5 用while实现for循环

create proc insertsql
as
begin
declare @count int
set @count=0;
while(@count<200000)
begin
print(@count);
insert into student values('young','m',100,'fujianxiament');
set @count=@count+1;
end
end