MySql 存储过程（procedure）实例

落日映苍穹つ 2022-02-03 08:51 200阅读 0赞

### 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)