DB存储过程精简教程PPT学习教案

1、会计学1DB存储过程精简教程存储过程精简教程2数据类型数据类型定长型字符串（定长型字符串（CHAR）变长型字符串（变长型字符串（VARCHAR）整数类型（整数类型（SMALLINT、INTEGER、BIGINT）带小数点的数字类型（带小数点的数字类型（DECIMAL、REAL、DOUBLE）时间类型（时间类型（DATE、TIME、TIMESTAMP）对象类型（对象类型（BLOB、CLOB、DBCLOB）第1页/共28页3使用储存过程优点使用储存过程优点减少客户机与服务器之间的网络使用率。客户机减少客户机与服务器之间的网络使用率。客户机应用程序将控制权传送到数据库服务器上的存储过应用程序将控制权

2、传送到数据库服务器上的存储过程。存储过程在数据库服务器上执行中间处理，而程。存储过程在数据库服务器上执行中间处理，而不需要在网络中传送不需要的数据。不需要在网络中传送不需要的数据。 提高安全性。通过使使用静态提高安全性。通过使使用静态 SQL 的存储过程包的存储过程包含数据库特权，数据库管理员（含数据库特权，数据库管理员（DBA）可以提高安）可以提高安全性。调用存储过程的客户机应用程序的用户不需全性。调用存储过程的客户机应用程序的用户不需要数据库特权。要数据库特权。 提高可靠性。在数据库应用程序环境中，许多任提高可靠性。在数据库应用程序环境中，许多任务是重复的。通过重用一个公共过程，存储过程就

3、务是重复的。通过重用一个公共过程，存储过程就可以高效地解决这些重复情况。可以高效地解决这些重复情况。第2页/共28页4存储过程结构存储过程结构存储过程结构如下：存储过程结构如下：CREATE PROCEDURE SP_STAFF (IN SAL INT ) 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;第3页/共

