MySql 存储过程（procedure）实例-蒲公英云

MySql 存储过程（procedure）实例

1.声明变量

delimiter $$
create procedure p2()
begin
  declare age int default 0;
  declare height int default 160;
  select concat('年龄是', age, '身高是',height);
end $$
delimiter ;

执行结果

mysql> call p2();
+----------------------------------------------+
| concat('年龄是', age, '身高是',height)       |
+----------------------------------------------+
| 年龄是0身高是160                             |
+----------------------------------------------+
1 row in set (0.00 sec)

2.运算

delimiter $$
create procedure p3()
begin
  declare age int default 0;
  set age := age+20;
  select concat('年龄是', age);
end $$
delimiter ;

执行结果

mysql> call p3();
+--------------------------+
| concat('年龄是', age)    |
+--------------------------+
| 年龄是20                 |
+--------------------------+
1 row in set (0.00 sec)

3.传参数

3.1 输入参数

delimiter $$
create procedure p4(in age int)
begin
  if age >= 18 then
    select concat(age,'是年富力强');
  else
    select concat(age,'是风华正茂');
  end if;
end $$
delimiter ;

执行结果

mysql> call p4(20);
+-------------------------------+
| concat(age,'是年富力强')      |
+-------------------------------+
| 20是年富力强                  |
+-------------------------------+
1 row in set (0.00 sec)

3.2 输出参数

delimiter $$
create procedure p6(out sum int)
begin
  set sum := 18;
end $$
delimiter ;

执行结果

mysql> call p6(@age);
Query OK, 0 rows affected (0.00 sec)
mysql> select @age;
+------+
| @age |
+------+
|   18 |
+------+
1 row in set (0.00 sec)

3.3输入输出参数

delimiter $$
create procedure p7(inout age int)
begin
  set age := age+20;
end $$
delimiter ;

执行结果

mysql> set @age=18;
Query OK, 0 rows affected (0.00 sec)
mysql> call p7(@age);
Query OK, 0 rows affected (0.00 sec)
mysql> select @age;
+------+
| @age |
+------+
|   38 |
+------+
1 row in set (0.00 sec)

4.while循环

delimiter $$
create procedure p5(in num int)
begin
   declare sum int default 0;
   declare i int default 0;
   while i<= num do
      set sum := sum+i;
      set i = i+1;
   end while;
   select concat('sum = ',sum);
end $$
delimiter ;

执行结果

mysql> call p5(100);
+----------------------+
| concat('sum = ',sum) |
+----------------------+
| sum = 5050           |
+----------------------+
1 row in set (0.00 sec)

5.case语句

delimiter $$
create procedure p8(in type int)
begin
  case type
  when 1 then select '墙';
  when 2 then select '梁';
  when 3 then select '板';
  when 4 then select '板';
  else select '未知类型';
  end case;
end $$
delimiter ;

执行结果

mysql> call p8(3);
+-----+
| 板  |
+-----+
| 板  |
+-----+
1 row in set (0.00 sec)

6.repeat循环

delimiter $$
create procedure p9(in num int)
begin
  declare i int default 0;
  declare sum int default 0; 
  repeat 
  set i := i+1;
  set sum := sum+i;
  until i>=num end repeat;
  select concat('sum = ',sum);
end $$
delimiter ;

执行结果

mysql> call p9(100);
+----------------------+
| concat('sum = ',sum) |
+----------------------+
| sum = 5050           |
+----------------------+
1 row in set (0.00 sec)