MySQL存储过程学习(http://zhanshenlvbu.iteye.com/blog/1162203)

来源:互联网 发布:全球最大电子书软件 编辑:程序博客网 时间:2024/03/29 23:44

存储过程学习

        文章开始前,首先给出本文示例表allIntersection。本文中的示例是在Navicate for MySQL中进行的。

 

 

 

一.存储过程的创建与调用

1.基本语法及示例:

        创建存储过程的基本语法:

 

Java代码  收藏代码
  1. create procedure sp_name()  
  2. begin  
  3.   ………  
  4. end  

        调用此存储过程基本语法:

Sql代码  收藏代码
  1. call sp_name();   

        注意:存储过程名称后面必须加括号,哪怕该存储过程没有参数传递

 

        删除存储过程的基本语法:

Sql代码  收藏代码
  1. drop procedure sp_name;  

    注意事项:不能在一个存储过程中删除另一个存储过程,只能调用另一个存储过程。

 

    其他常用命令:
        1. show procedure status
        显示数据库中所有存储的存储过程基本信息,包括所属数据库,存储过程名称,创建时间等。
        2. show create procedure sp_name
        显示某一个存储过程的详细信息。

 

    例1:

    创建一个存储过程:

Sql代码  收藏代码
  1. create procedure dayrepeat()  
  2. begin  
  3.   select IntersectionUnit as '路口单位' , IntersectionName as '路口名称' , DriveDirection as '行车方向' , SCar+MCar+BCar as '总流量'  
  4.   from allintersection;  
  5. end;  

    调用存储过程dayrepeat():

Sql代码  收藏代码
  1. call dayrepeat();  

    返回结果如下图所示:

 

 

2.带参数传递的存储过程
    例2:

Sql代码  收藏代码
  1. create procedure test1(  
  2. out ls decimal(8,2),  
  3. out hs decimal(8,2),  
  4. out avs decimal(8,2)  
  5. )  
  6. begin  
  7.   select min(SCar) as '小型车车流量最小值'  
  8.   into ls  
  9.   from allintersection;  
  10.   select max(SCar) as '小型车车流量最大值  
  11.   into hs  
  12.   from allintersection;  
  13.   select avg(SCar) as '小型车车流量平均值'  
  14.   into avs  
  15.   from allintersection;  
  16. end;  

        此存储过程接受3个参数:ls、hs、avs。每个参数必须制定类型,这里使用十进制值。关键字OUT指出相应的参数用来从存储过程传出一个值(返回给调用者)。MySQL支持IN(传递给存储过程)、OUT(从存储过程传出)和INOUT(对存储过程传入和传出)
注意:记录集不是允许的类型,因此,不能通过一个参数返回多个行和列。

        由于此存储过程要求3个参数,因此必须正好传递3个参数,不多也不少,所以这条call语句给出3个参数,他们是存储过程保存结果的3个变量的名字。

Sql代码  收藏代码
  1. call test1(@小型车车流量最小值,@小型车车流量最大值,@小型车车流量平均值);  

        在调用时,这条语句并不显示任何数据。它返回以后可以显示(或在其他处理中使用)的变量。


        为了显示检索出的结果,可如下进行:

Sql代码  收藏代码
  1. SELECT @小型车车流量平均值 , @小型车车流量最大值 , @小型车车流量平均值;  

    检索结果如下图所示:

 

 

例3:
        下面是另外一个例子,这次使用IN和OUT参数。isTotal接受路口名称参数并返回该路口的总车流量。

Sql代码  收藏代码
  1. create procedure isTotal(  
  2. IN isID int,  
  3. OUT iTotal decimal(8,2)  
  4. )  
  5. begin  
  6.   select SCar+MCar+BCar AS '路口总车流量'  
  7.   from allintersection  
  8.   where ID = isID  
  9.   into iTotal;  
  10. end;  

        isID被定义为IN,因为ID被传入存储过程。iTotal定义为OUT,因为要从存储过程返回合计。

 

        为调用这个存储过程和显示合计结果,可使用以下语句:

Sql代码  收藏代码
  1. call isTotal(1,@total);  
  2. select @total;  

        结果如下图所示:

 

 

二.使用游标

1.创建游标
        游标用declare语句创建。如下面的例子所示:

Sql代码  收藏代码
  1. create procedure test2()  
  2. begin  
  3.     declare cursorTest cursor  
  4.     for  
  5. select *  
  6. from allIntersection;  
  7. end;  

2.打开和关闭游标
        游标用OPEN CURSOR语句来打开,用CLOSE CURSOR来关闭

Sql代码  收藏代码
  1. open cursorTest;  
  2. close cursorTest;  

        CLOSE释放游标使用的所有内部内存和资源,因此在每个游标不再需要时都应该关闭。在一个游标关闭后,如果没有重新打开,则不能使用它。但是,声明过的游标不需要再次声明,用OPEN语句打开它就可以了。
3.使用游标数据
        在一个游标被打开后,可以使用FETCH语句分别访问它的每一行。FETCH语句指定检索什么数据(所需的列),检索出来的数据存储在什么地方。它还向前移动游标中的内部行指针,使下一条FETCH语句检索下一行(不重复读取同一行)。

Sql代码  收藏代码
  1. create procedure test3()  
  2. begin  
  3.     declare o int;            -- 声明一个局部变量  
  4.   declare cursorTest3 cursor        -- 声明一个游标  
  5.     for  
  6.       select ID  
  7.     from allintersection;  
  8.     open cursorTest3;          -- 打开游标  
  9.     fetch cursorTest3 into o;              -- 获取IntersectionName  
  10.     close cursorTest3;  -- 关闭游标  
  11. end;  

 

        其中FETCH用来检索当前行的IntersectionName列(将自动从第一行开始)到一个名为o的局部声明的变量中。对检索出的数据部做任何处理。

 

例4:

        这个例子循环检索数据,从第一行到最后一行。

Sql代码  收藏代码
  1. create procedure test4()  
  2. begin  
  3.     declare done boolean default 0;  
  4.     declare o int;            -- 声明一个局部变量  
  5.   declare cursorTest4 cursor        -- 声明一个游标  
  6.     for  
  7.       select ID  
  8.     from allintersection;  
  9.   declare continue handler for sqlstate '02000' set done=1;  
  10.     open cursorTest4;          -- 打开游标  
  11.     -- 遍历所有的行  
  12.     repeat  
  13.            fetch cursorTest4 into o;              -- 获取IntersectionName  
  14.     until done end repeat;           -- 结束循环  
  15.     close cursorTest4;  -- 关闭游标  
  16. end;  

        与上一个例子不同的是,这个例子中的FETCH是在REPEAT内,因此它反复执行到done为真(until done end repeat;规定)。为使它起作用,用一个default 0(假,不结束)定义变量done。那么,done怎样才能在结束时被设置为真呢?答案是用以下语句:

Sql代码  收藏代码
  1. declare continue handler for sqlstate '02000' set done=1;  

        这条语句定义了一个CONTINUE HANDLER,它是在条件出现时被执行的代码。这里,它指出SQLSTATE '02000'出现时,set done=1。SQLSTATE '02000'是一个未找到条件,当REPEAT由于没有更多的行供循环而不能继续时,出现这个条件。

 

 

例5:

        这个例子比上面的例子更复杂,对取出的数据进行了实际的处理。

Sql代码  收藏代码
  1. create procedure test5()  
  2. begin  
  3.     -- 声明局部变量  
  4.     declare done boolean default 0;  
  5.     declare o int;             
  6.   
  7.   -- declare t DECIMAL(8,2);  
  8.   
  9.   declare cursorTest5 cursor        -- 声明一个游标  
  10.   
  11.     for  
  12.       select ID from allintersection;  
  13.   -- 定义continue handler  
  14.   declare continue handler for sqlstate '02000' set done=1;             
  15.     -- 创建daysRepeat表来存储结果  
  16.     create table if not exists daysRepeat  
  17.     (ID int , total decimal(8,2));  
  18.   
  19.     open cursorTest5;          -- 打开游标  
  20.      
  21.   -- 遍历所有的行  
  22. fetch cursorTest5 into o;    
  23. repeat  
  24.            call isTotal(o , @t);  
  25.            insert into daysRepeat(ID , total)  
  26.            values(o,@t);  
  27.            fetch cursorTest5 into o;              -- 获取IntersectionName  
  28.     until done end repeat;           -- 结束循环  
  29.     close cursorTest5;  -- 关闭游标  
  30. end;  

        然后执行存储过程:

Sql代码  收藏代码
  1. call test5();    

        结果生成新的表daysRepeat,并往表里填充数据,填充后的表如下图所示:

 

                                                                                                                                         zhanshenlvbu

0 0