数据库脚本规范-阿里云开发者社区

 
  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)CONSTRAINTCK_Product_Navigation_IsFinishedCHECK(IsFinishedin('N','Y')),--约束命名规则:CK_表名_字段名,唯一约束命名规则:UN_表名_字段名 
 
 CatalogId
 intCONSTRAINTDF_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_开头
数据库脚本规范

Schema Change

Update Table

Update Data

View

Functions

Job

Trigger

热门文章

最新文章

相关课程

相关电子书

相关实验场景
