-- 重新定义分隔符
  delimiter $$

  create procedure sp_name()
    begin
    ...
    end$$

  -- 更改分隔符
  delimiter ;

  -- 调用
  call sp_name()

show procedure status\G

desc information_shcema.routines;
select * from information_shcema.routines\G

show create procedure_user procedure_name\G

mysql> drop procedure if exists database_name.procedure_name;

mysql> create table t1(id int);
mysql> delimiter $$
mysql> create procedure proce_while()
    -> begin
    -> declare i int; --声明了一个int类型的变量i
    -> set i = 1;
    -> while i < 5 do
    ->  insert into t1 values(i);
    ->  set i = i + 1;
    -> end while;
    -> end$$
mysql> delimiter ;

-- repeat 类似于do while
mysql> delimiter $$
mysql> create procedure proce_repeat()
    -> begin
    -> declare i int default 1; --创建一个int型变量且默认为1
    -> repeat
    ->   insert into t1 values(i);
    ->   set i = i + 1;
    ->   until i >= 6
    -> end repeat;
    -> end$$
Query OK, 0 rows affected (0.00 sec)
mysql> delimiter ;

mysql> delimiter $$
mysql> create procedure proce_loop()
    -> begin
    -> declare i int default 1;
    -> xff: loop
    ->  insert into t1 values('xff'i);
    ->  set i = i + 1;
    ->  if i >= 6 then
    ->   leave xff;
    ->  end if;
    -> end loop;
    -> end$$

mysql> delimiter $$
mysql> create procedure proce_param_in(in id int)
    -> begin
    ->  if(id is null) then
    ->   select 'id is null' as id_null; --会打印一句话
    ->  else
    ->   set id = id + 1;
    ->  end if;
    ->  select id as id_inside;  -- 会打印出id的值
    -> end$$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;
mysql> call proce_param_in(null);
+------------+
| id_null    |
+------------+
| id is null |
+------------+
1 row in set (0.00 sec)

+-----------+
| id_inside |
+-----------+
|      NULL |
+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

mysql> set @id = 20; --设置一个变量id = 20
Query OK, 0 rows affected (0.00 sec)

-- 存储过程里面的值改变并不影响存储过程外部的值
mysql> call proce_param_in(@id);
+-----------+
| id_inside |
+-----------+
|        21 |
+-----------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

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

mysql> delimiter $$
mysql> create procedure proce_param_out(out id int)
    -> begin
    ->   select id as id_inside_1;
    ->   if(id is not null) then
    ->     set id = id + 1;
    ->     select id as id_inside_2;
    ->   else
    ->     select 100 into id;
    ->   end if;
    ->   select id as id_inside_3;
    -> end$$
Query OK, 0 rows affected (0.01 sec)

mysql> delimiter ;

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

-- 不会把参数的值传入到存储过程中
mysql> call proce_param_out(@id);
+-------------+
| id_inside_1 |
+-------------+
|        NULL |
+-------------+
1 row in set (0.00 sec)

+-------------+
| id_inside_3 |
+-------------+
|         100 |
+-------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

-- 这里更改了id变量的值
mysql> select @id;
+------+
| @id  |
+------+
|  100 |
+------+
1 row in set (0.00 sec)

mysql> delimiter $$
mysql> create procedure proce_param_inout(inout id int)
    -> begin
    ->  select id as id_inside_1;
    ->  if(id is not null) then
    ->   set id = id + 1;
    ->   select id as id_inside_2;
    ->  else
    ->   select 100 into id;
    ->  end if;
    ->  select id as id_inside_3;
    -> end$$
Query OK, 0 row affected (0.00 sec)

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

-- 会把id的值传入存储过程并改变
mysql> call proce_param_inout(@id);
+-------------+
| id_inside_1 |
+-------------+
|         100 |
+-------------+
1 row in set (0.00 sec)

+-------------+
| id_inside_2 |
+-------------+
|         101 |
+-------------+
1 row in set (0.00 sec)

+-------------+
| id_inside_3 |
+-------------+
|         101 |
+-------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

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