117-118-0000WINCC中使用vbs脚本调用SQL存储过程

SIEMENS技术总结文档号TN-00-01版本0.0日期10/05/08页数1of17标题：WINCC中使用vbs脚本调用SQL存储过程SIAS---颜春明技术总结WINCC中使用vbs脚本调用SQL存储过程版本日期作者校对批准说明0.12008-5-10颜春明SIEMENS技术总结文档号TN-00-01版本0.0日期10/05/08页数2of17标题：WINCC中使用vbs脚本调用SQL存储过程SIAS---颜春明目录TOC\o"1-5"\h\z应用范围3详述3适用的系统3硬、软件配置3解决方法3存储过程编写3VBS调用存储过程6SQLSERVER存储过程及触发器10存储过程定义10创建存储过程10存储过程调试12存储过程中游标的使用13触发器介绍以及实例错误!未定义书签。附件附件文档号说明SIEMENS技术总结文档号TN-00-01版本0.0日期10/05/08页数3of17标题：WINCC中使用vbs脚本调用SQL存储过程SIAS---颜春明应用范围详述SQL存储过程执行起来比SQL命令文本快得多。当一个SQL语句包含在存储过程中时，服务器不必每次执行它时都要分析和编译它。调用存储过程，可以认为是一个三层结构。这使你的程序易于维护。如果程序需要做某些改动，你只要改动存储过程即可你可以在存储过程中利用Transact-SQL的强大功能。一个SQL存储过程可以包含多个SQL语句。你可以使用变量和条件。这意味着你可以用存储过程建立非常复杂的查询，以非常复杂的方式更新数据库。最后，这也许是最重要的，在存储过程中可以使用参数。你可以传送和返回参数。你还可以得到一个返回值（从SQLRETURN语句）。WINCC中执行存储过程与执行SQL命令又有以下优点。异常处理可以封装在存储过程中，复杂的数据处理过程有存储过程来完成，避免执行多条SQL命令来完成某一数据处理。减轻了WINCC系统的负担，当VBS脚本中大量执行SQL命令，系统消耗大量资源，同时影响了系统其他脚本的执行。适用的系统该方法适用于安装有WINCC的系统中硬、软件配置安装有WINCC,SQLServer软件，无版本限制解决方法存储过程编写进入EnterpriseManager,选择我们所需要的数据库。在StoredProcedures中创建一个新的存储过程。存储过程sp_givaudan_GetAreaAMission实现了一下功能，根据submixing查找到该配方的所有原料纪录，然后而该配方而记录插入区域A需要执行表中。首先查找是否有该配方存在，然后，然后检查当前是否有配方在执行中。如果所有条件满足，更新当前配方的信息，根据产品代码，以及现场所有原料伐的信息，找到对应原料在那个区，那个管道中，最后写入当前执行表中。从以下存储过程实现功能可以看出，如果使用SQL命令，实现这些功能将非常繁琐。用StoredProcedure来实现，方便了系统的调试和维护，同时大大提高了系统的效率。以下存储过程使用在Givaudan伐岛控制系统中，系统相关说明请参考相关文档。实例1SIEMENS技术总结文档号TN-00-01版本0.0日期10/05/08页数4of17标题：WINCC中使用vbs脚本调用SQL存储过程SIAS---颜春明--GETTHEMISSIONLISTANDINSERTINTOARCHIVE_AREACREATEPROCEDUREsp_givaudan_GetAreaAMission@submixingNVARCHAR(12)='-',@totalweightBIGINTOUTPUT,@soproductNVARCHAR(14)OUTPUT,@resultNVARCHAR(300)OUTPUTASDeclare@RcordCountintDeclare@FinishedintBEGIN--首先在配方统计表中查找相关配方是否存在SELECT@RcordCount=Count(*)FROMUA#FormularsCheckWHEREsubmixing=@submixingIF(@RcordCount=0)配方不存在BEGINSET@result='Sub-Mixing:"'+@submixing+'"isnotavailable!!'SET@totalweight=0RETURNENDELSEBEGIN--检查配方是否已经完成SELECT@Finished=[finished]FROMUA#FormularsCheckWHEREsubmixing=@submixingIF((@Finished&4)=0)--配方没有完成(bit2置位说明已经完成)BEGINSELECT@RcordCount=Count(*)FROMUA#Formulars_AreaAIF(@RcordCount=0)一当前A区没有配方在执行SIEMENS技术总结文档号TN-00-01版本0.0日期10/05/08页数5of17标题：WINCC中使用vbs脚本调用SQL存储过程SIAS---颜春明BEGINSELECT@totalweight=SUM(WEIGHTREQUESTED)/1000FROMUA#FormularsWHEREsubmixing=@submixing--GettotalweightSELECT@soproduct=[soproduct]FROMUA#FormularsCheckWHEREsubmixing=@submixingGetSOCode--updatetheconfigurationinformationUPDATEUA#FormularsSETtubenumber=UATC.tubenumber,productname=UATC.itemname,internalarea=UATC.internalarea,lotnumber=UATC.lotnumber,location=UATC.location,pourduration=UATC.dosefactor,perturbation=UATC.startindex,reserved=UATC.description,tolerance=round(weightrequested*UATC.toleranceproportion,0)FROMUA#FormularsUAF,UA#TubesConfigurationUATCWHEREUAF.submixing=@submixingANDUAF.productcode=UATC.itemnumberANDUAF.finished=0ANDUATC.internalarea=1ANDUATC.internalstatus=1UPDATEUA#FormularsSETtubenumber=UATC.tubenumber,productname=UATC.itemname,internalarea=UATC.internalarea,lotnumber=UATC.lotnumber,location=UATC.location,pourduration=UATC.dosefactor,perturbation=UATC.startindex,reserved=UATC.description,amtpoured=1FROMUA#FormularsUAF,UA#TubesConfigurationUATCWHEREUAF.submixing=@submixingANDUAF.productcode=UATC.itemnumberANDUAF.finished=0ANDUATC.internalarea=1ANDUATC.internalstatus<>1SELECT@RcordCount=Count(*)FROMUA#FormularsWHEREsubmixing=@submixingANDinternalarea=1ANDfinished<=1IF@RcordCount>0-如果A区有配方需要执行BEGIN--insertintoformulars_areaa.INSERTINTOUA#Formulars_AreaASELECT*FROMUA#FormularsWHEREsubmixing=@submixingANDinternalarea=1ANDfinished<=1--updateformularcheckinformation@Finished&253--meanstheremovethesubmittempflagUPDATEUA#FormularsCheckSETUA#FormularsCheck.finished=(@Finished&253)|1WHEREsubmixing=@submixingENDELSE--A区没有配方BEGINUPDATEUA#FormularsCheckSETUA#FormularsCheck.finished=(@Finished&253)|5WHEREsubmixing=@submixingSET@totalweight=-1----NOMISSIONINAREAASIEMENS技术总结文档号TN-00-01版本0.0日期10/05/08页数6of17标题：WINCC中使用vbs脚本调用SQL存储过程SIAS---颜春明ENDENDELSEA区还有配方在执行中BEGINSET@result='TherearestillformularsinArea_A'SET@totalweight=0RETURNENDENDELSE—该配方已经完成BEGINSET@result='Sub-Mixing:"'+@submixing+'"HavebeensubmittedorHavedone.!!'SET@totalweight=0RETURNENDENDIF@@error=0SET@result=@result+'Good'ELSEBEGINSET@result=@result+'Fail'SET@totalweight=0ENDENDGO3.2VBS调用存储过程连接字符串，系统中多次执行存储过程，为了方便维护连接字符串，因为WINCC的vbs没有全局变量的，需要将连接字符串放在公共函数中。建议将projectmodulars中，standardmodulars中的函数在修改后，不能立刻生效，需要对standardmodulars函数进行重新调用，而连接字符串在项目重新加载后系统数据库名称会发生变化，需要经常修改。FunctionconString()

