《DB存储过程开发基础知识.pptx》由会员分享，可在线阅读，更多相关《DB存储过程开发基础知识.pptx（26页珍藏版）》请在taowenge.com淘文阁网|工程机械CAD图纸|机械工程制图|CAD装配图下载|SolidWorks_CaTia_CAD_UG_PROE_设计图分享下载上搜索。
1、内容提要v数据类型数据类型v储存过程及函数的结构储存过程及函数的结构v参数定义参数定义v变量定义变量定义v赋值语句赋值语句v条件控制语句条件控制语句v循环语句循环语句v常用操作符常用操作符v游标使用游标使用v动态游标使用动态游标使用v异常处理异常处理vSESSION临时表使用临时表使用v常用函数常用函数v数据一致性和完整性数据一致性和完整性v事务隔离级别事务隔离级别第1页/共26页数据类型v定长型字符串（定长型字符串（CHAR）v变长型字符串（变长型字符串（VARCHAR）v整数类型（整数类型（SMALLINT、INTEGER、BIGINT）v带小数点的数字类型（带小数点的数字类型（DECIM
2、AL、REAL、DOUBLE）v时间类型（时间类型（DATE、TIME、TIMESTAMP）v对象类型（对象类型（BLOB、CLOB、DBCLOB）第2页/共26页使用储存过程优点v减少客户机与服务器之间的网络使用率。客户机应用减少客户机与服务器之间的网络使用率。客户机应用程序将控制权传送到数据库服务器上的存储过程。存程序将控制权传送到数据库服务器上的存储过程。存储过程在数据库服务器上执行中间处理，而不需要在储过程在数据库服务器上执行中间处理，而不需要在网络中传送不需要的数据。网络中传送不需要的数据。v提高安全性。通过使使用静态提高安全性。通过使使用静态 SQL 的存储过程包含的存储过程包含数
3、据库特权，数据库管理员（数据库特权，数据库管理员（DBA）可以提高安全性。）可以提高安全性。调用存储过程的客户机应用程序的用户不需要数据库调用存储过程的客户机应用程序的用户不需要数据库特权。特权。v提高可靠性。在数据库应用程序环境中，许多任务是提高可靠性。在数据库应用程序环境中，许多任务是重复的。通过重用一个公共过程，存储过程就可以高重复的。通过重用一个公共过程，存储过程就可以高效地解决这些重复情况。效地解决这些重复情况。第3页/共26页存储过程结构存储过程结构如下：存储过程结构如下：CREATEPROCEDUREsqlsamp(INdptvarchar(4)SPECIFICsqlsampRE
4、SULTSETS1LANGUAGESQL-SQLStoredProceduresqlsamp-P1:BEGIN-DeclarecursorDECLAREcursor1CURSORWITHRETURNFORSELECTDEPARTMENT.DEPTNO,DEPARTMENT.DEPTNAME,EMPLOYEE.FIRSTNME,EMPLOYEE.MIDINIT,EMPLOYEE.LASTNAMEFROMDEPARTMENT,EMPLOYEEWHERE(EMPLOYEE.EMPNO=DEPARTMENT.MGRNO)AND(DEPARTMENT.DEPTNO=dpt);-Cursorleftope
5、nforclientapplicationOPENcursor1;ENDP1第4页/共26页参数定义DB2储存过程的参数分为两部储存过程的参数分为两部分：输入和输出参数。分：输入和输出参数。参数表示方式参数表示方式:v输入参数用输入参数用IN开头开头v输出参数用输出参数用OUT开头开头v既是输入又是输出参数用既是输入又是输出参数用INOUT开头开头举例说明：举例说明：createproceduresp_sample(invar0varchar(10),outvar1varchar(20),inoutvar2varchar(20)第5页/共26页变量定义存储过程中可以使用关键字存储过程中可以使用
6、关键字DECLARE定义变量，然后在后定义变量，然后在后续程序过程中使用变量来处理逻续程序过程中使用变量来处理逻辑。定义变量时可以指定一个初辑。定义变量时可以指定一个初始值。始值。举例说明：举例说明：CREATEPROCEDUREP2(INOUTaVARCHAR(8),OUTbINTEGER)LANGUAGESQLBEGINDECLAREvar1INTEGERDEFAULT0;DECLAREvar2VARCHAR(5)DEFAULTa|bc;-otherSQLstatementsEND 第6页/共26页赋值语句存储过程使用关键字存储过程使用关键字SET给变量给变量赋值。赋值。举例说明举例说明:
7、CREATEPROCEDUREP2(INOUTaVARCHAR(8),OUTbINTEGER)LANGUAGESQLBEGINDECLAREvar1INTEGERDEFAULT0;DECLAREvar2VARCHAR(5)DEFAULTa|bc;SETvar1=0;SETvar1=var1+1;SETvar2=var2|def;SETa=var1;SETb=var2;END第7页/共26页条件控制语句1条件控制语句包括以下几种：条件控制语句包括以下几种：IFTHENELSEIFTHENELSEENDIF举例说明举例说明:IFrating=1THENUPDATEemployeeSETsalary
8、=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;第8页/共26页条件控制语句2vCASEWHEN举例说明：CASEWHENv_workdept=A00THENUPDATEdepartmentSETdeptna
9、me=DATAACCESS1;WHENv_workdept=B01THENUPDATEdepartmentSETdeptname=DATAACCESS2;ELSEUPDATEdepartmentSETdeptname=DATAACCESS3;ENDCASE第9页/共26页循环语句1循环语句包括以下几种：循环语句包括以下几种：vWHILE举例说明：举例说明：WHILEv_counter(v_numRecords/2+1)DOSETv_salary1=v_salary2;SETv_counter=v_counter+1;ENDWHILE;第10页/共26页循环语句循环语句2vLOOP举例说明举例说
10、明:LOOPFETCHc1INTOv_firstnme,v_midinit,v_lastname;-Usealocalvariablefortheiteratorvariable-becauseSQLproceduresonlyallowyoutoassign-valuestoanOUTparameterSETv_counter=v_counter+1;IFv_midinit=THENLEAVEfetch_loop;ENDIF;ENDLOOPfetch_loop;第11页/共26页常用操作符常用操作符有以下几种：常用操作符有以下几种：v关系运算符关系运算符关系运算符有六种：小于、小于等于、大于
11、、关系运算符有六种：小于、小于等于、大于、大于等于、等于、不等于大于等于、等于、不等于v逻辑运算符逻辑运算符逻辑运算符有三种：逻辑运算符有三种：AND、OR、NOT第12页/共26页游标使用1游标有两种类型：静态的和动态的。使用游标前要先定义，游标有两种类型：静态的和动态的。使用游标前要先定义，然后可以使用循环语句操作游标。然后可以使用循环语句操作游标。举例说明：举例说明：CREATEPROCEDUREleave_loop(OUTcounterINT)LANGUAGESQLBEGINDECLARESQLSTATECHAR(5);DECLAREv_firstnmeVARCHAR(12);DECL
12、AREv_midinitCHAR(1);DECLAREv_lastnameVARCHAR(15);DECLAREv_counterSMALLINTDEFAULT0;DECLAREat_endSMALLINTDEFAULT0;DECLAREnot_foundCONDITIONforSQLSTATE02000;第13页/共26页游标使用2DECLAREc1CURSORFORSELECTfirstnme,midinit,lastnameFROMemployee;DECLARECONTINUEHANDLERfornot_foundSETat_end=1;-initializeOUTparameterS
13、ETcounter=0;OPENc1;fetch_loop:LOOPFETCHc1INTOv_firstnme,v_midinit,v_lastname;-IFat_end0THENLEAVEfetch_loop;-ENDIF;第14页/共26页游标使用3-Usealocalvariablefortheiteratorvariable-becauseSQLproceduresonlyallowyoutoassign-valuestoanOUTparameterSETv_counter=v_counter+1;ENDLOOPfetch_loop;CLOSEc1;-Nowassignthevalu
14、eofthelocal-variabletotheOUTparameterSETcounter=v_counter;END第15页/共26页动态游标使用动态游标使用起来比较方便灵活，在存储过程中会动态游标使用起来比较方便灵活，在存储过程中会经常用到，也推荐使用动态游标处理逻辑。经常用到，也推荐使用动态游标处理逻辑。举例说明：举例说明：DECLAREc1CURSORFORs0;SETtemp_sql=SELECTcolnameFROMSYSCAT.COLUMNSWHERETABSCHEMA=|tgtschema|andTABNAME=|tgttabname|andcolnameLSBHORDER
15、BYcolno;PREPAREs0FROMtemp_sql；OPENc1;第16页/共26页异常处理存储过程中要有能够处理程序发生异常时的处理流存储过程中要有能够处理程序发生异常时的处理流程。程。举例说明：举例说明：-自定义异常处理DECLARECONTINUEHANDLERFORSQLEXCEPTIONSETERR_MSG=RTRIM(CHAR(CURRENTTIMESTAMP)|(P_GENERATESQL)系统错误：SQLCODE=|RTRIM(CHAR(SQLCODE)|,SQLSTATE=|SQLSTATE|;储存过程中间处理流程-执行过程中发生异常返回出错标志和出错信息IFERR_
16、MSGTHENSETRUNSTATUS=1;SETMESSAGE=MESSAGE|ERR_MSG;ENDIF;第17页/共26页SESSION临时表使用v临时表只能建立在用户临时表空间上，而不能建立临时表只能建立在用户临时表空间上，而不能建立在系统临时表空间上。在系统临时表空间上。v临时表是在一个临时表是在一个SESSION内有效的。如果程序有内有效的。如果程序有多线程，最好不要使用临时表，因为比较难控制。多线程，最好不要使用临时表，因为比较难控制。v建立临时表最好加上建立临时表最好加上withreplace选项，这样可选项，这样可以不显示地以不显示地drop临时表。临时表。举例说明：举例说明
17、：DECLAREGLOBALTEMPORARYTABLEETL_TEMPSQL(TGTSCHEMAVARCHAR(128),TGTTABLEVARCHAR(128),XHINTEGER,TYPECHARACTER(1),SQLVARCHAR(3000)NOTLOGGEDWITHREPLACE;第18页/共26页常用函数1DB2常用函数有以下一些：常用函数有以下一些：v类似类似oracle中中decode的判断操作的判断操作例如：selectcasea1when1thenn1when2thenn2elsen3endasaa1from表名 v类似类似charindex查找字符在字符串中的位置查找字
18、符在字符串中的位置例如：例如：Locate(y,dfdasfay)查找字符y在字符串dfdasfay中的位置第19页/共26页常用函数常用函数2v类似datedif计算两个日期的相差天数函数DAYS例如：days(date(2001-06-05)days(date(2001-04-01)v预防空字段的处理函数COALESCE例如：处理字符型COALESCE(NAME,)处理数字型COALESCE(BOX_NUM,0)第20页/共26页数据一致性和完整性数据一致性和完整性事务在多用户环境中并发的几种情况：事务在多用户环境中并发的几种情况：v脏读脏读这种情况发生在一个事务读取还未提交的数据时。例如
19、：事务这种情况发生在一个事务读取还未提交的数据时。例如：事务1改变了改变了一行数据，而在事务一行数据，而在事务1提交修改之前事务提交修改之前事务2读取了事务读取了事务1所改变的行所改变的行的数据。如果事务的数据。如果事务1回滚了修改，那么事务回滚了修改，那么事务2就读取了实际上并不存就读取了实际上并不存在的数据。在的数据。v不可重复读不可重复读这种情况发生在事务两次读取同一行中的数据却得到不同的数值时。这种情况发生在事务两次读取同一行中的数据却得到不同的数值时。例如：事务例如：事务1读取了一行数据，而事务读取了一行数据，而事务2改变或删除了那些行并提交改变或删除了那些行并提交了修改。如果事务了
20、修改。如果事务1再次读取了那一行，那么，事务再次读取了那一行，那么，事务1就得到了不同就得到了不同的值（如果那一行被更新）或发现那一行已不存在（如果那一行被的值（如果那一行被更新）或发现那一行已不存在（如果那一行被删除）删除）v幻影数据幻影数据这种情况发生在一行数据满足搜索规则，却在开始没有被看到时。例这种情况发生在一行数据满足搜索规则，却在开始没有被看到时。例如：事务如：事务1读取了一系列满足搜索规则的行，而事务读取了一系列满足搜索规则的行，而事务2插入了一个满插入了一个满足事务足事务1搜索规则的行。如果事务搜索规则的行。如果事务1再次查询语句，就会得到不同的再次查询语句，就会得到不同的一系
21、列行。一系列行。第21页/共26页事务隔离级别事务隔离级别1DB2使用四个不同的事务隔离级别来实现并发。使用四个不同的事务隔离级别来实现并发。v可重复读可重复读可重复读可重复读(RR)锁定应用程序在一个事务中引用的所有行，可以防止锁定应用程序在一个事务中引用的所有行，可以防止幻影行。例如如果扫描幻影行。例如如果扫描10000行数据并对它们进行过滤，尽管只行数据并对它们进行过滤，尽管只有有10行满足条件，但仍会锁定全部的行满足条件，但仍会锁定全部的10000行数据。在可重复读行数据。在可重复读隔离级别下，其他事务不能更新、删除、插入数据，执行的事务看隔离级别下，其他事务不能更新、删除、插入数据，
22、执行的事务看不到其他事务的未提交修改。可重复读可能会持有大量锁定最终导不到其他事务的未提交修改。可重复读可能会持有大量锁定最终导致行锁定升级到表锁定。致行锁定升级到表锁定。v读稳定性读稳定性读稳定性读稳定性(RS)只锁定满足查询条件的行，有可能看到幻影行。也就只锁定满足查询条件的行，有可能看到幻影行。也就是如果一个使用读稳定性隔离级别的事务多次执行同一个查询，该是如果一个使用读稳定性隔离级别的事务多次执行同一个查询，该事务可能会得到别的事物插入的符合该搜索规则的新行。事务可能会得到别的事物插入的符合该搜索规则的新行。第22页/共26页事务隔离级别事务隔离级别2v游标稳定性游标稳定性游标稳定性游
23、标稳定性(CS)是缺省隔离级别，事务只锁定当前游标位置的是缺省隔离级别，事务只锁定当前游标位置的行，可能出现幻影行和不可重复读取数据。别的事务不能对游行，可能出现幻影行和不可重复读取数据。别的事务不能对游标位置的行进行更新或删除，但还能在被锁定行的两端插入、标位置的行进行更新或删除，但还能在被锁定行的两端插入、删除或修改行。删除或修改行。v未提交读未提交读未提交读未提交读(UR)允许一个事务访问其他事务未提交的修改数据，允许一个事务访问其他事务未提交的修改数据，可能出现幻影行和不可重复读取数据。使用未提交读不需要对可能出现幻影行和不可重复读取数据。使用未提交读不需要对任何行锁定，适用于只读表上
24、的查询和那些并不关心是否可从任何行锁定，适用于只读表上的查询和那些并不关心是否可从其他应用程序中看到未提交的数据。其他应用程序中看到未提交的数据。举例说明为查询语句指定隔离级别：举例说明为查询语句指定隔离级别：select*fromstockwithur第23页/共26页事务隔离级别事务隔离级别3DB2通用数据库支持的四种隔离级别：通用数据库支持的四种隔离级别：DB2事务隔离事务隔离级别级别脏读脏读不可重复读不可重复读幻影数据幻影数据未提交读未提交读有有有有有有游标稳定性游标稳定性无无有有有有读稳定性读稳定性无无无无有有可重复读可重复读无无无无无无第24页/共26页谢谢！第25页/共26页谢谢您的观看！第26页/共26页