DB2存储过程开发基础知识.

DB2开发基础知识储存过程开发内容提要数据类型储存过程及函数的结构参数定义变量定义赋值语句条件控制语句循环语句常用操作符游标使用动态游标使用异常处理SESSION临时表使用常用函数数据一致性和完整性事务隔离级别数据类型定长型字符串（CHAR）变长型字符串（VARCHAR）整数类型（SMALLINT、INTEGER、BIGINT）带小数点的数字类型（DECIMAL、REAL、DOUBLE）时间类型（DATE、TIME、TIMESTAMP）对象类型（BLOB、CLOB、DBCLOB）使用储存过程优点减少客户机与服务器之间的网络使用率。客户机应用程序将控制权传送到数据库服务器上的存储过程。存储过程在数据库服务器上执行中间处理，而不需要在网络中传送不需要的数据。提高安全性。通过使使用静态SQL的存储过程包含数据库特权，数据库管理员（DBA）可以提高安全性。调用存储过程的客户机应用程序的用户不需要数据库特权。提高可靠性。在数据库应用程序环境中，许多任务是重复的。通过重用一个公共过程，存储过程就可以高效地解决这些重复情况。存储过程结构存储过程结构如下：CREATEPROCEDUREsqlsamp(INdptvarchar(4))SPECIFICsqlsampRESULTSETS1LANGUAGESQL--------------------------------------------------------------------------SQLStoredProceduresqlsamp------------------------------------------------------------------------P1:BEGIN--DeclarecursorDECLAREcursor1CURSORWITHRETURNFORSELECTDEPARTMENT.DEPTNO,DEPARTMENT.DEPTNAME,EMPLOYEE.FIRSTNME,EMPLOYEE.MIDINIT,EMPLOYEE.LASTNAMEFROMDEPARTMENT,EMPLOYEEWHERE((EMPLOYEE.EMPNO=DEPARTMENT.MGRNO)AND((DEPARTMENT.DEPTNO=dpt)));--CursorleftopenforclientapplicationOPENcursor1;ENDP1参数定义DB2储存过程的参数分为两部分：输入和输出参数。参数表示方式:输入参数用IN开头输出参数用OUT开头既是输入又是输出参数用INOUT开头举例说明：createproceduresp_sample(invar0varchar(10),outvar1varchar(20),inoutvar2varchar(20))变量定义存储过程中可以使用关键字DECLARE定义变量，然后在后续程序过程中使用变量来处理逻辑。定义变量时可以指定一个初始值。举例说明：CREATEPROCEDUREP2(INOUTaVARCHAR(8),OUTbINTEGER)LANGUAGESQLBEGINDECLAREvar1INTEGERDEFAULT0;DECLAREvar2VARCHAR(5)DEFAULTa||'bc';--otherSQLstatements–END赋值语句存储过程使用关键字SET给变量赋值。举例说明:CREATEPROCEDUREP2(INOUTaVARCHAR(8),OUTbINTEGER)LANGUAGESQLBEGINDECLAREvar1INTEGERDEFAULT0;DECLAREvar2VARCHAR(5)DEFAULTa||'bc';SETvar1=0;SETvar1=var1+1;SETvar2=var2||'def';SETa=var1;SETb=var2;END条件控制语句1条件控制语句包括以下几种：IFTHENELSEIFTHEN……ELSEENDIF举例说明:IFrating=1THENUPDATEemployeeSETsalary=salary*1.10,bonus=1000WHEREempno=employee_number;ELSEIFrating=2THENUPDATEemployeeSETsalary=salary*1.05,bonus=500WHEREempno=employee_number;ELSEUPDATEemployeeSETsalary=salary*1.03,bonus=0WHEREempno=employee_number;ENDIF;条件控制语句2CASE…WHEN举例说明：CASEWHENv_workdept='A00'THENUPDATEdepartmentSETdeptname='DATAACCESS1';WHENv_workdept='B01'THENUPDATEdepartmentSETdeptname='DATAACCESS2';ELSEUPDATEdepartmentSETdeptname='DATAACCESS3';ENDCASE循环语句1循环语句包括以下几种：WHILE举例说明：WHILEv_counter(v_numRecords/2+1)DOSETv_salary1=v_salary2;SETv_counter=v_counter+1;ENDWHILE;循环语句2LOOP举例说明:LOOPFETCHc1INTOv_firstnme,v_midinit,v_lastname;--Usealocalvariablefortheiteratorvariable--becauseSQLproceduresonlyallowyoutoassign--valuestoanOUTparameterSETv_counter=v_counter+1;IFv_midinit=''THENLEAVEfetch_loop;ENDIF;ENDLOOPfetch_loop;常用操作符常用操作符有以下几种：关系运算符关系运算符有六种：小于、小于等于、大于、大于等于、等于、不等于逻辑运算符逻辑运算符有三种：AND、OR、NOT游标使用1游标有两种类型：静态的和动态的。使用游标前要先定义，然后可以使用循环语句操作游标。举例说明：CREATEPROCEDUREleave_loop(OUTcounterINT)LANGUAGESQLBEGINDECLARESQLSTATECHAR(5);DECLAREv_firstnmeVARCHAR(12);DECLAREv_midinitCHAR(1);DECLAREv_lastnameVARCHAR(15);DECLAREv_counterSMALLINTDEFAULT0;DECLAREat_endSMALLINTDEFAULT0;DECLAREnot_foundCONDITIONforSQLSTATE'02000';游标使用2DECLAREc1CURSORFORSELECTfirstnme,midinit,lastnameFROMemployee;DECLARECONTINUEHANDLERfornot_foundSETat_end=1;--initializeOUTparameterSETcounter=0;OPENc1;fetch_loop:LOOPFETCHc1INTOv_firstnme,v_midinit,v_lastname;--IFat_end0THENLEAVEfetch_loop;--ENDIF;游标使用3--Usealocalvariablefortheiteratorvariable--becauseSQLproceduresonlyallowyoutoassign--valuestoanOUTparameterSETv_counter=v_counter+1;ENDLOOPfetch_loop;CLOSEc1;--Nowassignthevalueofthelocal--variabletotheOUTparameterSETcounter=v_counter;END@动态游标使用动态游标使用起来比较方便灵活，在存储过程中会经常用到，也推荐使用动态游标处理逻辑。举例说明：DECLAREc1CURSORFORs0;SETtemp_sql='SELECTcolnameFROMSYSCAT.COLUMNSWHERETABSCHEMA='''||tgtschema||'''andTABNAME='''||tgttabname||'''andcolname''LSBH''ORDERBYcolno';PREPAREs0FROMtemp_sql；OPENc1;异常处理存储过程中要有能够处理程序发生异常时的处理流程。举例说明：--自定义异常处理DECLARECONTINUEHANDLERFORSQLEXCEPTIONSETERR_MSG=RTRIM(CHAR(CURRENTTIMESTAMP))||'(P_GENERATESQL)系统错误：SQLCODE='||RTRIM(CHAR(SQLCODE))||',SQLSTATE='||SQLSTATE||'';储存过程中间处理流程--执行过程中发生异常返回出错标志和出错信息IFERR_MSG''THENSETRUNSTATUS=1;SETMESSAGE=MESSAGE||ERR_MSG;ENDIF;SESSION临时表使用临时表只能建立在用户临时表空间上，而不能建立在系统临时表空间上。临时表是在一个SESSION内有效的。如果程序有多线程，最好不要使用临时表，因为比较难控制。建立临时表最好加上withreplace选项，这样可以不显示地drop临时表。举例说明：DECLAREGLOBALTEMPORARYTABLEETL_TEMPSQL(TGTSCHEMAVARCHAR(128),TGTTABLEVARCHAR(128),XHINTEGER,TYPECHARACTER(1),SQLVARCHAR(3000))NOTLOGGEDWITHREPLACE;常用函数1DB2常用函数有以下一些：类似oracle中decode的判断操作例如：selectcasea1when'1'then'n1'when'2'then'n2'else'n3'endasaa1from表名类似charindex查找字符在字符串中的位置例如：Locate(‘y’,’dfdasfay’)查找字符y在字符串dfdasfay中的位置常用函数2类似datedif计算两个日期的相差天数函数DAYS例如：days(date(‘2001-06-05’))–days(date(‘2001-04-01’))预防空字段的处理函数COALESCE例如：处理字符型COALESCE(NAME,‘’)处理数字型COALESCE(BOX_NUM,0)数据一致性和完整性事务在多用户环境中并发的几种情况：脏读这种情况发生在一个事务读取还未提交的数据时。例如：事务1改变了一行数据，而在事务1提交修改之前事务2读取了事务1所改变的行的数据。如果事务1回滚了修改，那么事务2就读取了实际上并不存在的数据。不可重复读这种情况发生在事务两次读取同一行中的数据却得到不同的数值时。例如：事务1读取了一行数据，而事务2改变或删除了那些行并提交了修改。如果事务1再次读取了那一行，那么，事务1就得到了不同的值（如果那一行被更新）或发现那一行已不存在（如果那一行被删除）幻影数据这种情况发生在一行数据满足搜索规则，却在开始没有被看到时。例如：事务1读取了一系列满足搜索规则的行，而事务2插入了一个满足事务1搜索规则的行。如果事务1再次查询语句，就会得到不同的一系列行。事务隔离级别1DB2使用四个