My SQL 第7章 教案_mysql数据库

My SQL 第7章 教案由刀豆文库小编整理，希望给你工作、学习、生活带来方便，猜你可能喜欢“mysql数据库”。

7.1 存储过程

一、存储过程的创建格式： a)格式：

create procedure 存储过程名称([参数列表])[begin]

存储过程中执行的命令序列;[end] 结束符

b)参数的定义(参数可有可无，但小括号必须要)，包括三部分： i.输入|输出模式

1.in 输入型参数(默认)，从外部调用环境将值输入到存储过程内部，传递的方向：实参值-形参变量 2.out 输出型参数，从存储过程输出值到外部调用环境的变量，传递的方向：形参变量-实参变量

3.in|out 输入|输出型参数，从外部调用环境将值输入到存储过程内部，对数据进行处理，然后将新的结果再由存储过程输出到外部调用环境的变量，传递的方向：实参变量-形参变量-实参变量 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环境中 默认使用分号 作为结束符号，而每条语句输入完成后也是使用分号作为结束标记的。因此在定义存储过程的内部，如果需要写入多条语句，那么在第一条语句结束写 分号 的时候，整个存储过程的定义也就被结束了，因此，需要为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()begin set @a=100;set @b=1000;end;$

二、简单存储过程的例题 a)不执行任何操作的存储过程

delimiter!

create procedure pro3()begin end!

b)存储过程的执行：call 存储过程名称([实参列表]);

c)在别的数据库语言中这样做是不允许的。因为它们要求begin 和 end 之间至少存在一条语句。如下面的代码： create procedure pro4()begin null;end!使用null关键字表示不执行任何操作，但是这样做在MySQL中是不允许的。

d)查看错误信息

show errors

三、存储过程中的注释符号： a)单行注释符 i.--

该注释符需要注意：注释符和注释的内容之间要

有空格，否则语法错误 ii.# 该注释符和注释的内容之间的空格可有可无

b)多行注释符 i./*……*/ c)例题：

create procedure pro5()begin set @a=100;--定义一个用户变量@a set @b=1000;#定义一个用户变量@b set @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：局部变量的定义与赋值 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;--这种格式也可以直接在提示符下使用

select @d:=40;end$ 3.例8：局部变量与用户变量不同的使用范围 call pro6()$ call pro7()$

select a$--局部变量a超出了使用范围，访问失败 select @a$--用户变量@a可以在整个会话中使用

五、有参数存储过程的定义与调用 a)不同模式的形参对应的实参： i.ii.iii.in模式的形参，它的实参可以是常量、变量和表达式； out模式的形参，它的实参只能是变量； inout模式的形参，它的实参只能是变量；

b)输入型参数与输出型参数的使用 i.例1：

create procedure addValue(in oper1 int,in oper2 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 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)begin set oper1=oper1+oper2;set oper2=oper1-oper2;set oper1=oper1-oper2;end$ ii.写法二：

create procedure swapValue1(inout oper1 int,inout oper2 int)begin declare 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)begin insert into xs(学号,姓名,专业名,性别,出生日期,总学分)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))begin update xs set 专业名=zym where 学号=xh;end$ 调用修改的存储过程： call update_xs('2','计算机网络')$ iii.delete语句在存储过程中的使用

create procedure delete_xs_byID(in xh char(6))begin delete from xs where 学号=xh;end$ 调用删除的存储过程： call delete_xs_byID('1')$ iv.select语句在存储过程中的使用

create procedure select_xs_byID(xh char(6),out name char(8))begin select 姓名 into name from xs where 学号=xh;end$ 注意两点：

调用查询的存储过程：

call select_xs_byID('081101',@name)$ select @name$ b)DCL语句在存储过程中的使用 c)DDL语句在存储过程中的使用

七、流程控制语句在存储过程中的使用

八、处理程序和条件

九、游标的定义与使用

十、删除存储过程

十一、修改存储过程

7.2 存储函数