My SQL第7章 教案5.docx
《My SQL第7章 教案5.docx》由会员分享，可在线阅读，更多相关《My SQL第7章 教案5.docx（48页珍藏版）》请在冰豆网上搜索。
MySQL第7章教案5
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语句在存储过程中的使用
方式一：
直接执行select语句
createprocedureselect_xs（）
begin
select*fromxs;
end$
--调用存储过程，完成查询的功能。
callselect_xs（）;
方式二：
将select语句写成select字段|表达式into变量[from表]
例1：
createprocedureselect_xs_byID
（outnamechar（8））
begin
select姓名intonamefromxs;
end$--存储过程创建成功
--调用存储过程select_xs_byID
callselect_xs_byid（@a）$
ERROR1172（42000）:
Resultconsistedofmorethanonerow
例2：
createprocedureselect_xs_byID1
（xhchar（6）,outnamechar（8））
begin
select姓名intonamefromxswhere学号=xh;
end$
--调用查询的存储过程：
callselect_xs_byID1（'081101',@name）$
select@name$
注意两点：
1、当执行select…into…from…语句的时候，一定要注意由于要将字段或表达式的结果存入用户变量或局部变量中，因此必须保证查询结果只返回一行记录。
2、select…into…from…这种格式也可以在MySQL的会话环境下直接执行，也就是说不定义存储过程仍然可以在提示符下执行，只是在into子句的后面只能写用户变量名。
如下例：
mysql>select姓名into@namefromxswhere学号='081101'$
QueryOK,1rowaffected（0.00sec）
mysql>select@name$
b）DCL语句在存储过程中的使用
i.DCL语句主要与权限操作、事务操作相关，具体将在第9章与第10章介绍。
主要包括:
1.commit语句提交事务
2.rollback语句回滚事务
3.savepoint语句设置事务的保存的
4.grant语句为用户授予权限
5.revoke语句撤销用户的权限
此处，主要介绍DCL语句在存储过程中使用的时候与单独使用的时候，格式是否有变化。
下面，以commit语句为例：
第1步，做如下实验：
分别打开两个MySQL的会话窗口：
窗口1：
执行insertintoxs（学号,姓名,专业名,性别,出生日期,总学分）values（'1','张三','软件开发',1,'1990-9-8',40）
窗口2：
查看是否有学号”1”姓名“张三”的记录
select*fromxswhere学号='1';
第2步，做如下实验：
窗口1：
执行setautocommit=0;
insertintoxs（学号,姓名,专业名,性别,出生日期,总学分）values（'2','李四','软件开发',1,'1990-9-8',40）;
窗口2：
查看是否有学号”2”姓名“李四”的记录
select*fromxswhere学号='2';
第3步，做如下实验：
观察比较两次的查询结果，发现：
在没有执行setautocommit=0;之前，在第一个会话中插入的新数据将在第二个会话中查询到。
但是设置为0后，第一个会话中插入的新数据在第二个会话中查询不到了。
MySQL中的事务提交方式包括：
（1）手动提交，人工执行commit语句将对数据库的操作进行提交，成为对数据库的永久性变化。
（2）自动提交，系统自动执行的commit提交语句。
分析：
MySQL中默认的事务提交方式是哪种？
将自动提交改为手动提交：
setautocommit=0
第4步，做如下实验：
在窗口1中执行commit命令。
然后在窗口2中查看是否有学号是2的新数据。
ii.DCL语句在存储过程中的使用格式不变，如下例：
窗口1执行：
（窗口1已经改为手动的事务提交方式）
createprocedureinsert_xs1
（inxhchar（6）,xmchar（8）,zymchar（10）,xbtinyint,csrqdate,zxftinyint）
begin
insertintoxs（学号,姓名,专业名,性别,出生日期,总学分）values（xh,xm,zym,xb,csrq,zxf）;
commit;
end$
--调用存储过程
callinsert_xs1（'3','王五','软件开发',1,'1990-9-8',40）;
窗口2执行：
select*fromxs;
--窗口2中能够看到新数据，表名窗口1中存储过程执行的commit语句生效。
c）DDL语句在存储过程中的使用
i.DDL语句主要包括：
1.create创建对象的语句
2.alter修改对象的语句
3.drop删除对象的语句
ii.DDL语句在存储过程中的使用，格式不变：
--定义包含DDL语句的存储过程
createprocedurecreate_table（）
begin
createtablet1（idint）;
end;
$
--调用存储过程
callcreate_table（）$
--验证存储过程执行的有效性
desct1$
d）动态sql语句的执行、存储过程中执行动态sql语句
i.可以将sql语句定义字符串，然后任意更改语句中的表名、字段名、where条件等部分，这种执行sql语句的方式称为动态sql语句的执行。
ii.直接在提示符下动态执行sql语句，步骤如下：
1.定义要执行的sql语句的字符串
delimiter;
set@str1='select*from';
set@str2='xs';
set@str=concat（@str1,@str2）;--concat字符串连接函数
2.准备要执行的sql字符串
preparesqlstrfrom@str;
/*将字符串@str编译为合法的sql语句sqlstr（名称可以任意）。
此命令将检查@str变量中存储的sql语句的正确性，如果不正确该语句执行失败。
*
--例如：
preparesfrom'select*from';错误的
--改为：
preparesfrom'select*fromxs';
3.执行sql语句
executesqlstr;
4.释放准备的sql语句
deallocatepreparesqlstr;
释放操作执行后，再执行executesqlstr;将失败。
iii.在存储过程中动态执行sql语句：
1.上面的执行过程和执行思想，可以应用到存储过程中，使存储过程也能够动态的执行sql语句
2.例题：
例1：
创建存储过程，根据给定的条件删除xs表中的数据。
createproceduredelete_xs_condition（indelete_conditionvarchar（100））
begin
set@str=concat（'deletefromxswhere',delete_condition）;
preparesqlstrfrom@str;
executesqlstr;
deallocatepreparesqlstr;
end$
注意：
from后面可以是用户变量，或者是字符串常量，但不能使用局部变量。
这是因为局部变量的范围受存储过程的的限制，而prepare语句和execute语句可以在存储过程赋值后，在提示符下单独执行。
如下例：
--调用上面的存储过程，删除xs表中姓名为王五的记录。
delimiter;
calldelete_xs_condition（'姓名="王五"'）;
select*fromxs;
calldelete_xs_condition（'姓名like"张%"'）;
select*fromxs;
例2：
根据给定的名称动态创建一个新表。
delimiter$
createprocedurecreate_table（tableNamevarchar（50））
begin
set@a=concat（'createtable',tableName,'（idint）'）;
preparesfrom@a;
executes;
deallocateprepares;
end$
--调用存储过程
callcreate_table（'table1'）;
desctable1;
七、流程控制语句在存储过程中的使用
a）if语句的使用
i.格式：
if判断条件then要执行的语句序列
[elseif条件then语句序列]
……
[else语句序列]
endif;
ii.例题7.6：
createprocedurepar
（ink1integer,ink2integer,outk3char（6））
begin
ifk1>k2then
setk3='大于';
elseifk1=k2then
setk3='等于';
else
setk3='小于';
endif;
end$$
存储过程的调用：
callcompar（10,20,@result）$$
select@result$$
当某个条件满足后，要执行一组命令时，直接写这组命令就可以了，不需要加begin…end关键字，当然写上也没错。
见下面的写法（三）
上面的例子还可以改为：
（一）
createprocedurepar1
（ink1integer,ink2integer）
begin
declarek3char（6）;
ifk1>k2then
setk3='大于';
selectk3;
elseifk1=k2then
setk3='等于';--当然该程序可以优化只写一次selectk3的语句此处--主要为了试验当某个条件满足后执行多条语句的写法。
selectk3;
else
setk3='小于';
selectk3;
endif;
end$$
调用：
callcompar1（10,20）$$
（二）
createprocedurepar2
（ink1integer,ink2integer,outk3char（6））
begin
ifk1>k2then
setk3='大于';
selectk3;
elseifk1=k2then
setk3='等于';
selectk3;
else
setk3='小于';
selectk3;
endif;
end$$
调用：
callcompar2（10,20,@result）$$
（三）
createprocedurepar3
（ink1integer,ink2integer,outk3char（6））
begin
ifk1>k2then
begin
setk3='大于';
selectk3;
end;
elseifk1=k2then
begin
setk3='等于';
selectk3;
end;
else
begin
setk3='小于';
selectk3;
end;
endif;
end$$
调用：
callcompar2（20,20,@result）$$
b）case语句的使用
i.格式：
case表达式
when值1then语句序列;
when值2then语句序列;
……
else
语句序列;
endcase;
或者是：
case
when逻辑表达式或关系表达式1then语句序列;
when逻辑表达式或关系表达式2then语句序列;
……
else
语句序列;
endcase;
ii.例题：
例题7.7：
createprocedurexscj.result
（instrvarchar（4）,outsexvarchar（4））
be
升级会员 