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頁。調用者：根據每個雇員完成的項目的情況，決定是否給發(fā)獎金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：通過返回值判斷 執(zhí)行狀態(tài)第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在執(zhí)行過程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層，一個過程可以調用過程本身，即所謂的遞歸。下面的過程是一個使用遞歸調用的例子，它用來發(fā)現每個部門完整的報告鏈。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)發(fā)現該部門的上層管理部門第17頁，共20頁。 SEL