My SQL 第7章 教案.docx

1、My SQL 第7章 教案7.1 存储过程一、 存储过程的创建格式：a) 格式：create procedure 存储过程名称(参数列表)begin 存储过程中执行的命令序列;end 结束符b) 参数的定义(参数可有可无，但小括号必须要)，包括三部分：i. 输入|输出模式1. in 输入型参数(默认)，从外部调用环境将值输入到存储过程内部，传递的方向：实参值-形参变量2. out 输出型参数，从存储过程输出值到外部调用环境的变量，传递的方向：形参变量-实参变量3. in|out 输入|输出型参数，从外部调用环境将值输入到存储过程内部，对数据进行处理，然后将新的结果再由存储过程输出到外部调用环境

2、的变量，传递的方向：实参变量-形参变量-实参变量ii. 参数名称iii. 数据类型iv. 例如：1. create procedure addValue1(in oper1 int,in oper2 int,out sum int)2. create procedure swapValue(inout oper1 int,inout oper2 int)c) 结束符的定义:i. 结束符定义的原因：因为在mySQL环境中 默认使用分号 作为结束符号，而每条语句输入完成后也是使用分号作为结束标记的。因此在定义存储过程的内部，如果需要写入多条语句，那么在第一条语句结束写 分号 的时候，整个存储过程的

3、定义也就被结束了，因此，需要为mySQL环境定义额外的结束符作为结束标记。ii. 结束符定义的格式：delimiter 结束符号iii. 结束符的使用：1. 在存储过程内部（即begin end之间）仍然使用 分号 作为语句的结束标志。2. 在存储过程定义结束后(即 end 的后面) 需要使用自己定义的结束符作为整个存储过程定义结束的标志。d) 在存储过程中处理2条以上命令的时候，应该加上begin.end，如果只包含1条语句，可以省略begin.end关键字。i. 例如：create procedure pro1()set a=100;$create procedure pro2()begi

4、nset a=100;set b=1000;end;$二、 简单存储过程的例题a) 不执行任何操作的存储过程 delimiter ! create procedure pro3()beginend! b) 存储过程的执行：call 存储过程名称(实参列表); c) 在别的数据库语言中这样做是不允许的。因为它们要求begin 和 end 之间至少存在一条语句。如下面的代码：create procedure pro4()beginnull; end!使用null关键字表示不执行任何操作，但是这样做在MySQL中是不允许的。d) 查看错误信息 show errors三、 存储过程中的注释符号：a)

5、单行注释符i. - 该注释符需要注意：注释符和注释的内容之间要 有空格，否则语法错误ii. # 该注释符和注释的内容之间的空格可有可无b) 多行注释符i. /*/c) 例题：create procedure pro5()beginset a=100; - 定义一个用户变量aset b=1000;#定义一个用户变量bset c=1000;/*定义一个 用户变量c*/end;$四、 局部变量的定义和使用a) 局部变量的概念：在存储过程内部声明的变量，称为局部变量，这样的变量只能局限于存储过程内部使用。该变量不同于前面学的用户变量。b) 局部变量的定义：i. 在beginend之间进行定义。ii.

6、使用declare关键字进行定义，并且定义的同时可以赋值。格式：declare 变量名,变量2 数据类型 default 默认值;注意，此处只能使用default设置默认值，不能用等号iii. 必须遵循先定义后使用的原则。c) 局部变量与用户变量的不同：i. 命名格式不同：局部变量前面没有 ii. 定义和使用范围不同：局部变量只能在当前定义它的存储过程内部使用；而用户变量可以在当前的整个会话范围使用，包括存储过程。iii. 用户变量不用事先定义，可以直接赋值使用，而且数据类型是随着赋予它的值而确定的。但局部变量必须先定义后使用，见下面的两个例子。iv. 赋值方式不同，如下面。d) 分别使用se

