MYSQL存储过程技术.ppt

MYSQL存储过程技术 存储过程的概念为什么使用存储过程基本创建 删除语法存储过程中的变量BEGIN END复合语句流程控制结构语句注释语法使用权限条件和异常处理程序游标 MYSQL存储过程技术 存储过程的概念 所谓的存储过程就是存储在数据库当中的可以执行特定工作 查询和更新 的一组SQL代码的程序段 与自定义函数的区别 自定义函数有且只有一个返回值 就像普通的函数一样 可以直接在表达式中嵌入调用 存储过程可以没有返回值 也可以有任意个输出参数 必须单独调用 执行的本质都一样 只是函数有如只能返回一个变量的限制 而存储过程可以返回多个 而函数是可以嵌入在sql中使用的 可以在select中调用 而存储过程不行 函数限制比较多 比如不能用临时表 只能用表变量 还有一些函数都不可用等等 而存储过程的限制相对就比较少 一般来说 存储过程实现的功能要复杂一点 而函数的实现的功能针对性比较强 对于存储过程来说可以返回参数 而函数只能返回值或者表对象 存储过程一般是作为一个独立的部分来执行 而函数可以作为查询语句的一个部分来调用 由于函数可以返回一个表对象 因此它可以在查询语句中位于FROM关键字的后面 MYSQL存储过程技术 为什么使用存储过程 存储过程的优点 存储过程只在创造时进行编译 以后每次执行存储过程都不需再重新编译 而一般SQL语句每执行一次就编译一次 所以使用存储过程可提高数据库执行速度 当对数据库进行复杂操作时 如对多个表进行Update Insert Query Delete时 可将此复杂操作用存储过程封装起来与数据库提供的事务处理结合一起使用 存储过程可以重复使用 可减少数据库开发人员的工作量 安全性高 可设定只有某些用户才具有对指定存储过程的使用权 MYSQL存储过程技术 基本的创建 删除语法 CREATEPROCEDUREsp name proc parameter characteristic routine body默认地 子程序与当前数据库关联 要明确地把子程序与一个给定数据库关联起来 可以在创建子程序的时候指定其名字为db name sp name sp name存储过程的名字proc parameter指定参数为IN OUT 或INOUTcharacteristic特征routine body包含合法的SQL过程语句 DROP PROCEDURE FUNCTION IFEXISTS sp name这个语句被用来移除一个存储程序或函数 即 从服务器移除一个制定的子程序 在MySQL5 1中 你必须有ALTERROUTINE权限才可用此子程序 这个权限被自动授予子程序的创建者 IFEXISTS子句是一个MySQL的扩展 如果程序或函数不存在 它防止发生错误 MYSQL存储过程技术 基本的创建 调用 删除语法 delimiter DROPPROCEDUREIFEXISTStest CREATEPROCEDUREtest 存储过程名 INinparmsINT OUToutparamsvarchar 32 输入参数 BEGIN 语句块头 DECLAREvarCHAR 10 变量声明 IFinparms 1THEN IF条件开始 SETvar hello 赋值 ELSESETvar world ENDIF IF结束 INSERTINTOt1VALUES var SQL语句 SELECTnameFROMt1LIMIT1INTOoutparams END delimiter calltest 1 out MYSQL存储过程技术 存储过程的变量 声明变量 DECLAREvar name type DEFAULTvalue 这个语句被用来声明局部变量 要给变量提供一个默认值 需要包含一个DEFAULT子句 值可以被指定为一个表达式 不需要为一个常数 如果没有DEFAULT子句 初始值为NULL 局部变量的作用范围在它被声明的BEGIN END块内 它可以被用在嵌套的块中 除了那些用相同名字声明变量的块 变量赋值 SET语句 SETvar name expr var name expr 也可以用语句代替SET来为用户变量分配一个值 在这种情况下 分配符必须为 而不能用 因为在非SET语句中 被视为一个比较操作符 如下所示 mysql SET t1 0 t2 0 t3 0 mysql SELECT t1 0 t2 0 t3 0 对于使用select语句为变量赋值的情况 若返回结果为空 即没有记录 此时变量的值为上一次变量赋值时的值 如果没有对变量赋过值 则为NULL 变量赋值 SELECT INTO语句SELECTcol name INTOvar name table expr这个SELECT语法把选定的列直接存储到变量 因此 只有单一的行可以被取回 SELECTid dataINTOx yFROMtest t1LIMIT1 MYSQL存储过程技术 BEGIN END复合语句 begin label BEGIN statement list END end label 存储子程序可以使用BEGIN END复合语句来包含多个语句 statement list代表一个或多个语句的列表 statement list之内每个语句都必须用分号 来结尾 复合语句可以被标记 除非begin label存在 否则end label不能被给出 并且如果二者都存在 他们必须是同样的 使用多重语句需要客户端能发送包含语句定界符 的查询字符串 这个符号在命令行客户端被用delimiter命令来处理 改变查询结尾定界符 比如改变为 使得 可被用在子程序体中 MYSQL存储过程技术 流程控制结构语句 IF语句IFsearch conditionTHENstatement list ELSEIFsearch conditionTHENstatement list ELSEstatement list ENDIFIF实现了一个基本的条件构造 如果search condition求值为真 相应的SQL语句列表被执行 如果没有search condition匹配 在ELSE子句里的语句列表被执行 statement list可以包括一个或多个语句 举例 DELIMITER CREATEPROCEDUREp1 INparameter1INT BEGINDECLAREvariable1INT SETvariable1 parameter1 1 IFvariable1 0THENINSERTINTOtVALUES 17 ENDIF IFparameter1 0THENUPDATEtSETs1 s1 1 ELSEUPDATEtSETs1 s1 2 ENDIF END DELIMITER MYSQL存储过程技术 流程控制结构语句 CASE语句CASEcase valueWHENwhen valueTHENstatement list WHENwhen valueTHENstatement list ELSEstatement list ENDCASEOr CASEWHENsearch conditionTHENstatement list WHENsearch conditionTHENstatement list ELSEstatement list ENDCASE存储程序的CASE语句实现一个复杂的条件构造 如果search condition求值为真 相应的SQL被执行 如果没有搜索条件匹配 在ELSE子句里的语句被执行 举例 CREATEPROCEDUREp2 INparameter1INT BEGINDECLAREvariable1INT SETvariable1 parameter1 1 CASEvariable1WHEN0THENINSERTINTOtVALUES 17 WHEN1THENINSERTINTOtVALUES 18 ELSEINSERTINTOtVALUES 19 ENDCASE END MYSQL存储过程技术 流程控制结构语句 循环语句WHILE ENDWHILELOOP ENDLOOPREPEAT ENDREPEATGOTO前三种是标准的循环方式 至于GOTO就如C语言里的GOTO一样 尽量少用 在循环中还穿插一些循环控制语句 如LEAVE 类似C语言的break ITERATE 类似C语言的continue 等 LEAVE语句LEAVElabel这个语句被用来退出任何被标注的流程控制构造 它和BEGIN END或循环一起被使用 ITERATE语句ITERATElabelITERATE只可以出现在LOOP REPEAT 和WHILE语句内 ITERATE意思为 再次循环 MYSQL存储过程技术 流程控制结构语句 循环语句WHILE ENDWHILE举例 CREATEPROCEDUREp4 BEGINDECLAREvINT SETv 0 WHILEv 5DOINSERTINTOtVALUES v SETv v 1 ENDWHILE END MYSQL存储过程技术 流程控制结构语句 循环语句LOOP ENDLOOP举例 CREATEPROCEDUREp5 BEGINDECLAREvINT SETv 0 loop label LOOPINSERTINTOtVALUES v SETv v 1 IFv 5THENLEAVEloop label ENDIF ENDLOOP END begin label LOOPstatement listENDLOOP end label LOOP允许某特定语句或语句群的重复执行 实现一个简单的循环构造 在循环内的语句一直重复直到循环被退出 退出通常伴随着一个LEAVE语句 MYSQL存储过程技术 流程控制结构语句 循环语句REPEAT ENDREPEAT举例 CREATEPROCEDUREp6 BEGINDECLAREvINT SETv 0 REPEATINSERTINTOtVALUES v SETv v 1 UNTILv 5ENDREPEAT END 功能与WHILE差不多 差别是在执行一次后检查 而WHILE是在开始时检查 累死DO WHILE功能 MYSQL存储过程技术 流程控制结构语句 补充 迭代 ITERATE 语句CREATEPROCEDUREp7 BEGINDECLAREvINT SETv 0 loop label LOOPIFv 3THENSETv v 1 ITERATEloop label ENDIF INSERTINTOtVALUES v SETv v 1 IFv 5THENLEAVEloop label ENDIF ENDLOOP END MYSQL存储过程技术 注释语法 mysql存储过程可使用两种风格的注释双模杠 该风格一般用于单行注释c风格 注释内容 一般用于多行注释使用权限 关于存储过程的权限管理 一种是SQLSECURITYINVOKER 一种是SQLSECURITYDEFINER 也就是一种是调用者权限 一种是定义者权限 如果使用第一种 那么执行的时候是以执行者本身的权限来操作存储过程中包含的表 如果是第二种 那么执行的时候 是以该存储过程的定义者权限来操作 MYSQL存储过程技术 条件和异常处理程序 DECLAREhandler typeHANDLERFORcondition value sp statementhandler type CONTINUE EXITcondition value SQLSTATE VALUE sqlstate value condition name SQLWARNING NOTFOUND SQLEXCEPTION这个语句指定每个可以处理一个或多个条件的处理程序 如果产生一个或多个条件 指定的语句被执行 对一个CONTINUE处理程序 当前子程序的执行在执行处理程序语句之后继续 对于EXIT处理程序 当前BEGIN END复合语句的执行被终止 UNDO处理程序类型语句还不被支持 SQLWARNING是对所有以01开头的SQLSTATE代码的速记 NOTFOUND是对所有以02开头的SQLSTATE代码的速记 SQLEXCEPTION是对所有没有被SQLWARNING或NOTFOUND捕获的SQLSTATE代码的速记 声明自定义条件 DECLAREcondition nameCONDITIONFORcondition valuecondition value SQLSTATE VALUE sqlstate value MYSQL存储过程技术 条件和异常处理程序 举例 CREATETABLEtest t s1int primarykey s1 delimiter CREATEPROCEDUREhandlerdemo BEGINDECLARECONTINUEHANDLERFORSQLSTATE 23000 SET x2 1 SET x 1 INSERTINTOtest tVALUES 1 SET x 2 INSERTINTOtest tVALUES 1 SET x 3 END delimiter MYSQL存储过程技术 游标 声明游标DECLAREcursor nameCURSORFORselect statement这个语句声明一个光标 也可以在子程序中定义多个光标 但是一个块中的每一个光标必须有唯一的名字 打开游标OPENcursor name这个语句打开先前声明的光标 游标FETCHFETCHcursor nameINTOvar name var name 这个语句用指定的打开光标读取下一行 如果有下一行的话 并且前进光标指针 关闭游标CLOSECLOSEcursor name这个语句关闭先前打开的光标 如果未被明确地关闭 光标在它被声明的复合语句的末尾被关闭 游标的特性 READONLY只读 只能取值而不能赋值 NOTSCROOLABLE不可回滚 只能顺序读取 ASENSITIVE敏感 不能在已经打开游标的表上执行update事务 MYSQL存储过程技术 游标 举例 CREATEPROCEDUREcurdemo BEGINDECLAREdoneINTDEFAULT0 DECLAREaCHAR 16 DECLAREb cINT DECLAREcur1CURSORFORSELECTid dataFROMtest t1 DECLAREcur2CURSORFORSELECTiFROMtest t2 DECLARECONTINUEHANDLERFORSQLSTATE 02000 SETdone 1 OPENcur1 OPENcur2 REPEATFETCHcur1INTOa b FETCHcur2INTOc IFNOTdoneTHENIFb cTHENINSERTINTOtest t3VALUES a b ELSEINSERTINTOtest t3VALUES a c ENDIF ENDIF UNTILdoneENDREPEAT CLOSEcur1 CLOSEcur2 END 谢谢