SQL Server 项目中 SQL 脚本更新、升级方式，防止多次重复执行

MySQL 项目中 SQL 脚本更新、升级方式，防止多次重复执行

Oracle 项目中 SQL 脚本更新方式

一套代码，多家部署时，在SQL脚本升级时，通过一个SQL文件给运维，避免出现SQL执行序顺出错及漏执行SQL

常用的如下：

--如果不存在这条数据，就添加

IF NOT EXISTS (SELECT 1 FROM dbo.Settings WHERE PropertyName = 'DBVersion')

INSERT INTO dbo.Settings(ID, Code, Value) VALUES(NEWID(),'DBVersion','2018')

GO

--可以通过版本号控制

IF EXISTS (SELECT 1 FROM dbo.Settings WHERE PropertyName='DBVersion' AND CONVERT(INT, CONVERT(VARCHAR(10), PropertyValue)) <= 2019)

BEGIN

UPDATE TABLE1 SET Field1 = 'abc' WHERE field2 = 0

UPDATE TABLE2 SET FIELD2 = '1900-01-01 00:00:00' WHERE [Status] = 1

UPDATE TABLE3 SET FIELD3 = '1900-01-01 00:00:00' WHERE [Status] = 1

UPDATE dbo.Settings SET PropertyValue = '2020' WHERE PropertyName='DBVersion'

END

GO

--如果表里不存在字段，就添加

IF NOT EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'TABLE4' AND COLUMN_NAME = 'COLUMN2' AND TABLE_SCHEMA = 'dbo')

BEGIN

ALTER TABLE TABLE4 Add COLUMN2 nvarchar(31)

END

GO

--存储过程先删除，再创建

IF EXISTS (SELECT 1 FROM sysobjects WHERE type = 'P' AND name = 'PRO_TEST')

DROP PROCEDURE PRO_TEST

GO

CREATE PROCEDURE [dbo].[PRO_TEST]

@StartDate DATETIME = NULL,

@EndDate DATETIME = NULL,

@Num INT = NULL

AS

SELECT * FROM LogTable

WHERE Num = ISNULL(@Num, Num)

AND CreatedDate >= ISNULL(@StartDate, CreatedDate)

AND CreatedDate <= ISNULL(@EndDate, CreatedDate) + '23:59:59'

ORDER BY CreatedDate DESC

GO

 如果表不存在，创建表

IF NOT EXISTS (SELECT 1 FROM sysobjects WHERE type = 'U' AND name = 'Table_name')

BEGIN

CREATE TABLE Table_name(

Id varchar(36),

HospitalId varchar(36),

HospitalName nvarchar(80),

DeptId varchar(36),

DeptName nvarchar(80)

);

EXECUTE sp_addextendedproperty N'MS_Description', 'ID', N'user', N'dbo', N'table', N'Sys_DeptProcessRelation', N'column', N'Id';

EXECUTE sp_addextendedproperty N'MS_Description', '医院ID', N'user', N'dbo', N'table', N'Sys_DeptProcessRelation', N'column', N'HospitalId';

EXECUTE sp_addextendedproperty N'MS_Description', '医院名称', N'user', N'dbo', N'table', N'Sys_DeptProcessRelation', N'column', N'HospitalName';

EXECUTE sp_addextendedproperty N'MS_Description', '科室ID', N'user', N'dbo', N'table', N'Sys_DeptProcessRelation', N'column', N'DeptId';

EXECUTE sp_addextendedproperty N'MS_Description', '科室名称', N'user', N'dbo', N'table', N'Sys_DeptProcessRelation', N'column', N'DeptName';

PRINT 'Create table Table_name Successfully!';

END

GO

好文阅读