SQL存储过程

存储过程
存储过程就是具有名字的一段代码，用来完成一个特定的功能

— SET 和 DECLARE 定义变量的区别：
CREATE DEFINER=`root`@`localhost` PROCEDURE `p_test`()
这里写个注解，可以增强代码可读性: BEGIN -- 开始
-- declare定义的变量为局部变量，仅在方法中生效。即只在存储过程中的begin和end之间生效。
-- @set定义的类似全局变量
 DECLARE b INT DEFAULT 1;
 SET @a = @a + 1;
 SET b = b + 1;
 SELECT @a,b;
END -- 结束

mysql> set @a = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> call p_test();
+------+------+
| @a   | b    |
+------+------+
|    2 |    2 |
+------+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call p_test();
+------+------+
| @a   | b    |
+------+------+
|    3 |    2 |
+------+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

— 可以看到a一直增加，b一直没变；


— IN OUT INOUT  参数区别：

— IN e.g1

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_test2`(IN a INT)
测试IN: BEGIN
 SELECT a;
END

mysql> call p_test2(1);
+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

— IN e.g2

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_test2`(IN a INT)
测试IN: BEGIN
 SET a = a + 1;
 SELECT a;
END


mysql> set @a =3;
Query OK, 0 rows affected (0.00 sec)

mysql> call p_test2(@a);
+------+
| a    |
+------+
|    4 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @a;
+------+
| @a   |
+------+
|    3 |
+------+
1 row in set (0.00 sec)


— OUT e.g1
—  注： 传出值只能是变量，下面的参数a 是传出值
CREATE DEFINER=`root`@`localhost` PROCEDURE `p_test3`(OUT a INT)
测试OUT: BEGIN
 SELECT a;
 SET a = 1;
 SELECT a;
END

— call p_test3(123); 会报错 ERROR 1414
Call p_test(@abc);  

mysql> call p_test3(@abc);
+------+
| a    |
+------+
| NULL | — 因为不接收输入值
+------+
1 row in set (0.00 sec)

+------+
| a    |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

mysql> select @abc; -- 打印出上面的存储过程的传出值（变量@abc）
+------+
| @abc |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

— INOUT e.g1  输入输出值也必须为变量

mysql> set @abc = 10;
Query OK, 0 rows affected (0.00 sec)

mysql> call p_test4(@abc);
+------+
| a    |
+------+
|   10 |
+------+
1 row in set (0.00 sec)

+------+
| a    |
+------+
|   11 |
+------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> select @abc;
+------+
| @abc |
+------+
|   11 |
+------+
1 row in set (0.00 sec)

— 可以看到 输入变量值@abc 10 最后经过存储过程改变成了输出变量@abc 11，即改变了变量;


— 条件循环语句  

— if then else end if  语句

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_test5`(IN a INT)
测试条件和循环语句: BEGIN
    if a > 10 then 
      SELECT 'a大于10';
    else
        SELECT 'a小于10';
    end if;    
END

mysql> call p_test5(100);
+-----------+
| a大于10   | — 列头
+-----------+
| a大于10   |
+-----------+
1 row in set (0.00 sec)

— case when then end case 语句

mysql> call p_test6(10);
+----------------+
| 你输入了10     |
+----------------+
| 你输入了10     |
+----------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call p_test6(11);
+----------------+
| 你输入了11     |
+----------------+
| 你输入了11     |
+----------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> call p_test6(12);
+---------------------------------+
| 你输入了10、11之外的数          |
+---------------------------------+
| 你输入了10、11之外的数          |
+---------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


— while do end while 循环

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_test7`(IN a INT)
测试条件和循环语句: BEGIN
    while a < 3 do 
        SELECT concat('我循环了', a,'次');
    SET a=a + 1;
    end while;
END

mysql> call p_test7(1);
+---------------------------------+
| concat('我循环了', a,'次')      |
+---------------------------------+
| 我循环了1次                     |
+---------------------------------+
1 row in set (0.00 sec)

+---------------------------------+
| concat('我循环了', a,'次')      |
+---------------------------------+
| 我循环了2次                     |
+---------------------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


— repeat end repeat 执行操作后检查结果，while 则是执行前进行检查

CREATE DEFINER=`root`@`localhost` PROCEDURE `p_test8`(IN a INT)
测试条件和循环语句: BEGIN
    repeat 
        SELECT '我循环了';    
        SET a = a + 1;
    until a > 3
    end repeat;
END

mysql> call p_test8(1);
+--------------+
| 我循环了     |
+--------------+
| 我循环了     |
+--------------+
1 row in set (0.00 sec)

+--------------+
| 我循环了     |
+--------------+
| 我循环了     |
+--------------+
1 row in set (0.00 sec)

+--------------+
| 我循环了     |
+--------------+
| 我循环了     |
+--------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)


— 其他 

mysql> select 100 into @abc;
Query OK, 1 row affected (0.00 sec)

mysql> select @abc;
+------+
| @abc |
+------+
|  100 |
+------+
1 row in set (0.00 sec)