数据库脚本规范

Schema Change

/*

 * Description:  创建Table规范

 * Created:     

 * CreateDate:   2013-03-15

 * History:

 * =============================================================================

 * Author      DateTime        Alter Description

 * =============================================================================

 */

CREATE TABLE Product_Navigation(

    NavigationId int NOT NULL,

    ParentId int constraint FK_Product_Item_Product_Navigation foreign key references Product_Item(ProductId),--外键命名规则:FK_父表名_子表名

    IsFinished char(1) CONSTRAINT CK_Product_Navigation_IsFinished CHECK(IsFinished in ('N','Y')),--约束命名规则:CK_表名_字段名,唯一约束命名规则:UN_表名_字段名

    CatalogId int CONSTRAINT DF_Product_Navigation_CatalogId DEFAULT(1),--默认约束规则:DF_表名_字段名

    CreateDate datetime,

    CreateBy varchar(30),

    UpdateDate datetime,

    UpdateBy varchar(30)

  CONSTRAINT PK_Product_Navigation PRIMARY KEY CLUSTERED (NavigationId ASC) -- 主键命名规则:PK_表名

)

GO

 

--Index命名规则:IX_表名_字段名

CREATE NONCLUSTERED INDEX IX_Product_Navigation_ParentId ON Product_Navigation(ParentId)

GO

Update Table

/*

 * Description:  分批更新算法

 * Created:     

 * CreateDate:   2013-03-15

 * History:

 * =============================================================================

 * Author      DateTime        Alter Description

 * =============================================================================

 */

 

--获取需要更新的Orders表的主键值

SELECT o.OrderId,CONVERT(VARCHAR(50),'0') AS NewOrderIP

INTO #OrdersNeedUpdate

FROM dbo.Orders AS o WITH(NOLOCK)

WHERE o.Status='SHP' AND o.OrderDate > DATEADD(YEAR,-1,'2013-02-21');

 

--这个索引在下面的循环里,需要在删除数据的语句中使用

CREATE INDEX IX_#OrdersNeedUpdate_OrderId

ON #OrdersNeedUpdate(OrderId);

 

--创建存储按批删除的临时表

CREATE TABLE #OrdersCurrentPage(OrderId INT,NewOrderIp VARCHAR(50));

 

--计算好被更新数据字段,此处只有一个字段值需要更新;如果有多个,请一并将多个字段都计算好

UPDATE #OrdersNeedUpdate

SET NewOrderIP='value need to be updated';

 

--开始按批更新Orders表的数据

