第七章 存储过程.docx

第七章 存储过程.docx

《第七章 存储过程.docx》由会员分享，可在线阅读，更多相关《第七章 存储过程.docx（24页珍藏版）》请在冰豆网上搜索。

第七章存储过程

第七章过程式数据库对象

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_student

（inxhint,xmvarchar（30）,birthdate,provarchar（30）,xfint,beizhuint）

begin

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

begin

updatestudentsetprofession=zymwhereid=xh;

end$

调用修改的存储过程：

callupdate_student（27,'计算机网络'）$

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

createproceduredelete_student_byID

（inxhint）

begin

deletefromstudentwhereid=xh;

end$

调用删除的存储过程：

calldelete_student_byID（4）$

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

方式一：

直接执行select语句

createprocedureselect_student（）

begin

select*fromstudent;

end$

--调用存储过程，完成查询的功能。

callselect_student（）;

方式二：

将select语句写成select字段|表达式into变量[from表]

例1：

createprocedureselect_student_byID

（outnamevarchar（30））

begin

selectstunameintonamefromstudent;

end$--存储过程创建成功

--调用存储过程select_xs_byID

callselect_student_byid（@a）$

ERROR1172（42000）:

Resultconsistedofmorethanonerow

例2：

createprocedureselect_student_byID1

（xhint,outnamevarchar（30））

begin

selectstunameintonamefromstudentwhereid=xh;

end$

--调用查询的存储过程：

callselect_student_byID1（111,@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$

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

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

begin

casestr

when'M'thensetsex='男';

when'F'thensetsex='女';

elsesetsex='无';

endcase;

end$$

调用存储过程：

callxscj.result（'M',@sex）$$

select@sex$$

callxscj.result（'m',@sex）$$

select@sex$$

改进后的例题7.7

createprocedurexscj.result1

（instrvarchar（4）,outsexvarchar（4））

begin

caseupper（str）--使用upper函数将字符串大写

when'M'thensetsex='男';

when'F'thensetsex='女';

elsesetsex='无';

endcase;

end$$

callxscj.result1（'m',@sex）$$

select@sex$$

callxscj.result1（'M',@sex）$$

select@sex$$

例题7.8

createprocedurexscj.result2

（instrvarchar（4）,outsexvarchar（4））

begin

case

whenstr='M'thensetsex='男';

whenstr='F'thensetsex='女';

elsesetsex='无';

endcase;

end$$

调用存储过程：

callxscj.result2（'M',@sex）$$

select@sex$$

callxscj.result2（'m',@sex）$$--默认字符串比较不区分大小写

select@sex$$--结果也是“男”

如果将例7.8做如下的改动，那么字符串比较时将区分大小写：

createprocedurexscj.result3

（instrvarchar（4）,outsexvarchar（4））

begin

case

whencast（strasbinary）='M'thensetsex='男';--将str转换为二进制

whencast（strasbinary）='F'thensetsex='女';

elsesetsex='无';

endcase;

end$$

调用存储过程：

callxscj.result3（'M',@sex）$$

select@sex$$

callxscj.result3（'m',@sex）$$

select@sex$$

c）循环语句

i.while循环语句

1.格式：

看格式，注意与其他语言中while循环的格式的区别

[begin_label:

]while循环条件do

循环体中的语句序列;

endwhile[end_label];

其中，begin_label和end_label是while语句的标注。

除非begin_label存在，否则end_label不能被给出，也就是说它们是成对出现的。

如果出现，名字必须相同。

2.例题：

例题7.9：

createproceduredowhile（）

begin

declarev1intdefault5;

whilev1>0do

setv1=v1-1;

selectv1;

endwhile;

end$$

调用存储过程：

calldowhile（）$$

将循环用在数据表的操作中：

思考，下面的存储过程执行什么功能？

createproceduredowhile1（）

begin

declarev1intdefault5;

whilev1>0do

setv1=v1-1;

select*fromstudent;

endwhile;

end$$

调用存储过程：

calldowhile1（）$$

利用循环执行对表的操作，阅读并理解以下程序：

createprocedurepro1（innoint）

begin

declareavgdouble;

declarezfxint;

setavg=（selectavg（score）fromstudent）;

setzfx=（selectscorefromstudentwhereid=no）;

while（zfx

updatexssetscore=score+5whereid=no;

setavg=（selectavg（score）fromstudent）;

setzfx=（selectscorefromstudentwhereid=no）;

endwhile;

end$$

调用存储过程：

callpro1（27）$$

selectavg（score）fromstudent$$

selectscorefromstudentwhere学号=27$$

ii.repeat循环语句

1.格式：

[begin_label:

]repeat

循环体中的语句序列;

until循环的退出条件

endrepeat[end_label];

2.例题：

例题7.10：

createprocedurepro2（）

begin

declarev1intdefault5;

repeat

setv1=v1-1;

selectv1;

untilv1<1

endrepeat;

end$$

该语句使用的时候注意两点：

（1）until表达式的后面不能加分号

（2）until子句应是循环体中的最后一个子句，它后面不能再有任何语句了。

调用存储过程：

callpro2（）;

iii.loop循环语句

1.格式：

[begin_label:

]loop

循环体中的语句序列;

endloop[end_label];

通过该格式可以看出，loop循环没有退出条件，因此该循环一般会和leave语句一起使用。

leave语句的格式:

leavelabel;

2.例题：

例题7.11

createproceduredoloop（