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