SIEMENS技术总结文档号TN-00-01版本0.0日期10/05/08页数7of17标题：WINCC中使用vbs脚本调用SQL存储过程SIAS---颜春明conString="Provider=SQLOLEDB;Server=GSHMW5306\WINCC;Database=CCCluster2080426090643R;IntegratedSecurity=SSPI;"EndFunction以下函数实现了对3.1小节中sp_givaudan_GetAreaAMission存储过程的调用。改函数可以有某一特点操作来触发执行。当操作人员完成配方条形鬲的扫描后将触发该函数。'AreaAGetBarCode'ToExecutetheprocedure"sp_givaudan_GetAreaAMission"'Moreinformationaboutprocedure"sp_givaudan_GetAreaAMission",pleasegotoSQLServerDimobjCmd'ADODB.Command变量Dimresult'返回结果DimobjTagbarcodeareaa'条形码值DimobjTag_barcode_getmissionDimobjTag_A_TotalWeightDimobjTag_A_SOProductDimobjTag_AreaA_Submixing_Flag为DimobjTag_barcode_getmissionDimobjTag_A_TotalWeightDimobjTag_A_SOProductDimobjTag_AreaA_Submixing_Flag为1’成功获取配方后置该变量为True嘈己方总重量'SOCode’配方执行标记，当有新配方可以执行时，该变量值DimobjTag_AreaA_TextFlagDimConnectionString'连接字符串变量SetobjTag_barcode_areaa=HMIRuntime.Tags("A_SubmixingChar")SIEMENS技术总结文档号TN-00-01版本0.0日期10/05/08页数8of17标题：WINCC中使用vbs脚本调用SQL存储过程SIAS---颜春明SetobjTag_barcode_getmission=HMIRuntime.Tags("A_Get_Mission")SetobjTag_A_TotalWeight=HMIRuntime.Tags("A_TotalWeight")SetobjTag_A_SOProduct=HMIRuntime.Tags("A_SOProduct")SetobjTag_AreaA_Submixing_Flag=HMIRuntime.Tags("A_Flag")SetobjTag_AreaA_TextFlag=HMIRuntime.Tags("A_TextFlag")objTag_barcode_areaa.Read'If(Len(objTag_barcode_areaa.Value)=0)Then'条形码为空时退出'HMIRuntime.Trace"SubmixingCharisnull!!"&vbCrLf'ExitSub'EndIfSetobjCmd=CreateObject("ADODB.Command")ConnectionString=conString()'获取到上面的链接字符串ObjCmd.ActiveConnection=ConnectionStringObjCmd.CommandType=4'StoredProcedures等同二ObjCmd.CommandText="sp_givaudan_GetAreaAMission"ObjCmd.Parameters.Refresh'参数赋值ObjCmd.Parameters(1).Value=objTag_barcode_areaa.ValueObjCmd.Parameters(2)=NullObjCmd.Parameters(3)=NullObjCmd.Parameters(4)=NullObjCmd.Execute()result=ObjCmd.Parameters(4).ValueobjTag_A_TotalWeight.Value=ObjCmd.Parameters(2).Value尸adCmdStoredProc’存储过程名称‘传递条形码作为输入参数’总重量，输出参数'productcode,输出参数’返回信息SIEMENS技术总结文档号TN-00-01版本0.0日期10/05/08页数9of17标题：WINCC中使用vbs脚本调用SQL存储过程SIAS---颜春明objTag_A_TotalWeight.WriteobjTag_A_SOProduct.Value=ObjCmd.Parameters(3).ValueobjTag_A_SOProduct.WriteSetobjCmd=Nothing'释放数据库连接资源HMIRuntime.Trace"#AGetMission#"&result&vbCrLf’完成后续处理工作IfCLng(objTag_A_TotalWeight.Value)>0ThenobjTag_barcode_getmission.Value=TrueobjTag_barcode_getmission.WriteElseobjTag_AreaA_Submixing_Flag.Value=1objTag_AreaA_Submixing_Flag.WriteIfCLng(objTag_A_TotalWeight.Value)=0ThenobjTag_AreaA_TextFlag.Value=4objTag_AreaA_TextFlag.WriteHMIRuntime.Trace"PleaseScanNextBarCode."&vbCrLfElseobjTag_AreaA_TextFlag.Value=5objTag_AreaA_TextFlag.WriteHMIRuntime.Trace"EmptyInAreaA."&vbCrLfEndIfEndIfEndSubSIEMENS技术总结文档号TN-00-01版本0.0日期10/05/08页数10of17标题：WINCC中使用vbs脚本调用SQL存储过程SIAS---颜春明从存储过程的实例可以看出，vbs代码简洁清晰，仅仅和数据库完成了1次链接。大量繁琐的工作在存储过程中来实行。若完全有SQL命令来实行将大大增加WINCC系统负担，同时数据库的负担也相对比较大，每次都要对SQL命令进行编译，执行，同时SQL和WINCC之间有大量的数据通讯。4SQLSERVER存储过程及触发器4.1存储过程定义存储过程是保存起来的可以接受和返回用户提供的参数的Transact-SQL语句的集合。可以创建一个过程供永久使用，或在一个会话中临时使用（局部临时过程），或在所有会话中临时使用（全局临时过程）。也可以创建在MicrosoftSQLServer启动时自动运行的存储过程。4.2创建存储过程和数据表一样，在使用之前我们需要创建存储过程，它的简明语法是：具体语法请参考T-SQL相关的手册和文档。CREATEPROC存储过程名称［参数列表（多个以"/分隔）］ASSQL语句实例2--说明部分--DeleteAlltheRecordsinFormulars_AreaA--命名CREATEPROCEDUREsp_givaudan_DeleteAlltheRecordsInFormulars_AreaA@resultNVARCHAR(300)OUTPUT参数ASSIEMENS技术总结文档号TN-00-01版本0.0日期10/05/08页数11of17标题：WINCC中使用vbs脚本调用SQL存储过程SIAS---颜春明BEGIN详细SQLIFNOTEXISTS(SELECT*FROMUA#Formulars_AreaA)BEGINSET@result='NoFormularsavailableinUA#Formulars_AreaAnow!!'RETURNENDELSEBEGINDELETEFROMUA#Formulars_AreaAENDIF@@error=0SET@result='Good'ELSESET@result='Fail'ENDGO实例3—说明--'DELETEUA#Formulars_AreaA,UA#Formulars_AreaB,UA#FormularsCheckWHEREsubmixing=@submixing-命名CREATEPROCEDUREsp_givaudan_DeleteSelectedSubmixing@submixingNVARCHAR(12)='-',@resultNVARCHAR(300)OUTPUTAS-实现BEGINIF@submixing=''OR(NOTEXISTS(SELECT*FROMUA#FormularsCheckWHEREsubmixing=@submixing))--submixing为空或没有所指定的submixng就返回BEGINSET@result='ParemeterError,Thesubmixing'+@submixing+'isnotavailable!!'SIEMENS技术总结文档号TN-00-01版本0.0日期10/05/08页数12of17标题：WINCC中使用vbs脚本调用SQL存储过程SIAS---颜春明RETURNENDELSE--submixing存在BEGIN一删除指定的submixing.SET@resukDELETEUA#Formulars_AreaA,UA#Formulars_AreaB,UA#FormularsCheckWHERESUBMIXING='+@submixing+'!!'DELETEFROMUA#Formulars_AreaAWHEREsubmixing=@submixingDELETEFROMUA#Formulars_AreaBWHEREsubmixing=@submixingDELETEFROMUA#FormularsCheckWHEREsubmixing=@submixingENDIF@@error=0SET@result=@result+'Good'ELSESET@result=@result+'Fail'ENDGO4.3存储过程调试从SQLServerEnterpriseManager进入SQLQueryAnalyzer,默认情况下进入当前数据库，如果不正确需要选择当前数据库。执行以下程序，可以对实例1进行调试。Declare@totalweightBIGINT,--定义临时总重量变量@soproductNVARCHAR(14),--定义口缶时socode变量@resultNVARCHAR(300)--定义临时结果信息变量--执行存储过程，提供所有参数execsp_givaudan_GetAreaAMission'test',@totalweightoutput,@soproductoutput,@resultoutput--显示执行后的输出变量的信息select@totalweight,@soproduct,@result结果如下图，可以通过修改‘test'为具体的submixing编码，来真正获取配方。实例2和实例3的调试都方法都和实例1相同。首先定义需要的变量，用在返回信息上。然后执行存储过程，必须提供所有的输入输出参数。最后为了方便调试，需要输出所有返回变量。详细的语法请参考T-SQL文档。

