MySQL存储过程快速入门教程

2018-07-10 12:08:15 +0000


快速教程包括以下部分:

1、格式
2、参数
3、注释
4、语法
5、游标
6、存储过程调试
7、存储过程实例

 

 1、格式

  CREATE PROCEDURE 过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] 过程体

DELIMITER //

  CREATE PROCEDURE myproc(IN s int)

    BEGIN

      #定义变量
      DECLARE @item_id long;

      #业务逻辑写在这里

    END

    //

DELIMITER ;


2、参数

MySQL存储过程的参数用在存储过程的定义,共有三种参数类型,IN,OUT,INOUT:

IN参数的值必须在调用存储过程时指定,在存储过程中修改该参数的值不能被返回,为默认值

#存储过程IN参数

   DELIMITER //

  CREATE PROCEDURE in_param(IN p_in int)

    BEGIN

    SELECT p_in;

    SET p_in=2;

    SELECT p_in;

    END;

    //

DELIMITER ;

#调用

SET @p_in=1;

CALL test1(@p_in);

SELECT @p_in;

MySQL Workbence 执行结果:


OUT:该值可在存储过程内部被改变,并可返回

#存储过程OUT参数

DELIMITER //

  CREATE PROCEDUREtest2(OUT p_out int)

    BEGIN

      SELECT p_out;

      SET p_out=2;

      SELECT p_out;

    END;

    //

DELIMITER ;

#调用

SET @p_out=1;

CALLtest2(@p_out);

SELECT @p_out;

执行结果:


INOUT:调用时指定,并且可被改变和返回
#存储过程INOUT参数

DELIMITER //

  CREATE PROCEDURE test3(INOUT p_inout int)

    BEGIN

      SELECT p_inout;

      SET p_inout=2;

      SELECT p_inout;

    END;

    //

DELIMITER ;

#调用

SET @p_inout=1;

CALL test3(@p_inout) ;

SELECT @p_inout;

执行结果:


3、注释

单行:#存储过程单行注释

多行注释:

/**
这里是存储过程多行注释

**/


4、语法

SQL中给变量赋值

方式 1:

    DECLARE cnt INT DEFAULT 0;
    select count(*) into cnt from test_tbl;
    select cnt;
 

方式 2:

    set @cnt = (select count(*) from test_tbl);
    select @cnt;
 

方式 3:

    select count(*) into @cnt1 from test_tbl;
    select @cnt1;
 

多个列的情况下似乎只能用 into 方式

    select max(status), avg(status) into @max, @avg from test_tbl;

    select @max, @avg;

 

条件语句

if true then

  #业务代码

elseif true then

  #业务代码

else

  #业务代码

end if;

------------------------------------------

CASE var

        WHEN 0 THEN

          #业务代码

        WHEN 1 THEN

          #业务代码

        ELSE

          #业务代码

      END CASE ;


循环语句

WHILE-DO…END-WHILE

      WHILE trueDO

        #业务代码

      END WHILE ;

 

REPEAT...END REPEAT,先执行操作后检查结果

      SET v=0;

      REPEAT

        INSERT INTO t VALUES(v);

        SET v=v+1;

        UNTIL v>=5

      END REPEAT;

一般来说这2中循环基本够用,其他循环参考https://www.cnblogs.com/mark-chan/p/5384139.html


5、游标

游标循环和遍历

   游标的定义和使用同一般编程语言的逻辑相似,先定义后使用,使用完成后关闭。但是第一次使用中按照程序编写容易出现Mysql游标总是多循环一次,注意下图中的第15和24行。

注意:使用fetch into时,变量的定义不要和你的select的列的键同名!不然,fetch into 会失败!

 

多个游标循环嵌套
跟一般的代码一样,会遇到多层循环嵌套,且第2层循环还要使用第一层循环的数据。

  1. CREATE PROCEDURE test4 ()  
  2. BEGIN  
  3.   #定义  
  4.   #遍历数据结束标志  
  5.   DECLARE done INT DEFAULT FALSE;  
  6.   #定义游标  
  7.   DECLARE mycur CURSOR FOR SELECT id, name FROM t;  
  8.   #将结束标志绑定到游标  
  9.   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;  
  10.   #使用游标  
  11.   #打开游标  
  12.   OPEN mycur;  
  13.     FETCH mycur into item_id, item_name;  
  14.     #开始循环  
  15.     WHILE done do  
  16.       
  17.       #第2层游标  
  18.       BEGIN  
  19.         DECLARE second_done boolean DEFAULT true;  
  20.         #item_id为第1层循环传递的变量  
  21.         DECLARE mycur_2 CURSOR FOR SELECT price, quantity FROM t2 where id=item_id;  
  22.         DECLARE CONTINUE HANDLER FOR NOT FOUND SET second_done = TRUE;  
  23.           
  24.         OPEN mycur_2;  
  25.           FETCH mycur_2 into item_id, item_name;  
  26.           WHILE done do  
  27.              #第2层游标业务处理代码  
  28.              FETCH mycur_2 into item_id, item_name;  
  29.           END WHILE;  
  30.         CLOSE mycur_2;  
  31.       END;  
  32.         
  33.       FETCH mycur into item_id, item_name;  
  34.     END WHILE;  
  35.   #关闭游标  
  36.   CLOSE mycur;  
  37. END;  

6、存储过程调试

    1. 返回结果集使用select.
       返回一个变量的值:select 变量名; 。
        返回一张表数据:select * from t;。

   2. 调用存储过程
         call 存储过程名 (参数1, 参数2);


7、存储过程实例

。。。