第七章存储过程

第七章过程式数据库对象7.1存储过程一、存储过程的创建格式：a)格式：createprocedure存储过程名称([参数列表])[begin]存储过程中执行的命令序列;[end]结束符b)参数的定义(参数可有可无，但小括号必须要)，包括三部分：i.输入|输出模式1.in输入型参数(默认)，从外部调用环境将值输入到存储过程内部，传递的方向：实参值-形参变量2.out输出型参数，从存储过程输出值到外部调用环境的变量，传递的方向：形参变量-实参变量3.in|out输入|输出型参数，从外部调用环境将值输入到存储过程内部，对数据进行处理，然后将新的结果再由存储过程输出到外部调用环境的变量，传递的方向：实参变量-形参变量-实参变量ii.参数名称iii.数据类型iv.例如：1.createprocedureaddValue1(inoper1int,inoper2int,outsumint)……2.createprocedureswapValue(inoutoper1int,inoutoper2int)……c)结束符的定义:i.结束符定义的原因：因为在mySQL环境中默认使用分号作为结束符号，而每条语句输入完成后也是使用分号作为结束标记的。因此在定义存储过程的内部，如果需要写入多条语句，那么在第一条语句结束写分号的时候，整个存储过程的定义也就被结束了，因此，需要为mySQL环境定义额外的结束符作为结束标记。ii.结束符定义的格式：delimiter结束符号iii.结束符的使用：1.在存储过程内部（即beginend之间）仍然使用分号作为语句的结束标志。2.在存储过程定义结束后(即end的后面)需要使用自己定义的结束符作为整个存储过程定义结束的标志。d)在存储过程中处理2条以上命令的时候，应该加上begin...end，如果只包含1条语句，可以省略begin...end关键字。i.例如：createprocedurepro1()set@a=100;$createprocedurepro2()beginset@a=100;set@b=1000;end;$二、简单存储过程的例题a)不执行任何操作的存储过程delimiter!createprocedurepro3()beginend!b)存储过程的执行：call存储过程名称([实参列表]);c)在别的数据库语言中这样做是不允许的。因为它们要求begin和end之间至少存在一条语句。如下面的代码：createprocedurepro4()beginnull;end!使用null关键字表示不执行任何操作，但是这样做在MySQL中是不允许的。d)查看错误信息showerrors三、存储过程中的注释符号：a)单行注释符i.--该注释符需要注意：注释符和注释的内容之间要有空格，否则语法错误ii.#该注释符和注释的内容之间的空格可有可无b)多行注释符i./*……*/c)例题：createprocedurepro5()beginset@a=100;--定义一个用户变量@aset@b=1000;#定义一个用户变量@bset@c=1000;/*定义一个用户变量@c*/end;$四、局部变量的定义和使用a)局部变量的概念：在存储过程内部声明的变量，称为局部变量，这样的变量只能局限于存储过程内部使用。该变量不同于前面学的用户变量。b)局部变量的定义：i.在begin…end之间进行定义。ii.使用declare关键字进行定义，并且定义的同时可以赋值。格式：declare变量名,[变量2]数据类型[default默认值];注意，此处只能使用default设置默认值，不能用等号iii.必须遵循先定义后使用的原则。c)局部变量与用户变量的不同：i.命名格式不同：局部变量前面没有@ii.使用范围不同：局部变量只能在当前定义它的存储过程内部使用；而用户变量可以在当前的整个会话范围使用，包括存储过程。iii.用户变量不用事先定义，可以直接赋值使用，而且数据类型是随着赋予它的值而确定的。但局部变量必须先定义后使用，见下面的两个例子。iv.赋值方式不同，如下面。d)分别使用set和select关键字为局部变量和用户变量赋值首先要注意，为局部变量或用户变量赋值时，不能直接写成变量名=值;等号运算符应该配合set或select关键字一起使用。i.局部变量的赋值:1.set局部变量名=值2.set局部变量名：=值3.select值into局部变量名注意：由于局部变量的局限性，所以为局部变量的赋值的这些语句只能在存储过程内部执行。ii.用户变量的赋值方式：1.set@用户变量名=值2.set@用户变量名:=值3.select值into@用户变量名4.select@用户变量名:=值--这种形式只能用在用户变--量的赋值上注意：由于用户变量可以在整个会话的范围内使用，所以这些命令既可以在存储过程内部执行，也可以在存储过程的外部执行。iii.局部变量与用户变量的定义、赋值例题：1.例6：局部变量的定义与赋值createprocedurepro6()begindeclarea,b,c,dint;seta=10;setb:=20;select30intoc;--selectd:=40;end$2.例7：用户变量的定义与赋值createprocedurepro7()beginset@a=10;set@b:=20;select30into@c;--这种格式也可以直接在提示符下使用select@d:=40;end$3.例8：局部变量与用户变量不同的使用范围callpro6()$callpro7()$selecta$--局部变量a超出了使用范围，访问失败select@a$--用户变量@a可以在整个会话中使用五、有参数存储过程的定义与调用a)不同模式的形参对应的实参：i.in模式的形参，它的实参可以是常量、变量和表达式；ii.out模式的形参，它的实参只能是变量；iii.inout模式的形参，它的实参只能是变量；b)输入型参数与输出型参数的使用i.例1：createprocedureaddValue(inoper1int,inoper2int,outsumint)beginsetsum=oper1+oper2;end$或者写成：setsum:=oper1+oper2;selectoper1+oper2intosum;也就是说，形参本质与局部变量相同，所以赋值方式也相同。注意：add是关键字sum不是关键字上面存储过程的调用：delimiter;set@number;calladdValue(10,20,@number);select@number;ii.例2：使用用户变量保存存储过程的值delimiter$createprocedureaddValue1(inxint,inyint)beginselect@z:=x+y;end$calladdValue1(1,2)$select@z$c)输入输出型参数的使用i.写法一：delimiter$createprocedureswapValue(inoutoper1int,inoutoper2int)beginsetoper1=oper1+oper2;setoper2=oper1-oper2;setoper1=oper1-oper2;end$ii.写法二：createprocedureswapValue1(inoutoper1int,inoutoper2int)begindeclaretempint;settemp=oper1;setoper1=oper2;setoper2=temp;end$iii.调用：set@a=10,@b=20$callswapValue(@a,@b)$select@a,@b$六、标准SQL语句在存储过程中的使用a)DML语句在存储过程中的使用i.insert语句在存储过程中的使用createprocedureinsert_student(inxhint,xmvarchar(30),birthdate,provarchar(30),xfint,beizhuint)begininsertintostudent(id,stuname,birthday,profession,score,comment)values(xh,xm,birth,pro,xf,beizhu);end$调用插入的存储过程：callinsert_student(27,'张三','1990-9-8','软件开发',103,null)$callinsert_student(28,'李四','1990-9-8','软件开发',107,null)$ii.update语句在存储过程中的使用createprocedureupdate_student(inxhint,zymvarchar(30))beginupdatestudentsetprofession=zymwhereid=xh;end$调用修改的存储过程：callupdate_student(27,'计算机网络')$iii.delete语句在存储过程中的使用createproceduredelete_student_byID(inxhint)begindeletefromstudentwhereid=xh;end$调用删除的存储过程：calldelete_student_byID(4)$iv.select语句在存储过程中的使用方式一：直接执行select语句createprocedureselect_student()beginselect*fromstudent;end$--调用存储过程，完成查询的功能。callselect_student();方式二：将select语句写成select字段|表达式into变量[from表]例1：createprocedureselect_student_byID(outnamevarchar(30))beginselectstunameintonamefromstudent;end$--存储过程创建成功--调用存储过程select_xs_byIDcallselect_student_byid(@a)$ERROR1172(42000):Resultconsistedofmorethanonerow例2：createprocedureselect_student_byID1(xhint,outnamevarchar(30))beginselectstunameintonamefromstudentwhereid=xh;end$--调用查询的存储过程：callselect_student_byID1(111,@name)$select@name$注意两点：1、当执行select…into…from…语句的时候，一定要注意由于要将字段或表达式的结果存入用户变量或局部变量中，因此必须保证查询结果只返回一行记录。2、select…into…from…这种格式也可以在MySQL的会话环境下直接执行，也就是说不定义存储过程仍然可以在提示符下执行，只是在into子句的后面只能写用户变量名。如下例：mysqlselect姓名into@namefromxswhere学号='081101'$QueryOK,1rowaffected(0.00sec)mysqlselect@name$七、流程控制语句在存储过程中的使用a)if语句的使用i.格式：if判断条件then要执行的语句序列[elseif条件then语句序列]……[else语句序列]endif;ii.例题7.6：createprocedurexscj.compar(ink1integer,ink2integer,outk3char(6))beginifk1k2thensetk3='大于';elseifk1=k2thensetk3='等于';elsesetk3='小于';endif;end$$存储过程的调用：callcompar(10,20,@result)$$select@result$$