数据库脚本规范

Schema Change

/*

 * Description:  创建Table规范

 * Created:     

 * CreateDate:   2013-03-15

 * History:

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

 * Author      DateTime        AlterDescription

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

 */

CREATETABLE Product_Navigation(

    NavigationIdintNOT NULL,

    ParentIdintconstraint FK_Product_Item_Product_Navigation foreignkey references Product_Item(ProductId),--外键命名规则:FK_父表名_子表名

    IsFinishedchar(1)CONSTRAINTCK_Product_Navigation_IsFinishedCHECK(IsFinishedin('N','Y')),--约束命名规则:CK_表名_字段名,唯一约束命名规则:UN_表名_字段名

    CatalogIdintCONSTRAINTDF_Product_Navigation_CatalogIdDEFAULT(1),--默认约束规则:DF_表名_字段名

    CreateDate datetime,

    CreateByvarchar(30),

    UpdateDate datetime,

    UpdateByvarchar(30)

 CONSTRAINTPK_Product_Navigation PRIMARYKEY CLUSTERED (NavigationIdASC)-- 主键命名规则:PK_表名

)

GO

 

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

CREATENONCLUSTERED INDEXIX_Product_Navigation_ParentId ONProduct_Navigation(ParentId)

GO

Update Table

/*

 * Description:  分批更新算法

 * Created:     

 * CreateDate:   2013-03-15

 * History:

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

 * Author      DateTime        AlterDescription

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

 */

 

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

SELECTo.OrderId,CONVERT(VARCHAR(50),'0')ASNewOrderIP

INTO#OrdersNeedUpdate

FROMdbo.Orders ASo WITH(NOLOCK)

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

 

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

CREATEINDEX IX_#OrdersNeedUpdate_OrderId

ON#OrdersNeedUpdate(OrderId);

 

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

CREATETABLE #OrdersCurrentPage(OrderId INT,NewOrderIpVARCHAR(50));

 

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

UPDATE#OrdersNeedUpdate

SETNewOrderIP='value need to be updated';

 

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

