存储过程学习
文章开始前,首先给出本文示例表allIntersection。本文中的示例是在Navicate for MySQL中进行的。
一.存储过程的创建与调用
1.基本语法及示例:
创建存储过程的基本语法:
create procedure sp_name()
begin
………
end
调用此存储过程基本语法:
call sp_name();
注意:存储过程名称后面必须加括号,哪怕该存储过程没有参数传递
删除存储过程的基本语法:
drop procedure sp_name;
注意事项:不能在一个存储过程中删除另一个存储过程,只能调用另一个存储过程。
其他常用命令:
1. show procedure status
显示数据库中所有存储的存储过程基本信息,包括所属数据库,存储过程名称,创建时间等。
2. show create procedure sp_name
显示某一个存储过程的详细信息。
例1:
创建一个存储过程:
create procedure dayrepeat()
begin
select IntersectionUnit as '路口单位' , IntersectionName as '路口名称' , DriveDirection as '行车方向' , SCar+MCar+BCar as '总流量'
from allintersection;
end;
调用存储过程dayrepeat():
call dayrepeat();
返回结果如下图所示:
2.带参数传递的存储过程
例2:
create procedure test1(
out ls decimal(8,2),
out hs decimal(8,2),
out avs decimal(8,2)
)
begin
select min(SCar) as '小型车车流量最小值'
into ls
from allintersection;
select max(SCar) as '小型车车流量最大值
into hs
from allintersection;
select avg(SCar) as '小型车车流量平均值'
into avs
from allintersection;
end;
此存储过程接受3个参数:ls、hs、avs。每个参数必须制定类型,这里使用十进制值。关键字OUT指出相应的参数用来从存储过程传出一个值(返回给调用者)。MySQL支持IN(传递给存储过程)、OUT(从存储过程传出)和INOUT(对存储过程传入和传出)
注意:记录集不是允许的类型,因此,不能通过一个参数返回多个行和列。
由于此存储过程要求3个参数,因此必须正好传递3个参数,不多也不少,所以这条call语句给出3个参数,他们是存储过程保存结果的3个变量的名字。
call test1(@小型车车流量最小值,@小型车车流量最大值,@小型车车流量平均值);
在调用时,这条语句并不显示任何数据。它返回以后可以显示(或在其他处理中使用)的变量。
为了显示检索出的结果,可如下进行:
SELECT @小型车车流量平均值 , @小型车车流量最大值 , @小型车车流量平均值;
检索结果如下图所示:
例3:
下面是另外一个例子,这次使用IN和OUT参数。isTotal接受路口名称参数并返回该路口的总车流量。
create procedure isTotal(
IN isID int,
OUT iTotal decimal(8,2)
)
begin
select SCar+MCar+BCar AS '路口总车流量'
from allintersection
where ID = isID
into iTotal;
end;
isID被定义为IN,因为ID被传入存储过程。iTotal定义为OUT,因为要从存储过程返回合计。
为调用这个存储过程和显示合计结果,可使用以下语句:
call isTotal(1,@total);
select @total;
结果如下图所示:
二.使用游标
1.创建游标
游标用declare语句创建。如下面的例子所示:
create procedure test2()
begin
declare cursorTest cursor
for
select *
from allIntersection;
end;
2.打开和关闭游标
游标用OPEN CURSOR语句来打开,用CLOSE CURSOR来关闭
open cursorTest;
close cursorTest;
CLOSE释放游标使用的所有内部内存和资源,因此在每个游标不再需要时都应该关闭。在一个游标关闭后,如果没有重新打开,则不能使用它。但是,声明过的游标不需要再次声明,用OPEN语句打开它就可以了。
3.使用游标数据
在一个游标被打开后,可以使用FETCH语句分别访问它的每一行。FETCH语句指定检索什么数据(所需的列),检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针,使下一条FETCH语句检索下一行(不重复读取同一行)。
create procedure test3()
begin
declare o int; -- 声明一个局部变量
declare cursorTest3 cursor -- 声明一个游标
for
select ID
from allintersection;
open cursorTest3; -- 打开游标
fetch cursorTest3 into o; -- 获取IntersectionName
close cursorTest3; -- 关闭游标
end;
其中FETCH用来检索当前行的IntersectionName列(将自动从第一行开始)到一个名为o的局部声明的变量中。对检索出的数据部做任何处理。
例4:
这个例子循环检索数据,从第一行到最后一行。
create procedure test4()
begin
declare done boolean default 0;
declare o int; -- 声明一个局部变量
declare cursorTest4 cursor -- 声明一个游标
for
select ID
from allintersection;
declare continue handler for sqlstate '02000' set done=1;
open cursorTest4; -- 打开游标
-- 遍历所有的行
repeat
fetch cursorTest4 into o; -- 获取IntersectionName
until done end repeat; -- 结束循环
close cursorTest4; -- 关闭游标
end;
与上一个例子不同的是,这个例子中的FETCH是在REPEAT内,因此它反复执行到done为真(until done end repeat;规定)。为使它起作用,用一个default 0(假,不结束)定义变量done。那么,done怎样才能在结束时被设置为真呢?答案是用以下语句:
declare continue handler for sqlstate '02000' set done=1;
这条语句定义了一个CONTINUE HANDLER,它是在条件出现时被执行的代码。这里,它指出SQLSTATE '02000'出现时,set done=1。SQLSTATE '02000'是一个未找到条件,当REPEAT由于没有更多的行供循环而不能继续时,出现这个条件。
例5:
这个例子比上面的例子更复杂,对取出的数据进行了实际的处理。
create procedure test5()
begin
-- 声明局部变量
declare done boolean default 0;
declare o int;
-- declare t DECIMAL(8,2);
declare cursorTest5 cursor -- 声明一个游标
for
select ID from allintersection;
-- 定义continue handler
declare continue handler for sqlstate '02000' set done=1;
-- 创建daysRepeat表来存储结果
create table if not exists daysRepeat
(ID int , total decimal(8,2));
open cursorTest5; -- 打开游标
-- 遍历所有的行
fetch cursorTest5 into o;
repeat
call isTotal(o , @t);
insert into daysRepeat(ID , total)
values(o,@t);
fetch cursorTest5 into o; -- 获取IntersectionName
until done end repeat; -- 结束循环
close cursorTest5; -- 关闭游标
end;
然后执行存储过程:
call test5();
结果生成新的表daysRepeat,并往表里填充数据,填充后的表如下图所示:
zhanshenlvbu
2011.8.30
分享到:
相关推荐
MySQL存储过程学习资料 ,初学都必看
此文档中详细的记载了,mysql存储过程学习总结,希望可以帮助到你!
系统的学习MYSQL存储过程,从存储过程的创建到各种异常用的处理,进行了详细的讲解!
mysql经典教程+mysql存储过程讲解 重点讲解Mysql的存储过程,触发器,游标的使用 对mysql不太熟的朋友可以好好学习。。。
mysql_5.0存储过程学习总结 适用于初学者
内容概述:通过MySQL存储过程实战的例子,学会使用MySQL存储过程。包含以下内容: 创建无参存储过程、有参存储过程、IF-ELSE存储过程、WHILE循环存储过程、CASE-WHEN条件控制存储过程、REPEAT UNTIL循环存储过程、...
存储过程简单入门学习,文章中举实例学习,让你轻松入门
mysql存储过程电子书,里边对mysql存储过程详细简绍了一下,大家可以下载下来学习,交流
主要介绍了MySql存储过程学习知识小结的相关资料,非常不错,具有参考借鉴价值,需要的朋友可以参考下
想学习mysql存储过程吗?这是一份学习的好资源。
一、存储过程 存储过程(Stored Procedure)是在大型数据库系统中,一组为了完成特定功能的SQL语句集,经编译后存储在数据库中,用户 通过指定存储过程的名字并给出参数(如果该存储过程带有参数)来执行它。而...
mysql存储例程、存储过程、存储函数进阶学习
学习mysql存储过程的资料,中文的,可以随时查阅,个人感觉很有帮助。
MySQL存储过程,挺详细的一本书,值得学习!
MySQL视图及存储过程学习笔记
MySQL 5.0存储过程 .MySQL 存储过程 专题学习。
mysql 5_0存储过程学习总结 mysql 5_0存储过程学习总结