WHILE EXISTS(SELECT 1 FROM #OrdersNeedUpdate AS onu WITH(NOLOCK))

BEGIN

    --填充当前需要更新的Orders数据,并将最新的值放在临时表中

    INSERT INTO #OrdersCurrentPage (OrderId,NewOrderIp)

    SELECT TOP(1000) onu.OrderId,onu.NewOrderIP

    FROM #OrdersNeedUpdate AS onu;

 

    --按批更新Orders表.如果有必要,还需要加上rowlocal的hint.如果有疑问,可以咨询一下DBA.

    UPDATE o

    SET orderIP=ocp.NewOrderIp

    FROM #OrdersCurrentPage AS ocp

    JOIN dbo.Orders AS o ON ocp.OrderId = o.OrderId;

 

    --删除已更新过的数据记录

    DELETE onu

    FROM #OrdersNeedUpdate AS onu

    JOIN #OrdersCurrentPage AS ocp ON onu.OrderId = ocp.OrderId;

 

    --清空批量表

    TRUNCATE TABLE #OrdersCurrentPage;

END

GO

Update Data

/*

 * Description:  更新数据

 * Created:     

 * CreateDate:   2013-03-15

 * History:

 * =============================================================================

 * Author      DateTime        Alter Description

 * =============================================================================

 */

/*注意:

    #1: 当为Update语句时,需要备份将会被更新字段的数据.

    #2: 当删除数据时,需要将整个表的数据都进行备份.

    #3: 删除字段前,也需要将该字段的数据进行备份.

*/

--创建备份表,HistoryDB是默认的表,各个项目由于DBS,会有不同的HistoryDB.请各位Leader告知一下相应的开发人员.

IF OBJECT_ID('HistoryDb.dbo.COxxxx_Content_Management') IS NULL

BEGIN

    CREATE TABLE HistoryDb.dbo.COxxxx_Content_Management

    (

        ContentId INT,

        VALUE VARCHAR(MAX),

        UpdateBy VARCHAR(50),

        UpdateDate DATETIME,

        LogDate DATETIME

    )

END

 

--计算好需要更新的数据

SELECT cm.ContentId,REPLACE(cm.Value,'<item>23</item>','<itemid>234</itemid>') AS NewValue

INTO #UpdateContentManagement

FROM dbo.Content_Management AS cm WITH(NOLOCK)

WHERE cm.CatalogId=8;

 

--根据需要修改的数据,进行备份.

INSERT INTO HistoryDb.dbo.COxxxx_Content_Management (ContentId,VALUE,UpdateBy,UpdateDate,LogDate)

SELECT cm.ContentId,cm.Value,cm.UpdateBy,cm.UpdateDate,GETDATE()

from dbo.Content_Management AS cm WITH(NOLOCK)

JOIN #UpdateContentManagement AS ucm WITH(NOLOCK) ON cm.ContentId = ucm.ContentId;

 

--更新业务表

UPDATE cm

SET Value=ucm.NewValue

FROM dbo.Content_Management AS cm

JOIN #UpdateContentManagement AS ucm WITH(NOLOCK) ON cm.ContentId = ucm.ContentId;

 

GO

View

IF OBJECT_ID('V_ProductNavigation') IS NOT NULL

    DROP VIEW V_ProductNavigation

GO

 

-- =============================================================================

-- Author      DateTime        Alter Description

-- =============================================================================

CREATE VIEW V_ProductNavigation

AS

    SELECT * FROM Product_Navigation WITH (NOLOCK) WHERE IsFinished = 'Y'

GO

 

--视图命名规则:以V_开头

Functions

IF OBJECT_ID('Fun_CheckNavigatioinName') IS NOT NULL

    DROP FUNCTION Fun_CheckNavigatioinName

GO

 

-- =============================================================================

-- Author      DateTime        Alter Description

-- =============================================================================

CREATE FUNCTION Fun_CheckNavigatioinName(@parm VARCHAR(30))

RETURNS INT

AS

BEGIN

    --To Do

END

GO

 

--函数命名规则:以FUN_开头

Job

IF OBJECT_ID('dbo.Batch_JobExample','P') IS NOT NULL

    DROP PROC dbo.Batch_JobExample;

GO

 

/*

 * Description:  Job示例代码

 * Created:     

 * CreateDate:   18/03/2013

 * History:

 * =============================================================================

 * Author      DateTime        Alter Description

 * =============================================================================

 */

 

CREATE PROC dbo.Batch_JobExample

AS

BEGIN

 

    DECLARE @msg nvarchar(MAX);

    DECLARE @spName varchar(300)=OBJECT_NAME(@@PROCID);

    BEGIN TRY

    /*

    ** 开始一个step,尽量保证每个语句都能记录下相应的log,

    ** 若是比较复杂的Job,可能会存在Log太多的情况,此时可以将若干相同子功能的语句分为一个step.

    ** 注意:

    ** 当出现错误时,将会有错误消息返回.

    */

    EXEC JobInfoDb.dbo.DbJobBeginStep @BatchName=@spName;

 

    --添加临时表的主键,用作更新数据时使用.

    CREATE TABLE #ShipmentData(Shipment_Id INT NOT NULL PRIMARY KEY,ShipToAddress1 VARCHAR(50),CustomerNumber VARCHAR(15));

    /*

    ** 要求每个具体的业务逻辑都需要在JOb的代码中写清楚注释

    ** 开始step1

    */

    --获取半年前已经成功发货的Shipment对应的收件地址以及Customer号

    --示例代码中,使用Top(10)来限制数据量

    INSERT INTO #ShipmentData(Shipment_Id,ShipToAddress1,CustomerNumber)

    SELECT TOP(10) s.Shipment_id,s.ShipToAddress1,s.CustomerNumber

    FROM dbo.Shipment AS s WITH(NOLOCK)

    WHERE s.[Status]='SHP' AND s.ShipDate BETWEEN DATEADD(D,180,GETDATE()) AND GETDATE();

 

    /*

    ** 结束step1

    ** 注意:

    ** 当出现错误时,将会有错误消息返回.

    */

    EXEC JobInfoDb.dbo.DBJobEndStep @BatchName=@spName;

 

    /*

    ** 开始第二个step2

    */

    EXEC JobInfoDb.dbo.DbJobBeginStep @BatchName=@spName;

 

    --更新发货地址信息

    UPDATE sd

    SET ShipToAddress1=ISNULL(a.address1,ShipToAddress1)

    FROM #ShipmentData AS sd

    INNER JOIN [1800DiapersNEw].dbo.[Address] AS a WITH(NOLOCK) ON sd.CustomerNumber=a.CustomerNumber

    WHERE a.AddressType='S';

 

    --结束step2

    EXEC JobInfoDb.dbo.DBJobEndStep @BatchName=@spName;

 

    /*

    **

    ** 注意:@SubmitType参数有如下选项

    ** 1) U

    ** 负责更新DB Job中需要更新的物理表

    ** 此时临时表中所对应的字段除了物理表的主键以后,其他的字段均为需要进行更新的字段.

    ** 字段名两个表要一致,如果不是需要更新的字段,请不要保留在临时表中. 主键字段不会被更新;

    ** 不可更新,identity等自动属性字段.

    ** 2) I

    ** 负责插入DB Job中需要插入的物理表

    ** 此时,临时表中只包含需要插入的数据.如果没有把握,需要提前做一下存在校验.

    ** 不一定需要主键,不可插入identity等自动属性字段.

    ** 3) D

    ** 负责删除DB Job中的需要删除的物理表

    ** 此时,临时表中只包含需要删除的数据的主键字段.

    **

    ** 进行数据的更新,统一使用下面这个sp进行更新.

    **

    ** SP的执行内容:

    ** dbjob的真正执行将取决于具体参数的配置.在不同的环境配置中,可能会进行不同的操作.

    ** a) 当调试/测试配置开启时,数据将保存在历史记录表中.

    ** b) 当真正执行时,才会更新具体的物理表.

    **/

 

    EXEC JobInfoDb.dbo.DbJobSubmitTableData @BatchName=@spName,

    @TableName='[1800DiapersNew].dbo.Shipment',@DatasetName='#ShipmentData',

    @SubmitType='U';

 

 

    END TRY

    BEGIN CATCH

        SET @msg=ERROR_MESSAGE()+' at line:'+CONVERT(VARCHAR(10),ERROR_LINE());

        SELECT @msg AS ErrorMessage;

        EXEC JobInfoDb.dbo.DBJobWriteLog @BatchName=@spName,@LogMsg=@msg;

 

        RAISERROR(@msg,16,0);

    END CATCH

END

GO

Trigger

-- =============================================================================

-- Author      DateTime        Alter Description

-- =============================================================================

ALTER TRIGGER [dbo].[TGR_UpdateProductNavigate] 

   ON  [dbo].[Order_Item] 

   FOR UPDATE 

AS  

BEGIN 

    --TO DO

END 

GO

 

--触发器命名规则:以TGR_开头