[精选]db2数据库存储过程knc.pptx
《[精选]db2数据库存储过程knc.pptx》由会员分享,可在线阅读,更多相关《[精选]db2数据库存储过程knc.pptx(83页珍藏版)》请在得力文库 - 分享文档赚钱的网站上搜索。
1、-1-中国移动通信集团河南有限公司业务支援中中国移动通信集团河南有限公司业务支援中心心DB2DB2数据库中的存储过程数据库中的存储过程-2-主要内容:主要内容:1 1、存储过程介绍、存储过程介绍2 2、存储过程基础结构、存储过程基础结构3 3、控制语句、控制语句4 4、游标和结果集、游标和结果集5 5、异常处理器、异常处理器6 6、编写和调试存储过程、编写和调试存储过程-3-什么是存储过程?什么是存储过程?(Stored Procedure)(Stored Procedure)受受 DB2 DB2 服务器控制的一段可执行程序服务器控制的一段可执行程序可以通过可以通过SQLSQL的的CALLCA
2、LL语句来完成对存储过程的调用语句来完成对存储过程的调用在存储过程中可以包含业务逻辑在存储过程中可以包含业务逻辑存储过程可以在本地或远程进行调用存储过程可以在本地或远程进行调用存储过程可以接收或传递参数,生成结果集存储过程可以接收或传递参数,生成结果集-4-什么时候使用存储过程?什么时候使用存储过程?使用存储过程的合适时机使用存储过程的合适时机:应用程序的应用程序的性能性能无法满足预期时无法满足预期时客户端数量较多且应用程序中客户端数量较多且应用程序中SQLSQL代码分散时代码分散时应用程序需要进行应用程序需要进行繁重繁重的数据库操作,同时这些操作并不的数据库操作,同时这些操作并不需要进行太多
3、的客户交互需要进行太多的客户交互应用程序代码更改频繁应用程序代码更改频繁需要对客户应用代码进行访问控制时需要对客户应用代码进行访问控制时客户应用需要在一次操作中执行多条客户应用需要在一次操作中执行多条 SQL SQL 语句语句-5-C/S:C/S:宏观交互图宏观交互图-6-主要内容:主要内容:1 1、存储过程介绍、存储过程介绍2 2、存储过程基础结构、存储过程基础结构3 3、控制语句、控制语句4 4、游标和结果集、游标和结果集5 5、异常处理器、异常处理器6 6、编写和调试存储过程、编写和调试存储过程-7-存储过程结构存储过程结构CREATE OR REPLACE PROCEDURE CREA
4、TE OR REPLACE PROCEDURE (IN|OUT|INOUT (IN|OUT|INOUT 参数名参数名 数据类型数据类型 默认值默认值 )LANGUAGE SQLLANGUAGE SQLBEGINBEGIN 业务逻辑代码业务逻辑代码END;END;-8-参数类型参数类型ININ(输入参数)(输入参数)只是将实参传递给存储过程,但在存储过程中不能对其进行修改。换句话说,只是将实参传递给存储过程,但在存储过程中不能对其进行修改。换句话说,对于存储过程而言它是只读的。对于存储过程而言它是只读的。OUTOUT(输出参数)(输出参数)在存储过程结束时向调用者返回。一般在过程中都会被赋值。在
5、存储过程结束时向调用者返回。一般在过程中都会被赋值。INOUTINOUT(输入输出参数)(输入输出参数)上述两种参数类型的结合体。它可以帮助调用者将实参传递给进程,另外它上述两种参数类型的结合体。它可以帮助调用者将实参传递给进程,另外它也能够作为输出参数被修改和赋值。也能够作为输出参数被修改和赋值。-9-复合语句复合语句 复合语句是指包含在复合语句是指包含在BEGINBEGIN和和ENDEND间的语句。它一间的语句。它一般包括如下语句类型:般包括如下语句类型:声明语句声明语句赋值语句赋值语句控制语句控制语句条件处理语句条件处理语句-10-复合语句示例复合语句示例说明:说明:1.1.复合语句可以
6、嵌套使用。复合语句可以嵌套使用。2.BEGIN2.BEGIN语句可以和标签组合使用,这样可以更清晰的标识语句块的范围。语句可以和标签组合使用,这样可以更清晰的标识语句块的范围。-11-声明语句声明语句变量声明变量声明DECLARE my_var INTEGER DEFAULT 6;DECLARE my_var INTEGER DEFAULT 6;条件声明条件声明DECLARE not_found CONDITION FOR SQLSTATE 02000;DECLARE not_found CONDITION FOR SQLSTATE 02000;游标声明游标声明DECLARE c1 CURSO
7、R FOR select *from staff;DECLARE c1 CURSOR FOR select *from staff;异常处理器声明异常处理器声明DECLARE EXIT HANDLER FOR SQLEXCEPTION ;DECLARE EXIT HANDLER FOR SQLEXCEPTION ;-12-赋值语句赋值语句语法语法SET lv_name =expression;SET lv_name =expression;SET lv_name =NULL;SET lv_name =NULL;示例示例(1)SET salary =salary +salary *0.1;(1)
8、SET salary =salary +salary *0.1;(2)SET init_salary =NULL;(2)SET init_salary =NULL;(3)SET salary =(select salary from employee where empno =lv_emp_num);(3)SET salary =(select salary from employee where empno =lv_emp_num);注注:如果如果 SELECT SELECT 语句返回记录超过一行,示例语句返回记录超过一行,示例 3 3 将会返回将会返回SQLERRORSQLERROR。-1
9、3-存储过程例子存储过程例子-14-嵌套存储过程例子嵌套存储过程例子-15-模块模块(Module)(Module)模块是如下几种对象的集合:模块是如下几种对象的集合:SPSP,UDFUDF,global variables and cursorsglobal variables and cursors,typestypes,conditionsconditions模块的主要优势:模块的主要优势:结构良好,便于组织结构良好,便于组织范围限定范围限定CALL mySchema.myModule.myProc()CALL mySchema.myModule.myProc()信息隐藏信息隐藏每个对象
10、都可以是每个对象都可以是 public public 或或 privateprivate权限控制权限控制可以模块为单位,而不是以模块中的对象为单位来控制权限可以模块为单位,而不是以模块中的对象为单位来控制权限-16-模块模块-规格说明规格说明(Module Specification)(Module Specification)模块可以发布模块可以发布type,SP,UDFtype,SP,UDF以供外部使用。以供外部使用。CREATE OR REPLACE MODULE myMod;CREATE OR REPLACE MODULE myMod;ALTER MODULE myMod PUBLIS
11、HALTER MODULE myMod PUBLISH TYPE myRowTyp AS ANCHOR ROW myTab;TYPE myRowTyp AS ANCHOR ROW myTab;ALTER MODULE myMod PUBLISHALTER MODULE myMod PUBLISH FUNCTION myFunc(val1 ANCHOR myTab.col1)FUNCTION myFunc(val1 ANCHOR myTab.col1)RETURNS myRowTyp;RETURNS myRowTyp;ALTER MODULE myMod PUBLISHALTER MODULE
12、myMod PUBLISH PROCEDURE myProc(OUT param1 ANCHOR myTab.col2);PROCEDURE myProc(OUT param1 ANCHOR myTab.col2);-17-模块模块-实现实现(Module Implementation)(Module Implementation)下面的代码是模块的实现部分:下面的代码是模块的实现部分:ALTERALTER MODULEMODULE myModmyMod ADD VARIABLEADD VARIABLE pkgVarpkgVar ANCHORANCHOR myTab.col1;myTab.co
13、l1;ALTERALTER MODULEMODULE myModmyMod ADDADD FUNCTION FUNCTION myFunc(val1myFunc(val1 ANCHORANCHOR myTab.col1)myTab.col1)RETURNSRETURNS myRowTypmyRowTypBEGINBEGIN DECLARE DECLARE var1var1 myRowTyp;myRowTyp;SELECT SELECT *INTOINTO var1 var1 FROMFROM myTab myTab WHEREWHERE col1col1 col1 pkgVar;pkgVar;
14、RETURNRETURN var1;var1;ENDENDALTERALTER MODULEMODULE myModmyMod ADDADD PROCEDURE PROCEDURE myProc(myProc(OUTOUT param1param1 ANCHORANCHOR myTab.col2)myTab.col2)BEGINBEGIN DECLARE DECLARE varRowvarRow myRowTyp;myRowTyp;SETSET param1param1 =varRow.col2varRow.col2 pkgVar;pkgVar;ENDEND-18-模块模块-其他语句其他语句删
15、除整个模块删除整个模块DROP MODULE myMod;DROP MODULE myMod;保留规格说明内容,删除实现保留规格说明内容,删除实现ALTER MODULE myMod DROP BODY;ALTER MODULE myMod DROP BODY;删除模块中的存储过程删除模块中的存储过程(SP)(SP)ALTER MODULE myMod DROP PROCEDURE myProc;ALTER MODULE myMod DROP PROCEDURE myProc;将模块的执行权限赋给将模块的执行权限赋给joejoeGRANT EXECUTE ON MODULE myMod TO
16、joe;GRANT EXECUTE ON MODULE myMod TO joe;-19-主要内容:主要内容:1 1、存储过程介绍、存储过程介绍2 2、存储过程基础结构、存储过程基础结构3 3、控制语句、控制语句4 4、游标和结果集、游标和结果集5 5、异常处理器、异常处理器6 6、编写和调试存储过程、编写和调试存储过程-20-IF IF语句语句格式:格式:IF IF 条件1 THENTHEN statement1;statement1;ELSEIFELSEIF 条件2 THENTHEN statement2;statement2;ELSEELSE statement3;statement3;
17、END END IF;IF;注注:条件成立时为条件成立时为TRUE(TRUE(真真),),不成立时为不成立时为FALSE(FALSE(假假)和和 NULLNULL-21-IF IF语句例子语句例子IF IF ratingrating =1 1 THENTHENUPDATEUPDATE EMPLOYEE SETEMPLOYEE SET salarysalary =salary*1.10salary*1.10 WHEREWHERE empnoempno =i_num;i_num;(如果满足于(如果满足于.时,薪水调整时,薪水调整1.11.1倍)倍)ELSEIFELSEIF ratingrating
18、 =2 2 THENTHENUPDATEUPDATE EMPLOYEE SETEMPLOYEE SET salarysalary =salary*1.05salary*1.05 WHEREWHERE empnoempno =i_num;i_num;ELSEELSEUPDATEUPDATE EMPLOYEE SETEMPLOYEE SET salarysalary =salary*1.03salary*1.03 WHEREWHERE empnoempno =i_num;i_num;ENDEND IF;IF;-22-CASECASE语句(语句(1 of 21 of 2)简单简单CASECASE语句
19、语句-23-CASECASE语句(语句(2 of 22 of 2)稍加变形的稍加变形的CASECASE语句语句-24-LOOPLOOP语句语句语法语法LABEL LOOPLABEL LOOP SQL-procedure-statements;SQL-procedure-statements;END END LOOP LABEL;LOOP LABEL;示例示例fetch_loop:LOOPfetch_loop:LOOPFETCH c1 INTO v_firstname,v_lastname;c1 INTO v_firstname,v_lastname;SET counter =counter +
20、1;SET counter =counter +1;IF counter =51 THEN IF counter =51 THEN LEAVE fetch_loop;LEAVE fetch_loop;END IF;END IF;ENDEND LOOP fetch_loop;LOOP fetch_loop;标签关关键字字-25-FORFOR语句语句语法语法LABEL FOR for-loop-name AS cursor-name CURSOR FORLABEL FOR for-loop-name AS cursor-name CURSOR FOR select-statement select
21、-statement DODO SQL-procedure-statements;SQL-procedure-statements;END END FOR LABEL;LABEL;示例示例DECLARE fullname CHAR(40);DECLARE fullname CHAR(40);FOR v1 AS c1 CURSOR FOR FOR v1 AS c1 CURSOR FOR SELECT firstnme,midinit,lastname FROM employeeSELECT firstnme,midinit,lastname FROM employeeDODO SET fulln
22、ame=lastname|,|firstnme|,|midinit;SET fullname=lastname|,|firstnme|,|midinit;INSERT INTO tname VALUE (fullname);INSERT INTO tname VALUE (fullname);END FOR;END FOR;-26-其他控制语句其他控制语句REPEATREPEAT语句语句ftch_loop2:ftch_loop2:REPEATREPEAT FETCH c1 INTO v_firstname,v_midinit,v_lastname;FETCH c1 INTO v_firstna
23、me,v_midinit,v_lastname;UNTIL SQLCODE 0 AND REPEAT ftch_loop2;UNTIL SQLCODE 0 AND REPEAT ftch_loop2;WHILEWHILE语句语句WHILE at_end =0 DOWHILE at_end =0 DO FETCH c1 INTO v_firstname,v_midinit,v_lastname;FETCH c1 INTO v_firstname,v_midinit,v_lastname;IF SQLCODE =100 THEN IF SQLCODE =100 THEN SET at_end =1
24、;SET at_end =1;END IF;END IF;END WHILE;END WHILE;-27-LEAVELEAVE和和ITERATEITERATE语句语句LEAVELEAVE和和ITERATEITERATE语句来控制循环语句来控制循环LEAVELEAVE语句用来跳出循环语句用来跳出循环ITERATEITERATE语句用来回到语句用来回到forfor或者或者whilewhile循环的开始重新执行循环的开始重新执行示例示例FETCH_LOOP1:LOOPFETCH_LOOP1:LOOP FETCH c1 INTO v_dept,v_deptname,v_admdept;FETCH c1
25、 INTO v_dept,v_deptname,v_admdept;IF at_end =1 THEN IF at_end =1 THEN LEAVE FETCH_LOOP1;LEAVE FETCH_LOOP1;ELSEIF v_dept =D01 THEN ELSEIF v_dept =D01 THEN ITERATE FETCH_LOOP1;ITERATE FETCH_LOOP1;END IF;END IF;INSERT INTO department(deptno,deptname,admdept)INSERT INTO department(deptno,deptname,admdep
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 精选 db2 数据库 存储 过程 knc
限制150内