《db2数据库存储过程》由会员分享，可在线阅读，更多相关《db2数据库存储过程（83页珍藏版）》请在金锄头文库上搜索。
1、,DB2数据库中的存储过程,主要内容：,1、存储过程介绍 2、存储过程基础结构 3、控制语句 4、游标和结果集 5、异常处理器 6、编写和调试存储过程,什么是存储过程？(Stored Procedure),受 DB2 服务器控制的一段可执行程序 可以通过SQL的CALL语句来完成对存储过程的调用 在存储过程中可以包含业务逻辑 存储过程可以在本地或远程进行调用 存储过程可以接收或传递参数，生成结果集,什么时候使用存储过程？,使用存储过程的合适时机: 应用程序的性能无法满足预期时 客户端数量较多且应用程序中SQL代码分散时 应用程序需要进行繁重的数据库操作，同时这些操作并不需要进行太多的客户交互
2、应用程序代码更改频繁 需要对客户应用代码进行访问控制时 客户应用需要在一次操作中执行多条 SQL 语句,C/S: 宏观交互图,主要内容：,1、存储过程介绍 2、存储过程基础结构 3、控制语句 4、游标和结果集 5、异常处理器 6、编写和调试存储过程,存储过程结构,CREATE OR REPLACE PROCEDURE ( IN | OUT | INOUT 参数名 数据类型 默认值 ) LANGUAGE SQL BEGIN 业务逻辑代码 END;,参数类型,IN（输入参数） 只是将实参传递给存储过程，但在存储过程中不能对其进行修改。换句话说，对于存储过程而言它是只读的。 OUT（输出参数） 在存
3、储过程结束时向调用者返回。一般在过程中都会被赋值。 INOUT（输入输出参数） 上述两种参数类型的结合体。它可以帮助调用者将实参传递给进程，另外它也能够作为输出参数被修改和赋值。,复合语句,复合语句是指包含在BEGIN和END间的语句。它一般包括如下语句类型： 声明语句 赋值语句 控制语句 条件处理语句,复合语句示例,说明： 1. 复合语句可以嵌套使用。 2. BEGIN语句可以和标签组合使用，这样可以更清晰的标识语句块的范围。,声明语句,变量声明 DECLARE my_var INTEGER DEFAULT 6; 条件声明 DECLARE not_found CONDITION FOR SQ
4、LSTATE 02000; 游标声明 DECLARE c1 CURSOR FOR select * from staff; 异常处理器声明 DECLARE EXIT HANDLER FOR SQLEXCEPTION ;,赋值语句,语法 SET lv_name = expression; SET lv_name = NULL; 示例 (1) SET salary = salary + salary * 0.1; (2) SET init_salary = NULL; (3) SET salary = (select salary from employee where empno = lv_em
5、p_num); 注: 如果 SELECT 语句返回记录超过一行，示例 3 将会返回SQLERROR。,存储过程例子,嵌套存储过程例子,模块(Module),模块是如下几种对象的集合： SP，UDF，global variables and cursors，types，conditions 模块的主要优势： 结构良好，便于组织 范围限定 CALL mySchema.myModule.myProc() 信息隐藏 每个对象都可以是 public 或 private 权限控制 可以模块为单位，而不是以模块中的对象为单位来控制权限,模块 - 规格说明(Module Specification),模块可以
6、发布type, SP, UDF以供外部使用。 CREATE OR REPLACE MODULE myMod; ALTER MODULE myMod PUBLISH TYPE myRowTyp AS ANCHOR ROW myTab; ALTER MODULE myMod PUBLISH FUNCTION myFunc(val1 ANCHOR myTab.col1) RETURNS myRowTyp; ALTER MODULE myMod PUBLISH PROCEDURE myProc(OUT param1 ANCHOR myTab.col2);,模块 - 实现(Module Implemen
7、tation),下面的代码是模块的实现部分： ALTER MODULE myMod ADD VARIABLE pkgVar ANCHOR myTab.col1; ALTER MODULE myMod ADD FUNCTION myFunc(val1 ANCHOR myTab.col1) RETURNS myRowTyp BEGIN DECLARE var1 myRowTyp; SELECT * INTO var1 FROM myTab WHERE col1 pkgVar; RETURN var1; END ALTER MODULE myMod ADD PROCEDURE myProc(OUT
8、param1 ANCHOR myTab.col2) BEGIN DECLARE varRow myRowTyp; SET param1 = varRow.col2 pkgVar; END,模块 - 其他语句,删除整个模块 DROP MODULE myMod; 保留规格说明内容，删除实现 ALTER MODULE myMod DROP BODY; 删除模块中的存储过程(SP) ALTER MODULE myMod DROP PROCEDURE myProc; 将模块的执行权限赋给joe GRANT EXECUTE ON MODULE myMod TO joe;,主要内容：,1、存储过程介绍 2、
9、存储过程基础结构 3、控制语句 4、游标和结果集 5、异常处理器 6、编写和调试存储过程,IF语句,格式： IF 条件1 THEN statement1; ELSEIF 条件2 THEN statement2; ELSE statement3; END IF; 注:条件成立时为TRUE (真),不成立时为FALSE(假) 和 NULL,IF语句例子,IF rating = 1 THEN UPDATE EMPLOYEE SET salary = salary*1.10 WHERE empno = i_num;（如果满足于.时，薪水调整1.1倍） ELSEIF rating = 2 THEN UP
10、DATE EMPLOYEE SET salary = salary*1.05 WHERE empno = i_num; ELSE UPDATE EMPLOYEE SET salary = salary*1.03 WHERE empno = i_num; END IF;,CASE语句（1 of 2）,简单CASE语句,CASE语句（2 of 2）,稍加变形的CASE语句,LOOP语句,语法 LABEL LOOP SQL-procedure-statements; END LOOP LABEL; 示例 fetch_loop: LOOP FETCH c1 INTO v_firstname, v_la
11、stname; SET counter = counter + 1; IF counter = 51 THEN LEAVE fetch_loop; END IF; END LOOP fetch_loop;,标签,关键字,FOR语句,语法 LABEL FOR for-loop-name AS cursor-name CURSOR FOR select-statement DO SQL-procedure-statements; END FOR LABEL; 示例 DECLARE fullname CHAR(40); FOR v1 AS c1 CURSOR FOR SELECT firstnme,
12、 midinit, lastname FROM employee DO SET fullname=lastname|,|firstnme|,|midinit; INSERT INTO tname VALUE (fullname); END FOR;,其他控制语句,REPEAT语句 ftch_loop2: REPEAT FETCH c1 INTO v_firstname, v_midinit, v_lastname; UNTIL SQLCODE 0 AND REPEAT ftch_loop2; WHILE语句 WHILE at_end = 0 DO FETCH c1 INTO v_firstna
13、me, v_midinit, v_lastname; IF SQLCODE = 100 THEN SET at_end = 1; END IF; END WHILE;,LEAVE和ITERATE语句,LEAVE和ITERATE语句来控制循环 LEAVE语句用来跳出循环 ITERATE语句用来回到for或者while循环的开始重新执行 示例 FETCH_LOOP1: LOOP FETCH c1 INTO v_dept, v_deptname, v_admdept; IF at_end = 1 THEN LEAVE FETCH_LOOP1; ELSEIF v_dept = D01 THEN ITE
14、RATE FETCH_LOOP1; END IF; INSERT INTO department(deptno, deptname, admdept) VALUES(NEW, v_deptname, v_admdept); END LOOP FETCH_LOOP1;,GOTO语句,GOTO语句用于直接跳转到指定标签处。例如： IF v_DEPT = D11 GOTO bye; bye:,RETURN语句,RETURN语句用于向调用返回。 IF v_DEPT = D11 RETURN 1;,主要内容：,1、存储过程介绍 2、存储过程基础结构 3、控制语句 4、游标和结果集 5、异常处理器 6、编
15、写和调试存储过程,游标的声明,下面是游标声明的几个例子： DECLARE c1 CURSOR FOR select * from staff; （DECLARE关键字，cl游标名称， CURSOR是必须有的，；指通过c1的游标来操作staff里所有的数据）最常用的最普通的。 2.DECLARE c1 CURSOR WITH HOLD FOR select * form staff; 3.DECLARE c1 CURSOR WITH RETURN TO CALLER FOR select * form staff; 4.DECLARE c1 CURSOR WITH RETURN TO CLIEN
16、T FOR select * form staff;,游标的相关操作,打开游标 OPEN 提取游标 FETCH INTO 关闭游标 CLOSE ,游标的遍历,DECLARE at_end INT DEFAULT 0; （声明了at_end的变量，默认值是0） DECLARE PIID INTEGER DEFAULT 0; DECLARE PINT INTEGER DEFAULT 0; DECLARE not_found CONDITIONFORSQLSTATE02000; DECLARE c1 CURSOR FOR SELECT IID FROM YH; （声明了一个游标，把IID的指标拿出来） DECLARE CONTINUE HANDLER FOR not_found SET at_end = 1; OPENc1; （进行循环） SET PCOUNT = 0; ins_loop: LOOP FETCH c1 INTO PIID; IF at_end 0 THE
