DB2培训文档(共20张).pptx
IBM软件部 2007 IBM 公司嵌套存储过程陆川陆川021-63262288-2720021-63262288-2720IBM 软件部 | DB2 信息管理软件 2006 IBM公司你将了解下列内容：什么是嵌套存储过程如何在嵌套存储过程间传递参数从嵌套存储过程中返回值从嵌套存储过程中返回和接收结果集如何递归调用存储过程过程调用的安全性考虑IBM 软件部 | DB2 信息管理软件 2006 IBM公司 被调用的过程：统计每个雇员所完成的项目CREATE PROCEDURE count_projects ( IN p_empno CHAR(6) , out p_total INT ) LANGUAGE SQL SPECIFIC count_projects cp: BEGIN - Procedure logic SELECT COUNT(*) INTO p_total FROM emp_act WHERE empno = p_empno;END cp简单的存储过程嵌套的例子IBM 软件部 | DB2 信息管理软件 2006 IBM公司调用者：根据每个雇员完成的项目的情况，决定是否给发奖金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; - Procedure 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 bnIBM 软件部 | DB2 信息管理软件 2006 IBM公司传递参数 在存储过程嵌套调用过程中，参数依照出现的次序进行传递，如果数据类型不匹配，请使用cast函数进行数据类型转换；从嵌套的过程中取得返回值除了通过输出参数获得返回值以外，存储过程可以通过return语句返回一个值，下面的例子演示如何通过get diagnostics语句获得被调用过程的返回值。 下面的过程get_emp_name将基于雇员编号返回雇员的first name，当找到该雇员时返回99，否则返回1000。IBM 软件部 | DB2 信息管理软件 2006 IBM公司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 NOT FOUND SET v_return_code = 1000; - Procedure logic SELECT firstnme INTO p_fname FROM employee WHERE empno = p_empno; RETURN v_return_code;END genIBM 软件部 | DB2 信息管理软件 2006 IBM公司CREATE PROCEDURE find_emp ( IN p_empno CHAR(6) , out p_output VARCHAR(50) ) LANGUAGE SQL SPECIFIC find_emp fe: BEGIN - Declare variables DECLARE 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_output = Something else went wrong.; END IF;END fe调用者过程find_emp通过get diagnostics取得被调用者的返回值，get diagnostics必须是紧跟在call语句之后TIP：通过返回值判断 执行状态IBM 软件部 | DB2 信息管理软件 2006 IBM公司从嵌套存储过程返回结果集 存储过程中的结果集可以返回给别的存储过程，也可以返回到客户端的应用程序。定义游标的语法如下： declare cursor with hold with return to caller client for stmt name返回结果集到客户端下面的过程将返回结果集到客户端，返回的结果集是特定部门下的雇员的first name、last name和salary。IBM 软件部 | DB2 信息管理软件 2006 IBM公司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; OPEN v_cur;END tcIBM 软件部 | DB2 信息管理软件 2006 IBM公司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.00VINCENZOLUCCHESSI46500.00SEANOCONNELL29250.00IBM 软件部 | DB2 信息管理软件 2006 IBM公司p如何返回结果集到调用者过程 将结果集返回到调用者过程需要用到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；调用者过程total_salary在收到结果集之后，计算出部门的总的薪水。IBM 软件部 | DB2 信息管理软件 2006 IBM公司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; OPEN v_cur;END tc1IBM 软件部 | DB2 信息管理软件 2006 IBM公司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) - Declare 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_rsCur INTO v_fname, v_lname, v_salary; END WHILE;END tsIBM 软件部 | DB2 信息管理软件 2006 IBM公司p接收多个结果集:过程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 firstnme 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; OPEN v_cur2; OPEN v_cur3;END emIBM 软件部 | DB2 信息管理软件 2006 IBM公司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_salary 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 RESULT SET v_rs1; ALLOCATE v_rsCur2 CURSOR FOR RESULT SET v_rs2; ALLOCATE v_rsCur3 CURSOR FOR RESULT SET v_rs3;IBM 软件部 | DB2 信息管理软件 2006 IBM公司 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; END 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 rmIBM 软件部 | DB2 信息管理软件 2006 IBM公司过程的嵌套总共可以嵌套16层，一个过程可以调用过程本身，即所谓的递归。下面的过程是一个使用递归调用的例子，它用来发现每个部门完整的报告链。CREATE PROCEDURE managers ( IN p_deptno CHAR(3) , out p_report_chain VARCHAR(100) ) 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)发现该部门的上层管理部门IBM 软件部 | DB2 信息管理软件 2006 IBM公司 SELECT lastname -(3) INTO v_manager_name FROM employee, department WHERE empno=mgrno AND deptno=p_deptno; IF (v_manager_name IS NULL) THEN SET v_manager_name = VACANT; END IF; IF ( v_admrdept IS NULL ) THEN SET p_report_chain = Department | p_deptno | not found.; -(4) ELSEIF ( v_admrdept = p_deptno ) THEN -(5) SET p_report_chain = v_manager_name; ELSE PREPARE v_st from v_stmt; -(6) EXECUTE v_st INTO v_report_chain USING v_admrdept; -(7) SET p_report_chain = v_manager_name | - | v_report_chain; END IF;END mn行(7)中发现更高层的管理部门IBM 软件部 | DB2 信息管理软件 2006 IBM公司安全性考虑： 如果你创建的过程中指定了reads sql data子句，那么你只能调用创建时包含contains sql或reads sql data子句的过程，不能调用包含modifies sql data的过程。IBM 软件部 | DB2 信息管理软件 2006 IBM公司演讲完毕，谢谢观看！
