DB2培训文档(共20张)(PPT 20页)

1、嵌套存储过程陆川luchua2720第1页，共20页。你将了解下列内容：什么是嵌套存储过程如何在嵌套存储过程间传递参数从嵌套存储过程中返回值从嵌套存储过程中返回和接收结果集如何递归调用存储过程过程调用的安全性考虑第2页，共20页。 被调用的过程：统计每个雇员所完成的项目CREATE PROCEDURE count_projects ( IN p_empno CHAR(6) , out p_total INT ) LANGUAGE SQL SPECIFIC count_projects cp: BEGIN - Procedure logic SELECT COUNT(

2、*) INTO p_total FROM emp_act WHERE empno = p_empno;END cp简单的存储过程嵌套的例子第3页，共20页。调用者：根据每个雇员完成的项目的情况，决定是否给发奖金CREATE PROCEDURE bonus ( IN p_empno CHAR(6) , out p_bonus CHAR(1) ) LANGUAGE SQL SPECIFIC bonus bn: BEGIN - Declare variables DECLARE v_min INT DEFAULT 5; DECLARE v_total INT DEFAULT 0; - Procedu

3、re logic CALL count_projects(p_empno, v_total); IF ( v_total = v_min ) THEN SET p_bonus = Y; ELSE SET p_bonus = N; END IF;END bn第4页，共20页。传递参数 在存储过程嵌套调用过程中，参数依照出现的次序进行传递，如果数据类型不匹配，请使用cast函数进行数据类型转换；从嵌套的过程中取得返回值除了通过输出参数获得返回值以外，存储过程可以通过return语句返回一个值，下面的例子演示如何通过get diagnostics语句获得被调用过程的返回值。 下面的过程get_emp

4、_name将基于雇员编号返回雇员的first name，当找到该雇员时返回99，否则返回1000。第5页，共20页。CREATE PROCEDURE get_emp_name ( IN p_empno CHAR(6) , out p_fname VARCHAR(10) ) LANGUAGE SQL SPECIFIC get_emp_name gen: BEGIN - Declare variables DECLARE v_return_code INT DEFAULT 99; - Declare condition handlers DECLARE CONTINUE HANDLER FOR N

5、OT FOUND SET v_return_code = 1000; - Procedure logic SELECT firstnme INTO p_fname FROM employee WHERE empno = p_empno; RETURN v_return_code;END gen第6页，共20页。CREATE PROCEDURE find_emp ( IN p_empno CHAR(6) , out p_output VARCHAR(50) ) LANGUAGE SQL SPECIFIC find_emp fe: BEGIN - Declare variables DECLARE

6、 v_rc INT; DECLARE v_fname VARCHAR(15); - Procedure logic CALL get_emp_name( p_empno, v_fname ); -(1) GET DIAGNOSTICS v_rc = RETURN_STATUS; -(2) IF ( v_rc = 99 ) THEN SET p_output = The employee is: | v_fname | .; ELSEIF ( v_rc = 1000 ) THEN SET p_output = The employee does not exist!; ELSE SET p_ou

7、tput = Something else went wrong.; END IF;END fe调用者过程find_emp通过get diagnostics取得被调用者的返回值，get diagnostics必须是紧跟在call语句之后TIP：通过返回值判断 执行状态第7页，共20页。从嵌套存储过程返回结果集 存储过程中的结果集可以返回给别的存储过程，也可以返回到客户端的应用程序。定义游标的语法如下： declare cursor with hold with return to caller client for stmt name返回结果集到客户端下面的过程将返回结果集到客户端，返回的结果

8、集是特定部门下的雇员的first name、last name和salary。第8页，共20页。CREATE PROCEDURE to_client ( IN p_dept CHAR(3) ) LANGUAGE SQL SPECIFIC to_client DYNAMIC RESULT SETS 1tc: BEGIN - Procedure logic DECLARE v_cur CURSOR WITH RETURN TO CLIENT FOR SELECT firstnme, lastname, salary FROM employee WHERE workdept = p_dept; OP

9、EN v_cur;END tc第9页，共20页。CREATE PROCEDURE base_proc ( ) LANGUAGE SQL SPECIFIC base_proc DYNAMIC RESULT SETS 1 bp: BEGIN - Declare variables DECLARE v_dept CHAR(3) DEFAULT A00; - Procedure logic CALL to_client(v_dept);END bp在执行过程base_proc之后，将返回下面的结果：FIRSTNME LASTNAMESALARYCHRISTINEHAAS52750.00VINCENZO

10、LUCCHESSI46500.00SEANOCONNELL29250.00第10页，共20页。如何返回结果集到调用者过程 将结果集返回到调用者过程需要用到associate locators语句和allocate cursor语句associate locators的语法： associate locator with procedure allocate cursor的语法： allocate cursor for result set 在下面的例子中，过程total_salary调用过程to_caller1，to_caller过程返回firstname、lastname和salary；调用

