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

 
  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

热门文章

最新文章

相关课程

相关电子书

相关实验场景

热门

活动广场

任务中心

开发者评测

高校计划

乘风者计划

训练营

阿里云MVP

话题

直播

下载

镜像站

技术资料

插件

数据库脚本规范

Schema Change

Update Table

Update Data

View

Functions

Job

Trigger

热门文章

最新文章

相关课程

相关电子书

相关实验场景
