db2数据库存储过程

-1-中国移动通信集团河南有限公司业务支援中心DB2数据库中的存储过程-2-主要内容：1、存储过程介绍2、存储过程基础结构3、控制语句4、游标和结果集5、异常处理器6、编写和调试存储过程-3-什么是存储过程？(StoredProcedure)受DB2服务器控制的一段可执行程序可以通过SQL的CALL语句来完成对存储过程的调用在存储过程中可以包含业务逻辑存储过程可以在本地或远程进行调用存储过程可以接收或传递参数，生成结果集-4-什么时候使用存储过程？使用存储过程的合适时机:应用程序的性能无法满足预期时客户端数量较多且应用程序中SQL代码分散时应用程序需要进行繁重的数据库操作，同时这些操作并不需要进行太多的客户交互应用程序代码更改频繁需要对客户应用代码进行访问控制时客户应用需要在一次操作中执行多条SQL语句-5-C/S:宏观交互图-6-主要内容：1、存储过程介绍2、存储过程基础结构3、控制语句4、游标和结果集5、异常处理器6、编写和调试存储过程-7-存储过程结构CREATEORREPLACEPROCEDURE过程名([IN|OUT|INOUT]参数名数据类型默认值)LANGUAGESQLBEGIN业务逻辑代码END;-8-参数类型IN（输入参数）−只是将实参传递给存储过程，但在存储过程中不能对其进行修改。换句话说，对于存储过程而言它是只读的。OUT（输出参数）−在存储过程结束时向调用者返回。一般在过程中都会被赋值。INOUT（输入输出参数）−上述两种参数类型的结合体。它可以帮助调用者将实参传递给进程，另外它也能够作为输出参数被修改和赋值。-9-复合语句复合语句是指包含在BEGIN和END间的语句。它一般包括如下语句类型：声明语句赋值语句控制语句条件处理语句-10-复合语句示例说明：1.复合语句可以嵌套使用。2.BEGIN语句可以和标签组合使用，这样可以更清晰的标识语句块的范围。-11-声明语句变量声明DECLAREmy_varINTEGERDEFAULT6;条件声明DECLAREnot_foundCONDITIONFORSQLSTATE‘02000’;游标声明DECLAREc1CURSORFORselect*fromstaff;异常处理器声明DECLAREEXITHANDLERFORSQLEXCEPTION…;-12-赋值语句语法SETlv_name=expression;SETlv_name=NULL;示例(1)SETsalary=salary+salary*0.1;(2)SETinit_salary=NULL;(3)SETsalary=(selectsalaryfromemployeewhereempno=lv_emp_num);注:如果SELECT语句返回记录超过一行，示例3将会返回SQLERROR。-13-存储过程例子-14-嵌套存储过程例子-15-模块(Module)模块是如下几种对象的集合：−SP，UDF，globalvariablesandcursors，types，conditions模块的主要优势：−结构良好，便于组织−范围限定•CALLmySchema.myModule.myProc()−信息隐藏•每个对象都可以是public或private−权限控制•可以模块为单位，而不是以模块中的对象为单位来控制权限-16-模块-规格说明(ModuleSpecification)模块可以发布type,SP,UDF以供外部使用。CREATEORREPLACEMODULEmyMod;ALTERMODULEmyModPUBLISHTYPEmyRowTypASANCHORROWmyTab;ALTERMODULEmyModPUBLISHFUNCTIONmyFunc(val1ANCHORmyTab.col1)RETURNSmyRowTyp;ALTERMODULEmyModPUBLISHPROCEDUREmyProc(OUTparam1ANCHORmyTab.col2);-17-模块-实现(ModuleImplementation)下面的代码是模块的实现部分：ALTERMODULEmyModADDVARIABLEpkgVarANCHORmyTab.col1;ALTERMODULEmyModADDFUNCTIONmyFunc(val1ANCHORmyTab.col1)RETURNSmyRowTypBEGINDECLAREvar1myRowTyp;SELECT*INTOvar1FROMmyTabWHEREcol1val1ANDcol1pkgVar;RETURNvar1;ENDALTERMODULEmyModADDPROCEDUREmyProc(OUTparam1ANCHORmyTab.col2)BEGINDECLAREvarRowmyRowTyp;SETparam1=varRow.col2–pkgVar;END-18-模块-其他语句删除整个模块−DROPMODULEmyMod;保留规格说明内容，删除实现−ALTERMODULEmyModDROPBODY;删除模块中的存储过程(SP)−ALTERMODULEmyModDROPPROCEDUREmyProc;将模块的执行权限赋给joe−GRANTEXECUTEONMODULEmyModTOjoe;-19-主要内容：1、存储过程介绍2、存储过程基础结构3、控制语句4、游标和结果集5、异常处理器6、编写和调试存储过程-20-IF语句格式：IF条件1THENstatement1;ELSEIF条件2THENstatement2;ELSEstatement3;ENDIF;注:条件成立时为TRUE(真),不成立时为FALSE(假)和NULL-21-IF语句例子IFrating=1THENUPDATEEMPLOYEESETsalary=salary*1.10WHEREempno=i_num;（如果满足于...时，薪水调整1.1倍）ELSEIFrating=2THENUPDATEEMPLOYEESETsalary=salary*1.05WHEREempno=i_num;ELSEUPDATEEMPLOYEESETsalary=salary*1.03WHEREempno=i_num;ENDIF;-22-CASE语句（1of2）简单CASE语句-23-CASE语句（2of2）稍加变形的CASE语句-24-LOOP语句语法[LABEL]LOOPSQL-procedure-statements;ENDLOOP[LABEL];示例fetch_loop:LOOPFETCHc1INTOv_firstname,v_lastname;SETcounter=counter+1;IFcounter=51THENLEAVEfetch_loop;ENDIF;ENDLOOPfetch_loop;标签关键字-25-FOR语句语法[LABEL]FORfor-loop-nameAS[cursor-nameCURSORFOR]select-statementDOSQL-procedure-statements;ENDFOR[LABEL];示例DECLAREfullnameCHAR(40);FORv1ASc1CURSORFORSELECTfirstnme,midinit,lastnameFROMemployeeDOSETfullname=lastname||‘,’||firstnme||’,’||midinit;INSERTINTOtnameVALUE(fullname);ENDFOR;-26-其他控制语句REPEAT语句ftch_loop2:REPEATFETCHc1INTOv_firstname,v_midinit,v_lastname;UNTILSQLCODE0ANDREPEATftch_loop2;WHILE语句WHILEat_end=0DOFETCHc1INTOv_firstname,v_midinit,v_lastname;IFSQLCODE=100THENSETat_end=1;ENDIF;ENDWHILE;-27-LEAVE和ITERATE语句LEAVE和ITERATE语句来控制循环−LEAVE语句用来跳出循环−ITERATE语句用来回到for或者while循环的开始重新执行示例FETCH_LOOP1:LOOPFETCHc1INTOv_dept,v_deptname,v_admdept;IFat_end=1THENLEAVEFETCH_LOOP1;ELSEIFv_dept=‘D01’THENITERATEFETCH_LOOP1;ENDIF;INSERTINTOdepartment(deptno,deptname,admdept)VALUES(‘NEW’,v_deptname,v_admdept);ENDLOOPFETCH_LOOP1;-28-GOTO语句GOTO语句用于直接跳转到指定标签处。例如：IFv_DEPT=‘D11’GOTObye;……bye:-29-RETURN语句RETURN语句用于向调用返回。IFv_DEPT=‘D11’RETURN1;-30-主要内容：1、存储过程介绍2、存储过程基础结构3、控制语句4、游标和结果集5、异常处理器6、编写和调试存储过程-31-游标的声明下面是游标声明的几个例子：1.DECLAREc1CURSORFORselect*fromstaff;（DECLARE关键字，cl游标名称，CURSOR是必须有的，；指通过c1的游标来操作staff里所有的数据）最常用的最普通的。2.DECLAREc1CURSORWITHHOLDFORselect*formstaff;3.DECLAREc1CURSORWITHRETURNTOCALLERFORselect*formstaff;4.DECLAREc1CURSORWITHRETURNTOCLIENTFORselect*formstaff;-32-游标的相关操作打开游标OPEN游标名提取游标FETCH游标名INTO变量列表关闭游标CLOSE游标名-33-游标的遍历DECLAREat_endINTDEFAULT0;（声明了at_end的变量，默认值是0）DECLAREPIIDINTEGERDEFAULT0;DECLAREPINTINTEGERDEFAULT0;DECLAREnot_foundCONDITIONFORSQLSTATE'02000';DECLAREc1CURSORFORSELECTIIDFROMYH;（声明了一个游标，把IID的指标拿出来）DECLARECONTINUEHANDLERFORnot_foundSETat_end=1;OPENc1;（进行循环）SETPCOUNT=0;ins_loop:LOOPFETCHc1INTOPIID;IFat_end0THENLEAVEins_loop;（LEAVE跳出循环）ENDIF;SETPCOUNT=PCOUNT+1;（表示提取了多少条记录）ENDLOOP;-34-删除游标对应的数据行DECLAREcursor1CURSORFORSELECTDEPTNO,DEPTNAME,LOCATIONFROMDB2ADMIN.ORGFORUPDATE;（声明一个cursor1的游标，从一个表时提出部门名称，...，位置）OPENcursor1;（打开游标）FETCHFROMcursor1INTOv_DEPTNO,V_DEPTNAME,v_LOCATION;DELETEFROMDB2ADMIN.ORGWHERECURRENTOFcursor1;（删除DB2ADMIN.ORG的记录；CURRENTOFcursor1这是的游标是指向某一个位置；删除游标指向的当前行。）CLOSEcursor1;（关闭游标，也可做一个循环，删除所有的内容）-35-更新游标对应的数据行DECLAREcursor