DB2存储过程培训

*,DB2,存储过程基础培训,2008,年,7,月,28,日,内容提要,数据类型,使用存储过程的优点,储存过程的结构,参数定义,变量定义,赋值语句,条件控制语句,循环语句,常用操作符,异常处理,游标使用,动态游标使用,SESSION,临时表使用,2,数据类型,定长型字符串（,CHAR,）,变长型字符串（,VARCHAR,）,整数类型（,SMALLINT,、,INTEGER,、,BIGINT,）,带小数点的数字类型（,DECIMAL,、,REAL,、,DOUBLE,）,时间类型（,DATE,、,TIME,、,TIMESTAMP,）,对象类型（,BLOB,、,CLOB,、,DBCLOB,）,3,使用储存过程优点,减少客户机与服务器之间的网络使用率。客户机应用程序将控制权传送到数据库服务器上的存储过程。存储过程在数据库服务器上执行中间处理，而不需要在网络中传送不需要的数据。,提高安全性。通过使使用静态,SQL,的存储过程包含数据库特权，数据库管理员（,DBA,）可以提高安全性。调用存储过程的客户机应用程序的用户不需要数据库特权。,提高可靠性。在数据库应用程序环境中，许多任务是重复的。通过重用一个公共过程，存储过程就可以高效地解决这些重复情况。,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;,5,参数定义,1,DB2,储存过程的参数分为两部分：输入、输出参数和性能相关参数。,输入、输出参数表示方式,:,输入参数用,IN,开头,输出参数用,OUT,开头,既是输入又是输出参数用,INOUT,开头,举例说明：,create procedure sp_sample (,in var0 varchar(10),out var1 varchar(20),inout var2 varchar(20),6,参数定义,2,创建存储过程语句（,CREATE PROCEDURE,）可以包含很多参数，虽然从语法角度讲它们不是必须的，但是在创建存储过程时提供它们可以提高执行效率。下面是一些常用的参数,容许,SQL,（,allowed,SQL,）,容许,SQL,（,allowed,SQL,）子句的值指定了存储过程是否会使用,SQL,语句，如果使用，其类型如何。它的可能值如下所示：,7,参数定义,3,NO SQL,： 表示存储过程不能够执行任何,SQL,语句。,CONTAINS SQL,： 表示存储过程可以执行,SQL,语句，但不会读取,SQL,数据，也不会修改,SQL,数据。,READS SQL DATA,： 表示在存储过程中包含不会修改,SQL,数据的,SQL,语句。也就是说该储存过程只从数据库中读取数据。,MODIFIES SQL DATA,： 表示存储过程可以执行任何,SQL,语句。即可以对数据库中的数据进行增加、删除和修改。,如果没有明确声明,allowed-SQL,，其默认值是,MODIFIES SQL DATA,。不同类型的存储过程执行的效率是不同的，其中,NO SQL,效率最好，,MODIFIES SQL DATA,最差。如果存储过程只是读取数据，但是因为没有声明,allowed-SQL,使其被当作对数据进行修改的存储过程来执行，这显然会降低程序的执行效率。因此创建存储过程时，应当明确声明其,allowed-SQL,。,8,参数定义,4,返回结果集个数（,DYNAMIC RESULT SETS n,）,存储过程能够返回,0,个或者多个结果集。为了从存储过程中返回结果集，需要执行如下步骤：,在,CREATE PROCEDURE,语句的,DYNAMIC RESULT SETS,子句中声明存储过程将要返回的结果集的数量（,number-of-result-sets,）。如果这里声明的返回结果集的数量小于存储过程中实际返回的结果集数量，在执行该存储过程的时候，,DB2,会返回一个警告。,如下存储过程就会返回警告：,9,参数定义,5,CREATE 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 RETURN,FOR SELECT name, dept, job,salary,FROM staff,WHERE salary 20000;,OPEN cur1;,OPEN cur2;,END;,10,变量定义,存储过程中可以使用关键字,DECLARE,定义变量，然后在后续程序过程中使用变量来处理逻辑。定义变量时可以指定一个初始值。,举例说明：,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 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;,DECLARE temp11 TIMESTAMP DEFAULT 2001-01-05-12.00.00;,DECLARE temp12 CLOB(2G);,DECLARE temp13 BLOB(2G);,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 = CURRENT SCHEMA;,12,条件控制语句,2,CASEWHEN,举例说明：,CASE,WHEN 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,13,循环语句,1,循环语句包括以下几种：,WHILE,举例说明：,WHILE v_counter ?,;,PREPARE st FROM stmt,;,OPEN cur1 USING SAL,;,END,;,25,SESSION,临时表使用,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,;,DECLARE SQLSTATE CHAR,(,5,),DEFAULT 00000,;,DECLARE not_found CONDITION FOR SQLSTATE 02000,;,DECLARE at_end INTEGER DEFAULT 0,;,26,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 CURSOR WITH RETURN,FOR SELECT name,dept,job,salary,FROM staff WHERE salary,20000,;,27,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,THANK YOU,