DB2存储过程精简教程

1、DB2DB2存储过程基础培训存储过程基础培训20092009年年1 1月月1 1日日2内容提要内容提要数据类型数据类型使用存储过程的优点使用存储过程的优点储存过程的结构储存过程的结构参数定义参数定义变量定义变量定义赋值语句赋值语句条件控制语句条件控制语句循环语句循环语句常用操作符常用操作符异常处理异常处理游标使用游标使用动态游标使用动态游标使用SESSIONSESSION临时表使用临时表使用3数据类型数据类型定长型字符串（定长型字符串（CHARCHAR）变长型字符串（变长型字符串（VARCHARVARCHAR）整数类型（整数类型（SMALLINTSMALLINT、INTEGERINTEGER、

2、BIGINTBIGINT）带小数点的数字类型（带小数点的数字类型（DECIMALDECIMAL、REALREAL、DODOUBLEUBLE）时间类型（时间类型（DATEDATE、TIMETIME、TIMESTAMPTIMESTAMP）对象类型（对象类型（BLOBBLOB、CLOBCLOB、DBCLOBDBCLOB）4使用储存过程优点使用储存过程优点减少客户机与服务器之间的网络使用率。客户机应减少客户机与服务器之间的网络使用率。客户机应用程序将控制权传送到数据库服务器上的存储过程。用程序将控制权传送到数据库服务器上的存储过程。存储过程在数据库服务器上执行中间处理，而不需存储过程在数据库服务器上执

3、行中间处理，而不需要在网络中传送不需要的数据。要在网络中传送不需要的数据。 提高安全性。通过使使用静态提高安全性。通过使使用静态 SQL SQL 的存储过程包的存储过程包含数据库特权，数据库管理员（含数据库特权，数据库管理员（DBADBA）可以提高安）可以提高安全性。调用存储过程的客户机应用程序的用户不需全性。调用存储过程的客户机应用程序的用户不需要数据库特权。要数据库特权。 提高可靠性。在数据库应用程序环境中，许多任务提高可靠性。在数据库应用程序环境中，许多任务是重复的。通过重用一个公共过程，存储过程就可是重复的。通过重用一个公共过程，存储过程就可以高效地解决这些重复情况。以高效地解决这些重

4、复情况。5存储过程结构存储过程结构存储过程结构如下：存储过程结构如下：CREATE PROCEDURE SP_STAFF (IN SAL INT )CREATE PROCEDURE SP_STAFF (IN SAL INT ) DYNAMIC RESULT SETS 1 DYNAMIC RESULT SETS 1 LANGUAGE SQL LANGUAGE SQL READS SQL DATA READS SQL DATA BEGIN BEGIN DECLARE cur1 CURSOR WITH RETURN DECLARE cur1 CURSOR WITH RETURN FOR SELECT

5、 name, dept, job,salary FOR SELECT name, dept, job,salary FROM staff FROM staff WHERE salary SAL; WHERE salary SAL; OPEN cur1; OPEN cur1; END; END;6参数定义参数定义1 1DB2DB2储储存存过过程的程的参数参数分分为两为两部分：部分：输输入、入、输输出出参数参数和和性能相性能相关参数关参数。输输入、入、输输出出参数参数表示方式表示方式: :u输入参数用输入参数用ININ开头开头u输出参数用输出参数用OUTOUT开头开头u既是输入又是输出参数用既是输

