DB2存储过程培训

DB2存储过程基础培训2008年7月28日2内容提要数据类型使用存储过程的优点储存过程的结构参数定义变量定义赋值语句条件控制语句循环语句常用操作符异常处理游标使用动态游标使用SESSION临时表使用3数据类型定长型字符串（CHAR）变长型字符串（VARCHAR）整数类型（SMALLINT、INTEGER、BIGINT）带小数点的数字类型（DECIMAL、REAL、DOUBLE）时间类型（DATE、TIME、TIMESTAMP）对象类型（BLOB、CLOB、DBCLOB）4使用储存过程优点减少客户机与服务器之间的网络使用率。客户机应用程序将控制权传送到数据库服务器上的存储过程。存储过程在数据库服务器上执行中间处理，而不需要在网络中传送不需要的数据。提高安全性。通过使使用静态SQL的存储过程包含数据库特权，数据库管理员（DBA）可以提高安全性。调用存储过程的客户机应用程序的用户不需要数据库特权。提高可靠性。在数据库应用程序环境中，许多任务是重复的。通过重用一个公共过程，存储过程就可以高效地解决这些重复情况。5存储过程结构存储过程结构如下：CREATEPROCEDURESP_STAFF(INSALINT)DYNAMICRESULTSETS1LANGUAGESQLREADSSQLDATABEGINDECLAREcur1CURSORWITHRETURNFORSELECTname,dept,job,salaryFROMstaffWHEREsalarySAL;OPENcur1;END;6参数定义1DB2储存过程的参数分为两部分：输入、输出参数和性能相关参数。输入、输出参数表示方式:输入参数用IN开头输出参数用OUT开头既是输入又是输出参数用INOUT开头举例说明：createproceduresp_sample(invar0varchar(10),outvar1varchar(20),inoutvar2varchar(20))7参数定义2创建存储过程语句（CREATEPROCEDURE）可以包含很多参数，虽然从语法角度讲它们不是必须的，但是在创建存储过程时提供它们可以提高执行效率。下面是一些常用的参数•容许SQL（allowed－SQL）容许SQL（allowed－SQL）子句的值指定了存储过程是否会使用SQL语句，如果使用，其类型如何。它的可能值如下所示：8参数定义3NOSQL：表示存储过程不能够执行任何SQL语句。CONTAINSSQL：表示存储过程可以执行SQL语句，但不会读取SQL数据，也不会修改SQL数据。READSSQLDATA：表示在存储过程中包含不会修改SQL数据的SQL语句。也就是说该储存过程只从数据库中读取数据。MODIFIESSQLDATA：表示存储过程可以执行任何SQL语句。即可以对数据库中的数据进行增加、删除和修改。如果没有明确声明allowed-SQL，其默认值是MODIFIESSQLDATA。不同类型的存储过程执行的效率是不同的，其中NOSQL效率最好，MODIFIESSQLDATA最差。如果存储过程只是读取数据，但是因为没有声明allowed-SQL使其被当作对数据进行修改的存储过程来执行，这显然会降低程序的执行效率。因此创建存储过程时，应当明确声明其allowed-SQL。9参数定义4•返回结果集个数（DYNAMICRESULTSETSn）存储过程能够返回0个或者多个结果集。为了从存储过程中返回结果集，需要执行如下步骤：在CREATEPROCEDURE语句的DYNAMICRESULTSETS子句中声明存储过程将要返回的结果集的数量（number-of-result-sets）。如果这里声明的返回结果集的数量小于存储过程中实际返回的结果集数量，在执行该存储过程的时候，DB2会返回一个警告。如下存储过程就会返回警告：10参数定义5CREATEPROCEDURERESULT_SET()DYNAMICRESULTSETS1LANGUAGESQLREADSSQLDATABEGINDECLAREcur1CURSORWITHRETURNFORSELECTname,dept,job,salaryFROMstaffWHEREsalary20000;DECLAREcur2CURSORWITHRETURNFORSELECTname,dept,job,salaryFROMstaffWHEREsalary20000;OPENcur1;OPENcur2;END;11变量定义存储过程中可以使用关键字DECLARE定义变量，然后在后续程序过程中使用变量来处理逻辑。定义变量时可以指定一个初始值。举例说明：DECLAREtemp1SMALLINTDEFAULT0;DECLAREtemp2INTEGERDEFAULT10;DECLAREtemp3DECIMAL(10,2)DEFAULT100.10;DECLAREtemp4REALDEFAULT10.1;DECLAREtemp5DOUBLEDEFAULT10000.1001;DECLAREtemp6BIGINTDEFAULT10000;DECLAREtemp7CHAR(10)DEFAULT'yes';DECLAREtemp8VARCHAR(10)DEFAULT'hello';DECLAREtemp9DATEDEFAULT'1998-12-25';DECLAREtemp10TIMEDEFAULT'1:50PM';DECLAREtemp11TIMESTAMPDEFAULT'2001-01-05-12.00.00';DECLAREtemp12CLOB(2G);DECLAREtemp13BLOB(2G);12赋值语句存储过程使用关键字SET给变量赋值。举例说明:SETtotal=100;VALUES(100,200,200+1)INTOvar1,var2,var3;/*并行赋值，效率高*/SETtotal=NULL;SETtotal=(selectsum(c1)fromT1);SETsch=CURRENTSCHEMA;13条件控制语句2CASE…WHEN举例说明：CASEWHENv_workdept='A00'THENUPDATEdepartmentSETdeptname='DATAACCESS1';WHENv_workdept='B01'THENUPDATEdepartmentSETdeptname='DATAACCESS2';ELSEUPDATEdepartmentSETdeptname='DATAACCESS3';ENDCASE14循环语句1循环语句包括以下几种：WHILE举例说明：WHILEv_counter(v_numRecords/2+1)DOSETv_salary1=v_salary2;SETv_counter=v_counter+1;ENDWHILE;15循环语句2LOOP举例说明:LOOPFETCHc1INTOv_firstnme,v_midinit,v_lastname;--Usealocalvariablefortheiteratorvariable--becauseSQLproceduresonlyallowyoutoassign--valuestoanOUTparameterSETv_counter=v_counter+1;IFv_midinit=''THENLEAVEfetch_loop;ENDIF;ENDLOOPfetch_loop;16循环语句3FOR举例说明：CREATEPROCEDUREConcat_names()LANGUAGESQLBEGIN--Note:implicitcursormanipulationDECLAREfullnameCHAR(140);FORv1ASSELECTfirstnme,midinit,lastnameFROMemployeeDOSETfullname=v1.lastname||','||v1.firstnme||''||v1.midinit;INSERTINTOtnameVALUES(fullname);ENDFOR;END17常用操作符常用操作符有以下几种：关系运算符关系运算符有六种：小于、小于等于、大于、大于等于、等于、不等于逻辑运算符逻辑运算符有三种：AND、OR、NOT18异常处理1任何SQL语句执行若发生SQLSTATE’00000’的情况都可能唤起condition，可以是通用的conditions：SQLWARNING,SQLEXCEPTION,NOTFOUND，如：DECLAREnot_foundCONDITIONFORNOTFOUND;也可以是指定SQLSTATE的conditions，如：DECLAREtruncCONDITIONFORSQLSTATE'01004';注意：为避免未预见的出错情况出现后被忽略，应尽量避免定义SQLEXCEPTIONCONDITION，而应针对具体的SQLSTATE定义CONDITION。19异常处理2CONDITIONHANDLE的定义：BEGINDECLAREtypeHANDLERFORconditions唤醒conditionshandler-actionCONTINUE点statement_1;statement_2;EXIT或UNDO点statement_3;ENDtype定义出错处理动作：CONTINUE，EXIT或UNDO。conditions为已经定义的condition名或是直接的通用conditions，可以是多个handler-action是一条或多条语句，可以包含控制语句20异常处理3例子：DECLARECONTINUEHANDLERFORnot_found,SQLEXCEPTIONSETat_end=1;DECLARECONTINUEHANDLERFORtruncBEGINSETtruncated=1;SETmsg=’message’;END;注意：若SQLPROCEDURE语句执行后SQLSTATE=’02000’或SQLSTATE=’01xxx’,引起SQLWARNING或NOTFOUND条件，且定义了相应条件的handler，则DB2将控制交给相应handler；若未定义handler，则DB2设SQLSTATE及SQLCODE值并继续运行。21异常处理4若SQLPROCEDURE语句执行后出错，引起SQLEXCEPTION条件，且定义了相应条件的handler，则DB2将控制交给相应handler，若handler运行成功，则SQLCODE及SQLSTATE重置为0及’00000’；若未定义handler，则DB2中止PROCEDURE并返回CLIENT。需要注意的是，任何语句的成功执行都会将SQLCODE、SQLSTATE重置为0,’00000’。若需要截获出错代码，唯一的方法是在handler的第一条语句将其中的一个值保存在变量中，如：DECLARECONTINUEHANDLERforSQLEXCEPTIONSETSaved_SQLCODE=SQLCODE;若PROCEDURE中需要向客户端返回用户错误信息，可使用SIGNAL：SIGNALSQLSTATE‘20000’SETMESSAGE_TEXT=’找不到用户记录’MESSAGE_TEXT也可以是一个字符串变量，此功能可以用来调试存储过程。22游标使用1游标有两种类型：静态的和动态的。使用游标前要先定义，然后可以使用循环语句操作游标。举例说明：CREATEPROCEDUREleave_loop(OUTcounterINT)LANGUAGESQLBEGINDECLARESQLSTATECHAR(5);DECLAREv_firstnmeVARCHAR(12);DECLAREv_midinitCHAR(1);DECLAREv_lastnameVARCHAR(15);D