数据库开发基础.ppt
DB2开发基础知识,储存过程开发,内容提要,数据类型储存过程及函数的结构参数定义变量定义赋值语句条件控制语句循环语句常用操作符游标使用动态游标使用异常处理SESSION临时表使用常用函数数据一致性和完整性事务隔离级别,数据类型,定长型字符串（CHAR）变长型字符串（VARCHAR）整数类型（SMALLINT、INTEGER、BIGINT）带小数点的数字类型（DECIMAL、REAL、DOUBLE）时间类型（DATE、TIME、TIMESTAMP）对象类型（BLOB、CLOB、DBCLOB）,使用储存过程优点,减少客户机与服务器之间的网络使用率。客户机应用程序将控制权传送到数据库服务器上的存储过程。存储过程在数据库服务器上执行中间处理，而不需要在网络中传送不需要的数据。提高安全性。通过使使用静态 SQL 的存储过程包含数据库特权，数据库管理员（DBA）可以提高安全性。调用存储过程的客户机应用程序的用户不需要数据库特权。提高可靠性。在数据库应用程序环境中，许多任务是重复的。通过重用一个公共过程，存储过程就可以高效地解决这些重复情况。,存储过程结构,存储过程结构如下：CREATE PROCEDURE sqlsamp(IN dpt varchar(4)SPECIFIC sqlsamp RESULT SETS 1 LANGUAGE SQL-SQL Stored Procedure sqlsamp-P1:BEGIN-Declare cursor DECLARE cursor1 CURSOR WITH RETURN FOR SELECT DEPARTMENT.DEPTNO,DEPARTMENT.DEPTNAME,EMPLOYEE.FIRSTNME,EMPLOYEE.MIDINIT,EMPLOYEE.LASTNAMEFROM DEPARTMENT,EMPLOYEE WHERE(EMPLOYEE.EMPNO=DEPARTMENT.MGRNO)AND(DEPARTMENT.DEPTNO=dpt);-Cursor left open for client application OPEN cursor1;END P1,参数定义,DB2储存过程的参数分为两部分：输入和输出参数。参数表示方式:输入参数用IN开头输出参数用OUT开头既是输入又是输出参数用INOUT开头举例说明：create procedure sp_sample(in var0 varchar(10),out var1 varchar(20),inout var2 varchar(20),变量定义,存储过程中可以使用关键字DECLARE定义变量，然后在后续程序过程中使用变量来处理逻辑。定义变量时可以指定一个初始值。举例说明：CREATE PROCEDURE P2(INOUT a VARCHAR(8),OUT b INTEGER)LANGUAGE SQL BEGIN DECLARE var1 INTEGER DEFAULT 0;DECLARE var2 VARCHAR(5)DEFAULT a|bc;-other SQL statements END,赋值语句,存储过程使用关键字SET给变量赋值。举例说明:CREATE PROCEDURE P2(INOUT a VARCHAR(8),OUT b INTEGER)LANGUAGE SQL BEGIN DECLARE var1 INTEGER DEFAULT 0;DECLARE var2 VARCHAR(5)DEFAULT a|bc;SET var1=0;SET var1=var1+1;SET var2=var2|def;SET a=var1;SET b=var2;END,条件控制语句1,条件控制语句包括以下几种：IFEND IF举例说明:IF rating=1 THEN UPDATE employee SET salary=salary*1.10,bonus=1000 WHERE empno=employee_number;ELSEIF rating=2 THEN UPDATE employee SET salary=salary*1.05,bonus=500 WHERE empno=employee_number;ELSE UPDATE employee SET salary=salary*1.03,bonus=0 WHERE empno=employee_number;END IF;,条件控制语句2,CASEWHEN举例说明：CASEWHEN v_workdept=A00 THEN UPDATE department SET deptname=DATA ACCESS 1;WHEN v_workdept=B01 THEN UPDATE department SET deptname=DATA ACCESS 2;ELSE UPDATE department SET deptname=DATA ACCESS 3;END CASE,循环语句1,循环语句包括以下几种：WHILE举例说明：WHILE v_counter(v_numRecords/2+1)DO SET v_salary1=v_salary2;SET v_counter=v_counter+1;END WHILE;,循环语句2,LOOP举例说明:LOOPFETCH c1 INTO v_firstnme,v_midinit,v_lastname;-Use a local variable for the iterator variable-because SQL procedures only allow you to assign-values to an OUT parameter SET v_counter=v_counter+1;IF v_midinit=THEN LEAVE fetch_loop;END IF;END LOOP fetch_loop;,常用操作符,常用操作符有以下几种：关系运算符关系运算符有六种：小于、小于等于、大于、大于等于、等于、不等于逻辑运算符逻辑运算符有三种：AND、OR、NOT,游标使用1,游标有两种类型：静态的和动态的。使用游标前要先定义，然后可以使用循环语句操作游标。举例说明：CREATE PROCEDURE leave_loop(OUT counter INT)LANGUAGE SQLBEGIN DECLARE SQLSTATE CHAR(5);DECLARE v_firstnme VARCHAR(12);DECLARE v_midinit CHAR(1);DECLARE v_lastname VARCHAR(15);DECLARE v_counter SMALLINT DEFAULT 0;DECLARE at_end SMALLINT DEFAULT 0;DECLARE not_found CONDITION for SQLSTATE 02000;,游标使用2,DECLARE c1 CURSOR FOR SELECT firstnme,midinit,lastname FROM employee;DECLARE CONTINUE HANDLER for not_found SET at_end=1;-initialize OUT parameter SET counter=0;OPEN c1;fetch_loop:LOOP FETCH c1 INTO v_firstnme,v_midinit,v_lastname;IF at_end 0 THEN LEAVE fetch_loop;END IF;,游标使用3,-Use a local variable for the iterator variable-because SQL procedures only allow you to assign-values to an OUT parameter SET v_counter=v_counter+1;END LOOP fetch_loop;CLOSE c1;-Now assign the value of the local-variable to the OUT parameter SET counter=v_counter;END,动态游标使用,动态游标使用起来比较方便灵活，在存储过程中会经常用到，也推荐使用动态游标处理逻辑。举例说明：DECLARE c1 CURSOR FOR s0;SET temp_sql=SELECT colname FROM SYSCAT.COLUMNS WHERE TABSCHEMA=|tgtschema|and TABNAME=|tgttabname|and colnameLSBH ORDER BY colno;PREPARE s0 FROM temp_sql；OPEN c1;,异常处理,存储过程中要有能够处理程序发生异常时的处理流程。举例说明：-自定义异常处理DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET ERR_MSG=RTRIM(CHAR(CURRENT TIMESTAMP)|(P_GENERATESQL)系统错误：SQLCODE=|RTRIM(CHAR(SQLCODE)|,SQLSTATE=|SQLSTATE|;储存过程中间处理流程-执行过程中发生异常返回出错标志和出错信息IF ERR_MSG THEN SET RUNSTATUS=1;SET MESSAGE=MESSAGE|ERR_MSG;END IF;,SESSION临时表使用,临时表只能建立在用户临时表空间上，而不能建立在系统临时表空间上。临时表是在一个SESSION内有效的。如果程序有多线程，最好不要使用临时表，因为比较难控制。建立临时表最好加上with replace选项，这样可以不显示地drop临时表。举例说明：DECLARE GLOBAL TEMPORARY TABLE ETL_TEMPSQL(TGTSCHEMA VARCHAR(128),TGTTABLE VARCHAR(128),XH INTEGER,TYPE CHARACTER(1),SQL VARCHAR(3000)NOT LOGGED WITH REPLACE;,常用函数1,DB2常用函数有以下一些：类似oracle中decode的判断操作例如：selectcasea1 when1thenn1 when2thenn2 elsen3 endasaa1 from 表名 类似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再次查询语句，就会得到不同的一系列行。,事务隔离级别1,DB2使用四个不同的事务隔离级别来实现并发。可重复读可重复读(RR)锁定应用程序在一个事务中引用的所有行，可以防止幻影行。例如如果扫描10000行数据并对它们进行过滤，尽管只有10行满足条件，但仍会锁定全部的10000行数据。在可重复读隔离级别下，其他事务不能更新、删除、插入数据，执行的事务看不到其他事务的未提交修改。可重复读可能会持有大量锁定最终导致行锁定升级到表锁定。读稳定性读稳定性(RS)只锁定满足查询条件的行，有可能看到幻影行。也就是如果一个使用读稳定性隔离级别的事务多次执行同一个查询，该事务可能会得到别的事物插入的符合该搜索规则的新行。,事务隔离级别2,游标稳定性游标稳定性(CS)是缺省隔离级别，事务只锁定当前游标位置的行，可能出现幻影行和不可重复读取数据。别的事务不能对游标位置的行进行更新或删除，但还能在被锁定行的两端插入、删除或修改行。未提交读未提交读(UR)允许一个事务访问其他事务未提交的修改数据，可能出现幻影行和不可重复读取数据。使用未提交读不需要对任何行锁定，适用于只读表上的查询和那些并不关心是否可从其他应用程序中看到未提交的数据。举例说明为查询语句指定隔离级别：select*fromstockwithur,事务隔离级别3,DB2通用数据库支持的四种隔离级别：,谢谢！,