6、入又是输出参数用INOUTINOUT开头开头举例说明：举例说明：create procedure sp_sample (create procedure sp_sample (in var0 varchar(10),in var0 varchar(10),out var1 varchar(20),out var1 varchar(20),inout var2 varchar(20)inout var2 varchar(20)7参数定义参数定义2 2创建存储过程语句（CREATE PROCEDURE）可以包含很多参数，虽然从语法角度讲它们不是必须的，但是在创建存储过程时提供它们可以提高执行效率。

7、下面是一些常用的参数 容许容许 SQL SQL （allowedallowedSQLSQL） 容许 SQL （allowedSQL）子句的值指定了存储过程是否会使用 SQL 语句，如果使用，其类型如何。它的可能值如下所示：8参数定义参数定义3 3NO SQLNO SQL： 表示存表示存储过储过程不能程不能够执够执行任何行任何 SQL SQL 语语句。句。 CONTAINS SQLCONTAINS SQL： 表示存表示存储过储过程可以程可以执执行行 SQL SQL 语语句，但不句，但不会会读读取取 SQL SQL 数数据，也不据，也不会会修改修改 SQL SQL 数数据。据。 READS SQL

8、 DATAREADS SQL DATA： 表示在存表示在存储过储过程中包含不程中包含不会会修改修改 SQL SQL 数数据的据的 SQL SQL 语语句。也就是句。也就是说该储说该储存存过过程只程只从数从数据据库库中中读读取取数数据。据。 MODIFIES SQL DATAMODIFIES SQL DATA： 表示存表示存储过储过程可以程可以执执行任何行任何 SQL SQL 语语句。句。即可以即可以对数对数据据库库中的中的数数据据进进行增加、行增加、删删除和修改。除和修改。如果没有明确声明 allowed-SQL，其默认值是 MODIFIES SQL DATA。不同类型的存储过程执行的效率是不

9、同的，其中 NO SQL 效率最好，MODIFIES SQL DATA 最差。如果存储过程只是读取数据，但是因为没有声明 allowed-SQL 使其被当作对数据进行修改的存储过程来执行，这显然会降低程序的执行效率。因此创建存储过程时，应当明确声明其 allowed-SQL。9参数定义参数定义4 4返回结果集个数（返回结果集个数（DYNAMIC RESULT SETS nDYNAMIC RESULT SETS n） 存储过程能够返回 0 个或者多个结果集。为了从存储过程中返回结果集，需要执行如下步骤： 在 CREATE PROCEDURE 语句的 DYNAMIC RESULT SETS 子句中

10、声明存储过程将要返回的结果集的数量（number-of-result-sets）。如果这里声明的返回结果集的数量小于存储过程中实际返回的结果集数量，在执行该存储过程的时候，DB2 会返回一个警告。 如下存储过程就会返回警告：10参数定义参数定义5 5CREATE PROCEDURE RESULT_SET ( ) CREATE PROCEDURE RESULT_SET ( ) DYNAMIC RESULT SETS 1 DYNAMIC RESULT SETS 1 LANGUAGE SQL LANGUAGE SQL READS SQL DATA READS SQL DATA BEGIN BEGIN

11、 DECLARE cur1 CURSOR WITH RETURN DECLARE cur1 CURSOR WITH RETURN FOR SELECT name, dept, job,salary FOR SELECT name, dept, job,salary FROM staff FROM staff WHERE salary 20000; WHERE salary 20000; DECLARE cur2 CURSOR WITH RETURN DECLARE cur2 CURSOR WITH RETURN FOR SELECT name, dept, job,salary FOR SEL

12、ECT name, dept, job,salary FROM staff FROM staff WHERE salary 20000; WHERE salary 20000; OPEN cur1; OPEN cur1; OPEN cur2; OPEN cur2;END;END;11变量定义变量定义存储过程中可以使用关键字存储过程中可以使用关键字DECLAREDECLARE定义变量，然后在后续程序定义变量，然后在后续程序过程中使用变量来处理逻辑。定义变量时可以指定一个初始值。过程中使用变量来处理逻辑。定义变量时可以指定一个初始值。举例说明：举例说明：DECLARE temp1 SMALLINT

13、 DEFAULT 0;DECLARE temp1 SMALLINT DEFAULT 0;DECLARE temp2 INTEGER DEFAULT 10;DECLARE temp2 INTEGER DEFAULT 10;DECLARE temp3 DECIMAL(10,2) DEFAULT 100.10;DECLARE temp3 DECIMAL(10,2) DEFAULT 100.10;DECLARE temp4 REAL DEFAULT 10.1;DECLARE temp4 REAL DEFAULT 10.1;DECLARE temp5 DOUBLE DEFAULT 10000.1001;

14、DECLARE temp5 DOUBLE DEFAULT 10000.1001;DECLARE temp6 BIGINT DEFAULT 10000;DECLARE temp6 BIGINT DEFAULT 10000;DECLARE temp7 CHAR(10) DEFAULT yes;DECLARE temp7 CHAR(10) DEFAULT yes;DECLARE temp8 VARCHAR(10) DEFAULT hello;DECLARE temp8 VARCHAR(10) DEFAULT hello;DECLARE temp9 DATE DEFAULT 1998-12-25;DE

15、CLARE temp9 DATE DEFAULT 1998-12-25;DECLARE temp10 TIME DEFAULT 1:50 PM;DECLARE temp10 TIME DEFAULT 1:50 PM;DECLARE temp11 TIMESTAMP DEFAULT 2001-01-05-12.00.00;DECLARE temp11 TIMESTAMP DEFAULT 2001-01-05-12.00.00;DECLARE temp12 CLOB(2G);DECLARE temp12 CLOB(2G);DECLARE temp13 BLOB(2G);DECLARE temp13

16、 BLOB(2G);12赋值语句赋值语句存储过程使用关键字存储过程使用关键字SETSET给变量赋值。给变量赋值。举例说明举例说明: :SET total = 100;SET total = 100;VALUES(100,200,200+1) INTO var1,var2,var3; /VALUES(100,200,200+1) INTO var1,var2,var3; /* *并并行行赋值赋值，效率高，效率高* */ /SET total = NULL;SET total = NULL;SET total = (select sum(c1) from T1);SET total = (sele

17、ct sum(c1) from T1);SET sch = CURRENT SCHEMA;SET sch = CURRENT SCHEMA;13条件控制语句条件控制语句2 2CASEWHENCASEWHEN举例说明：举例说明：CASECASEWHEN v_workdept = A00 WHEN v_workdept = A00 THEN UPDATE department THEN UPDATE department SET deptname = DATA ACCESS 1;SET deptname = DATA ACCESS 1; WHEN v_workdept = B01 WHEN v_w

18、orkdept = B01 THEN UPDATE department THEN UPDATE department SET deptname = DATA ACCESS 2; SET deptname = DATA ACCESS 2; ELSE UPDATE department ELSE UPDATE department SET deptname = DATA ACCESS 3;SET deptname = DATA ACCESS 3; END CASE END CASE 14循环语句循环语句1 1循环语句包括以下几种：循环语句包括以下几种：WHILEWHILE举例说明：举例说明：WH

19、ILE v_counter (v_numRecords / 2 + 1) DOWHILE v_counter (v_numRecords / 2 + 1) DO SET v_salary1 = v_salary2; SET v_salary1 = v_salary2; SET v_counter = v_counter + 1; SET v_counter = v_counter + 1;END WHILE;END WHILE;15循环语句循环语句2 2LOOPLOOP举例说明举例说明: :LOOPLOOPFETCH c1 INTO v_firstnme, v_midinit, v_lastn

20、ame;FETCH c1 INTO v_firstnme, v_midinit, v_lastname; - Use a local variable for the iterator variable - Use a local variable for the iterator variable - because SQL procedures only allow you to assign - because SQL procedures only allow you to assign - values to an OUT parameter - values to an OUT p

21、arameter SET v_counter = v_counter + 1; SET v_counter = v_counter + 1; IF v_midinit = THEN IF v_midinit = THEN LEAVE fetch_loop; LEAVE fetch_loop; END IF; END IF;END LOOP fetch_loop;END LOOP fetch_loop;16循环语句循环语句3 3FORFOR举例说明：举例说明：CREATE PROCEDURE Concat_names()CREATE PROCEDURE Concat_names()LANGUAG

22、E SQLLANGUAGE SQLBEGIN - Note: implicit cursor manipulationBEGIN - Note: implicit cursor manipulation DECLARE fullname CHAR(140); DECLARE fullname CHAR(140); FOR v1 AS SELECT firstnme, midinit, lastname FOR v1 AS SELECT firstnme, midinit, lastname FROM employee FROM employee DO DO SET fullname = v1.

23、lastname | , | v1.firstnme | SET fullname = v1.lastname | , | v1.firstnme | | v1.midinit;| | v1.midinit; INSERT INTO tname VALUES (fullname); INSERT INTO tname VALUES (fullname); END FOR; END FOR;ENDEND17常用操作符常用操作符常用操作符有以下几种：常用操作符有以下几种：关系运算符关系运算符关系运算符有六种：小于、小于等于、大于、关系运算符有六种：小于、小于等于、大于、大于等于、等于、不等于大于等

24、于、等于、不等于逻辑运算符逻辑运算符逻辑运算符有三种：逻辑运算符有三种：ANDAND、OROR、NOTNOT18异常处理异常处理1 1任何任何SQLSQL语句执行若发生语句执行若发生SQLSTATE00000SQLSTATE00000的情况都可的情况都可能唤起能唤起conditioncondition，可以是通用的，可以是通用的conditionsconditions：SQLWARNING, SQLWARNING, SQLEXCEPTION, NOT FOUNDSQLEXCEPTION, NOT FOUND，如：，如： DECLARE not_found CONDITION FOR NOT F

25、OUND;DECLARE not_found CONDITION FOR NOT FOUND;也可以是指定也可以是指定SQLSTATESQLSTATE的的conditionsconditions，如：，如： DECLARE trunc CONDITION FOR SQLSTATE 01004;DECLARE trunc CONDITION FOR SQLSTATE 01004;注意：注意： 为避免未预见的出错情况出现后被忽略，应尽量避免定义为避免未预见的出错情况出现后被忽略，应尽量避免定义SQLEXCEPTION CONDITIONSQLEXCEPTION CONDITION，而应针对具体的，

26、而应针对具体的SQLSTATESQLSTATE定义定义CONDITIONCONDITION。19异常处理异常处理2 2CONDITION HANDLECONDITION HANDLE的定的定义义： BEGIN BEGIN DECLARE DECLARE HANDLER FOR HANDLER FOR 唤唤醒醒conditionsconditionsCONTINUECONTINUE点点statement_1;statement_1;statement_2;EXITstatement_2;EXIT或或UNDOUNDO点点statement_3;statement_3; ENDEND定定义义出出错处

27、错处理理动动作：作：CONTINUECONTINUE，EXITEXIT或或UNDOUNDO。为为已已经经定定义义的的conditioncondition名或是直接的通用名或是直接的通用conditionsconditions，可以是多可以是多个个是一是一条条或多或多条语条语句，可以包含控制句，可以包含控制语语句句20异常处理异常处理3 3例子：例子：DECLARE CONTINUE HANDLER FOR not_found, SQLEXCEPTIONDECLARE CONTINUE HANDLER FOR not_found, SQLEXCEPTION SET at_end = 1; SET

28、 at_end = 1;DECLARE CONTINUE HANDLER FOR trunc BEGINDECLARE CONTINUE HANDLER FOR trunc BEGIN SET truncated = 1; SET truncated = 1; SET msg=message; SET msg=message; END; END;注意：注意： 若若SQL PROCEDURESQL PROCEDURE语句执行后语句执行后SQLSTATE=02000SQLSTATE=02000或或SQLSTATE=SQLSTATE=01xxx,01xxx,引起引起SQLWARNINGSQLWARN

29、ING或或NOT FOUNDNOT FOUND条件，且定义了相应条件的条件，且定义了相应条件的hahandlerndler，则，则DB2DB2将控制交给相应将控制交给相应handlerhandler；若未定义；若未定义handlerhandler，则，则DB2DB2设设SQSQLSTATELSTATE及及SQLCODESQLCODE值并继续运行。值并继续运行。21异常处理异常处理4 4若若SQL PROCEDURESQL PROCEDURE语句执行后出错，引起语句执行后出错，引起SQLEXCEPTIONSQLEXCEPTION条件，且条件，且定义了相应条件的定义了相应条件的handlerhan

30、dler，则，则DB2DB2将控制交给相应将控制交给相应handlerhandler，若，若handlerhandler运行成功，则运行成功，则SQLCODESQLCODE及及SQLSTATESQLSTATE重置为重置为0 0及及0000000000；若未定义；若未定义hahandlerndler，则，则DB2DB2中止中止PROCEDUREPROCEDURE并返回并返回CLIENTCLIENT。需要注意的是，任何语句的成功执行都会将需要注意的是，任何语句的成功执行都会将SQLCODESQLCODE、SQLSTATESQLSTATE重重置为置为0,000000,00000。若需要截获出错代码，

31、唯一的方法是在。若需要截获出错代码，唯一的方法是在handlerhandler的第一条的第一条语句将其中的一个值保存在变量中，如：语句将其中的一个值保存在变量中，如： DECLARE CONTINUE HANDLER for SQLEXCEPTION DECLARE CONTINUE HANDLER for SQLEXCEPTION SET Saved_SQLCODE = SQLCODE; SET Saved_SQLCODE = SQLCODE;若若PROCEDUREPROCEDURE中需要向客户端返回用户错误信息，可使用中需要向客户端返回用户错误信息，可使用SIGNALSIGNAL： SIG

32、NAL SQLSTATE 20000 SET MESSAGE_TEXT=SIGNAL SQLSTATE 20000 SET MESSAGE_TEXT=找不到用户记录找不到用户记录MESSAGE_TEXTMESSAGE_TEXT也可以是一个字符串变量，此功能可以用来调试存储也可以是一个字符串变量，此功能可以用来调试存储过程。过程。22游标使用游标使用1 1游标有两种类型：静态的和动态的。使用游标前要先游标有两种类型：静态的和动态的。使用游标前要先定义，然后可以使用循环语句操作游标。定义，然后可以使用循环语句操作游标。举例说明：举例说明：CREATE PROCEDURE leave_loop(OU

33、T counter INT)CREATE PROCEDURE leave_loop(OUT counter INT)LANGUAGE SQLLANGUAGE SQLBEGINBEGIN DECLARE SQLSTATE CHAR(5); DECLARE SQLSTATE CHAR(5); DECLARE v_firstnme VARCHAR(12); DECLARE v_firstnme VARCHAR(12); DECLARE v_midinit CHAR(1); DECLARE v_midinit CHAR(1); DECLARE v_lastname VARCHAR(15); DECLA

34、RE v_lastname VARCHAR(15); DECLARE v_counter SMALLINT DEFAULT 0; DECLARE v_counter SMALLINT DEFAULT 0; DECLARE at_end SMALLINT DEFAULT 0; DECLARE at_end SMALLINT DEFAULT 0; DECLARE not_found CONDITION for SQLSTATE 02000 DECLARE not_found CONDITION for SQLSTATE 02000; ;23游标使用游标使用2 2DECLARE c1 CURSOR

35、FOR DECLARE c1 CURSOR FOR SELECT firstnme, midinit, lastname SELECT firstnme, midinit, lastname FROM employee; FROM employee; DECLARE CONTINUE HANDLER for not_found DECLARE CONTINUE HANDLER for not_found SET at_end = 1; SET at_end = 1; - initialize OUT parameter - initialize OUT parameter SET counte

36、r = 0; SET counter = 0; OPEN c1;OPEN c1; fetch_loop: fetch_loop: LOOP LOOP FETCH c1 INTO FETCH c1 INTO v_firstnme, v_midinit, v_lastname; v_firstnme, v_midinit, v_lastname; IF at_end 0 THEN LEAVE fetch_loop; IF at_end 0 THEN LEAVE fetch_loop; END IF; END IF;24游标使用游标使用3 3- Use a local variable for th

37、e iterator variable- Use a local variable for the iterator variable- because SQL procedures only allow you to assign - because SQL procedures only allow you to assign - values to an OUT parameter- values to an OUT parameter SET v_counter = v_counter + 1; SET v_counter = v_counter + 1; END LOOP fetch

38、_loop; END LOOP fetch_loop; CLOSE c1; CLOSE c1;- Now assign the value of the local- Now assign the value of the local - variable to the OUT parameter - variable to the OUT parameter SET counter = v_counter; SET counter = v_counter;END END 25动态游标使用动态游标使用动态游标使用起来比较方便灵活，在存储过程中会经常用到，动态游标使用起来比较方便灵活，在存储过程

39、中会经常用到，也推荐使用动态游标处理逻辑。也推荐使用动态游标处理逻辑。举例说明：举例说明：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 WITH RETURN FOR st; ; SET stmt = = SELECT name, dept, job,salary FROM staff WHERE salary ?; ; PREPARE st FROM stmt; ; OPEN cur1 USING SAL; ; END; ;26SESSIONSESSION临时表使用临时表使用1 1临时表只能建立在用户临时表空间上，而不能建立在系统临时表空间临时表只能建立在用户