DB2存储过程精简教程

DB2存储过程基础培训2009年1月1日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,salaryFROMstaffWHEREsalary>SAL;OPENcur1;END;6参数定义1DB2储存过程的参数分为两部分：输入、输出参数和性能相关参数。输入、输出参数表示方式:输入参数用IN开头输出参数用OUT开头既是输入又是输出参数用INOUT开头举例说明：createproceduresp_sample( invar0varchar(10), outvar1varchar(20), inoutvar2varchar(20))9参数定义4返回结果集个数（DYNAMICRESULTSETSn）

存储过程能够返回0个或者多个结果集。为了从存储过程中返回结果集，需要执行如下步骤：

在CREATEPROCEDURE语句的DYNAMICRESULTSETS子句中声明存储过程将要返回的结果集的数量（number-of-result-sets）。如果这里声明的返回结果集的数量小于存储过程中实际返回的结果集数量，在执行该存储过程的时候，DB2会返回一个警告。如下存储过程就会返回警告：10参数定义5CREATEPROCEDURERESULT_SET()DYNAMICRESULTSETS1LANGUAGESQLREADSSQLDATABEGINDECLAREcur1CURSORWITHRETURNFORSELECTname,dept,job,salaryFROMstaffWHEREsalary>20000;DECLAREcur2CURSORWITHRETURNFORSELECTname,dept,job,salaryFROMstaffWHEREsalary<20000;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条件控制语句2CASE…WHEN举例说明：CASEWHENv_workdept='A00' THENUPDATEdepartment SETdeptname='DATAACCESS1';WHENv_workdept='B01' THENUPDATEdepartment SETdeptname='DATAACCESS2';ELSEUPDATEdepartment SETdeptname='DATAACCESS3';ENDCASE

14循环语句1循环语句包括以下几种：WHILE举例说明：WHILEv_counter<(v_numRecords/2+1)DOSETv_salary1=v_salary2; SETv_counter=v_counter+1;ENDWHILE;15循环语句2LOOP举例说明:LOOPFETCHc1INTOv_firstnme,v_midinit,v_lastname;--Usealocalvariablefortheiteratorvariable--becauseSQLproceduresonlyallowyoutoassign--valuestoanOUTparameterSETv_counter=v_counter+1;IFv_midinit=''THENLEAVEfetch_loop;ENDIF;ENDLOOPfetch_loop;16循环语句3FOR举例说明：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的定义：BEGIN

DECLARE<type>HANDLERFOR<conditions>唤醒conditions

<handler-action>CONTINUE点

statement_1;

statement_2;EXIT或UNDO点

statement_3;

END<type>定义出错处理动作：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);DECLAREv_counterSMALLINTDEFAULT0;DECLAREat_endSMALLINTDEFAULT0;DECLAREnot_foundCONDITIONforSQLSTATE'02000';23游标使用2DECLAREc1CURSORFORSELECTfirstnme,midinit,lastnameFROMemployee;DECLARECONTINUEHANDLERfornot_foundSETat_end=1;--initializeOUTparameterSETcounter=0;

OPENc1;fetch_loop:LOOPFETCHc1INTOv_firstnme,v_midinit,v_lastname;IFat_end<>0THENLEAVEfetch_loop;ENDIF;24游标使用3--Usealocalvariablefortheiteratorvariable--becauseSQLproceduresonlyallowyoutoassign--valuestoanOUTparameterSETv_counter=v_counter+1;ENDLOOPfetch_loop;CLOSEc1;--Nowassignthevalueofthelocal--variabletotheOUTparameterSETcounter=v_counter;END@25动态游标使用动态游标使用起来比较方便灵活，在存储过程中会经常用到，也推荐使用动态游标处理逻辑。举例说明：CREATEPROCEDUREDynamic_Cursor(INSALINT)

DYNAMICRESULTSETS1

LANGUAGESQL

READSSQLDATA

BEGIN

DECLAREstmtvarchar(255);

DECLAREstSTATEMENT;

DECLAREcur1CURSORWITHRETURNFORst;

SETstmt='SELECTname,dept,job,salaryFROMstaffWHEREsalary>?';

PREPAREstFROMstmt;

OPENcur1USINGSAL;

END;26SESSION临时表使用1临时表只能建立在用户临时表空间上，而不能建立在系统临时表空间上。临时表是在一个SESSION内有效的。如果程序有多线程，最好不要使用临时表，因为比较难控制。建立临时表最好加上withreplace选项，这样可以不显示地drop临时表。举例说明：CREATEPROCEDUREDB2ADMIN.TEP_TABLE_TEST(

)

DYNAMICRESULTSETS1

LANGUAGESQL

MODIFIESSQLDATA

BEGIN

--定义错误代码DECLARESQLCODEINTEGERDEFAULT0;

DECLARESQLSTATECHAR(5)DEFAULT'00000';

DECLAREnot_foundCONDITIONFORSQLSTATE'02000';

DECLAREat_endINTEGERDEFAULT0;27SESSION临时表使用2--定义变量

DECLAREGET_NAMEVARCHAR(9);

DECLAREGET_DEPTSMALLINT;

DECLAREGET_JOBCHARACTER(5);

DECLAREGET_SALARYDE