SIEMENS技术总结文档号TN-00-01版本0.0日期10/05/08页数13of17标题：WINCC中使用vbs脚本调用SQL存储过程SIAS---颜春明呷！芈」？■力二口球”口厅4口以；匚二_匚妨⑶2_必」万_洞」匚0」/见加不但九川皿建同心川■U」H…..」Queryb^tch5TA5CNJ4YCai\WINCL(8,0)5IA5CN01YC]l\cnO4A0l(62)C€^Clu>ter2JQ8050^l6^,45JOj0C^00IrowsLn%Col10图1.实例调试结果4.4存储过程中游标的使用在数据库开发过程中，当你检索的数据只是一条记录时，你所编写的事务语句代码往往使用SELECTINSERT语句。但是我们常常会遇到这样情况，即从某一结果集中逐一地读取一条记录。那么如何解决这种问题呢？在c,vbs等语言中，我们可以通过对纪录集合的访问来实现这样的功能。在数据库中，游标为我们提供了一种极为优秀的解决方案。在数据库中，游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条T_SQL选择语句相关联因为游标由结果集（可以是零条、一条或由相关的选择语句检索出的多条记录）和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时，必须声明一个指向该结果集的游标。我们知道关系数据库管理系统实质是面向集合的，在MSSQLSERVER中并没有一种描述表中单一记录的表达形式，除非使用where子句来限制只有一条记录被选中。因此我们必须借助于游标来进行面向单条记录的数据处理。由此可见，游标允许应用程序对查询语句select返回的行结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作；它还提供对基于游标位置而对表中数据进行删除或更新的能力；而且，正是游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来，使两个数据处理方式能够进行沟通。MSSQLSERVER支持三种类型的游标：Transact_SQL游标，API服务器游标和客户游标。这里主要讨论的是Transact_SQL游标

