关于SQL脚本编写建议

最近在做安装包的时候编写sql脚本自动生成库,发现没有在写sql脚本没有一定的规范,写出来的脚本可能会害了同事和自己,所以总结了以下内容,以供参考。

1、创建表的脚本范例
IF NOT EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DIMRoles]') AND xtype = 'U')

BEGIN

CREATE TABLE [DIMRoles](

[Key] [int] NOT NULL,

[Name] [nvarchar](256) NOT NULL,

PRIMARY KEY CLUSTERED

(

[Key] ASC

) ON [PRIMARY],

UNIQUE NONCLUSTERED

(

[Name] ASC

) ON [PRIMARY]

) ON [PRIMARY]

END



注意事项:

A、一定要先判断表是否存在,不存在才创建,而不能存在删除原表进行新表创建,重复执行会导致客户数据丢失!

2、修改表范例
2.1增加字段,增加控制(索引,不能为空等等)
IF NOT exists(select * from syscolumns where id=object_id('DIMUsers') and name='Phone')

BEGIN

ALTER TABLE dbo.DIMUsers ADD Phone NVARCHAR(20) NOT NULL DEFAULT('')

END



注意事项:

A、一定要判断字段是否存在表,不存在才创建。

B、一定看清楚判断的字段和表是否一样,不要上面判断A表,下面修改B表。

2.2修改删除字段
修改:

IF exists(select * from syscolumns where id=object_id('DIMUsers') and name='Phone')

BEGIN

ALTER TABLE dbo.DIMUsers ALTER COLUMN Phone NVARCHAR(50)

END

删除:

IF exists(select * from syscolumns where id=object_id('DIMUsers') and name='Phone')

BEGIN

ALTER TABLE dbo.DIMUsers DROP COLUMN Phone

END



注意事项:

A、一定要判断表的字段存在,不存在的话不存在修改和删除的意义。



3、初始化表数据范例
注意事项:

1、切勿使用delete 你要插入的表没有加上条件。

2、如果自增长字段最好关闭自增长,然后删除先插入你要加入到这张表的数据,因为这个ID是固定的。

SET IDENTITY_INSERT DIMUsers ON

DELETE FROM DIMUsers WHERE [Key] IN(1,2,3,4);

INSERT INTO DIMUsers ([Key], [Name], Password, Type, Nickname, UpperName, RegisterTime)

values (1, N'sa', N'123', 0, N'系统管理员', N'SA', getdate());

INSERT INTO DIMUsers ([Key], [Name], Password, Type, Nickname, UpperName, RegisterTime)

values (2, N'administrator', N'', 0, N'系统管理员', N'ADMINISTRATOR', getdate());

INSERT INTO DIMUsers ([Key], [Name], Password, Type, Nickname, UpperName, RegisterTime)

values (3, N'admin', N'123', 0, N'系统管理员', N'ADMIN', getdate());

INSERT INTO DIMUsers ([Key], [Name], Password, Type, Nickname, UpperName, RegisterTime)

values (4, N'public', N'', 1, N'公共聊天室', N'PUBLIC', getdate());

SET IDENTITY_INSERT DIMUsers OFF

如果系统的主键不是自增长的最好办了,上面的例子是最难办的自增长例子。

4、修改函数、存储过程、视图、索引
注意事项:

A、因为这些东西重复更新的话可以删除原来的保证最新的就可以,所以只需要判断是否存在即可。

函数示例:

IF OBJECT_ID('FUN_GetChildList') IS NOT NULL

DROP FUNCTION FUN_GetChildList

GO

/****** 对象: UserDefinedFunction [dbo].[FUN_GetChildList] 脚本日期: 07/05/2011 09:40:16 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

/*

author:wenjl

createtime:2011-4-29

desc:--用途 根据传递的参数获取所有子节点

--取法举例SELECT * FROM FUN_GetChildList('COMPANY','TQ00000100')

*/

CREATE FUNCTION [dbo].[FUN_GetChildList] (

@Table Nvarchar(100),

@Value Nvarchar(100)

)



RETURNS @tResult TABLE (

-- 定义结果表

RESULT Nvarchar(100)

)



AS

BEGIN

-- 子类型表、父类型表、子类型数

DECLARE @tChild TABLE(RESULT Nvarchar(100))

DECLARE @tFather TABLE(RESULT Nvarchar(100))

DECLARE @Count SmallInt



-- 预先置传入的值到@tFather中

INSERT INTO @tFather

VALUES (@Value)



WHILE 1 = 1

BEGIN

-- 循环中,先将@tFather中内容放到@tResult中;

INSERT INTO @tResult SELECT RESULT FROM @tFather



-- 将@tChild清空,再取@tFather的所有子类型到@tChild中;

DELETE FROM @tChild



-- 根据不同的表追加写到IF内部的语句

---------------------------------------------------------------------------------------

IF @Table ='DIMDEPT'

BEGIN

INSERT INTO @tChild

SELECT RESULT = did

FROM DIMDEPT

WHERE pdid IN (SELECT RESULT FROM @tFather)

END

---------------------------------------------------------------------------------------

-- 当@tChild空时,退出;

SELECT @Count = COUNT(*) FROM @tChild

IF @Count = 0

BREAK



-- 将@tFather清空,并将@tChild中内容给@tFather。

DELETE FROM @tFather

INSERT INTO @tFather SELECT RESULT FROM @tChild

END

RETURN

END



存储过程示例:

IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[DIMAddFriend]') AND OBJECTPROPERTY(id,N'IsProcedure') = 1)

DROP PROC DIMAddFriend

GO

create proc [dbo].[DIMAddFriend](@user nvarchar(256), @friend nvarchar(256))

as

begin

insert into DIMUserRelationship (HostKey,GuestKey,Relationship,RenewTime)

select host.[Key] as HostKey,guest.[Key] as GuestKey,0,getdate()

from DIMUsers host,DIMUsers guest

where

(host.UpperName=upper(@user) or host.UpperName=upper(@friend)) and

(guest.UpperName=upper(@friend) or guest.UpperName=upper(@user)) and

host.[Key]<>guest.[Key]

end

视图示例:

由于视图判断没有办法所以版本统一,所以写了2个版本。

2005以上版本:

IF EXISTS (SELECT * FROM sys.views WHERE OBJECT_ID=OBJECT_ID(N'VB_AccountPayableDetail') )

DROP VIEW VB_AccountPayableDetail

go



CREATE VIEW VB_AccountPayableDetail

AS

SELECT

A.COMPANY_ID,

A.ACCOUNT_PAYABLE_ID,

A.SNO,

A.MATERIAL_ID,

A.MATERIAL_NO,

A.MATERIAL_NAME,

A.MATERIAL_SPECIFICATION,

A.CURRENCY_ID,

EXCHANGE_RATE = CAST(A.EXCHANGE_RATE AS NUMERIC(18,2)),

A.UNIT_ID,

QUANTITY = CAST(A.QUANTITY AS NUMERIC(18,2)),

UNIT_PRICE = CAST(A.UNIT_PRICE AS NUMERIC(18,2)),

TAX_RATE = CAST(A.TAX_RATE AS NUMERIC(18,2)),

AMOUNT = CAST(A.AMOUNT AS NUMERIC(18,2)),

A.REMARK,

CURRENCY = B.NAME,

UNIT = C.NAME

FROM ACCOUNT_PAYABLE_ITEM A

LEFT JOIN S_CODELIB B

ON A.COMPANY_ID = B.COMPANY_ID

AND A.CURRENCY_ID = B.ID

LEFT JOIN S_CODELIB C

ON A.COMPANY_ID = C.COMPANY_ID

AND A.UNIT_ID = C.ID

2000版本:

IF EXISTS (SELECT * FROM sysviews WHERE OBJECT_ID=OBJECT_ID(N'VB_AccountPayableDetail') )

DROP VIEW VB_AccountPayableDetail

go



CREATE VIEW VB_AccountPayableDetail

AS

SELECT

A.COMPANY_ID,

A.ACCOUNT_PAYABLE_ID,

A.SNO,

A.MATERIAL_ID,

A.MATERIAL_NO,

A.MATERIAL_NAME,

A.MATERIAL_SPECIFICATION,

A.CURRENCY_ID,

EXCHANGE_RATE = CAST(A.EXCHANGE_RATE AS NUMERIC(18,2)),

A.UNIT_ID,

QUANTITY = CAST(A.QUANTITY AS NUMERIC(18,2)),

UNIT_PRICE = CAST(A.UNIT_PRICE AS NUMERIC(18,2)),

TAX_RATE = CAST(A.TAX_RATE AS NUMERIC(18,2)),

AMOUNT = CAST(A.AMOUNT AS NUMERIC(18,2)),

A.REMARK,

CURRENCY = B.NAME,

UNIT = C.NAME

FROM ACCOUNT_PAYABLE_ITEM A

LEFT JOIN S_CODELIB B

ON A.COMPANY_ID = B.COMPANY_ID

AND A.CURRENCY_ID = B.ID

LEFT JOIN S_CODELIB C

ON A.COMPANY_ID = C.COMPANY_ID

AND A.UNIT_ID = C.ID



索引示例:

if NOT exists(select * from sysindexes where name='IX_mis_AskForLeaveSheetFact_001')

BEGIN

CREATE NONCLUSTERED INDEX [IX_mis_AskForLeaveSheetFact_001] ON [dbo].[mis_AskForLeaveSheetFact]

(

[begin_date] ASC,

[begin_time] ASC

)

INCLUDE ( [Fact_leave_id],

[e_id],

[end_date],

[end_time],

[holidayNo]) WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY]

END

统计信息示例:

这个没有2000版本的只有2005版本

IF NOT EXISTS(SELECT NAME FROM sys.stats WHERE name='pms_exam_emp_001')

BEGIN

CREATE STATISTICS [pms_exam_emp_001] ON [dbo].[pms_exam_emp]([p_e_id], [e_type], [e_id])

END



这个统计信息是什么东东,这个玩意是跟索引配合的,在进行数据优化的时候很有用处。



以上脚本都要记住的就如果你要增加、修改、删除一个表,视图,函数,存储过程,索引,字段都必须判断是否存在。



5、关于脚本上传到svn的规范
1、文件命名规则:
A、每天建立一个文件夹

B、文件名称以设计的表名,字段名,函数,存储过程,日期时间,姓名

示例:doa_deletefield_BOM_alter_201109061105_wenjl.sql

存储过程名称+动作+时间+姓名

个人觉得加上时间比较好,这样可以有效避免存储过程函数等更改的先后问题。最好可以统一开发库。

 

posted @ 2012-03-22 11:24  温景良(Jason)  Views(9779)  Comments(1Edit  收藏  举报