7、t和select关键字为局部变量和用户变量赋值首先要注意，为局部变量或用户变量赋值时，不能直接写成 变量名 = 值; 等号 运算符应该 配合set或 select 关键字一起使用。i. 局部变量的赋值:1. set 局部变量名=值2. set 局部变量名：=值 3. select值 into 局部变量名注意：由于局部变量的局限性，所以为局部变量的赋值的这些语句只能在存储过程内部执行。ii. 用户变量的赋值方式：1. set 用户变量名=值 2. set 用户变量名:=值 3. select 值 into 用户变量名4. select 用户变量名:=值 -这种形式只能用在用户变 -量的赋值上注意

8、：由于用户变量可以在整个会话的范围内使用，所以这些命令既可以在存储过程内部执行，也可以在存储过程的外部执行。iii. 局部变量与用户变量的定义、赋值例题：1. 例6：局部变量的定义与赋值create procedure pro6()begin declare a,b,c,d int; set a=10; set b:=20; select 30 into c; - select d:=40;end$2. 例7：用户变量的定义与赋值create procedure pro7()begin set a=10; set b:=20; select 30 into c; -这种格式也可以直接在提示符下

9、使用 select d:=40;end$3. 例8：局部变量与用户变量不同的使用范围call pro6( )$call pro7( )$select a$ - 局部变量a超出了使用范围，访问失败select a$ - 用户变量a可以在整个会话中使用五、 有参数存储过程的定义与调用a) 不同模式的形参对应的实参：i. in模式的形参，它的实参可以是常量、变量和表达式；ii. out模式的形参，它的实参只能是变量；iii. inout模式的形参，它的实参只能是变量；b) 输入型参数与输出型参数的使用i. 例1：create procedure addValue(in oper1 int,in op

10、er2 int,out sum int) begin set sum=oper1+oper2; end$或者写成：set sum:=oper1+oper2; select oper1+oper2 into sum;也就是说，形参本质与局部变量相同，所以赋值方式也相同。注意：add是关键字 sum不是关键字上面存储过程的调用：delimiter ;set number;call addValue(10,20,number);select number;ii. 例2：使用用户变量保存存储过程的值delimiter $create procedure addValue1(in x int,in y

11、int) begin select z:=x+y; end$call addValue1(1,2)$select z$c) 输入输出型参数的使用i. 写法一：delimiter$create procedure swapValue(inout oper1 int,inout oper2 int)beginset oper1=oper1+oper2;set oper2=oper1-oper2;set oper1=oper1-oper2;end$ii. 写法二：create procedure swapValue1(inout oper1 int,inout oper2 int)begindecl

12、are temp int;set temp=oper1;set oper1=oper2;set oper2=temp;end$iii. 调用：set a=10,b=20$ call swapValue(a,b)$ select a,b$六、 标准SQL语句在存储过程中的使用a) DML语句在存储过程中的使用i. insert 语句在存储过程中的使用create procedure insert_xs(in xh char(6),xm char(8),zym char(10),xb tinyint,csrq date,zxf tinyint )begininsert into xs(学号,姓名,

13、专业名,性别,出生日期,总学分) values(xh,xm,zym,xb,csrq,zxf);end$调用插入的存储过程：call insert_xs(1,张三,软件开发,1,1990-9-8,40)$call insert_xs(2,李四,软件开发,1,1990-9-8,40)$ii. update语句在存储过程中的使用create procedure update_xs(in xh char(6),zym char(10)beginupdate xs set 专业名=zym where 学号=xh;end$调用修改的存储过程：call update_xs(2,计算机网络)$iii. del

14、ete语句在存储过程中的使用create procedure delete_xs_byID(in xh char(6)begindelete from xs where 学号=xh;end$调用删除的存储过程：call delete_xs_byID(1)$iv. select语句在存储过程中的使用create procedure select_xs_byID(xh char(6),out name char(8)beginselect 姓名 into name from xs where 学号=xh;end$注意两点：调用查询的存储过程：call select_xs_byID(081101,name)$select name$b) DCL语句在存储过程中的使用c) DDL语句在存储过程中的使用七、 流程控制语句在存储过程中的使用八、 处理程序和条件九、 游标的定义与使用一十、 删除存储过程一十一、 修改存储过程7.2 存储函数