DB2存储过程培训.ppt

1、DB2DB2存储过程基础培训存储过程基础培训2008年年7月月28日日第1页，共30页。内容提要内容提要数据类型数据类型使用存储过程的优点使用存储过程的优点储存过程的结构储存过程的结构参数定义参数定义变量定义变量定义赋值语句赋值语句条件控制语句条件控制语句循环语句循环语句常用操作符常用操作符异常处理异常处理游标使用游标使用动态游标使用动态游标使用SESSION临时表使用临时表使用第2页，共30页。数据类型数据类型定长型字符串（定长型字符串（CHAR）变长型字符串（变长型字符串（VARCHAR）整数类型（整数类型（SMALLINT、INTEGER、BIGINT）带小数点的数字类型（带小数点的数字

2、类型（DECIMAL、REAL、DOUBLE）时间类型（时间类型（DATE、TIME、TIMESTAMP）对象类型（对象类型（BLOB、CLOB、DBCLOB）第3页，共30页。使用储存过程优点使用储存过程优点减少客户机与服务器之间的网络使用率。客户机应用程序减少客户机与服务器之间的网络使用率。客户机应用程序将控制权传送到数据库服务器上的存储过程。存储过程在数将控制权传送到数据库服务器上的存储过程。存储过程在数据库服务器上执行中间处理，而不需要在网络中传送不需要据库服务器上执行中间处理，而不需要在网络中传送不需要的数据。的数据。提高安全性。通过使使用静态提高安全性。通过使使用静态 SQL 的存

3、储过程包含数据库特的存储过程包含数据库特权，数据库管理员（权，数据库管理员（DBA）可以提高安全性。调用存储过程的）可以提高安全性。调用存储过程的客户机应用程序的用户不需要数据库特权。客户机应用程序的用户不需要数据库特权。提高可靠性。在数据库应用程序环境中，许多任务是重提高可靠性。在数据库应用程序环境中，许多任务是重复的。通过重用一个公共过程，存储过程就可以高效地复的。通过重用一个公共过程，存储过程就可以高效地解决这些重复情况。解决这些重复情况。第4页，共30页。存储过程结构存储过程结构存储过程结构如下：存储过程结构如下：CREATE PROCEDURE SP_STAFF(IN SAL INT

4、)DYNAMIC RESULT SETS 1 LANGUAGE SQL READS SQL DATA BEGIN DECLARE cur1 CURSOR WITH RETURN FOR SELECT name,dept,job,salary FROM staff WHERE salary SAL;OPEN cur1;END;第5页，共30页。参数定义参数定义1DB2储储存存过过程的程的参数参数分分为两为两部分：部分：输输入、入、输输出出参数参数和性能相和性能相关参数关参数。输输入、入、输输出出参数参数表示方式表示方式:u输入参数用输入参数用IN开头开头u输出参数用输出参数用OUT开头开头u既是

