存储过程可以理解为一个方法，里面可以包含业务逻辑、查询语句；

首先创建一个简单的存储过程

#如果存储过程无法执行，请去掉前面的空格
DELIMITER $$
  # 定义存储过程，名称为 no_param 无参数
  CREATE PROCEDURE no_param()
  # begin ···  end 之间的数据为存储过程主体内容
    BEGIN
      #定义变量，默认值为0
      DECLARE p_out INT DEFAULT 0;
      # 变量赋值
      SET p_out= 22;
      # 输出
      SELECT p_out;
    END;
    $$
DELIMITER ;
# 调用存储过程
CALL no_param();

输出结果

分隔符 $$

MySQL默认以”;”为分隔符，如果没有声明分割符，则编译器会把存储过程当成SQL语句进行处理，因此编译过程会报错，所以要事先用“DELIMITER ”声明当前段分隔符，让编译器把两个””之间的内容当做存储过程的代码，不会执行这些代码；“DELIMITER ;”的意为把分隔符还原。

带输入参数的存储过程

#如果存储过程无法执行，请去掉前面的空格
DELIMITER $$
  #in 参数
  CREATE PROCEDURE in_param(IN p_in int)
    BEGIN
        SELECT p_in;
    END;
    $$
DELIMITER ;
# 调用
CALL in_param(44)

输出结果

44

带输出参数的存储过程

DELIMITER $$
  CREATE PROCEDURE out_param(OUT p_out int)
    BEGIN
      SET p_out= 88 ;
    END;
    $$
DELIMITER ;

调用输出存储过程

SET @p_out=1;
CALL out_param(@p_out);
SELECT @p_out;

配合查询语句

DELIMITER $$
      CREATE PROCEDURE out_param(OUT p_out INT)
        BEGIN
          #SET p_out= (select count(1) from t_home_article) ;
          # 或者这样
          SELECT COUNT(1) INTO p_out FROM t_home_article;
        END;
        $$
    DELIMITER ;

条件语句 IF-THEN-ELSE

语句表达式

IF （条件） THEN ｛条件结果为true执行｝ ELSE ｛条件结果为false执行｝ END IF

使用到存储过程

DELIMITER //
CREATE PROCEDURE proc3(IN parameter int)
  BEGIN
    DECLARE var int;
    SET var=parameter+1;
    IF var=0 THEN
      INSERT INTO t VALUES (17);
    END IF ;
    IF parameter=0 THEN
      UPDATE t SET s1=s1+1;
    ELSE
      UPDATE t SET s1=s1+2;
    END IF ;
  END ;
  //
DELIMITER ;

条件语句 CASE-WHEN-THEN-ELSE

语句表达式

相当于程序里面的switch关键字的作用

使用方式

DELIMITER //
  CREATE PROCEDURE proc4 (IN parameter INT)
    BEGIN
      DECLARE var INT;
      SET var=parameter+1;
      CASE var
        WHEN 0 THEN
          INSERT INTO t VALUES (17);
        WHEN 1 THEN
          INSERT INTO t VALUES (18);
        ELSE
          INSERT INTO t VALUES (19);
      END CASE ;
    END ;
  //
DELIMITER ;

循环语句 WHILE-DO…END-WHILE

DELIMITER //
  CREATE PROCEDURE proc5()
    BEGIN
      DECLARE var INT;
      SET var=0;
      WHILE var<6 DO
        INSERT INTO t VALUES (var);
        SET var=var+1;
      END WHILE ;
    END;
  //
DELIMITER ;

循环语句 REPEAT…END REPEAT

此语句的特点是执行操作后检查结果

DELIMITER //
  CREATE PROCEDURE proc6 (IN num INT)
    BEGIN
      DECLARE v INT;
      SET v=0;
      REPEAT
        INSERT INTO t VALUES(v);
        SET v=v+1;
        # UNTIL 表达式结果为true时退出循环
        UNTIL v>num END REPEAT;
      SELECT v;
    END;
  //
DELIMITER ;

循环语句 LOOP…END LOOP

类似c语言的 loop ··· goto loop 语句

DELIMITER //
  CREATE PROCEDURE proc7 ()
    BEGIN
      DECLARE v INT;
      SET v=0;
      LOOP_LABLE:LOOP
        INSERT INTO t VALUES(v);
        SET v=v+1;
        IF v >=5 THEN
          -- 当v大于等于5时退出循环
          LEAVE LOOP_LABLE;
        END IF;
      END LOOP;
    END;
  //
DELIMITER ;

LABLES标号

标号可以用在begin repeat while 或者loop 语句前，语句标号只能在合法的语句前面使用。可以跳出循环，使运行指令达到复合语句的最后一步。

删除存储 过程

#直接删除，如果过程不存在则会报错
DROP PROCEDURE xxx;
# 如果存储过程存在则删除，不存在不会删除，不报错，会有警告
DROP PROCEDURE IF EXISTS xxx;

另外，这里在说下存储过程和函数的区别

• 函数限制比较多，如不能用临时表，只能用表变量等，而存储过程的限制相对就比较少。

1. 一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。
2. 对于存储过程来说可以返回参数，而函数只能返回值或者表对象。
3. 存储过程一般是作为一个独立的部分来执行，而函数可以作为查询语句的一个部分来调用，由于函数可以返回一个表对象，因此它可以在查询语句中位于FROM关键字的后面。
4. 当存储过程和函数被执行的时候，SQLManager会到procedurecache中去取相应的查询语句，如果在procedurecache里没有相应的查询语句，SQLManager就会对存储过程和函数进行编译。

• Procedurecache：中保存的是执行计划，当编译好之后就执行procedurecache中的executionplan，之后SQLSERVER会根据每个executionplan的实际情况来考虑是否要在cache中保存这个plan，评判的标准一个是这个executionplan可能被使用的频率；其次是生成这个plan的代价，也就是编译的耗时。保存在cache中的plan在下次执行时就不用再编译了。