1、主过程proc_insert_cmdb_main
-- =============================================-- Author: -- Create date: 2014/3/5-- Description: 变更管理更新CMDB 主存储过程-- =============================================ALTER PROCEDURE [dbo].[proc_insert_cmdb_main] @CI_ORDER_HEAD_ID VARCHAR(50)='', @FLAG CHAR(1) = 'N' outputAS declare @RESULT1 VARCHAR(200) declare @sql1 VARCHAR(200) declare @CI_ORDER_LINE_ID DECIMAL declare @CI_ID DECIMAL declare @CI_CODE VARCHAR(100) declare @CI_NAME VARCHAR(500) declare @CATEGORY_CODE VARCHAR(50) declare @FN_7 DECIMAL declare @FN_8 DECIMAL declare @sql2 VARCHAR(200) /*执行的SQL语句*/ declare @num int declare @countnum int BEGIN SET NOCOUNT ON; set @RESULT1 = '' set @sql1 = '' set @CI_ORDER_LINE_ID = 0 set @CI_ID = 0 set @CI_CODE= '' set @CI_NAME= '' set @CATEGORY_CODE = '' set @FN_7 = 0 set @FN_8 = 0 set @sql2 = '' /*执行的SQL语句*/ set @num = 0 set @countnum = 1/*============================================================ 生成唯一的CI_CODE：CI_CODE=行表catagery_code+CI_ORDER_LINE_ID ============================================================*/ set @sql1='update T_CI_ORDER_LOGICAL set CI_CODE = CATEGORY_CODE+convert(varchar(18),CI_ORDER_LINE_ID) where [email protected]_ORDER_HEAD_ID exec (@sql1) set @sql1='update T_CI_ORDER_APPLICATION set CI_CODE = CATEGORY_CODE+convert(varchar(18),CI_ORDER_LINE_ID) where [email protected]_ORDER_HEAD_ID exec (@sql1) set @sql1='update T_CI_ORDER_PHYSICAL set CI_CODE = CATEGORY_CODE+convert(varchar(18),CI_ORDER_LINE_ID) where [email protected]_ORDER_HEAD_ID exec (@sql1)/*=============================================================逐个获取更新的配置项条目（行表记录），并调用子存储过程proc_insert_cmdb 逐一处理===============================================================*/ select @countnum = count(*) from T_ORDERCONFIGFORM_V where [email protected]_ORDER_HEAD_ID if @countnum=0 set @FLAG='Y' /*进行数据更新操作*/ DECLARE MyCursor1 CURSOR LOCAL FORWARD_ONLY for select CI_ORDER_LINE_ID,CI_ID,CI_CODE,CI_NAME,CATEGORY_CODE,FN_7,FN_8 from T_ORDERCONFIGFORM_V where [email protected]_ORDER_HEAD_ID OPEN MyCursor1 FETCH NEXT FROM MyCursor1 INTO @CI_ORDER_LINE_ID,@CI_ID,@CI_CODE,@CI_NAME,@CATEGORY_CODE,@FN_7,@FN_8 WHILE @@FETCH_STATUS = 0 BEGIN /*set @RESULT1 = convert(varchar(18),@CI_ORDER_LINE_ID)+convert(varchar(18),isnull(@CI_ID,0))[email protected][email protected]_CODE+convert(varchar(18),isnull(@FN_7,0))*/ /* print('检测点')*/ /* 执行数据写入操作 */ EXEC proc_insert_cmdb @CI_ORDER_HEAD_ID,@CATEGORY_CODE,@CI_ORDER_LINE_ID,@CI_ID,@CI_CODE,@CI_NAME,@FN_7,@FN_8,@FLAG OUTPUT FETCH NEXT FROM MyCursor1 INTO @CI_ORDER_LINE_ID,@CI_ID,@CI_CODE,@CI_NAME,@CATEGORY_CODE,@FN_7,@FN_8 END CLOSE MyCursor1 DEALLOCATE MyCursor1 END
?
2、子过程proc_insert_cmdb
-- =============================================-- Author: <Author,,Name>-- Create date: <Create Date,,>-- Description: <Description,,>-- =============================================ALTER PROCEDURE [dbo].[proc_insert_cmdb] @CI_ORDER_HEAD_ID VARCHAR(50) = '', @CATEGORY_CODE CHAR(50) = '', @ORDER_LINE_ID VARCHAR(20) = '', @CI_ID DECIMAL = 0, @CI_CODE VARCHAR(100) = '', @CI_NAME VARCHAR(100) = '', @FN_7 DECIMAL = 0, @FN_8 DECIMAL = 0, @FLAG CHAR(1) = 'N' outputAS declare @CATEGORY_CI_TABLENAME VARCHAR(50) declare @result VARCHAR(2000) declare @result_from VARCHAR(2000) declare @result_to VARCHAR(2000) declare @ATTRIBUTE1 VARCHAR(50) declare @ATTRIBUTE2 VARCHAR(50) declare @ATTRIBUTE3 VARCHAR(50) declare @S_TEMP VARCHAR(500) declare @RECORD_VERSION DECIMAL(18,2) declare @CI_ORDER_CODE VARCHAR(500) declare @countnum_temp intBEGIN SET NOCOUNT ON; set @CATEGORY_CI_TABLENAME ='' set @result = '' set @result_from = '' set @result_to = '' set @ATTRIBUTE1 ='' set @ATTRIBUTE2 ='' set @ATTRIBUTE3 ='' set @S_TEMP ='' set @RECORD_VERSION=0 set @CI_ORDER_CODE='' set @countnum_temp =0 /*================================预处理1：获取行表的配置参数=================================*/select @CATEGORY_CI_TABLENAME = CATEGORY_CI_TABLENAME,@ATTRIBUTE1=ATTRIBUTE1,@ATTRIBUTE2=ATTRIBUTE2,@ATTRIBUTE3=ATTRIBUTE3 from T_CI_CATEGORY where CATEGORY_CODE= @CATEGORY_CODE/*================================预处理：获取行表的更新sql 赋值给 @result=================================*/IF (@ATTRIBUTE2='A') begin set @result=(select ATTRIBUTE_CONFIG_CODE FROM T_ATTRIBUTE_CONFIG where [email protected]_CODE and CMDB_UPDATE_FLAG='Y' for xml path('')) if LEN(@result)>0 begin set @result_to=REPLACE( @result, '</ATTRIBUTE_CONFIG_CODE><ATTRIBUTE_CONFIG_CODE>',',') set @result_to=REPLACE( @result_to, '<ATTRIBUTE_CONFIG_CODE>','') set @result_to=REPLACE( @result_to, '</ATTRIBUTE_CONFIG_CODE>','') set @result_from=REPLACE( @result_to, 'CREATED_DATE','getDate()') set @result_from=REPLACE( @result_from, 'CATEGORY_CODE',[email protected]+'''') set @result_from=REPLACE( @result_from, 'LAST_UPDATE_DATE','getDate()') set @result_from=REPLACE( @result_from, 'RECORD_VERSION','1') set @result_from=case when substring (@CATEGORY_CODE, 1 , 13)='ORDER_ONSHELF' then REPLACE(REPLACE( @result_from, 'GROUNDING_DATE','getDate()') ,'GUARANTEE_DATE','dateadd(year,FN_10,BUY_DATE)') when substring (@CATEGORY_CODE, 1 , 12)='ORDER_ONLINE' then REPLACE( @result_from, 'ON_LINE_DATE','getDate()') else @result_from END set @result = 'insert into ' + @ATTRIBUTE1 + '(' + @result_to +') select '+ @result_from +' from [email protected]_CI_TABLENAME +' where [email protected]_LINE_ID end end else if (@ATTRIBUTE2='U') begin set @result=(select ATTRIBUTE_CONFIG_CODE+'=b.'+ATTRIBUTE_CONFIG_CODE+',' FROM T_ATTRIBUTE_CONFIG where [email protected]_CODE and CMDB_UPDATE_FLAG='Y' for xml path('')) /* PRINT([email protected])*/ if len(@result)>0 begin set @result=substring(@result,1,LEN(@result)-1) set @result=REPLACE( @result, 'LAST_UPDATE_DATE=b.LAST_UPDATE_DATE','LAST_UPDATE_DATE=getDate()') set @result=case when substring(@CATEGORY_CODE,1, 14)='ORDER_OFFSHELF' then REPLACE( @result, 'UNDER_DATE=b.UNDER_DATE','UNDER_DATE=getDate()') when substring (@CATEGORY_CODE, 1 , 12)='ORDER_ONLINE' then REPLACE( @result, 'ON_LINE_DATE=b.ON_LINE_DATE','ON_LINE_DATE=getDate()') when substring (@CATEGORY_CODE, 1 , 13)='ORDER_OFFLINE' then REPLACE( @result, 'OFF_LINE_DATE=b.OFF_LINE_DATE','OFF_LINE_DATE=getDate()') else @result end set @result = 'update '+ @ATTRIBUTE1 +' set '+ @result + ' from [email protected] + ' a,[email protected]_CI_TABLENAME +' b where a.ci_id=b.ci_id and [email protected]_LINE_ID end end/* print ('参数: @[email protected]_CODE+' @[email protected]_LINE_ID +' @[email protected]_NAME) print ('参数CI_ID: @CI_ID=:'+convert(varchar(18),@CI_ID) ) print([email protected][email protected]) print('断点2')*//*================================================开启事务==================================================*/BEGIN TRANSACTIONBEGIN TRY --print([email protected][email protected]) /*============================================== 执行语句1: 更新行表数据更新sql @result ================================================*/ if len(@result)>0 begin exec (@result) /*特殊处理：*/ if (@ATTRIBUTE2='U') begin /*设备下线时清除设备上线时的配置数据*/ if @CATEGORY_CODE='ORDER_OFFLINE_SERVER' begin set @result = 'update '+ @ATTRIBUTE1 +' set RECORD_VERSION=RECORD_VERSION+1,USE_OBJECTIVE=null,FV_3=null, FV_6=null,FV_7=null,FV_8=null,FV_9=null,FV_10=null,FV_11=null,FV_12=null,FV_13=null,FV_14=null, FV_20=null,FV_22=null,FN_4=null,FN_5=null,FN_6=null where ci_id='+ltrim(convert(varchar(18),@CI_ID)) --print([email protected]) exec (@result) end else /*处理记录版本号*/ begin set @result = 'update '+ @ATTRIBUTE1 +' set RECORD_VERSION=RECORD_VERSION+1 where USE_STATUS not in (''Canceled'') and ci_id='+ltrim(convert(varchar(18),@CI_ID)) --print([email protected]) exec (@result) end end end/*================================================================执行语句2: [email protected]_ID 2.对于新增的配置项，向行表回写CI_ID==============================================================*/set @result=''IF (@ATTRIBUTE2='A') begin /* set @result='SELECT @a=CI_ID FROM '+ @ATTRIBUTE1 +' WHERE [email protected]_NAME+'''' print (' @[email protected]) exec sp_executesql @result,[email protected] int output',@CI_ID output print ('参数CI_ID: @CI_ID=:'+convert(varchar(18),@CI_ID)) */ IF @CATEGORY_CODE='ORDER_ONLINE_VM' or @CATEGORY_CODE='ORDER_ONLINE_LEASEDLINE' SELECT @CI_ID=CI_ID FROM T_CI_LOGICAL WHERE [email protected]_CODE ELSE IF SUBSTRING(@CATEGORY_CODE,1,16)='ORDER_ONLINE_APP' SELECT @CI_ID=CI_ID FROM T_CI_APPLICATION WHERE [email protected]_CODE ELSE IF SUBSTRING(@CATEGORY_CODE,1,13)='ORDER_ONSHELF' SELECT @CI_ID=CI_ID FROM T_CI_PHYSICAL WHERE [email protected]_CODE set @result = 'update [email protected]_CI_TABLENAME +' set CI_ID='+Ltrim(convert(varchar(18),@CI_ID))+',[email protected]+'_'+Ltrim(convert(varchar(18),@CI_ID))+''' WHERE ([email protected]_LINE_ID+''')' /*print('A-IP配置更新：[email protected])*/ exec (@result) set @result = 'update [email protected] +' set [email protected]+'_'+Ltrim(convert(varchar(18),@CI_ID))+''' WHERE ([email protected]_CODE+''')' exec (@result) end /*print ('参数fn8: @CI_ID=:'+convert(varchar(18),@FN_8)) print ('参数CI_ID: @CI_ID=:'+convert(varchar(18),@CI_ID)) */ --print('断点3')/*================================================================执行语句3: 更新关联表 1. 虚拟机上线/应用上线：增加关系，更新相关配置项状态 （1）虚拟机关联服务器V_S （2）应用关联虚拟机A_V 2. 虚拟机下线/应用下线：注销关系，更新相关配置项状态==============================================================*/SET @countnum_temp=0select @CI_ORDER_CODE=CI_ORDER_CODE from T_CI_ORDER_HEAD where [email protected]_ORDER_HEAD_IDIF @CATEGORY_CODE='ORDER_ONLINE_VM' BEGIN INSERT INTO T_CI_RELATIONSHIP (CI_PHYSICAL_ID,CI_LOGICAL_ID,CI_RELATIONSHIP,DELETED_FLAG) VALUES(@FN_7,@CI_ID,'V_S','N') SELECT @S_TEMP=USE_STATUS FROM T_CI_PHYSICAL WHERE [email protected]_7 IF @S_TEMP='Idle' BEGIN select @RECORD_VERSION=RECORD_VERSION FROM T_CI_PHYSICAL WHERE [email protected]_7 UPDATE T_CI_PHYSICAL SET USE_STATUS='Using',LAST_UPDATE_DATE=getDate(),RECORD_VERSION=RECORD_VERSION+0.01 WHERE [email protected]_7 /*插入日志*/ INSERT INTO T_CI_UPDATE_LOG(TABLE_NAME,MASTER_FLAG,CATEGORY_CODE,CI_ID,CI_BATCH_ID,UPDATE_NAME,UPDATE_BEFORE_NAME,UPDATE_BEFORE_VALUE,UPDATE_AFTER_NAME,UPDATE_AFTER_VALUE,UPDATE_FLAG,DESCRIPTION,CREATED_BY,CREATED_DATE) VALUES('T_CI_PHYSICAL','Y','CMDB_SERVER',@FN_7,@RECORD_VERSION+0.01,'USE_STATUS','空闲','Idle','使用','Using','U', [email protected]_ORDER_CODE,'PROGRAM',GETDATE()) END END ELSE IF @CATEGORY_CODE='ORDER_OFFLINE_VM' BEGIN select @FN_7=FN_7 from T_CI_LOGICAL where [email protected]_ID delete from T_CI_RELATIONSHIP WHERE [email protected]_ID AND [email protected]_7 SELECT @countnum_temp=COUNT(RELATIONSHIP_ID) FROM T_CI_RELATIONSHIP WHERE [email protected]_7 AND DELETED_FLAG='N' AND ((CASE WHEN CI_LOGICAL_ID=NULL THEN 0 ELSE CI_LOGICAL_ID END)>0 OR (CASE WHEN CI_APPLICATION_ID=NULL THEN 0 ELSE CI_APPLICATION_ID END)>0) IF @countnum_temp=0 begin select @RECORD_VERSION=RECORD_VERSION FROM T_CI_PHYSICAL WHERE [email protected]_7 UPDATE T_CI_PHYSICAL SET USE_STATUS='Idle',LAST_UPDATE_DATE=getDate(),RECORD_VERSION=RECORD_VERSION+0.01 WHERE [email protected]_7 /*插入日志*/ INSERT INTO T_CI_UPDATE_LOG(TABLE_NAME,MASTER_FLAG,CATEGORY_CODE,CI_ID,CI_BATCH_ID,UPDATE_NAME,UPDATE_BEFORE_NAME,UPDATE_BEFORE_VALUE,UPDATE_AFTER_NAME,UPDATE_AFTER_VALUE,UPDATE_FLAG,DESCRIPTION,CREATED_BY,CREATED_DATE) VALUES('T_CI_PHYSICAL','Y','CMDB_SERVER',@FN_7,@RECORD_VERSION+0.01,'USE_STATUS','使用','Using','空闲','Idle','U', [email protected]_ORDER_CODE,'PROGRAM',GETDATE()) end END ELSE IF SUBSTRING(@CATEGORY_CODE,1,16)='ORDER_ONLINE_APP' BEGIN INSERT INTO T_CI_RELATIONSHIP (CI_LOGICAL_ID,CI_APPLICATION_ID,CI_RELATIONSHIP,DELETED_FLAG) VALUES(@FN_8,@CI_ID,'A_V','N') SELECT @S_TEMP=USE_STATUS FROM T_CI_LOGICAL WHERE [email protected]_8 IF @S_TEMP='Idle' BEGIN select @RECORD_VERSION=RECORD_VERSION FROM T_CI_LOGICAL WHERE [email protected]_8 UPDATE T_CI_LOGICAL SET USE_STATUS='Using',LAST_UPDATE_DATE=getDate(),RECORD_VERSION=RECORD_VERSION+0.01 WHERE [email protected]_8 /*插入日志*/ INSERT INTO T_CI_UPDATE_LOG(TABLE_NAME,MASTER_FLAG,CATEGORY_CODE,CI_ID,CI_BATCH_ID,UPDATE_NAME,UPDATE_BEFORE_NAME,UPDATE_BEFORE_VALUE,UPDATE_AFTER_NAME,UPDATE_AFTER_VALUE,UPDATE_FLAG,DESCRIPTION,CREATED_BY,CREATED_DATE) VALUES('T_CI_LOGICAL','Y','CMDB_VM',@FN_8,@RECORD_VERSION+0.01,'USE_STATUS','空闲','Idle','使用','Using','U', [email protected]_ORDER_CODE,'PROGRAM',GETDATE()) END END ELSE IF SUBSTRING(@CATEGORY_CODE,1,17) ='ORDER_OFFLINE_APP' BEGIN select @FN_8=FN_8 from T_CI_APPLICATION where [email protected]_ID delete from T_CI_RELATIONSHIP WHERE [email protected]_ID AND [email protected]_8 SELECT @countnum_temp=COUNT(RELATIONSHIP_ID) FROM T_CI_RELATIONSHIP WHERE [email protected]_8 AND DELETED_FLAG='N' AND (CASE WHEN CI_APPLICATION_ID=NULL THEN 0 ELSE CI_APPLICATION_ID END)>0 IF @countnum_temp=0 BEGIN select @RECORD_VERSION=RECORD_VERSION FROM T_CI_LOGICAL WHERE [email protected]_8 UPDATE T_CI_LOGICAL SET USE_STATUS='Idle',LAST_UPDATE_DATE=getDate(),RECORD_VERSION=RECORD_VERSION+0.01 WHERE [email protected]_8 /*插入日志*/ INSERT INTO T_CI_UPDATE_LOG(TABLE_NAME,MASTER_FLAG,CATEGORY_CODE,CI_ID,CI_BATCH_ID,UPDATE_NAME,UPDATE_BEFORE_NAME,UPDATE_BEFORE_VALUE,UPDATE_AFTER_NAME,UPDATE_AFTER_VALUE,UPDATE_FLAG,DESCRIPTION,CREATED_BY,CREATED_DATE) VALUES('T_CI_LOGICAL','Y','CMDB_VM',@FN_8,@RECORD_VERSION+0.01,'USE_STATUS','使用','Using','空闲','Idle','U', [email protected]_ORDER_CODE,'PROGRAM',GETDATE()) END END --print('断点4')/*==============================================================================执行语句4:更新IP配置信息T_CI_FOLLOW_TABLE，更新规则 1.变更配置项的IP配置数据更新规则：先删除'DELETE'',''UPDATE'两类数据，再增加'INSERT'',''UPDATE'两类数据 2.特殊处理：（1）服务器下线时删除IP配置数据 （2）虚拟机下线、专线下线、设备下架时，注销IP配置数据================================================================================*/ /*判断是否有IP更新记录*/ set @result='' select @countnum_temp = count(CI_ORDER_LINE_ID) from T_CI_ORDER_FOLLOW_TABLE where CI_ORDER_LINE_ID [email protected]_LINE_ID and [email protected]_CODE IF (@countnum_temp>0) /*有*/ BEGIN /*1.删除'update'和'delete'两类数据*/ delete from T_CI_FOLLOW_TABLE where FOLLOW_ID IN (select CONVERT(decimal,ATTRIBUTE10) from T_CI_ORDER_FOLLOW_TABLE where CI_ORDER_LINE_ID [email protected]_LINE_ID and [email protected]_CODE and ATTRIBUTE5 in ('DELETE','UPDATE')) /*2.插入'update'和'insert'两类数据*/ insert into T_CI_FOLLOW_TABLE (FOLLOW_CLASS,CATEGORY_CODE,CI_ORDER_LINE_ID,CI_ID,CONNECT_TYPE,SOURCE,TARGET,IP_ADDRESS,IP_NETCODE,IP_GATEWAY,IP_VLAN,IP_PORT, DESCRIPTION,CREATED_BY,CREATED_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,DELETED_FLAG,RECORD_VERSION,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3, ATTRIBUTE4) select FOLLOW_CLASS,ATTRIBUTE6,CI_ORDER_LINE_ID,CASE WHEN CI_ID IS NULL THEN @CI_ID WHEN CI_ID=0 THEN @CI_ID ELSE CI_ID END ,CONNECT_TYPE,SOURCE,TARGET,IP_ADDRESS,IP_NETCODE,IP_GATEWAY,IP_VLAN,IP_PORT, DESCRIPTION,CREATED_BY,CREATED_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,'N',RECORD_VERSION,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3, ATTRIBUTE4 from T_CI_ORDER_FOLLOW_TABLE WHERE T_CI_ORDER_FOLLOW_TABLE.CI_ORDER_LINE_ID = @ORDER_LINE_ID and [email protected]_CODE and ATTRIBUTE5 IN ('INSERT','UPDATE') END ELSE BEGIN set @result= case when @CATEGORY_CODE='ORDER_OFFLINE_SERVER' then 'delete from T_CI_FOLLOW_TABLE where CI_ID='+CONVERT(varchar(100), @CI_ID) when @CATEGORY_CODE='ORDER_OFFLINE_VM' OR @CATEGORY_CODE='ORDER_OFFSHELF_NETWORK' OR @CATEGORY_CODE='ORDER_OFFLINE_LEASEDLINE' then 'update T_CI_FOLLOW_TABLE set DELETED_FLAG=''Y'' where CI_ID='+CONVERT(varchar(100), @CI_ID)+' and [email protected]_CODE+'''' END --print('IP注销：[email protected]) if len(@result)>0 exec (@result) END--print('断点5') /*==================== 提交事务 ======================*/ COMMIT set @FLAG='Y'END TRYBEGIN CATCH ROLLBACKEND CATCH END
?
?
?