MySQL存储过程学习

存储过程学习

        文章开始前，首先给出本文示例表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

 

 

评论

2 楼 u012273744 2014-12-05  

很不错 

1 楼 jd2bs 2014-06-26  

介绍挺详细的 
改天试试看