My SQL 第7章 教案.docx

My SQL 第7章 教案.docx

《My SQL 第7章 教案.docx》由会员分享，可在线阅读，更多相关《My SQL 第7章 教案.docx（10页珍藏版）》请在冰豆网上搜索。

MySQL第7章教案

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（）

begin

set@a=100;

set@b=1000;

end;

$

二、简单存储过程的例题

a）不执行任何操作的存储过程

delimiter!

createprocedurepro3（）

begin

end!

b）存储过程的执行：

call存储过程名称（[实参列表]）;

c）在别的数据库语言中这样做是不允许的。

因为它们要求begin和end之间至少存在一条语句。

如下面的代码：

createprocedurepro4（）

begin

null;

end!

使用null关键字表示不执行任何操作，但是这样做在MySQL中是不允许的。

d）查看错误信息showerrors

三、存储过程中的注释符号：

a）单行注释符

i.--该注释符需要注意：

注释符和注释的内容之间要有空格，否则语法错误

ii.#该注释符和注释的内容之间的空格可有可无

b）多行注释符

i./*……*/

c）例题：

createprocedurepro5（）

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：

局部变量的定义与赋值

createprocedurepro6（）

begin

declarea,b,c,dint;

seta=10;

setb:

=20;

select30intoc;

--selectd:

=40;

end$

2.例7：

用户变量的定义与赋值

createprocedurepro7（）

begin

set@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）

begin

setsum=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）

begin

select@z:

=x+y;

end$

calladdValue1（1,2）$

select@z$

c）输入输出型参数的使用

i.写法一：

delimiter$

createprocedureswapValue（inoutoper1int,inoutoper2int）

begin

setoper1=oper1+oper2;

setoper2=oper1-oper2;

setoper1=oper1-oper2;

end$

ii.写法二：

createprocedureswapValue1（inoutoper1int,inoutoper2int）

begin

declaretempint;

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_xs

（inxhchar（6）,xmchar（8）,zymchar（10）,xbtinyint,csrqdate,zxftinyint）

begin

insertintoxs（学号,姓名,专业名,性别,出生日期,总学分）values（xh,xm,zym,xb,csrq,zxf）;

end$

调用插入的存储过程：

callinsert_xs（'1','张三','软件开发',1,'1990-9-8',40）$

callinsert_xs（'2','李四','软件开发',1,'1990-9-8',40）$

ii.update语句在存储过程中的使用

createprocedureupdate_xs

（inxhchar（6）,zymchar（10））

begin

updatexsset专业名=zymwhere学号=xh;

end$

调用修改的存储过程：

callupdate_xs（'2','计算机网络'）$

iii.delete语句在存储过程中的使用

createproceduredelete_xs_byID

（inxhchar（6））

begin

deletefromxswhere学号=xh;

end$

调用删除的存储过程：

calldelete_xs_byID（'1'）$

iv.select语句在存储过程中的使用

createprocedureselect_xs_byID

（xhchar（6）,outnamechar（8））

begin

select姓名intonamefromxswhere学号=xh;

end$

注意两点：

调用查询的存储过程：

callselect_xs_byID（'081101',@name）$

select@name$

b）DCL语句在存储过程中的使用

c）DDL语句在存储过程中的使用

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

八、处理程序和条件

九、游标的定义与使用

一十、删除存储过程

一十一、修改存储过程

7.2存储函数