金沙澳门官网网址-澳门金沙官网登录

金沙澳门官网网址拥有大量的原创内容,澳门金沙官网登录提供最权威、快速、全面、专业的报道,所以金沙澳门官网网址注册送彩金就相当于直接送现金了,享受最尊贵的贵宾服务。

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

2020-01-23 10:46栏目:金沙澳门官网
TAG:

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

while @n<=@colid

while @k<=@number

 

begin

                       end)

when 'IF' then

 

    declare @name sysname

end

                          else 'ALTER PROCEDURE '+ @objectName+' WITH ENCRYPTION AS '

set @n=@n+1

        fetch next from tb into @name

go  

SET @OrigSpText2=(case when @number>1 then 'CREATE PROCEDURE '+ @objectName +';'+rtrim(@k)+' WITH ENCRYPTION AS '

    --对所有的存储过程解密

while @k<=@number

print @resultsp

begin

 

--修正上一版视图触发器不能正确解密错误

                          end)

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_decrypt]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

set @OrigSpText2='CREATE TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION FOR INSERT AS PRINT 1 '

                                (UNICODE(substring(@OrigSpText2, @i, 1)) ^

if @type='FN' or @type='TF' or @type='IF'

if @type='V'

begin

GO

@dirname nvarchar(20)=''

set @sql1=@sql1+REPLICATE('-',4000-@q)

存储过程、存储函数的解密(以下是一位绝世高人编写的代码)

begin

 

                                UNICODE(substring(@OrigSpText3, @i, 1)))))

set @sql1=(case when @number>1 then 'ALTER PROCEDURE '+ @objectName +';'+rtrim(@k)+' WITH ENCRYPTION AS '

end

        print '/*-------存储过程 ['+@name+'] -----------*/'

begin

end

select @colid=max(colid) from #temp where number=@k

SET @OrigSpText2=REPLICATE('-', 4000)

AS

--作者:J9988--*/

set @sql1='ALTER TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION FOR INSERT AS PRINT 1 '

BEGIN

declare @sql1 nvarchar(4000),@sql2 varchar(8000),@sql3 nvarchar(4000),@sql4 nvarchar(4000)

GO

set @n=1

if @type='P'

'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns @b table(a varchar(10)) with encryption as begin insert @b select @a return end '

set @k=@k+1

@table_name nvarchar(260)='',

when 'FN' then

    end

select @number=max(number) from #temp

end

set nocount on

存储过程、存储函数的加密:WITH ENCRYPTION
<!--[if !supportLineBreakNewLine]-->
<!--[endif]-->

 

 

 

exec sp_executesql @sql3,N'@sql1 nvarchar(4000),@ varchar(8000)',@sql1=@sql1,@=@sql2

set @OrigSpText2='CREATE TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION INSTEAD OF INSERT AS PRINT 1 '

end)

   

'ALTER FUNCTION '+ @objectName+'(@a char(1)) returns @b table(a varchar(10)) with encryption as begin insert @b select @a return end '

    begin

 

 

select @OrigSpText1=ctext,@encrypted=encrypted,@status=status FROM #temp  WHERE colid=@n and number=@k

end

begin

as

end)

'ALTER FUNCTION '+ @objectName+'(@a char(1)) returns table with encryption as return select @a as a'

end

end

select @sql2=REPLICATE('-',8000)

set @sql1='ALTER TRIGGER '+@objectname+' ON '+OBJECT_NAME(@parentid)+' WITH ENCRYPTION INSTERD OF INSERT AS PRINT 1 '

 

set @sql3=@sql3+'+@'

/*--破解函数,过程,触发器,视图.仅限于SQLSERVER2000

end

set @q=4000-len(@OrigSpText2)

--CSDN:j9988 copyright:2004.04.15

begin

SET @resultsp = replicate(N'A', (datalength(@OrigSpText1) / 2))

begin

then @resultsp

if @type='FN' or @type='TF' or @type='IF'

 

set @orgvarbin=cast(@OrigSpText1 as varbinary(8000))

 

--发现有错,请E_MAIL:CSDNj9988@tom.com

 

WHILE @i<=datalength(@OrigSpText1)/2

set @sql1='ALTER VIEW '+@objectname+' WITH ENCRYPTION AS SELECT 1 as f'

    drop procedure [dbo].[SP_DECRYPT]

begin

'CREATE FUNCTION '+ @objectName+'(@a char(1)) returns char(1) with encryption as begin return @a end'

end

 

select @parent_obj=parent_obj from sysobjects where id=object_id(@objectName)

版权声明:本文由金沙澳门官网网址发布于金沙澳门官网,转载请注明出处:规范化的SQL数据修改语句总结