5、输入又是输出参数用既是输入又是输出参数用INOUT开头开头举例说明：举例说明：create procedure sp_sample(in var0 varchar(10),out var1 varchar(20),inout var2 varchar(20)第6页，共30页。参数定义参数定义2创建存储过程语句（CREATE PROCEDURE）可以包含很多参数，虽然从语法角度讲它们不是必须的，但是在创建存储过程时提供它们可以提高执行效率。下面是一些常用的参数 容许容许 SQL（allowedSQL）容许 SQL（allowedSQL）子句的值指定了存储过程是否会使用 SQL 语句，如果使用，其

6、类型如何。它的可能值如下所示：第7页，共30页。参数定义参数定义3NO SQL：表示存表示存储过储过程不能程不能够执够执行任何行任何 SQL 语语句。句。CONTAINS SQL：表示存表示存储过储过程可以程可以执执行行 SQL 语语句，但不句，但不会读会读取取 SQL 数数据，也据，也不不会会修改修改 SQL 数数据。据。READS SQL DATA：表示在存表示在存储过储过程中包含不程中包含不会会修改修改 SQL 数数据的据的 SQL 语语句。也句。也就是就是说该储说该储存存过过程只程只从数从数据据库库中中读读取取数数据。据。MODIFIES SQL DATA：表示存表示存储过储过程可以程

7、可以执执行任何行任何 SQL 语语句。即可以句。即可以对数对数据据库库中的中的数数据据进进行增加、行增加、删删除和修改。除和修改。如果没有明确声明 allowed-SQL，其默认值是 MODIFIES SQL DATA。不同类型的存储过程执行的效率是不同的，其中 NO SQL 效率最好，MODIFIES SQL DATA 最差。如果存储过程只是读取数据，但是因为没有声明 allowed-SQL 使其被当作对数据进行修改的存储过程来执行，这显然会降低程序的执行效率。因此创建存储过程时，应当明确声明其 allowed-SQL。第8页，共30页。参数定义参数定义4返回结果集个数（返回结果集个数（DY

8、NAMIC RESULT SETS n）存储过程能够返回 0 个或者多个结果集。为了从存储过程中返回结果集，需要执行如下步骤：在 CREATE PROCEDURE 语句的 DYNAMIC RESULT SETS 子句中声明存储过程将要返回的结果集的数量（number-of-result-sets）。如果这里声明的返回结果集的数量小于存储过程中实际返回的结果集数量，在执行该存储过程的时候，DB2 会返回一个警告。如下存储过程就会返回警告：第9页，共30页。参数定义参数定义5CREATE PROCEDURE RESULT_SET()DYNAMIC RESULT SETS 1 LANGUAGE SQ

9、L READS SQL DATA BEGIN DECLARE cur1 CURSOR WITH RETURN FOR SELECT name,dept,job,salary FROM staff WHERE salary 20000;DECLARE cur2 CURSOR WITH RETURN FOR SELECT name,dept,job,salary FROM staff WHERE salary 20000;OPEN cur1;OPEN cur2;END;第10页，共30页。变量定义变量定义存储过程中可以使用关键字存储过程中可以使用关键字DECLARE定义变量，然后在后续程序过程中定

10、义变量，然后在后续程序过程中使用变量来处理逻辑。定义变量时可以指定一个初始值。使用变量来处理逻辑。定义变量时可以指定一个初始值。举例说明：举例说明：DECLARE temp1 SMALLINT DEFAULT 0;DECLARE temp2 INTEGER DEFAULT 10;DECLARE temp3 DECIMAL(10,2)DEFAULT 100.10;DECLARE temp4 REAL DEFAULT 10.1;DECLARE temp5 DOUBLE DEFAULT 10000.1001;DECLARE temp6 BIGINT DEFAULT 10000;DECLARE tem

11、p7 CHAR(10)DEFAULT yes;DECLARE temp8 VARCHAR(10)DEFAULT hello;DECLARE temp9 DATE DEFAULT 1998-12-25;DECLARE temp10 TIME DEFAULT 1:50 PM;DECLARE temp11 TIMESTAMP DEFAULT 2001-01-05-12.00.00;DECLARE temp12 CLOB(2G);DECLARE temp13 BLOB(2G);第11页，共30页。赋值语句赋值语句存储过程使用关键字存储过程使用关键字SET给变量赋值。给变量赋值。举例说明举例说明:SET

12、 total=100;VALUES(100,200,200+1)INTO var1,var2,var3;/*并并行行赋值赋值，效率高效率高*/SET total=NULL;SET total=(select sum(c1)from T1);SET sch=CURRENT SCHEMA;第12页，共30页。条件控制语句条件控制语句2CASEWHEN举例说明：举例说明：CASEWHEN v_workdept=A00 THEN UPDATE department SET deptname=DATA ACCESS 1;WHEN v_workdept=B01 THEN UPDATE department

13、 SET deptname=DATA ACCESS 2;ELSE UPDATE department SET deptname=DATA ACCESS 3;END CASE 第13页，共30页。循环语句循环语句1循环语句包括以下几种：循环语句包括以下几种：WHILE举例说明：举例说明：WHILE v_counter (v_numRecords/2+1)DO SET v_salary1=v_salary2;SET v_counter=v_counter+1;END WHILE;第14页，共30页。循环语句循环语句2LOOP举例说明举例说明:LOOPFETCH c1 INTO v_firstnme

14、,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;第15页，共30页。循环语句循环语句3FOR举例说明：举例说明：CREATE PROCEDURE Concat_names()LA

15、NGUAGE SQLBEGIN-Note:implicit cursor manipulation DECLARE fullname CHAR(140);FOR v1 AS SELECT firstnme,midinit,lastname FROM employee DO SET fullname=v1.lastname|,|v1.firstnme|v1.midinit;INSERT INTO tname VALUES(fullname);END FOR;END第16页，共30页。常用操作符常用操作符常用操作符有以下几种：常用操作符有以下几种：关系运算符关系运算符关系运算符有六种：小于、小于等

16、于、大于、关系运算符有六种：小于、小于等于、大于、大于等于、等于、不等于大于等于、等于、不等于逻辑运算符逻辑运算符逻辑运算符有三种：逻辑运算符有三种：AND、OR、NOT第17页，共30页。异常处理异常处理1任何任何SQL语句执行若发生语句执行若发生SQLSTATE00000的情况都可能唤起的情况都可能唤起condition，可以是通用的，可以是通用的conditions：SQLWARNING,SQLEXCEPTION,NOT FOUND，如：，如：DECLARE not_found CONDITION FOR NOT FOUND;也可以是指定也可以是指定SQLSTATE的的condition

17、s，如：，如：DECLARE trunc CONDITION FOR SQLSTATE 01004;注意：注意：为避免未预见的出错情况出现后被忽略，应尽量避免定义为避免未预见的出错情况出现后被忽略，应尽量避免定义SQLEXCEPTION CONDITION，而应针对具体的，而应针对具体的SQLSTATE定义定义CONDITION。第18页，共30页。异常处理异常处理2CONDITION HANDLE的定的定义义：BEGIN DECLARE HANDLER FOR 唤唤醒醒conditionsCONTINUE点点statement_1;statement_2;EXIT或或UNDO点点statem

18、ent_3;END定定义义出出错处错处理理动动作：作：CONTINUE，EXIT或或UNDO。为为已已经经定定义义的的condition名或是直接的通用名或是直接的通用conditions，可以是多，可以是多个个是一是一条条或多或多条语条语句，可以包含控制句，可以包含控制语语句句第19页，共30页。异常处理异常处理3例子：例子：DECLARE CONTINUE HANDLER FOR not_found,SQLEXCEPTION SET at_end=1;DECLARE CONTINUE HANDLER FOR trunc BEGIN SET truncated=1;SET msg=messa

19、ge;END;注意：注意：若若SQL PROCEDURE语句执行后语句执行后SQLSTATE=02000或或SQLSTATE=01xxx,引起引起SQLWARNING或或NOT FOUND条件，且定义了相应条件的条件，且定义了相应条件的handler，则，则DB2将控制交给相应将控制交给相应handler；若未定义；若未定义handler，则，则DB2设设SQLSTATE及及SQLCODE值并继续运行。值并继续运行。第20页，共30页。异常处理异常处理4若若SQL PROCEDURE语句执行后出错，引起语句执行后出错，引起SQLEXCEPTION条件，且定义了相应条件的条件，且定义了相应条件的

20、handler，则，则DB2将控制交给相应将控制交给相应handler，若，若handler运行成功，则运行成功，则SQLCODE及及SQLSTATE重置为重置为0及及00000；若未定义；若未定义handler，则，则DB2中止中止PROCEDURE并返回并返回CLIENT。需要注意的是，任何语句的成功执行都会将需要注意的是，任何语句的成功执行都会将SQLCODE、SQLSTATE重置为重置为0,00000。若需要截获出错代码，唯一的方法是在。若需要截获出错代码，唯一的方法是在handler的第一条语句将其中的一的第一条语句将其中的一个值保存在变量中，如：个值保存在变量中，如：DECLARE

21、 CONTINUE HANDLER for SQLEXCEPTION SET Saved_SQLCODE=SQLCODE;若若PROCEDURE中需要向客户端返回用户错误信息，可使用中需要向客户端返回用户错误信息，可使用SIGNAL：SIGNAL SQLSTATE 20000 SET MESSAGE_TEXT=找不到用户记录找不到用户记录MESSAGE_TEXT也可以是一个字符串变量，此功能可以用来调试存储过程。也可以是一个字符串变量，此功能可以用来调试存储过程。第21页，共30页。游标使用游标使用1游标有两种类型：静态的和动态的。使用游标前要先定义，然游标有两种类型：静态的和动态的。使用游标

22、前要先定义，然后可以使用循环语句操作游标。后可以使用循环语句操作游标。举例说明：举例说明：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 n

23、ot_found CONDITION for SQLSTATE 02000;第22页，共30页。游标使用游标使用2DECLARE 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

24、THEN LEAVE fetch_loop;END IF;第23页，共30页。游标使用游标使用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 coun

25、ter=v_counter;END 第24页，共30页。动态游标使用动态游标使用动态游标使用起来比较方便灵活，在存储过程中会经常用到，也动态游标使用起来比较方便灵活，在存储过程中会经常用到，也推荐使用动态游标处理逻辑。推荐使用动态游标处理逻辑。举例说明：举例说明：CREATE PROCEDURE Dynamic_Cursor(IN SAL INT)DYNAMIC RESULT SETS 1 LANGUAGE SQL READS SQL DATA BEGIN DECLARE stmt varchar(255);DECLARE st STATEMENT;DECLARE cur1 CURSOR WI

26、TH RETURN FOR st;SET stmt=SELECT name,dept,job,salary FROM staff WHERE salary?;PREPARE st FROM stmt;OPEN cur1 USING SAL;END;第25页，共30页。SESSION临时表使用临时表使用1临时表只能建立在用户临时表空间上，而不能建立在系统临时表空间上。临时表只能建立在用户临时表空间上，而不能建立在系统临时表空间上。临时表是在一个临时表是在一个SESSION内有效的。如果程序有多线程，最好不要使用临时表，因为比较难控内有效的。如果程序有多线程，最好不要使用临时表，因为比较难控制。制

27、。建立临时表最好加上建立临时表最好加上with replace选项，这样可以不显示地选项，这样可以不显示地drop临时表。临时表。举例说明：举例说明：CREATE PROCEDURE DB2ADMIN.TEP_TABLE_TEST()DYNAMIC RESULT SETS 1 LANGUAGE SQL MODIFIES SQL DATA BEGIN-定义错误代码 DECLARE SQLCODE INTEGER DEFAULT 0;DECLARE SQLSTATE CHAR(5)DEFAULT 00000;DECLARE not_found CONDITION FOR SQLSTATE 0200

28、0;DECLARE at_end INTEGER DEFAULT 0;第26页，共30页。SESSION临时表使用临时表使用2-定定义变义变量量 DECLARE GET_NAME VARCHAR(9);DECLARE GET_DEPT SMALLINT;DECLARE GET_JOB CHARACTER(5);DECLARE GET_SALARY DECIMAL(7,2);-定义全局临时表 DECLARE GLOBAL TEMPORARY TABLE SESSION.TEMP LIKE STAFF1 WITH REPLACE NOT LOGGED IN QCTEMPTS;P2:BEGIN-定义

29、游标 DECLARE cur1 CURSOR WITH RETURN FOR SELECT name,dept,job,salary FROM staff WHERE salary 20000;第27页，共30页。SESSION临时表使用临时表使用3-定义异常处理 DECLARE CONTINUE HANDLER FOR not_found SET at_end=1;OPEN cur1;FETCH_LOOP1:LOOP FETCH CUR1 INTO GET_NAME,GET_DEPT,GET_JOB,GET_SALARY;IF at_end=1 THEN SET at_end=0;LEAVE FETCH_LOOP1;END IF;INSERT INTO SESSION.TEMP VALUES(GET_NAME,GET_DEPT,GET_JOB,GET_SALARY);END LOOP FETCH_LOOP1;INSERT INTO STAFF1 SELECT*FROM SESSION.TEMP;END P2;END;第28页，共30页。第29页，共30页。演讲完毕，谢谢观看！第30页，共30页。