DB2培训文档

IBM软件部©2007IBM公司•Confidentiality/dateline:13ptArialRegular,whiteMaximumlength:1line•Informationseparatedbyverticalstrokes,withtwospacesoneitherside•Disclaimerinformationmayalsobeappearinthisarea.Placeflushleft,alignedatbottom,8-10ptArialRegular,white•IBMlogomustnotbemoved,addedto,oralteredinanyway.Indicationsingreen=LivecontentIndicationsinwhite=EditinmasterIndicationsinblue=LockedelementsIndicationsinblack=Optionalelements•Presentationtitle:28ptArialRegular,blackRecommendedmaximumlength:2lines•Groupname:17ptArialRegular,whiteMaximumlength:1line•Copyright:10ptArialRegular,whiteTemplaterelease:Oct02Forthelatest,goto’slogomaygointhisarea嵌套存储过程陆川[e-mail]021-63262288-2720IBM软件部|DB2信息管理软件©2006IBM公司•IBMlogomustnotbemoved,addedto,oralteredinanyway.•Backgroundshouldnotbemodified.•Title/subtitle/confidentialityline:10ptArialRegular,whiteMaximumlength:1lineInformationseparatedbyverticalstrokes,withtwospacesoneitherside•Slideheading:28ptArialRegular,blueR120|G137|B251Maximumlength:2lines•Slidebody:18ptArialRegular,blackSquarebulletcolor:tealR045|G182|B179Recommendedmaximumtextlength:5principalpoints•Groupname:14ptArialRegular,whiteMaximumlength:1line•Copyright:10ptArialRegular,whiteOptionalslidenumber:10ptArialBold,whiteTemplaterelease:Oct02Forthelatest,goto=LivecontentIndicationsinwhite=EditinmasterIndicationsinblue=LockedelementsIndicationsinblack=Optionalelements你将了解下列内容：什么是嵌套存储过程如何在嵌套存储过程间传递参数从嵌套存储过程中返回值从嵌套存储过程中返回和接收结果集如何递归调用存储过程过程调用的安全性考虑IBM软件部|DB2信息管理软件©2006IBM公司•IBMlogomustnotbemoved,addedto,oralteredinanyway.•Backgroundshouldnotbemodified.•Title/subtitle/confidentialityline:10ptArialRegular,whiteMaximumlength:1lineInformationseparatedbyverticalstrokes,withtwospacesoneitherside•Slideheading:28ptArialRegular,blueR120|G137|B251Maximumlength:2lines•Slidebody:18ptArialRegular,blackSquarebulletcolor:tealR045|G182|B179Recommendedmaximumtextlength:5principalpoints•Groupname:14ptArialRegular,whiteMaximumlength:1line•Copyright:10ptArialRegular,whiteOptionalslidenumber:10ptArialBold,whiteTemplaterelease:Oct02Forthelatest,goto=LivecontentIndicationsinwhite=EditinmasterIndicationsinblue=LockedelementsIndicationsinblack=Optionalelements被调用的过程：统计每个雇员所完成的项目CREATEPROCEDUREcount_projects(INp_empnoCHAR(6),outp_totalINT)LANGUAGESQLSPECIFICcount_projectscp:BEGIN--ProcedurelogicSELECTCOUNT(*)INTOp_totalFROMemp_actWHEREempno=p_empno;ENDcp简单的存储过程嵌套的例子IBM软件部|DB2信息管理软件©2006IBM公司•IBMlogomustnotbemoved,addedto,oralteredinanyway.•Backgroundshouldnotbemodified.•Title/subtitle/confidentialityline:10ptArialRegular,whiteMaximumlength:1lineInformationseparatedbyverticalstrokes,withtwospacesoneitherside•Slideheading:28ptArialRegular,blueR120|G137|B251Maximumlength:2lines•Slidebody:18ptArialRegular,blackSquarebulletcolor:tealR045|G182|B179Recommendedmaximumtextlength:5principalpoints•Groupname:14ptArialRegular,whiteMaximumlength:1line•Copyright:10ptArialRegular,whiteOptionalslidenumber:10ptArialBold,whiteTemplaterelease:Oct02Forthelatest,goto=LivecontentIndicationsinwhite=EditinmasterIndicationsinblue=LockedelementsIndicationsinblack=Optionalelements调用者：根据每个雇员完成的项目的情况，决定是否给发奖金CREATEPROCEDUREbonus(INp_empnoCHAR(6),outp_bonusCHAR(1))LANGUAGESQLSPECIFICbonusbn:BEGIN--DeclarevariablesDECLAREv_minINTDEFAULT5;DECLAREv_totalINTDEFAULT0;--ProcedurelogicCALLcount_projects(p_empno,v_total);IF(v_total=v_min)THENSETp_bonus='Y';ELSESETp_bonus='N';ENDIF;ENDbnIBM软件部|DB2信息管理软件©2006IBM公司•IBMlogomustnotbemoved,addedto,oralteredinanyway.•Backgroundshouldnotbemodified.•Title/subtitle/confidentialityline:10ptArialRegular,whiteMaximumlength:1lineInformationseparatedbyverticalstrokes,withtwospacesoneitherside•Slideheading:28ptArialRegular,blueR120|G137|B251Maximumlength:2lines•Slidebody:18ptArialRegular,blackSquarebulletcolor:tealR045|G182|B179Recommendedmaximumtextlength:5principalpoints•Groupname:14ptArialRegular,whiteMaximumlength:1line•Copyright:10ptArialRegular,whiteOptionalslidenumber:10ptArialBold,whiteTemplaterelease:Oct02Forthelatest,goto=LivecontentIndicationsinwhite=EditinmasterIndicationsinblue=LockedelementsIndicationsinblack=Optionalelements传递参数在存储过程嵌套调用过程中，参数依照出现的次序进行传递，如果数据类型不匹配，请使用cast函数进行数据类型转换；从嵌套的过程中取得返回值除了通过输出参数获得返回值以外，存储过程可以通过return语句返回一个值，下面的例子演示如何通过getdiagnostics语句获得被调用过程的返回值。下面的过程get_emp_name将基于雇员编号返回雇员的firstname，当找到该雇员时返回99，否则返回1000。IBM软件部|DB2信息管理软件©2006IBM公司•IBMlogomustnotbemoved,addedto,oralteredinanyway.•Backgroundshouldnotbemodified.•Title/subtitle/confidentialityline:10ptArialRegular,whiteMaximumlength:1lineInformationseparatedbyverticalstrokes,withtwospacesoneitherside•Slideheading:28ptArialRegular,blueR120|G137|B251Maximumlength:2lines•Slidebody:18ptArialRegular,blackSquarebulletcolor:tealR045|G182|B179Recommendedmaximumtextlength:5principalpoint