数据库设计和SQL编写规范

水深无声 2022-08-19 08:29 188阅读 0赞

## 1   数据库设计规范： ##

1.        表名约定：新增加的表统一按“\{子系统\}\_\{模块名\}\_\{表名\}\[\_bak|\_tmp|\_mid\]”，其中\_bak：表示备份，将不生成代码，|\_tmp：为某表的临时表，|\_mid：中间结果表或仅在过程中使用表，不生成代码，表名尽可能精简明了，缩短表名字符数量；

2.        视图命名约定：\{子系统\}\_V\[\_\{模块名\}\]\_视图名；存储过程命名约定：\{子系统\}\_SP\[\_\{模块名\}\]\_过程名，其中模块名可省略，模块名为“job”则表示作业自动调用过程；函数命名约定：\{子系统\}\_FUN\[\_\{模块名\}\]\_函数名；

3.        备注字段：必须要有中文备注说明，状态之类字段备注格式为“\{中文说明\}\[：\{ \{值\}-\{意义\} \[;...n\]\}\]”，如“审批状态：1-通过;2-不通过

4.        主外键字段：主键一般情况下用Id为整型自动递增，外键名为\{关联表表名\}\_Id，主外键要建立关联;

5.        状态字段：建议统一用Status命名，记录的有效性字段统一用RecordStatus命；

6.        附加列：为保持兼容，所有表最后4个字段为Creater、CreateTime、Modifer、ModifiedTime;

## 2   sql编写规范： ##

1.        存储过程编写规范：（1）统一建立日志表记录过程执行中的日志、异常，要记录当前出错的异常行号、异常消息、执行时间，方便问题排查；（2）过程中如果存在两层以上嵌套，要严格按缩进语法来编写，并把嵌套开始结束位置标记层级深度，复杂的逻辑还要写好注释和案例说明，函数或存储过程，推荐写好各种条件测试用例，并作为注释一部分，如下：

