《DB2存储过程精简教程.ppt》由会员分享，可在线阅读，更多相关《DB2存储过程精简教程.ppt（29页珍藏版）》请在三一办公上搜索。
1、DB2存储过程基础培训,2009年1月1日,2,内容提要,数据类型使用存储过程的优点储存过程的结构参数定义变量定义赋值语句条件控制语句循环语句常用操作符异常处理游标使用动态游标使用SESSION临时表使用,3,数据类型,定长型字符串（CHAR）变长型字符串（VARCHAR）整数类型（SMALLINT、INTEGER、BIGINT）带小数点的数字类型（DECIMAL、REAL、DOUBLE）时间类型（DATE、TIME、TIMESTAMP）对象类型（BLOB、CLOB、DBCLOB）,4,使用储存过程优点,减少客户机与服务器之间的网络使用率。客户机应用程序将控制权传送到数据库服务器上的存储过程。
2、存储过程在数据库服务器上执行中间处理，而不需要在网络中传送不需要的数据。提高安全性。通过使使用静态 SQL 的存储过程包含数据库特权，数据库管理员（DBA）可以提高安全性。调用存储过程的客户机应用程序的用户不需要数据库特权。提高可靠性。在数据库应用程序环境中，许多任务是重复的。通过重用一个公共过程，存储过程就可以高效地解决这些重复情况。,5,存储过程结构,存储过程结构如下：CREATE PROCEDURE SP_STAFF(IN SAL INT)DYNAMIC RESULT SETS 1 LANGUAGE SQL READS SQL DATA BEGIN DECLARE cur1 CURSOR
3、 WITH RETURN FOR SELECT name,dept,job,salary FROM staff WHERE salary SAL;OPEN cur1;END;,6,参数定义1,DB2储存过程的参数分为两部分：输入、输出参数和性能相关参数。输入、输出参数表示方式:输入参数用IN开头输出参数用OUT开头既是输入又是输出参数用INOUT开头举例说明：create procedure sp_sample(in var0 varchar(10),out var1 varchar(20),inout var2 varchar(20),7,参数定义2,创建存储过程语句（CREATE PROC
4、EDURE）可以包含很多参数，虽然从语法角度讲它们不是必须的，但是在创建存储过程时提供它们可以提高执行效率。下面是一些常用的参数 容许 SQL（allowedSQL）容许 SQL（allowedSQL）子句的值指定了存储过程是否会使用 SQL 语句，如果使用，其类型如何。它的可能值如下所示：,8,参数定义3,NO SQL：表示存储过程不能够执行任何 SQL 语句。CONTAINS SQL：表示存储过程可以执行 SQL 语句，但不会读取 SQL 数据，也不会修改 SQL 数据。READS SQL DATA：表示在存储过程中包含不会修改 SQL 数据的 SQL 语句。也就是说该储存过程只从数据库中
5、读取数据。MODIFIES SQL DATA：表示存储过程可以执行任何 SQL 语句。即可以对数据库中的数据进行增加、删除和修改。如果没有明确声明 allowed-SQL，其默认值是 MODIFIES SQL DATA。不同类型的存储过程执行的效率是不同的，其中 NO SQL 效率最好，MODIFIES SQL DATA 最差。如果存储过程只是读取数据，但是因为没有声明 allowed-SQL 使其被当作对数据进行修改的存储过程来执行，这显然会降低程序的执行效率。因此创建存储过程时，应当明确声明其 allowed-SQL。,9,参数定义4,返回结果集个数（DYNAMIC RESULT SETS
6、 n）存储过程能够返回 0 个或者多个结果集。为了从存储过程中返回结果集，需要执行如下步骤：在 CREATE PROCEDURE 语句的 DYNAMIC RESULT SETS 子句中声明存储过程将要返回的结果集的数量（number-of-result-sets）。如果这里声明的返回结果集的数量小于存储过程中实际返回的结果集数量，在执行该存储过程的时候，DB2 会返回一个警告。如下存储过程就会返回警告：,10,参数定义5,CREATE PROCEDURE RESULT_SET()DYNAMIC RESULT SETS 1 LANGUAGE SQL READS SQL DATA BEGIN DE
7、CLARE 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;,11,变量定义,存储过程中可以使用关键字DECLARE定义变量，然后在后续程序过程中使用变量来处理逻辑。定义变量时可以指定一个初始值。举例说明：DECLARE temp1 SMA
8、LLINT 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 temp7 CHAR(10)DEFAULT yes;DECLARE temp8 VARCHAR(10)DEFAULT hello;DECLARE temp9 DATE DEFAULT 19
9、98-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);,12,赋值语句,存储过程使用关键字SET给变量赋值。举例说明:SET total=100;VALUES(100,200,200+1)INTO var1,var2,var3;/*并行赋值，效率高*/SET total=NULL;SET total=(select sum(c1)from T1);SET sc
10、h=CURRENT SCHEMA;,13,条件控制语句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,14,循环语句1,循环语句包括以下几种：WHILE举例说明：WHILE v_counter(v_numReco
11、rds/2+1)DO SET v_salary1=v_salary2;SET v_counter=v_counter+1;END WHILE;,15,循环语句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_midini
12、t=THEN LEAVE fetch_loop;END IF;END LOOP fetch_loop;,16,循环语句3,FOR举例说明：CREATE PROCEDURE Concat_names()LANGUAGE 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
13、 tname VALUES(fullname);END FOR;END,17,常用操作符,常用操作符有以下几种：关系运算符关系运算符有六种：小于、小于等于、大于、大于等于、等于、不等于逻辑运算符逻辑运算符有三种：AND、OR、NOT,18,异常处理1,任何SQL语句执行若发生SQLSTATE00000的情况都可能唤起condition，可以是通用的conditions：SQLWARNING,SQLEXCEPTION,NOT FOUND，如：DECLARE not_found CONDITION FOR NOT FOUND;也可以是指定SQLSTATE的conditions，如：DECLARE
14、trunc CONDITION FOR SQLSTATE 01004;注意：为避免未预见的出错情况出现后被忽略，应尽量避免定义SQLEXCEPTION CONDITION，而应针对具体的SQLSTATE定义CONDITION。,19,异常处理2,CONDITION HANDLE的定义：BEGIN DECLARE HANDLER FOR 唤醒conditionsCONTINUE点statement_1;statement_2;EXIT或UNDO点statement_3;END定义出错处理动作：CONTINUE，EXIT或UNDO。为已经定义的condition名或是直接的通用conditions
15、，可以是多个是一条或多条语句，可以包含控制语句,20,异常处理3,例子：DECLARE CONTINUE HANDLER FOR not_found,SQLEXCEPTION SET at_end=1;DECLARE CONTINUE HANDLER FOR trunc BEGIN SET truncated=1;SET msg=message;END;注意：若SQL PROCEDURE语句执行后SQLSTATE=02000或SQLSTATE=01xxx,引起SQLWARNING或NOT FOUND条件，且定义了相应条件的handler，则DB2将控制交给相应handler；若未定义handl
16、er，则DB2设SQLSTATE及SQLCODE值并继续运行。,21,异常处理4,若SQL PROCEDURE语句执行后出错，引起SQLEXCEPTION条件，且定义了相应条件的handler，则DB2将控制交给相应handler，若handler运行成功，则SQLCODE及SQLSTATE重置为0及00000；若未定义handler，则DB2中止PROCEDURE并返回CLIENT。需要注意的是，任何语句的成功执行都会将SQLCODE、SQLSTATE重置为0,00000。若需要截获出错代码，唯一的方法是在handler的第一条语句将其中的一个值保存在变量中，如：DECLARE CONTIN
17、UE HANDLER for SQLEXCEPTION SET Saved_SQLCODE=SQLCODE;若PROCEDURE中需要向客户端返回用户错误信息，可使用SIGNAL：SIGNAL SQLSTATE 20000 SET MESSAGE_TEXT=找不到用户记录MESSAGE_TEXT也可以是一个字符串变量，此功能可以用来调试存储过程。,22,游标使用1,游标有两种类型：静态的和动态的。使用游标前要先定义，然后可以使用循环语句操作游标。举例说明：CREATE PROCEDURE leave_loop(OUT counter INT)LANGUAGE SQLBEGIN DECLARE
18、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;,23,游标使用2,DECLARE c1 CURSOR FOR SELECT firstnme,midinit,lastname FROM employe
19、e;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;,24,游标使用3,-Use a local variable for the iterator variable-because SQL procedures only allow you
20、 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,25,动态游标使用,动态游标使用起来比较方便灵活，在存储过程中会经常用到，也推荐使用动态游标处理逻辑。举例说明：CREATE PROCEDURE Dynamic_Cursor(IN SAL INT)DYNAMIC RESULT SE
21、TS 1 LANGUAGE SQL READS SQL DATA BEGIN DECLARE stmt varchar(255);DECLARE st STATEMENT;DECLARE cur1 CURSOR WITH RETURN FOR st;SET stmt=SELECT name,dept,job,salary FROM staff WHERE salary?;PREPARE st FROM stmt;OPEN cur1 USING SAL;END;,26,SESSION临时表使用1,临时表只能建立在用户临时表空间上，而不能建立在系统临时表空间上。临时表是在一个SESSION内有效的
22、。如果程序有多线程，最好不要使用临时表，因为比较难控制。建立临时表最好加上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 02000;
23、DECLARE at_end INTEGER DEFAULT 0;,27,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-定义游标 DECLARE cur1
24、CURSOR WITH RETURN FOR SELECT name,dept,job,salary FROM staff WHERE salary 20000;,28,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;,THANK YOU,