WHILE EXISTS(SELECT1 FROM#OrdersNeedUpdate ASonu WITH(NOLOCK))

BEGIN

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

    INSERTINTO #OrdersCurrentPage (OrderId,NewOrderIp)

    SELECTTOP(1000) onu.OrderId,onu.NewOrderIP

    FROM#OrdersNeedUpdate ASonu;

 

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

    UPDATEo

    SETorderIP=ocp.NewOrderIp

    FROM#OrdersCurrentPage ASocp

    JOINdbo.Orders ASo ONocp.OrderId = o.OrderId;

 

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

    DELETEonu

    FROM#OrdersNeedUpdate ASonu

    JOIN#OrdersCurrentPage ASocp ONonu.OrderId = ocp.OrderId;

 

    --清空批量表

    TRUNCATETABLE #OrdersCurrentPage;

END

GO

Update Data

/*

 * Description:  更新数据

 * Created:     

 * CreateDate:   2013-03-15

 * History:

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

 * Author      DateTime        AlterDescription

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

 */

/*注意:

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

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

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

*/

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

IF OBJECT_ID('HistoryDb.dbo.COxxxx_Content_Management')ISNULL

BEGIN

    CREATETABLE HistoryDb.dbo.COxxxx_Content_Management

    (

        ContentIdINT,

        VALUEVARCHAR(MAX),

        UpdateByVARCHAR(50),

        UpdateDate DATETIME,

        LogDate DATETIME

    )

END

 

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

SELECTcm.ContentId,REPLACE(cm.Value,'<item>23</item>','<itemid>234</itemid>')ASNewValue

INTO#UpdateContentManagement

FROMdbo.Content_Management AScm WITH(NOLOCK)

WHEREcm.CatalogId=8;

 

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

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

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

fromdbo.Content_Management AScm WITH(NOLOCK)

JOIN#UpdateContentManagement ASucm WITH(NOLOCK)ONcm.ContentId = ucm.ContentId;

 

--更新业务表

UPDATEcm

SETValue=ucm.NewValue

FROMdbo.Content_Management AScm

JOIN#UpdateContentManagement ASucm WITH(NOLOCK)ONcm.ContentId = ucm.ContentId;

 

GO

View

IF OBJECT_ID('V_ProductNavigation')ISNOT NULL

    DROPVIEW V_ProductNavigation

GO

 

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

-- Author      DateTime        Alter Description

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

CREATEVIEW V_ProductNavigation

AS

    SELECT* FROMProduct_Navigation WITH(NOLOCK) WHEREIsFinished = 'Y'

GO

 

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

Functions

IF OBJECT_ID('Fun_CheckNavigatioinName')ISNOT NULL

    DROPFUNCTION Fun_CheckNavigatioinName

GO

 

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

-- Author      DateTime        Alter Description

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

CREATEFUNCTION Fun_CheckNavigatioinName(@parm VARCHAR(30))

RETURNSINT

AS

BEGIN

    --To Do

END

GO

 

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

Job

IF OBJECT_ID('dbo.Batch_JobExample','P')ISNOT NULL

    DROPPROC dbo.Batch_JobExample;

GO

 

/*

 * Description:  Job示例代码

 * Created:     

 * CreateDate:   18/03/2013

 * History:

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

 * Author      DateTime        AlterDescription

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

 */

 

CREATEPROC dbo.Batch_JobExample

AS

BEGIN

 

    DECLARE@msg nvarchar(MAX);

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

    BEGINTRY

    /*

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

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

    ** 注意:

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

    */

    EXECJobInfoDb.dbo.DbJobBeginStep @BatchName=@spName;

 

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

    CREATETABLE #ShipmentData(Shipment_Id INTNOT NULL PRIMARY KEY,ShipToAddress1VARCHAR(50),CustomerNumberVARCHAR(15));

    /*

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

    ** 开始step1

    */

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

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

    INSERTINTO #ShipmentData(Shipment_Id,ShipToAddress1,CustomerNumber)

    SELECTTOP(10) s.Shipment_id,s.ShipToAddress1,s.CustomerNumber

    FROMdbo.Shipment ASs WITH(NOLOCK)

    WHEREs.[Status]='SHP'AND s.ShipDate BETWEENDATEADD(D,180,GETDATE()) ANDGETDATE();

 

    /*

    ** 结束step1

    ** 注意:

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

    */

    EXECJobInfoDb.dbo.DBJobEndStep @BatchName=@spName;

 

    /*

    ** 开始第二个step2

    */

    EXECJobInfoDb.dbo.DbJobBeginStep @BatchName=@spName;

 

    --更新发货地址信息

    UPDATEsd

    SETShipToAddress1=ISNULL(a.address1,ShipToAddress1)

    FROM#ShipmentData ASsd

    INNERJOIN [1800DiapersNEw].dbo.[Address] ASa WITH(NOLOCK)ONsd.CustomerNumber=a.CustomerNumber

    WHEREa.AddressType='S';

 

    --结束step2

    EXECJobInfoDb.dbo.DBJobEndStep @BatchName=@spName;

 

    /*

    **

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

    ** 1) U

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

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

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

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

    ** 2) I

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

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

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

    ** 3) D

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

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

    **

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

    **

    ** SP的执行内容:

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

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

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

    **/

 

    EXECJobInfoDb.dbo.DbJobSubmitTableData @BatchName=@spName,

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

    @SubmitType='U';

 

 

    ENDTRY

    BEGINCATCH

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

        SELECT@msg ASErrorMessage;

        EXECJobInfoDb.dbo.DBJobWriteLog @BatchName=@spName,@LogMsg=@msg;

 

        RAISERROR(@msg,16,0);

    ENDCATCH

END

GO

Trigger

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

-- Author      DateTime        Alter Description

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

ALTERTRIGGER [dbo].[TGR_UpdateProductNavigate] 

   ON [dbo].[Order_Item] 

   FORUPDATE 

AS  

BEGIN 

    --TO DO

END 

GO

 

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