ALTER function [dbo].[fun_findPlanNo2](@planNo varchar(150),@index int)
    returns varchar(150)
    as
    begin
    	/*从计划编号中,按序号分离出子计划编号,示例如下:
    	@planNo="BPG4090682/(0.3)5010032(0.6)"
    	如@index为1时返回BPG4090682
    	  @index为2时返回BPG5010032(还需补齐前面缺失部分前缀)
    	测试样例：
    	select dbo.fun_findPlanNo2('SPG5091760P（0.3）/1803P（1）/5101907P（0.7）/1961P（0.6）/2019P（0.4）',5);
    	print:SPG5102019P
    	select dbo.fun_findPlanNo2('YFP5090272（1）/5100303（1.2）/0309（0.85）/0314（0.85）',4);
    	print:YFP5100314
    	select dbo.fun_findPlanNo2('YFP5090253（0.3）/5090267（0.3）/TMR5090097（0.4）/0106（0.3）',4)
    	print:TMR5090106
    	测试用例(请在修改函数后运行以下用例):
    	--带有”-1“之类字符
    	select case when dbo.fun_findPlanNo2('BPG5050282P（0.9）/0282P-1（3.1）',1)='BPG5050282P' then '测试通过' else '错误' end;
    	select case when dbo.fun_findPlanNo2('BPG5050282P（0.9）/0282P-1（3.1）',2)='BPG5050282P-1' then '测试通过' else '错误' end;
    	select case when dbo.fun_findPlanNo2('BPG5070387P（0.3）/0387-1（0.2）',2)='BPG5070387P-1' then '测试通过' else '错误' end;
    	--多级缩写
    	select case when dbo.fun_findPlanNo2('SPG5091760P（0.3）/1803P（1）/5101907P（0.7）/1961P（0.6）/2019P（0.4）',1)='SPG5091760P' then '测试通过' else '错误' end;
    	select case when dbo.fun_findPlanNo2('SPG5091760P（0.3）/1803P（1）/5101907P（0.7）/1961P（0.6）/2019P（0.4）',2)='SPG5091803P' then '测试通过' else '错误' end;
    	select case when dbo.fun_findPlanNo2('SPG5091760P（0.3）/1803P（1）/5101907P（0.7）/1961P（0.6）/2019P（0.4）',3)='SPG5101907P' then '测试通过' else '错误' end;
    	select case when dbo.fun_findPlanNo2('SPG5091760P（0.3）/1803P（1）/5101907P（0.7）/1961P（0.6）/2019P（0.4）',4)='SPG5101961P' then '测试通过' else '错误' end;
    	select case when dbo.fun_findPlanNo2('SPG5091760P（0.3）/1803P（1）/5101907P（0.7）/1961P（0.6）/2019P（0.4）',5)='SPG5102019P' then '测试通过' else '错误' end;
    	--中间有字母
    	select case when dbo.fun_findPlanNo2('YFP5090253（0.3）/5090267（0.3）/TMR5090097（0.4）/0106（0.3）',1)='YFP5090253' then '测试通过' else '错误' end;
    	select case when dbo.fun_findPlanNo2('YFP5090253（0.3）/5090267（0.3）/TMR5090097（0.4）/0106（0.3）',2)='YFP5090267' then '测试通过' else '错误' end;
    	select case when dbo.fun_findPlanNo2('YFP5090253（0.3）/5090267（0.3）/TMR5090097（0.4）/0106（0.3）',3)='TMR5090097' then '测试通过' else '错误' end;
    	select case when dbo.fun_findPlanNo2('YFP5090253（0.3）/5090267（0.3）/TMR5090097（0.4）/0106（0.3）',4)='TMR5090106' then '测试通过' else '错误' end;
    	--父子缩写时后缀字母情况1
    	select case when dbo.fun_findPlanNo2('YFP5090253P（0.3）/5090267（0.3）/TMR5090097（0.4）/0106（0.3）',1)='YFP5090253P' then '测试通过' else '错误' end;
    	select case when dbo.fun_findPlanNo2('YFP5090253P（0.3）/5090267（0.3）/TMR5090097（0.4）/0106（0.3）',2)='YFP5090267P' then '测试通过' else '错误' end;
    	select case when dbo.fun_findPlanNo2('YFP5090253P（0.3）/5090267（0.3）/TMR5090097（0.4）/0106（0.3）',3)='TMR5090097' then '测试通过' else '错误' end;
    	select case when dbo.fun_findPlanNo2('YFP5090253P（0.3）/5090267（0.3）/TMR5090097（0.4）/0106（0.3）',4)='TMR5090106' then '测试通过' else '错误' end;
    	--父子缩写时后缀字母情况2
    	select case when dbo.fun_findPlanNo2('YFP5090253（0.3）/5090267（0.3）/TMR5090097（0.4）/0106（0.3）',1)='YFP5090253' then '测试通过' else '错误' end;
    	select case when dbo.fun_findPlanNo2('YFP5090253（0.3）/5090267P（0.3）/TMR5090097（0.4）/0106（0.3）',2)='YFP5090267P' then '测试通过' else '错误' end;
    	select case when dbo.fun_findPlanNo2('YFP5090253（0.3）/5090267（0.3）/TMR5090097P（0.4）/0106（0.3）',3)='TMR5090097P' then '测试通过' else '错误' end;
    	select case when dbo.fun_findPlanNo2('YFP5090253（0.3）/5090267（0.3）/TMR5090097（0.4）/0106P（0.3）',4)='TMR5090106P' then '测试通过' else '错误' end;
    	*/
    	declare @splitFlagStart varchar(2);
    	declare @splitFlagEnd varchar(2);
    	declare @IgnFlag varchar(2);
    	declare @UnicodeValue int;
    	declare @startindex int;
    	declare @endindex int;
    	declare @curIndex int;
    	declare @splitIndex int;
    	declare @curPlanNo varchar(150);
    	declare @tempPlanNo varchar(150);
    	declare @basePlanNo varchar(150);
    	declare @preBasePlanNo varchar(150);
    	declare @subBasePlanNo varchar(150);
    	declare @prefixLent int;
    	declare @sufixSplitFlag varchar(2);
    	declare @sufix varchar(10);
    	set @splitFlagStart = '（';
    	set @splitFlagEnd = '）';
    	set @IgnFlag = '/';
    	set @sufixSplitFlag = '-';
    	set @curIndex = 2;
    	set @preBasePlanNo = '';
    	set @subBasePlanNo = '';
    	set @sufix = '';
    	
    	set @basePlanNo=replace(substring(@planNo,1,dbo.char_index(@planNo,'（',1)-1),@IgnFlag,'');
    	if(@index = 1) 
    	begin
    		return @basePlanNo;
    	end
    	
    	/*
    	逻辑：
    	（1）分别找出基准计划号和子基准计划号，如:SPG5091760P（0.3）/1803P（1）/5101907P（0.7）/1961P（0.6）/2019P（0.4）',5)
    	则“1961P（0.6）”和“2019P（0.4）”的基准号是SPG5091760P，子基准号是5101907P，最终返回的计划号分别是：SPG5101961P、SPG5102019P
    	（2）基准计划号规则：找出离当前编号最近的一个前缀以字母开头的计划号，如'YFP5090253（0.3）/5090267（0.3）/TMR5090097（0.4）/0106（0.3）'
    	则"5090267"的基准号是：YFP5090253，而0106的基准号是TMR5090097
    	（3）子基准计划号规则：找出离当前编号最近的一个长度长于自身的计划编号，可以为空
    	修改记录：chenpeng 2015-12-15
    	（1）出现”BPG5050282P（0.9）/0282P-1（3.1）“这种情况时，其中的”-1“部分应在与父字符比对时去掉，合并后再加上，
    	实际结果应该是”BPG5050282P-1“不应该是”BPG50282P-1“；
    	*/
    	while(@curIndex <= @index)
    	begin--1
    		set @endindex=dbo.char_index(@planNo,@splitFlagStart,@curIndex);
    		set @startindex=dbo.char_index(@planNo,@splitFlagEnd,@curIndex-1)+1;
    		if(@endindex-@startindex>0)
    		begin--2
    			set @tempPlanNo = replace(substring(@planNo,@startindex,@endindex-@startindex),@IgnFlag,'');
    			/*
    			（1）出现”BPG5050282P（0.9）/0282P-1（3.1）“这种情况时，其中的”-1“部分应在与父字符比对时去掉，合并后再加上，
    			实际结果应该是”BPG5050282P-1“不应该是”BPG50282P-1“；
    			*/
    			set @splitIndex = dbo.char_index(@tempPlanNo,@sufixSplitFlag,1);
    			if(@splitIndex>0)
    			begin--3.1
    				set @sufix = substring(@tempPlanNo,@splitIndex-1,10);
    				set @tempPlanNo = substring(@tempPlanNo,0,@splitIndex);
    			end;--3.1
    			set @UnicodeValue = unicode(substring(@tempPlanNo,1,1));
    			if((@UnicodeValue > 97 and @UnicodeValue<122)  or (@UnicodeValue > 65 And @UnicodeValue < 90))
    			begin--3.2
    				set @basePlanNo = @tempPlanNo;
    			end;--3.3
    			if(len(@preBasePlanNo) > len(@tempPlanNo))
    			begin--3.4
    				set @subBasePlanNo = @preBasePlanNo;
    			end;--3.4
    			set @preBasePlanNo =  @tempPlanNo;
    		end;--2
    		set @curIndex = @curIndex + 1;
    	end;--1
    	
    	-- return @basePlanNo;
    	-- return @basePlanNo + ','  + @subBasePlanNo;
    	-- return @test;
    	--return @planNo;
    	set @endindex=dbo.char_index(@planNo,@splitFlagStart,@index);
    	set @startindex=dbo.char_index(@planNo,@splitFlagEnd,@index-1)+1;
    	-- return @endindex-@startindex;
    	set @curPlanNo = replace(substring(@planNo,@startindex,@endindex-@startindex),@IgnFlag,'');
    	/*
    	（1）出现”BPG5050282P（0.9）/0282P-1（3.1）“这种情况时，其中的”-1“部分应在与父字符比对时去掉，合并后再加上，
    	实际结果应该是”BPG5050282P-1“不应该是”BPG50282P-1“；
    	*/
    	set @splitIndex = dbo.char_index(@curPlanNo,@sufixSplitFlag,1);
    	set @sufix = '';
    	if(@splitIndex>0)
    	begin
    		set @sufix = substring(@curPlanNo,@splitIndex,10);
    		set @curPlanNo = substring(@curPlanNo,0,@splitIndex);
    	end;
    	
    	-- 如果当前计划编号前缀为字母，则直接返回
    	set @UnicodeValue = unicode(substring(@curPlanNo,1,1))
    	if((@UnicodeValue > 97 and @UnicodeValue<122)  or (@UnicodeValue > 65 And @UnicodeValue < 90))
    	begin
    		return @curPlanNo + @sufix;
    	end
    	
    	/*
    	依据上面找到的基准计划号和子基准计划号及当前编号拼接出完整的计划编号，规则是：
    	（1）首先把当前编号与子基准比较，多出的前缀部分加入到当前编号前方
    	（2）在（1）中拼接完后，接在进一步与基准比较，多出的前缀部分加入再次加入到前方
    	*/
    	--子基准计划号比对加前缀
    	--return @curPlanNo +',' + @basePlanNo + ','  + @subBasePlanNo;
    	set @curPlanNo = dbo.char_replace(@subBasePlanNo,@curPlanNo);
    	
    	--父基准编号比对加前缀
    	set @curPlanNo = dbo.char_replace(@basePlanNo,@curPlanNo);
    	return @curPlanNo + @sufix;
    end

