数据库高级应用技术03-(存储过程).ppt

1 高等职业技术院校教材 数据库高级应用技术 主编 温立辉wenlihui2004 2 存储过程 本单元教学目标了解存储过程的种类认识 了解存储过程的作用掌握存储过程的开发语法熟练使用数据库IDE开发 调试存储过程 3 存储过程概念 一 SQL语句 SQL语句在执行的时候需要先编译 然后执行存储过程 存储过程 StoredProcedure 是一组为了完成特定功能的SQL语句集经编译后存储在数据库中用户通过指定存储过程的名字与参数 如果该存储过程带有参数 来调用执行它 4 存储过程概念 二 一个存储过程是一个可编程的函数它在数据库中创建并保存它由SQL语句和一些特殊的控制结构组成存储过程适用于以下场合在不同的平台 应用程序 上执行相同的函数封装特定功能是数据库编程中面向对象方法的模拟 5 存储过程优点 一 1 存储过程增强了SQL语言的功能和灵活性存储过程可以用流控制语句编写 有很强的灵活性可以完成复杂的判断和较复杂的运算 2 存储过程是允许编程的标准组件被创建后 可以在程序中被多次调用 而不必重新编写该存储过程的SQL语句数据库专业人员可以随时对存储过程进行修改 对应用程序源代码毫无影响 3 存储过程能实现较快的执行速度如果某一操作包含大量的SQL代码或分别被多次执行 存储过程比批处理执行速度快很多 因存储过程是预编译的批处理的SQL语句在每次运行时要进行编译 速度相对慢 6 存储过程优点 二 4 存储过程能过减少网络流量 当同一操作所涉及的众多SQL语句被编码到存储过程中在客户机上调用该存储过程时 网络中只需传送该调用语句从而大大减少了网络流量并降低了网络负载 5 存储过程被作为一种安全机制来充分利用系统管理员通过执行某一存储过程的权限进行限制能够实现对相应的数据的访问权限的限制 避免了非授权用户对数据的访问 从而保证了数据的安全 7 存储过程的创建 MySQL格式 CREATEPROCEDURE存储过程名 过程参数 BEGIN 过程控制语句 Transaction SQL END 8 存储过程案例 创建User表 CREATEDATABASEIFNOTEXISTSmydb USEmydb DROPTABLEIFEXISTS user CREATETABLE user user id int 10 unsignedNOTNULLauto increment user name varchar 45 NOTNULL pass word varchar 45 NOTNULL email varchar 45 NOTNULL phone varchar 45 NOTNULL sex char 1 NOTNULL score int 10 unsignedNOTNULL PRIMARYKEY user id ENGINE InnoDBAUTO INCREMENT 11DEFAULTCHARSET utf8 INSERTINTO user user id user name pass word email phone sex score VALUES 1 LiMing LiMing LiMing 83278904 0 60 2 ZhuangPing ZhuangPing ZhuangPing 83278678 0 70 3 LuMei LuMei LuMei 83278904 1 80 4 QiaoBing QiaoBing QiaoBing 83278452 1 70 5 Kerry Kerry Kerry 83278678 1 50 6 Jetty Jetty Jetty 83278904 0 90 7 Lucy Lucy Lucy 83278904 0 40 8 Honey Honey Honey 83278904 1 80 9 Wendy Wendy Wendy 83278452 0 50 10 Rose Rose Rose 83278904 0 30 9 存储过程案例 开发编码 DELIMITER CREATEPROCEDUREproc1 insint BEGINupdateusersetscore swhereuser idin 2 4 6 8 10 END DELIMITER 10 案例语句解释 DELIMITER 表示把MySQL分隔符修改为 MySQL默认以 为分隔符如果没有声明分割符 编译器会把存储过程中的 当成SQL语句结束分隔符进行处理 则存储过程的编译过程会报错 所以要事先用DELIMITER关键字声明当前段分隔符 这样MySQL才会将 当做存储过程中的代码 而不是语句MySQL中语句的结束分隔符DELIMITER 用完了之后要把分隔符还原为 存储过程根据需要可能会有输入 输出 输入输出参数本案例中有一个输入参数s 类型是int型如果有多个参数用 分割开过程体的开始与结束使用BEGIN与END进行标识 11 存储过程调用 调用格式如下 call 存储过程名 参数callproc1 100 调用后看到user表中user id为 2 4 6 8 10的记录的score字段值变为了100删除存储过程格式 drop PROCEDURE 存储过程名dropPROCEDUREproc1 12 存储过程参数 存储过程共有三种参数类型 IN OUT INOUT 形式如 CREATEPROCEDURE IN OUT INOUT 参数名数据类形 IN输入参数 表示该参数值必须在调用存储过程时指定 在存储过程中修改该参数值将不被返回 为默认值OUT输出参数 该值可在存储过程内部被改变 并可返回INOUT输入输出参数 调用时指定 并且可被改变和返回 13 IN参数例子 DELIMITER CREATEPROCEDUREdemo in parameter INp idint INp score addint BEGINupdateusersetscore score p score add whereuser id p id END DELIMITER 14 存储过程调用 IN参数 直接调用 CALLdemo in parameter 10 5000 通过预定义参数变量调用 先定义两个参数变量set p id 10 set p score add 5000 再把变量作为参数调用CALLdemo in parameter p id p score add 调用后可看到user id为10的score字段的值增加了5000 15 OUT参数例子 DELIMITER CREATEPROCEDUREdemo out parameter OUTp outint BEGINSELECTCOUNT INTOp outFROMuser END DELIMITER 16 存储过程调用 OUT参数 通过预定义输出参数变量调用 先定义输出参数变量set p out 0 再把变量作为参数调用CALLdemo out parameter p out 可以看到 调用前p out的变量值为0调用后p out的变量值为10 17 INOUT参数例子 DELIMITER CREATEPROCEDUREdemo inout parameter INOUTp inout numint INOUTp inout strvarchar 50 BEGINDECLAREidintdefault0 SETid p inout num UPDATEuserSETemail p inout strWHEREuser id id SELECTscoreINTOp inout numFROMuserWHEREuser id id SELECTphoneINTOp inout strFROMuserWHEREuser id id END DELIMITER 18 INOUT参数例子 通过预定义输出参数变量调用 先定义输出参数变量set p inout num 5 set p inout str Test 再把变量作为参数调用calldemo inout parameter p inout num p inout str 19 INOUT参数例子 结果分析 调用前 p inout num的变量值为5p inout str的变量值为 Test 调用后 p inout num的变量值为50对应score字段值p inout str的变量值为 83278678 对应phone字段值 20 课堂练习1 写一个存储过程 实现以下功能 把上面USRE表中积分 score 大于60的用户同步到一个VIP USER表中insertintonew table column1 column2 selectcolumn1 column2fromold table给积分60以下的女性会员 sex字段值为0 每人加5分返回以上操作以后男会员的平均分与女会员的平均分 21 变量 mysql存储过程中 定义变量有两种方式 会话变量 也叫用户变量 使用set直接赋值 变量名以 开头例如 set num 1 可以在一个客户端会话的任何地方声明 作用域是整个会话 存储过程变量 以DECLARE关键字声明的变量 只能在存储过程中使用 例如 DECLAREmynumINTDEFAULT0 主要用在存储过程中 或者是给存储传参数中 两者的区别是 以DECLARE声明的变量都会被初始化为NULL而会话变量 即 开头的变量 则不会被再初始化在一个会话内 只须初始化一次会话断开后 会话变量也就消失 22 存储过程变量 变量定义DECLARE 变量名 数据类型 DEFAULTvalue 数据类型为MySQL的数据类型如 int float date varchar length 默认值 DEFAULTvalue 可有可无例如 DECLAREmy intintdefault4000000 DECLAREmy numericnumber 8 2 DEFAULT9 95 DECLAREmy datedateDEFAULT 1999 12 31 DECLAREmy datetimedatetimeDEFAULT 1999 12 3123 59 59 DECLAREmy varcharvarchar 255 DEFAULT Thiswillnotbepadded 23 存储过程变量 变量赋值SET变量名 表达式值例如 SETmy int 100SETmy numeric 11 02SETmy date 2009 11 21 SETmy datetime 2009 11 2120 50 50 SETmy varchar Hello 变量取值直接调用变量名即可取得变量值例如 INSERTINTOtable1VALUES my int 上面的INSERT语句直接用前面定义的my int变量名 即可取得变量值100 24 变量案例 DELIMITER CREATEPROCEDUREproc declare demo INp inINTEGER BEGINDECLAREmystrCHAR 10 IFp in 17THENSETmystr birds ELSESETmystr beasts ENDIF INSERTINTO user user name pass word email phone sex score VALUES mystr mystr 81234567 0 111 END DELIMITER 25 变量作用域 变量的作用范围 往上回溯 从最靠近变量的第一个begin开始往下延伸 到最靠近变量的第一个end结束内部的变量比外部变量在其作用域范围内享有更高的优先权 26 变量作用域案例 DELIMITER CREATEPROCEDUREproc3 begindeclarex1varchar 5 default outer begindeclarex1varchar 5 default inner selectx1 end selectx1 end DELIMITER 27 课程练习2 写一个存储过程 实现以下功能 给上面用户表 User表 增加一个备注字段 mark ALTERTABLE user ADDCOLUMN mark VARCHAR 45 AFTER score 用DECLARE定义几个存储过程变量变量1值为 不活跃会员 变量2值为 普通会员 变量3值为 高级会员 根据上面定义的变量填充mark字段如果积分在60分以下 填充变量1的值如果积分在60 79分 填充变量2的值如果积分在80分以上 填充变量3的值返回以上操作以后每个等级会员的个数 28 注释 MySQL存储过程可使用两种风格的注释双模杠 空格该风格一般用于单行注释编程风格 一般用于多行注释 29 注释样例 30 条件语句 if then if then elseif then else语句if后面跟判断条件elseif后面跟判断条件then后面跟条件分支语句块可以有多个elseif then语句块 也可以没有else表示以上条件均不满足时会执行的语句块整个条件语句块的最后面以endif表示结束 31 条件语句案例 if then DELIMITER CREATEPROCEDUREproc if else INif parameterint begindeclarecondition paraint setcondition para if parameter ifcondition para 1thenINSERTINTO user user name pass word email phone sex score VALUES if user if user if user 88888888 0 100 elseifcondition para 2thenupdate user setscore 200whereuser id5 elseupdate user setscore 1000 endif end DELIMITER 32 课程练习3 写一个存储过程 如果输入参数值为1把user表数据同步到user info表如果输入参数值为2删除user info表数据如果输入参数值为3把user表数据同步到user bak表如果输入参数值为4删除user bak表数据如果输入参数值为5同时删除user info user bak表数据user user info user bak三个表结构相同用if then elseif then else语句实现 33 SELECT中的IF条件语句 IF表达式 IF expr1 expr2 expr3 如果expr1是TRUE则IF 的返回值为expr2否则返回值则为expr3IF 的返回值为数字值或字符串值 具体情况视其所在语境而定selectif sex 0 女 男 fromuser 34 条件语句 case when case when then else语句case后面跟条件变量when后面跟条件值then后面跟条件分支语句块可以有多个when then语句块 也可以只有有一个else表示以上条件均不满足时会执行的语句块整个条件语句块最后面以endcase表示结束 35 条件语句案例 case when DELIMITER CREATEPROCEDUREproc case when INcase parameterint begindeclarecondition paraint setcondition para case parameter casecondition parawhen1thenINSERTINTO user user name pass word email phone sex score VALUES case user case user case user 7777777 0 200 when2thenupdate user setscore 300whereuser id5 elseupdate user setscore 2000 endcase end DELIMITER 36 课程练习4 用case when then else语句改写课程练习3 37 SELECT中的CASE WHEN 语句格式 selectcasewhenA条件then字段1whenB条件then字段2else字段0endfromtbSELECTCASEWHENSEX 0THEN 女 WHENSEX 1THEN 男 ELSE ERROR ENDFROMUSER 38 循环语句 while do while do语句while后面跟条件表达式do后面跟条件分支语句块整个条件语句块最后面以endwhile表示结束 39 循环语句案例 while do DELIMITER CREATEPROCEDUREproc while do begindeclareaint seta 0 whilea 100doINSERTINTO user user name pass word email phone sex score VALUES case user case user case user 7777777 0 a seta a 1 endwhile end DELIMITER 40 课程练习5 用when do循环语句往User Info表 User Bak表各插入500条记录User Info User Bak表与User表结构相同 41 循环语句 repeat until repeat until语句until后面跟条件表达式repeat后面跟条件分支语句块整个条件语句块最后面以endrepeat表示结束先执行repeat操作后检查until条件语句 而while则是执行前进行检查 42 循环语句案例 repeat until DELIMITER CREATEPROCEDUREproc repeat until begindeclarebint setb 0 repeatINSERTINTO user user name pass word email phone sex score VALUES repeat user repeat user repeat user 11111111 0 b setb b 1 untilb 200endrepeat end DELIMITER 43 课程练习6 用repeat until语句改写课程练习5 44 循环语句 loop leave loop leave语句loop循环不需要初始条件loop循环格式 循环标识 loop如 LOOP LABLE loop不需要结束条件 leave语句即表示跳出循环leave的格式是 leave 循环标识如 leaveLOOP LABLE整个条件语句块最后面以endloop表示结束 45 循环语句案例 loop leave DELIMITER CREATEPROCEDUREproc loop begindeclarecint setc 0 LOOP LABLE loopINSERTINTO user user name pass word email phone sex score VALUES loop user loop user loop user 22222222 0 c setc c 1 ifc 400thenleaveLOOP LABLE endif endloop end DELIMITER 46 课程练习7 用loop leave语句改写课程练习5 47 游标 cursor 游标的作用及属性游标的作用是 对查询数据库所返回的记录进行遍历游标如下属性 游标是只读的 也就是不能更新它 游标是不能滚动的 也就是只能在一个方向上进行遍历 不能在记录之间随意进退 不能跳过某些记录 避免在已经打开游标的表上更新数据 48 如何使用游标 首先用DECLARE语句声明一个游标格式 DECLARE 游标名 CURSORFOR SELECT语句例如 声明一个叫mycoursor的游标 DECLAREmycoursorCURSORFORselectuser name passwordfromuser 49 如何使用游标 其次需要使用OPEN语句来打开定义的游标格式 OPEN 游标名例如 打开一个叫mycoursor的游标 OPENmycoursor 50 如何使用游标 接下来可以用FETCH语句来获得下一行数据游标将移动到对应的记录上类似java里面的iterator格式 FETCH 游标名 INTO 变量列表例如 把mycoursor游标的一行记录装进my name my pwd变量FETCHmycoursorINTOmy name my pwdmy name my pwd两个变量需提前定义好 51 如何使用游标 最后操作都结束后要把游标释放掉格式 CLOSE 游标名例如 关闭mycoursor游标CLOSEmycoursor 52 如何使用游标 定义一个NOTFOUND条件处理函数游标到达记录的末尾时避免出现 nodatatofetch 这样的错误格式 DECLARECONTINUEHANDLERFORNOTFOUND 需要执行的语句例如 DECLARECONTINUEHANDLERFORNOTFOUNDSETno more products 1 53 游标使用案例 建表 CREATEDATABASEIFNOTEXISTSmydb USEmydb DROPTABLEIFEXISTS products CREATETABLE products id int 10 unsignedNOTNULLauto increment name varchar 45 NOTNULL category varchar 45 NOTNULL price floatNOTNULL code varchar 45 NOTNULL quantity int 10 unsignedNOTNULL PRIMARYKEY id ENGINE InnoDBAUTO INCREMENT 6DEFAULTCHARSET utf8 INSERTINTO products id name category price code quantity VALUES 1 cake dessert 10 112011 323 2 cheese dairy 12 5 112010 55 3 cookie dessert 50 112012 323 4 carpet furniture 100 113010 323 5 couch furniture 1250 113011 15 54 游标使用案例 游标使用演示获取库存量小于100的产品的代码code DELIMITER DROPPROCEDUREIFEXISTS CursorProc CREATEPROCEDURE CursorProc BEGINDECLAREno more products quantity in stockINTDEFAULT0 DECLAREprd codeVARCHAR 255 DECLAREcur productCURSORFORSELECTcodeFROMproducts First Delcareacursor 首先这里对游标进行定义 DECLARECONTINUEHANDLERFORNOTFOUNDSETno more products 1 when notfound occur justcontinue 这个是个条件处理 针对NOTFOUND的条件 forloggginginformation创建个临时表格来保持 CREATETEMPORARYTABLEinfologs Idint 11 NOTNULLAUTO INCREMENT Msgvarchar 255 NOTNULL PRIMARYKEY Id OPENcur product Second Openthecursor接着使用OPEN打开游标 FETCHcur productINTOprd code Third nowyoucanFetchtherow把第一行数据写入变量中 游标也随之指向了记录的第一行 REPEATSELECTquantityINTOquantity in stockFROMproductsWHEREcode prd code IFquantity in stock 100THENINSERTINTOinfologs msg VALUES prd code ENDIF FETCHcur productINTOprd code UNTILno more products 1ENDREPEAT CLOSEcur product Finally cursorneedbeclosed用完后记得用CLOSE把资源释放掉 SELECT FROMinfologs DROPTABLEinfologs END DELIMITER 55 课程练习7 改用游标功能实现课程练习1 56 基本函数 字符串类 CHARSET str 返回字串字符集CONCAT string2 string3 连接字串INSTR string substring 返回substring首次在string中出现的位置 不存在返回0LCASE string2 转换成小写UCASE string2 转换成大写LEFT string2 length 从string2中的左边起取length个字符RIGHT string2 length 从string2中的右边起取length个字符LENGTH string string长度LTRIM string2 去除前端空格RTRIM string2 去除后端空格TRIM string2 去除前后端空格SPACE count 生成count个空格REPLACE str search str replace str 在str中用replace str替换search strSUBSTRING str position length 从str的position开始 取length个字符 position从1开始 57 基本函数 数学类 ABS number2 绝对值BIN decimal number 十进制转二进制CEILING number2 向上取整FLOOR number2 向下取整FORMAT number decimal places 保留小数位数LEAST number number2 求最小值MOD numerator denominator 求余SQRT number2 开平方RAND 随机数ROUND number 四舍五入 保留到整数ROUND number decimals 四舍五入 decimals为小数位数 58 基本函数 日期类 CURRENT DATE 当前日期CURRENT TIME 当前时间CURRENT TIMESTAMP 当前时间戳NOW 当前时间SEC TO TIME seconds 秒数转成时间 时 分 秒 MAKETIME hour minute second 生成时间串YEAR datetime 年份MONTH datetime 月DATE datetime 返回datetime的日期部分HOUR datetime 小时 59 存储过程函数演示 字符串类数学类日期类 60 数学操作符 61 单元作业1 用存储过程对商品销售明细表 order detail 作如下统计操作 按月统计出7 8月份的销售金额 把数据汇总到月销售汇总表 order month 按商品类型统计出各种类型商品销售金额 把数据汇总到类型销售汇总表 order total type 按月份及商品类型统计出7 8月份各种类型商品销售金额 把数据汇总到月类型销售汇总表 order total type month 参考语句 insertintonew table column1 column2 selectcolumn1 column2fromold table 62 建表脚本 CREATEDATABASEIFNOTEXISTSmydb USEmydb DROPTABLEIFEXISTS order detail CREATETABLE order detail order id int 10 unsignedNOTNULLauto increment commodity varchar 45 NOTNULL commodity num int 10 unsignedNOTNULL order money floatNOTNULL order time datetimeNOTNULL commodity type varchar 45 defaultNULL PRIMARYKEY order id ENGINE InnoDBAUTO INCREMENT 15DEFAULTCHARSET utf8 INSERTINTO order detail order id commodity commodity num order money order time commodity type VALUES 1 衬衣 9 900 2015 07 0212 13 20 服装 2 帽子 30 600 2015 08 0712 13 40 服装 3 裤子 8 640 2015 08 1711 13 20 服装 4 大米 100 350 2015 07 2012 13 20 食品 5 零食 50 200 2015 08 1112 13 40 食品 6 蔬菜 30 90 2015 07 0912 12 30 食品 7 面包 20 80 2015 08 2512 13 40 食品 8 风车 10 70 2015 07 2112 13 30 玩具 9 汽球 40 60 2015 08 1312 13 45 玩具 10 钢笔 25 500 2015 07 1012 13 35 文具 11 铅笔 150 300 2015 08 1412 13 45 文具 12 毛笔 10 50 2015 07 0311 13 42 文具 13 作业本 200 400 2015 08 1812 13 15 文具 14 文件夹 50 150 2015 08 1712 10 45 文具 DROPTABLEIFEXISTS order month CREATETABLE order month id int 10 unsignedNOTNULLauto increment month varchar 45 NOTNULL total money floatNOTNULL PRIMARYKEY id ENGINE InnoDBDEFAULTCHARSET utf8 DROPTABLEIFEXISTS order total type CREATETABLE order total type id int 10 unsignedNOTNULLauto increment commodity type varchar 45 NOTNULL total money floatNOTNULL PRIMARYKEY id ENGINE InnoDBDEFAULTCHARSET utf8 DROPTABLEIFEXISTS order total type month CREATETABLE order total type month id int 10 unsignedNOTNULLauto increment month varchar 45 NOTNULL commodity type varchar 45 NOTNULL total money floatNOTNULL PRIMARYKEY id ENGINE InnoDBDEFAULTCHARSET utf8 63 单元作业2 用存储过程实现以下功能创建一个业务表创建表前先判断此表是否存在 如果存在先删除字段满足如下要求ID自增业务名称字段为字符串类型订阅时间字段为日期类型收费方式字段 0 按流量收取 1 按小时收取 2 按日收取 3 按月收到 4 按年收取 是否生效字段 0 未生效 1 已生效 用循环往业务表插入1000条记录按ID字段排序 升序 检索出251 300行业务数据收费方式字段显示 按流量收取 或 按小时收取 或 按日收取 或 按年收取 是否生效字段显示 未生效 或 已生效 64 单元作业3 用存储过程游标为student info表统计各个学生总分 total 字段值总分 数学成绩 语文成绩 音乐成绩 历史成绩 65 建表脚本 CREATEDATABASEIFNOTEXISTSmydb USEmydb DROPTABLEIFEXISTS student info CREATETABLE student info sn int 10 unsignedNOTNULLauto increment name varchar 45 NOTNULL math int 10 unsignedNOTNULL chinese int 10 unsignedNOTNULL music int 10 unsignedNOTNULL history int 10 unsignedNOTNULL total int 10 unsigneddefaultNULL PRIMARYKEY sn ENGINE InnoDBAUTO INCREMENT 9DEFAULTCHARSET utf8 INSERTINTO student info sn name math chinese music history total VALUES 1 张小明 75 66 61 72 0 2 刘齐清 68 77 73 63 0 3 梁代年 80 64 60 69 0 4 许志锋 62 69 78 72 0 5 王平丰 78 81 75 91 0 6 何静静 65 79 80 73 0 7 苗青青 88 90 81 88 0 8 路小斌 72 71 68 83 0 66 单元作业4 用存储过程游标统计各个学生是否达到最低修读要求 若course student表中成绩字段 course score 大于60分 则学生取得该门课程学分每门课程的学分 credit 在course表中可查询到若某学生的所取得的全部学分大于7分 则该学生达到最低修学要求 在student表的mark字段标注 合格 否则标注 不合格 67 建表脚本 CREATEDATABASEIFNOTEXISTSmydb USEmydb DROPTABLEIFEXISTS course CREATETABLE course id int 10 unsignedNOTNULLauto increment name varchar 45 NOTNULL credit int 10 unsignedNOTNULL PRIMARYKEY id ENGINE InnoDBAUTO INCREMENT 7DEFAULTCHARSET utf8 INSERTINTO course id name credit VALUES 1 工业设计 3 2 大学语文 2 3 音乐鉴赏 3 4 高等数学 4 5 世界历史 2 6 美术素描 2 DROPTABLEIFEXISTS course student CREATETABLE course student course id int 10 unsignedNOTNULL student id int 10 unsignedNOTNULL course score int 10 unsignedNOTNULL PRIMARYKEY course id student id ENGINE InnoDBDEFAULTCHARSET utf8 INSERTINTO course student course id student id course score VALUES 1 1 70 1 2 75 1 5 45 2 2 85 2 3 50 3 1 70 3 2 80 4 3 65 4 4 70 4 5 90 5 4 65 5 5 65 6 1 90 6 3 70 6 4 80 DROPTABLEIFEXISTS student CREATETABLE student id int 10 unsignedNOTNULLauto increment name varchar 45 NOTNULL mark varchar 45 defaultNULL PRIMARYKEY id ENGINE InnoDBAUTO INCREMENT 6DEFAULTCHARSET utf8 INSERTINTO student id name mark VALUES 1 李广平 NULL 2 乔明明 NULL 3 刘中青 NULL 4 王志平 NULL 5 苗伟红 NULL