SIEMENS技术总结文档号TN-00-01版本0.0日期10/05/08页数14of17标题：WINCC中使用vbs脚本调用SQL存储过程SIAS---颜春明Transact_SQL游标是由DECLARECURSOR语法定义、主要用在Transact_SQL脚本、存储过程和触发器.。Transact_SQL游标主要用在服务器上，由从客户端发送给服务器的Transact_SQL语句或是批处理、存储过程、触发器中的Transact_SQL进行管理。Transact_SQL游标不支口提取数据块或多行数据。一一游标操作使用游标有四种基本的步骤：声明游标、打开游标、提取数据、关闭游标。下面我们以游标在存储过程中作为实例。实例4--GETTHEFIRSTTWOMISSIONID--命名存储过程，声明输入输出变量CREATEPROCEDUREspgivaudanGetStatIDAreaA@firstidBIGINTOUTPUT,@secondidBIGINTOUTPUT,@firstidBIGINTOUTPUT,@secondidBIGINTOUTPUT,@flagINTOUTPUT,onemission,morethan2mission.@resultNVARCHAR(300)OUTPUTAS--定义临时变量Declare@RcordCountint--FIRSTID--SECONDID--FLAG,0,nomissionisavailable,1,onelyDeclare@idbigint--声明游标DeclarecurFormulars_AreaACursorforSelectTOP2IDFromUA#Formulars_AreaAWHEREfinished<3ORDERBYTUBENUMBERBEGINSELECT@RcordCount=Count(*)FROMUA#Formulars_A