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

bitsCN.com 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(@CountBegin Print(@Count);Insert into Student values('YOUNG','M',100,'FUJIANXIAMENT');set @Count=@Count+1;END END bitsCN.com