4、28页5参数定义参数定义1DB2储存过程的参数分为两部分：输入、输出参数储存过程的参数分为两部分：输入、输出参数和性能相关参数。和性能相关参数。输入、输出参数表示方式输入、输出参数表示方式:u输入参数用输入参数用IN开头开头u输出参数用输出参数用OUT开头开头u既是输入又是输出参数用既是输入又是输出参数用INOUT开头开头举例说明：举例说明：create procedure sp_sample (in var0 varchar(10),out var1 varchar(20),inout var2 varchar(20)第4页/共28页6参数定义参数定义2创建存储过程语句（CREATE PRO

5、CEDURE）可以包含很多参数，虽然从语法角度讲它们不是必须的，但是在创建存储过程时提供它们可以提高执行效率。下面是一些常用的参数 容许容许 SQL （allowedSQL） 容许 SQL （allowedSQL）子句的值指定了存储过程是否会使用 SQL 语句，如果使用，其类型如何。它的可能值如下所示：第5页/共28页7参数定义参数定义3NO SQL： 表示存储过程不能够执行任何表示存储过程不能够执行任何 SQL 语句。语句。 CONTAINS SQL： 表示存储过程可以执行表示存储过程可以执行 SQL 语句，但不会读取语句，但不会读取 SQL 数据，也不会修改数据，也不会修改 SQL 数据。

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

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

8、声明的返回结果集的数量小于存储过程中实际返回的结果集数量，在执行该存储过程的时候，DB2 会返回一个警告。 如下存储过程就会返回警告：第7页/共28页9参数定义参数定义5CREATE PROCEDURE RESULT_SET ( ) 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 20000; DECLARE cur2 CURSOR WITH RETUR

9、N FOR SELECT name, dept, job,salary FROM staff WHERE salary 20000; OPEN cur1; OPEN cur2;END;第8页/共28页10变量定义变量定义存储过程中可以使用关键字存储过程中可以使用关键字DECLARE定义变量，然后在后续程定义变量，然后在后续程序过程中使用变量来处理逻辑。定义变量时可以指定一个初始值序过程中使用变量来处理逻辑。定义变量时可以指定一个初始值。举例说明：举例说明：DECLARE temp1 SMALLINT DEFAULT 0;DECLARE temp2 INTEGER DEFAULT 10;DECL

10、ARE 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 1998-12-25;DECLARE temp10 TIME DEFAULT 1:50 PM;DECLA

11、RE temp11 TIMESTAMP DEFAULT 2001-01-05-12.00.00;DECLARE temp12 CLOB(2G);DECLARE temp13 BLOB(2G);第9页/共28页11赋值语句赋值语句存储过程使用关键字存储过程使用关键字SET给变量赋值。给变量赋值。举例说明举例说明:SET total = 100;VALUES(100,200,200+1) INTO var1,var2,var3; /*并行赋值，效率高并行赋值，效率高*/SET total = NULL;SET total = (select sum(c1) from T1);SET sch = C

12、URRENT SCHEMA;第10页/共28页12条件控制语句条件控制语句2CASEWHEN举例说明：举例说明：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 第11页/共28页13循环语句循环语句1循环语句包括以下

13、几种：循环语句包括以下几种：WHILE举例说明：举例说明：WHILE v_counter (v_numRecords / 2 + 1) DO SET v_salary1 = v_salary2; SET v_counter = v_counter + 1;END WHILE;第12页/共28页14循环语句循环语句2LOOP举例说明举例说明:LOOPFETCH c1 INTO v_firstnme, v_midinit, v_lastname; - Use a local variable for the iterator variable - because SQL procedures on

14、ly 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;第13页/共28页15循环语句循环语句3FOR举例说明：举例说明：CREATE PROCEDURE Concat_names()LANGUAGE SQLBEGIN - Note: implicit cursor manipulation DECLARE fullname CHAR(140); FOR v

15、1 AS SELECT firstnme, midinit, lastname FROM employee DO SET fullname = v1.lastname | , | v1.firstnme | | v1.midinit; INSERT INTO tname VALUES (fullname); END FOR;END第14页/共28页16常用操作符常用操作符常用操作符有以下几种：常用操作符有以下几种：关系运算符关系运算符关系运算符有六种：小于、小于等于、大于、关系运算符有六种：小于、小于等于、大于、大于等于、等于、不等于大于等于、等于、不等于逻辑运算符逻辑运算符逻辑运算符有三种：

16、逻辑运算符有三种：AND、OR、NOT第15页/共28页17异常处理异常处理1任何任何SQL语句执行若发生语句执行若发生SQLSTATE00000的情况都可能唤起的情况都可能唤起condition，可以是通用的，可以是通用的conditions：SQLWARNING, SQLEXCEPTION, NOT FOUND，如：，如： DECLARE not_found CONDITION FOR NOT FOUND;也可以是指定也可以是指定SQLSTATE的的conditions，如：，如： DECLARE trunc CONDITION FOR SQLSTATE 01004;注意：注意： 为避免未

17、预见的出错情况出现后被忽略，应尽量避免定义为避免未预见的出错情况出现后被忽略，应尽量避免定义SQLEXCEPTION CONDITION，而应针对具体的，而应针对具体的SQLSTATE定义定义CONDITION。第16页/共28页18异常处理异常处理2CONDITION HANDLE的定义：的定义： BEGIN DECLARE HANDLER FOR 唤醒唤醒conditionsCONTINUE点点statement_1;statement_2;EXIT或或UNDO点点statement_3; END定义出错处理动作：定义出错处理动作：CONTINUE，EXIT或或UNDO。为已经定义的为已经

18、定义的condition名或是直接的通用名或是直接的通用conditions，可以是多个，可以是多个是一条或多条语句，可以包含控制语句是一条或多条语句，可以包含控制语句第17页/共28页19异常处理异常处理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=

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

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

21、SQLCODE = SQLCODE;若若PROCEDURE中需要向客户端返回用户错误信息，可使用中需要向客户端返回用户错误信息，可使用SIGNAL： SIGNAL SQLSTATE 20000 SET MESSAGE_TEXT=找不到用户记录找不到用户记录MESSAGE_TEXT也可以是一个字符串变量，此功能可以用来调试存储过程。也可以是一个字符串变量，此功能可以用来调试存储过程。第19页/共28页21游标使用游标使用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 not_found CONDITION for SQLSTATE 020

23、00;第20页/共28页22游标使用游标使用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 THEN LEAVE fetch_lo

24、op; END IF;第21页/共28页23游标使用游标使用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 =

25、 v_counter;END 第22页/共28页24动态游标使用动态游标使用动态游标使用起来比较方便灵活，在存储过程中会经常用到动态游标使用起来比较方便灵活，在存储过程中会经常用到，也推荐使用动态游标处理逻辑。，也推荐使用动态游标处理逻辑。举例说明：举例说明：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

26、WITH RETURN FOR st; SET stmt = SELECT name, dept, job,salary FROM staff WHERE salary ?; PREPARE st FROM stmt; OPEN cur1 USING SAL; END;第23页/共28页25SESSION临时表使用临时表使用1临时表只能建立在用户临时表空间上，而不能建立在系统临时表空间上。临时表只能建立在用户临时表空间上，而不能建立在系统临时表空间上。临时表是在一个临时表是在一个SESSION内有效的。如果程序有多线程，最好不要使用临时表，因为比较难控制。内有效的。如果程序有多线程，最好不要使用临时表，因为比较难控制。建立临时表最好加上建立临时表最好加上with replace选项，这样可以不显示地选项，这样可以不显示地drop临时表。临时表。举例说明：举例说明：CREATE PROCEDURE DB2ADMIN.TEP_TABLE_TEST ( ) DYNAMIC RESULT SETS 1 LANGUAGE SQL MODIFIES SQL DATA BEGIN -定义错误代码 DECLARE SQLCODE INTEGER DEFAULT 0; D