**以上代码中还带有测试用例，方便在修改后判断是否可发布。**

**2.        复杂SQL推荐写法样例：**

SELECT     
    		M1.操作者, M1.预测时间, M1.productNo, M1.useCnt- M2.useCnt + M2.nouseCnt AS 第一周的计划套数, M1.第二周的计划套数
    	FROM   
    		(
    			SELECT     
    				dbo.电装主计划_主表.操作者, dbo.电装主计划_主表.预测时间, dbo.电装主计划_明细.产品代码 AS productNo, 
    				SUM(ISNULL(dbo.电装主计划_明细.未完情况, 0)+ISNULL(dbo.电装主计划_明细.w1, 0) + ISNULL(dbo.电装主计划_明细.w2, 0) + ISNULL(dbo.电装主计划_明细.w3, 0) 
    				+ ISNULL(dbo.电装主计划_明细.w4, 0) + ISNULL(dbo.电装主计划_明细.w5, 0) + ISNULL(dbo.电装主计划_明细.w6, 0) 
    				+ ISNULL(dbo.电装主计划_明细.w7, 0) + ISNULL(dbo.电装主计划_明细.w8, 0) + ISNULL(dbo.电装主计划_明细.w9, 0) 
    				+ ISNULL(dbo.电装主计划_明细.w10, 0) + ISNULL(dbo.电装主计划_明细.w11, 0) + ISNULL(dbo.电装主计划_明细.w12, 0) 
    				+ ISNULL(dbo.电装主计划_明细.w13, 0) + ISNULL(dbo.电装主计划_明细.w14, 0)) AS useCnt,
    				SUM( ISNULL(dbo.电装主计划_明细.w15, 0) + ISNULL(dbo.电装主计划_明细.w16, 0)+ISNULL(dbo.电装主计划_明细.w17, 0)
    				+ ISNULL(dbo.电装主计划_明细.w18, 0)+ ISNULL(dbo.电装主计划_明细.w19, 0) + ISNULL(dbo.电装主计划_明细.w20, 0) 
    				+ ISNULL(dbo.电装主计划_明细.w21, 0) + ISNULL(dbo.电装主计划_明细.w22, 0) + ISNULL(dbo.电装主计划_明细.w23, 0)) AS 第二周的计划套数
    			FROM          
    				dbo.电装主计划_主表 
    				LEFT OUTER JOIN dbo.电装主计划_明细 ON dbo.电装主计划_主表.RCId = dbo.电装主计划_明细.RCId
    			where 
    				预测时间=@JHDate 
    			GROUP BY 
    				dbo.电装主计划_明细.产品代码, dbo.电装主计划_主表.操作者, dbo.电装主计划_主表.预测时间
    		) AS M1
    		LEFT OUTER JOIN
    		(
    			SELECT     
    				PD.产品代码 AS productNo, 
    				 SUM(CASE WHEN DZ.productNo IS NULL THEN 0 ELSE isnull(已领数量, 0) END) AS useCnt, 
    				 SUM(CASE WHEN DZ.productNo IS NULL THEN isnull(未领数量, 0) ELSE 0 END) AS nouseCnt
    			FROM    
    				dbo.包材主计划表_明细 as PD
    				LEFT OUTER JOIN
    				(
    					SELECT     
    						distinct A.productNo, dbo.fun_findPlanNo2(A.planNo, B.No) AS subPlanNo
    					FROM          
    					(
    						SELECT   
    							产品代码 AS productNo, 计划编号 AS planNo, dbo.char_findcnt(N'（', 计划编号) AS planNoCnt
    						FROM    
    							dbo.电装主计划_主表 LEFT OUTER JOIN
    							dbo.电装主计划_明细 ON dbo.电装主计划_主表.RCId = dbo.电装主计划_明细.RCId
    						where 预测时间=@JHDate 
    					) AS A
    					INNER JOIN dbo.SeqNumber AS B  ON A.planNoCnt >= B.No  
    				) AS DZ 
    				ON PD.产品代码 = DZ.productNo AND PD.计划编号 = DZ.subPlanNo 
    			GROUP BY 
    				PD.产品代码
    		) AS M2 
    		ON M1.productNo = M2.productNo 
    	order by 
    		第一周的计划套数