11、者过程total_salary在收到结果集之后，计算出部门的总的薪水。第11页，共20页。CREATE PROCEDURE to_caller1 ( IN p_dept CHAR(3) ) LANGUAGE SQL SPECIFIC to_caller1 DYNAMIC RESULT SETS 1tc1: BEGIN - Procedure logic DECLARE v_cur CURSOR WITH RETURN TO CALLER FOR SELECT firstnme, lastname, salary FROM employee WHERE workdept = p_dept; OP

12、EN v_cur;END tc1第12页，共20页。CREATE PROCEDURE total_salary ( IN p_dept CHAR(3) , out p_total DECIMAL(9,2) ) LANGUAGE SQL SPECIFIC total_salary ts: BEGIN DECLARE v_fname VARCHAR(12); DECLARE v_lname VARCHAR(15); DECLARE v_salary DECIMAL(9,2) DEFAULT 0.0; DECLARE v_rs RESULT_SET_LOCATOR VARYING; -(1) - D

13、eclare returncodes DECLARE SQLSTATE CHAR(5) DEFAULT 00000; CALL to_caller1(p_dept); -(2) ASSOCIATE RESULT SET LOCATOR (v_rs) WITH PROCEDURE to_caller1; -(3) ALLOCATE v_rsCur CURSOR FOR RESULT SET v_rs; -(4) SET p_total = 0; WHILE ( SQLSTATE = 00000 ) DO SET p_total = p_total + v_salary; FETCH FROM v

14、_rsCur INTO v_fname, v_lname, v_salary; END WHILE;END ts第13页，共20页。接收多个结果集:过程emp_multi将返回3个结果集CREATE PROCEDURE emp_multi ( IN p_dept CHAR(3) ) LANGUAGE SQL SPECIFIC emp_multi DYNAMIC RESULT SETS 3em: BEGIN - Procedure logic - Selects firstname DECLARE v_cur1 CURSOR WITH RETURN TO CALLER FOR SELECT fi

15、rstnme FROM employee WHERE workdept = p_dept; - Selects lastname DECLARE v_cur2 CURSOR WITH RETURN TO CALLER FOR SELECT lastname FROM employee WHERE workdept = p_dept; - Selects salary DECLARE v_cur3 CURSOR WITH RETURN TO CALLER FOR SELECT salary FROM employee WHERE workdept = p_dept; OPEN v_cur1; O

16、PEN v_cur2; OPEN v_cur3;END em第14页，共20页。CREATE PROCEDURE receive_multi ( in p_dept CHAR(3) , out p_names VARCHAR(100) , out p_total DECIMAL(9,2) ) LANGUAGE SQL SPECIFIC receive_multi rm: BEGIN - Declare variables DECLARE v_fname VARCHAR(12) DEFAULT ; DECLARE v_lname VARCHAR(15) DEFAULT ; DECLARE v_s

17、alary DECIMAL(9,2) DEFAULT 0.0; DECLARE v_rs1, v_rs2, v_rs3 RESULT_SET_LOCATOR VARYING; - Declare returncodes DECLARE SQLSTATE CHAR(5) DEFAULT 00000; - Procedure logic CALL emp_multi(p_dept); ASSOCIATE RESULT SET LOCATOR (v_rs1, v_rs2, v_rs3) WITH PROCEDURE emp_multi; ALLOCATE v_rsCur1 CURSOR FOR RE

18、SULT SET v_rs1; ALLOCATE v_rsCur2 CURSOR FOR RESULT SET v_rs2; ALLOCATE v_rsCur3 CURSOR FOR RESULT SET v_rs3;第15页，共20页。 SET p_names = The employees are:; WHILE (SQLSTATE = 00000) DO SET p_names = p_names | v_fname | | v_lname | ; FETCH FROM v_rsCur1 INTO v_fname; FETCH FROM v_rsCur2 INTO v_lname; EN

19、D WHILE; SET p_total = 0; WHILE ( SQLSTATE = 00000 ) DO SET p_total = p_total + v_salary; FETCH FROM v_rsCur3 INTO v_salary; END WHILE;END rm第16页，共20页。过程的嵌套总共可以嵌套16层，一个过程可以调用过程本身，即所谓的递归。下面的过程是一个使用递归调用的例子，它用来发现每个部门完整的报告链。CREATE PROCEDURE managers ( IN p_deptno CHAR(3) , out p_report_chain VARCHAR(100

20、) ) LANGUAGE SQL SPECIFIC managers mn: BEGIN - Declare variables DECLARE v_manager_name VARCHAR(15); DECLARE v_admrdept CHAR(3); DECLARE v_report_chain VARCHAR(100); DECLARE v_stmt VARCHAR(100) DEFAULT CALL managers(?,?); -(1) - Procedure logic SELECT admrdept -(2) INTO v_admrdept FROM department WHERE deptno=p_deptno;行(2)发现该部门的上层管理部门第17页，共20页。 SEL