MySQL数据库之存储过程 procedure

create procedure my_pro1()
select * from my_student;

-- 修改语句结束符
delimiter $$
-- 创建过程
create procedure my_pro2()
begin
    -- 求1到100之间的和
    -- 创建局部变量
    declare i int default 1;
    -- declare sum int default 0;
    -- 会话变量
    set @sum = 0;
    -- 开始循环获取结果
    while i <= 100 do
        -- 求和
        set @sum = @sum + i;
        set i = i + 1;
    end while;

    -- 显示结果
    select @sum;
end
$$
delimiter ;

-- 查看所有存储过程
show procedure status [like 'pattern'];
-- 查看过程的创建语句
show create procedure 过程名字\G

call 过程名([实参列表]);
-- eg:
call my_pro2();
+------+
| @sum |
+------+
| 5050 |
+------+

drop procedure 过程名;

-- 创建3个会话变量
set @var1 = 1;
set @var2 = 2;
set @var3 = 3;
-- 查询会话变量
select @var1, @var2, @var3;
+-------+-------+-------+
| @var1 | @var2 | @var3 |
+-------+-------+-------+
|     1 |     2 |     3 |
+-------+-------+-------+
1 row in set (0.00 sec)
-- 修改语句结束符
delimiter $$
-- 定义过程
create procedure my_pro3(in a int, out b int, inout c int)
begin
    -- 查看传入的3个数据值
    select a, b, c;
    -- +------+------+------+
    -- | a    | b    | c    |
    -- +------+------+------+
    -- |    1 | NULL |    3 |
    -- +------+------+------+
    -- 修改3个变量值
    set a = 10;
    set b = 20;
    set c = 30;
    select a, b, c;
    -- +------+------+------+
    -- | a    | b    | c    |
    -- +------+------+------+
    -- |   10 |   20 |   30 |
    -- +------+------+------+
    -- 查看会话变量
    select @var1, @var2, @var3;
    -- +-------+-------+-------+
    -- | @var1 | @var2 | @var3 |
    -- +-------+-------+-------+
    -- |     1 |     2 |     3 |
    -- +-------+-------+-------+
    -- 修改会话变量
    set @var1 = 'a';
    set @var2 = 'b';
    set @var3 = 'c';
    select @var1, @var2, @var3;
    -- +-------+-------+-------+
    -- | @var1 | @var2 | @var3 |
    -- +-------+-------+-------+
    -- | a     | b     | c     |
    -- +-------+-------+-------+
end
$$
delimiter ;
-- 调用过程
call my_pro3(@var1, @var2, @var3);

-- 再次查看会话变量
Mysql> select @var1, @var2, @var3;
+-------+-------+-------+
| @var1 | @var2 | @var3 |
+-------+-------+-------+
| a     |    20 |    30 |
+-------+-------+-------+