存储过程-进阶

---

通常，复杂的业务逻辑需要多条 SQL 语句。这些语句要分别地从客户机发送到服务器，当客户机和服务器之间的操作很多时，将产生大量的网络传输。如果将这些操作放在一个存储过程中，那么客户机和服务器之间的网络传输就会大大减少，降低了网络负载

1.存储过程的变量

1.定义变量

存储过程中，使用declare声明变量。通过两个简单的例子来说明存储过程中的变量

例1：使用set赋值

delimiter //
create procedure varTest()
begin
    # 声明变量employ_name
    declare employ_name varchar(32) default '';
    # 使用set语句给变量赋值
    set employ_name='你礼貌么';
    # 查看变量
    select employ_name;
end
//
delimiter ;

调用存储过程call varTest;，查看变量赋值结果

例2：使用select into赋值

有如下数据：

在存储过程中通过select into 把表中的数据赋值给声明的变量

delimiter //
create procedure varTest2()
begin
    # 声明变量employ_name
    declare employ_name varchar(32) default '';
    # 将employ表中id=52456456的名称赋值给employ_name
    select name into employ_name from employ where id=52456456;
    # 查看变量
    select employ_name;
end
//
delimiter ;

调用存储过程call varTest2;，查看变量赋值结果

2.变量的作用域

在存储过程中，变量也存在作用域，作用域范围在变量申明的begin-end代码块中

delimiter //
create procedure varScopeTest()
begin
    begin
        # 第一个begin-end
        declare employ_name varchar(32) default '';
        # 将employ表中id=52456456的名称赋值给employ_name
        select name into employ_name from employ where id=52456456;
        # 查看变量
        select employ_name;
    end;
    begin
        # 第二个begin-end
        # 调用第一个begin-end中的employ_name 变量
        select employ_name;
    end;
end
//
delimiter ;

调用存储过程call varScopeTest

这里由于employ_name变量作用域只在第一个begin-end，在第二个begin-end中使用的时候就会报Unknown错误

如果需要在多个同级begin-end中使用同一个变量，则需要在上一级begin-end中声明

delimiter //
create procedure varScopeTest3()
begin
    declare employ_name varchar(32) default '你礼貌么';
    begin
        # 将employ表中id=52456456的名称赋值给employ_name
        select name into employ_name from employ where id=52456456;
        # 查看变量
        select employ_name;
        # 使用set改变变量的值
        set employ_name='你礼貌么';
    end;
    begin
        select employ_name;
    end;
end
//
delimiter ;

调用存储过程，查看结果

通过结果可以看到，变量定义为成员变量后。在其后的begin-end块共享这个变量

2.存储过程的条件判断分支

任何一门编程语言都少不了条件判断，存储过程同样有，他的语法格式如下：

if(条件1) then 分支代码块1....
elseif(条件2) then 分支代码块2...
elseif(条件n) then 分支代码块n...
else 所有条件都不满足时进入该代码块...
end if;

编写存储过程判断奇数偶数

delimiter //
create procedure assertTest(in num integer)
begin
    declare message varchar(100) default '';
    if(num & 1 = 0) then set message='传入的是偶数';
    else set message='传入的是奇数';
    end if;
    select message;
end
//
delimiter ;

注意：在存储过程中是否相等用=（一个等于号）

调用存储过程查看结果

3.存储过程的循环

在mysql存储过程的语句中有三个标准的循环方式：

• WHILE循环

WHILE 循环条件(为true则执行，否则无法执行) DO
          循环体
END WHILE

使用存储过程实现拼接0-9

delimiter //
create procedure whileTest()
begin
    declare idx integer default 0;
    declare message varchar(100) CHARACTER SET utf8 default '';

    while idx < 10 do
           set message=concat(message,idx,',');
           set idx=idx+1;
    end while;

    select message;
end
//
delimiter ;

执行结果如下

while循环总是在执行前检查语句的表达式，对比java中while循环

• LOOP循环

loop_name:LOOP
    IF 条件表达式 THEN -- 满足条件时离开循环
        LEAVE loop_name;  -- 和 break 差不多都是结束训话
    END IF;
END LOOP;

delimiter //
create procedure loopTest()
begin
    declare idx integer default 0;
    declare message varchar(100) CHARACTER SET utf8 default '';

    concat_loop:LOOP
        IF idx>10 THEN -- 满足条件时离开循环
            LEAVE concat_loop;  -- 和 break 差不多都是结束训话
        END IF;
        set message=concat(message,idx,',');
        set idx=idx+1;
    END LOOP;

    select message;
end
//
delimiter ;

调用存存储过程结果如下

loop循环要明确退出条件，不然会导致死循环。leave 的作用就是结束循环，相当于java中的break

• REPEAT循环

REPEAT
          循环体
UNTIL 循环条件(为false则执行，否则无法执行)
END REPEAT

delimiter //
create procedure repeatTest()
begin
    declare idx integer default 0;
    declare message varchar(100) CHARACTER SET utf8 default '';

    repeat
            set message=concat(message,idx,',');
            set idx=idx+1;
        until idx>10
    end repeat;

    select message;
end
//
delimiter ;

调用存储过程

repeat循环先执行一次再检查循环条件，所以无论如何repeat循环都至少执行一次，对比java中do...while