U9存储过程的问题及改进方法.doc

U9存储过程的问题及改进方法（初稿）对U9的存储过程和函数大致看了一遍。问题很多，先不管效率如何，很多存储过程的隐患将造成今后的Bug。主要问题及改进办法如下。批量插入，可以不使用游标，而使用insert into . select .见下例：/*这个部分要改写declare parentItemsCur cursor for select id from cbo_itemmaster where id not in (select itemmaster from cbo_bomcomponent where (EffectiveRange_FromDate0date) and (dateEffectiveRange_toDate1) and bommaster in (select id from cbo_bommaster where organization=organizationID )open parentItemsCurfetch NEXT from parentItemsCur INTO itemIDwhile fetch_status=0 begin INSERT INTO #LLCode (itemMasterID,levelCode) VALUES (itemID,LEVEL); fetch NEXT from parentItemsCur INTO itemID endclose parentItemsCurdeallocate parentItemsCur*/-下面为改写部分:insert into #LLCode (itemMasterID,levelCode)select id,LEVEL from cbo_itemmaster where id not in (select itemmaster from cbo_bomcomponent where (EffectiveRange_FromDate0date) and (date如果没有条件限制删除一个表，使用truncate table能够更加迅速！见下例：/*这个部分要改写 delete from #parentItems */ -下面为改写部分: truncate table #parentItems -循环体中不要包含声明变量语句。否则会出现重复定义错误。如：While()BeginDeclare aa intEnd用表中值对变量赋值请使用以下方式：select var1=col1,var2=col2 from tablename .代替set var1=(select col1 from tablename . )set var2=(select col2 from tablename . )书写的规范。判断逻辑块或循环逻辑块，哪怕只有一条语句，也要用begin.end包起来。并且要有相应的缩行。如：If()BeginEndElseBeginEnd声明变量类型时一定要注意！看下面的例子：（1）、精度数字问题ALTER FUNCTION dbo.fn_MRP_AreEqual(ParamOnedecimal,ParamTwodecimal)RETURNS BitASBEGINDECLARE Resultbitif abs(ParamOne - ParamTwo) 0.001set Result = 1elseset Result = 0RETURN ResultEND这个例子中传入参数一和传入参数二由于类型声明为decimal，没有指定精度，将导致传入参数为有精度值时错误！例如调用：select dbo.fn_MRP_AreEqual (2.12,2.11)将错误的返回1。正确的方法是：ParamOnedecimal(18,4),ParamTwodecimal(18,4)（2）、字符串的应用陷阱。如：cast(aa as varchar)是我们存储过程中非常常见的写法。但是这里没有指定varchar的大小，默认为30个。当aa超出30个的时候会被默认截断，而且不会提示错误！这是引发Bug的隐患。建议显示指定varchar大小。如：cast(aa as varchar(100)另外我们的表字符类型几乎都是nvarchar，希望在存储过程中也统一使用nvarchar类型。无用的order by。如：/*确定物料对应的生产线*/SELECT pl = A.ID FROM CBO_ItemProductionLineLink AS AWHERE A.ItemMaster = Item /*A.ItemMasterVersion = ItemVersion*/ORDER BY A.Priority ASC这是一条赋值语句，这个时候order by 有什么用？多余的判断。如：/*处理批量为0的情况*/IF(Lot = 0)BEGINIF(l = 0)BEGINSET Lot = 1ENDEND这条语句可以改写为：IF(Lot = 0 and l = 0)BEGINSET Lot = 1ENDNull的陷阱。如：（1）、/*处理UOM为空的情况*/IF(UOM = null)BEGINIF(EXISTS(SELECT * FROM CBO_BOMMaster AS A WHERE A.ProductUOM = builduom)BEGINSET UOM = builduomENDELSEBEGINSET UOM = invuomENDEND这个判断中的语句永远也不会被执行！因为null=null也是false！涉及到null值的判断一律要用is null上面改写为：/*处理UOM为空的情况*/IF(UOM is null)BEGINIF(EXISTS(SELECT * FROM CBO_BOMMaster AS A WHERE A.ProductUOM = builduom)BEGINSET UOM = builduomENDELSEBEGINSET UOM = invuomENDEND（2）、declare aa varchar(20)set aa=aa+ttselect aa我们可能以为上面的程序块会返回tt。但是很不幸，由于没有为aa初始化赋值，导致null+tt返回的是null！所以我们定义变量后，在使用之前对其设置初始值绝对是个好习惯！没有必要的判断。如：-flag为bit类型if(flag = 0)beginWith Calendar(Date) AS(Select Top(Interval) WorkDate from Base_WorkCalendarDay where WorkDate = Date and IsWorkDay = 1 and WorkCalendar = WorkCalendar )select WorkDate = min(Date) from Calendarend这里面使用到了新特性CTE，很好！但是既然flag为bit类型。其值只有0和1不必再判断if(flag = 1)了。直接else即可！命名问题。其他的自定义函数都定义为fn_业务名_.很好！希望dbo.Increase这个也保持命名一致！这个自定义函数是生成主键ID的。现在应该没有用了吧？动态执行SQL的方法。动态执行SQL请统一使用：execute sp_executesql 存储过程名execute sp_executesql比execute相比能够更好的语句重用性，减少编译次数。游标的销毁。对游标的销毁最好先close cursorname然后再deallocate cursorname。存储过程中的排序规则。看到有些存储过程中显式的使用了字符排序规则如：Chinese_PRC_CI_AS我们的产品今后会支持多国语言，这样使用很容易产生乱码。还希望引起重视！慎重使用ROWCOUNT。一些错误： -这个地方不用order by SELECT RowCountInTempTable=count(*) FROM Table_Temp_ItemInfo ORDER BY Item -上面的select肯定会传回1，下面的ROWCOUNT=0永远不可能触发。 -IF (RowCountInTempTable=0) IF (ROWCOUNT=0) BEGIN RETURN null END临时表的处理。如：delete from #mrp_itemsinsert into #mrp_items (FactoryOrg,ItemNum,PurOrg,purOrgItemNum) select CBO_OrgBusinessRelation.FromOrg,CBO_OrgBusinessRelation.FromItem,CBO_OrgBusinessRelation.ToOrg,CBO_OrgBusinessRelation.ToItem from MRP_INNER_ExpandItemMapping,CBO_OrgBusinessRelation where MRP_INNER_ExpandItemMapping.OrganizationNumber_ID = CBO_OrgBusinessRelation.FromOrg and CBO_OrgBusinessRelation.BusinessRelationType = 5 and MRP_INNER_ExpandItemMapping.Item_ID = CBO_OrgBusinessRelation.FromItem 在上面程序段的更上级没有看到建立临时表#mrp_items的代码。请注意！SQL Server的临时表在数据库连接断开后就会自动销毁！上面的delete from #mrp_items容易出现错误！而且我们的存储过程中大量的应用到了临时表。在此临时表的操作要点说明一下：（1）、作用域SQL Server临时表分为两种：一种是数据库连接级临时表。声明的方法为：create table #aa .这个#aa只是在这个创建临时表的数据库连接中可见。其他数据库连接建立#aa后不会和其发生冲突，因为DBMS自动为数据库连接级的临时表加了_.后缀，所以各个数据库连接间的临时表不会互相冲突。另一种是全局临时表。声明的方法为：create table #aa .这个#aa是所有数据库连接都可见的！其他数据库连接如果再建立同名全局临时表会出错。（2）、销毁时间当建立临时表的数据库连接断开数据库时，两种临时表都会自动销毁。但是我们不要让系统自动进行回收。在使用完毕后，请显式的将其删掉！操作流程：create table #aa .operate #aadrop table #aa（3）、更好的临时表类型：表变量表变量不同于临时表的是其优先在内存中操作，而且耗费的资源比临时表要小得多。而且存储过程结束后其会和其他变量一样被销毁。但是当记录集过大的时候，表变量也会在tempdb中生成，而且有从内存和磁盘的往返，会更加耗费资源。因此建议当记录过多的时候还是要使用临时表。表变量的用法：declare vartable table(aa int,bb varchar(20)insert into vartable values(1,Tom)insert into vartableselect 2,Scottunion allselect 3,Jamesunion allselect 4,Birdupdate vartable set aa=aa+1select * from vartabledelete from vartable变量的判断不要放在SQL语句中selectfrom #demand_temp T inner join SD_SOHeader A on T.Header=A.ID inner join SD_SOLine B on T.Line = B.ID inner join SD_ShipLine C on T.PlanLine = C.ID inner join #mrp_items D on B.CurOrganization = D.OperatingOrg and B.ItemID = D.OperatingOrgItem inner join CBO_Warehouse E on B.Warehouse=E.ID and E.Code between FromWh and ToWhwhere C.Demand=DemandCode and (IncludeRelyn = 1 and D.IsRelativeEnterprise = 1) or (IncludeSoyn = 1 and D.IsRelativeEnterprise = 0);对变量的判断要放在if else 语句中做。不要放在SQL中进行判断。如果放在SQL中，如果条件不成立，也会对表进行查询，严重影响性能！如何使用UBFAllocSerials生成主键存储过程批量生成数据当调用UBFAllocSerials存储过程的时候，其会返回一批主键ID的起始ID，当在存储过程中想成批插入数据，还要作循环对起始主键ID进行累加。如果出现这种情况，可以考虑使用下面的函数解决其效率问题。例如：declare sqlnv