DB2存储过程精简教程.ppt

《DB2存储过程精简教程.ppt》由会员分享，可在线阅读，更多相关《DB2存储过程精简教程.ppt（29页珍藏版）》请在金锄头文库上搜索。

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

2、BIGINTBIGINT）带小数点的数字类型（带小数点的数字类型（DECIMALDECIMAL、REALREAL、DOUBLEDOUBLE）时间类型（时间类型（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 DATAREADS SQL DA

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

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

10、-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 DECLARE cur1 CURSOR WITH RETURN D

11、ECLARE 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 SELECT name, dept, job,salary FROM st

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

13、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;DECLARE temp5 DOUBLE DEFAULT 10000

14、.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;DECLARE temp9 DATE DEFAULT 1998-12-2

15、5;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 BLOB(2G);12赋值语句赋值语句存储过程使用关键字存储过程使

16、用关键字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 = (select sum(c1) from T1);SET sch = CURRENT

17、 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_workdept = B01 THEN UPDATE department

18、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举例说明：举例说明：WHILE v_counter (v_numRecords / 2 + 1)

19、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_lastname;FETCH c1 INTO v_firstnme, v_midin

20、it, 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 parameter SET v_counter = v_counter +

21、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()LANGUAGE SQLLANGUAGE SQLBEGIN - Note: implic

22、it 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.lastname | , | v1.firstnme SET fullna

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

24、AND、OROR、NOTNOT18异常处理异常处理1 1任何任何SQLSQL语句执行若发生语句执行若发生SQLSTATE00000SQLSTATE00000的情况都可的情况都可能唤起能唤起conditioncondition，可以是通用的，可以是通用的conditionsconditions：SQLWARNING, SQLWARNING, SQLEXCEPTION, NOT FOUNDSQLEXCEPTION, NOT FOUND，如：，如： DECLARE not_found CONDITION FOR NOT FOUND;DECLARE not_found CONDITION FOR NO

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

26、DITION。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定定义出出错处理理动作：作：CONTINUECONTINUE，EXITEXIT或或UNDOUNDO

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

28、INDECLARE 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=01xxx,SQLSTATE=01xxx,引起引起SQLWARNINGSQLWARNING或或NOT FOUNDNOT FOUND条件，且定义了条件，且定义了相应条件的相应条件的han

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

30、lerhandler运行成功，则运行成功，则SQLCODESQLCODE及及SQLSTATESQLSTATE重置为重置为0 0及及 0000000000；若未定义；若未定义handlerhandler，则，则DB2DB2中止中止PROCEDUREPROCEDURE并返回并返回CLIENTCLIENT。需要注意的是，任何语句的成功执行都会将需要注意的是，任何语句的成功执行都会将SQLCODESQLCODE、SQLSTATESQLSTATE重重置为置为0,000000,00000。若需要截获出错代码，唯一的方法是在。若需要截获出错代码，唯一的方法是在handlerhandler的第一条的第一条语句

31、将其中的一个值保存在变量中，如：语句将其中的一个值保存在变量中，如： DECLARE CONTINUE HANDLER for SQLEXCEPTION DECLARE CONTINUE HANDLER for SQLEXCEPTION SET Saved_SQLCODE = SQLCODE; SET Saved_SQLCODE = SQLCODE;若若PROCEDUREPROCEDURE中需要向客户端返回用户错误信息，可使用中需要向客户端返回用户错误信息，可使用SIGNALSIGNAL： SIGNAL SQLSTATE 20000 SET MESSAGE_TEXT=SIGNAL SQLSTA

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

33、unter 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); DECLARE v_lastname VARCHAR(15); DECLARE v_counter SMA

34、LLINT 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 DECLARE not_found CONDITION for SQLSTATE 02000;02000;23游标使用游标使用2 2DECLARE c1 CURSOR FOR DECLARE c1 CURSOR FOR SELECT firstnme, midinit

35、, 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 counter = 0; SET counter = 0; OPEN c1;OPEN c1; fetch_loo

36、p: 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 the iterator variable- Use a local variable for the

37、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_loop; END LOOP fetch_loop; CLOSE c1; CLOSE c1;- N

38、ow 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动态游标使用动态游标使用动态游标使用起来比较方便灵活，在存储过程中会经常用到，动态游标使用起来比较方便灵活，在存储过程中会经常用到，也推荐使用动态游标处理逻辑。也推荐使用动态游标处理逻辑。举例说明：举例说明：CREAT

39、E 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

40、USING SAL; ; END; ;26SESSIONSESSION临时表使用临时表使用1 1临时表只能建立在用户临时表空间上，而不能建立在系统临时表空间上。临时表只能建立在用户临时表空间上，而不能建立在系统临时表空间上。临时表是在一个临时表是在一个SESSIONSESSION内有效的。如果程序有多线程，最好不要使内有效的。如果程序有多线程，最好不要使用临时表，因为比较难控制。用临时表，因为比较难控制。建立临时表最好加上建立临时表最好加上with replacewith replace选项，这样可以不显示地选项，这样可以不显示地dropdrop临时表。临时表。举例说明：举例说明：CREATE

41、 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; ; DECLARE at_end INTEGER DEFAULT 0; ;27SESSIONSESSION临时表使用临

42、时表使用2 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 CURSOR WITH RETURN F

43、OR SELECT name, , dept, , job, ,salary FROM staff WHERE salary 20000; ;28SESSIONSESSION临时表使用临时表使用3 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; ;