MySql 存储过程（procedure）实例

最新推荐文章于 2025-05-16 22:42:11 发布

万里归来少年心

最新推荐文章于 2025-05-16 22:42:11 发布

阅读量938

点赞数 1

CC 4.0 BY-SA版权

分类专栏： MySQL基础文章标签：存储过程 repeat循环 case语句 while循环输入输出参数

本文链接：https://blog.youkuaiyun.com/liyazhen2011/article/details/90017991

MySQL基础专栏收录该内容

32 篇文章

订阅专栏

本文深入讲解MySQL存储过程的创建与使用，包括变量声明、运算、参数传递、循环、条件判断等核心功能，通过实例演示如何利用存储过程提高SQL操作效率。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

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)