/ 中存储网

MySQL存储过程的变量声明if和case指令

2014-07-13 15:55:53 来源:中存储网

    声明局部变量
    DECLARE仅被用在BEGIN……END复合语句中声明局部变量,并且必须在复合语句的开头,在任何其他语句之前。DECLARE声明局部变量的语法是:DECLARE var_name[...] type [DEFAULT value],声明的变量如果没有设定初始值,那么系统默认为NULL,如果设定初始值,初始值可以使一个表达式,不需要为一个常数。局部变量的作用范围在它被声明的BEGIN……END块内,如果在嵌套的块中,局部变量只声明了一次,这个变量也可以在嵌套的块中使用。
mysql> create procedure pro2()
    -> begin
    -> declare x int;
    -> declare y int;
    -> set x=2;
    -> set y=4;
    -> insert into t1(filed) values(x);
    -> select filed * x from t1 where filed >=y;
    -> end;//
Query OK, 0 rows affected (0.01 sec)
mysql> select * from t1//
+——-+
| filed |
+——-+
|     4 |
|     2 |
+——-+
2 rows in set (0.00 sec)

mysql> call pro2()//
+———–+
| filed * x |
+———–+
|         8 |
+———–+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)
使用declare声明的变量和会话变量不一样,不能使用@进行修饰,所以使用变量和会话变量的时候一定要分清他们的不同。

在上例中
    -> declare x int;
    -> declare y int;
声明局部变量的时候,没有设定他们的值,这个时候,他们的初始值是NULL,可以使用SET语句在BEGIN……END块中给变量赋值。
如果在声明局部变量的时候,就设定他们的值,那么pro2()存储过程程序变成如下:
mysql> create procedure pro3()
    -> begin
    -> declare x,y int default 5;
    -> insert into t1(filed) values(x);
    -> select filed * x from t1 where filed >= y;
    -> end;//
Query OK, 0 rows affected (0.00 sec)
和声明变量然后使用SET语句给变量赋值是一个效果,下面来执行一下:
mysql> select * from t1//
Empty set (0.00 sec)

mysql> call pro3()//
+———–+
| filed * x |
+———–+
|        25 |
+———–+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
    从执行结果来看,声明局部变量后使用set赋值和声明变量的时候直接赋值,效果一样。

     变量的作用域            
     下面我们再来看一下局部变量的作用域。首先看下面的例子:
mysql> create procedure pro4()
    -> begin
    -> declare a char(5) default “outer”;
    ->  begin
    ->  declare a char(5) default “inner”;
    ->  select a;
    ->  end;
    -> select a;
    -> end;//
Query OK, 0 rows affected (0.00 sec)
在这个例子中,有嵌套的BEGIN/END块,每个块中都有名字为a的变量,这样是可以的,合法的。内部变量在其作用域内具有更高的优先权,当执行到END语句后,变量已经不在嵌套的作用域内,在嵌套的语句外不可见,即内部变量在嵌套语句模块外不可用,但是可以通过out参数或者将内部变量的值赋给会话变量来保存它的值。  
    如果调用这个存储过程的话, 应该首先输出inner,然后输出outer,下面执行 看一下,是否和分析的一样:
mysql> call pro4()//
+——-+
| a     |
+——-+
| inner |
+——-+
1 row in set (0.00 sec)

+——-+
| a     |
+——-+
| outer |
+——-+
1 row in set (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
    和分析的结果一样。 

流程控制语句    IF THEN ELSE END IF
首先看下面的例子:
mysql> create procedure pro5(in parameter1 int)
    -> begin
    -> declare variable1 int;
    -> set variable1=parameter1+1;
    -> if variable1=0 then
    ->     insert into t1(filed) values(11);
    -> end if;
    -> if parameter1=0 then
    ->     update t1 set filed=filed+1;
    -> else
    ->     update t1 set filed=filed+2;
    -> end if;
    -> end;//
Query OK, 0 rows affected (0.00 sec)
这里是包含if语句的存储过程。里面有两个if语句,一个是IF语句END IF语句,另一个是IF语句 ELSE语句 END IF。以上语句就是IF语句的常用方式。
    现在调用一下这个存储过程:
mysql> select * from t1//
+——-+
| filed |
+——-+
|     1 |
+——-+
1 row in set (0.00 sec)

mysql> call pro5(0)//
Query OK, 1 row affected (0.00 sec)。

mysql> select * from t1//
+——-+
| filed |
+——-+
|     2 |
+——-+
1 row in set (0.00 sec)
调用存储过程后,filed字段值+1,因为调用存储过程的时候传入的值是0

        下面来分析下call pro5(0)的执行过程。首先0被传入,variable1=parameter1+1,即variable1被赋值为parameter1+1的值,所以执行后variable1的值为1。
    因为variable1的值为1,所以 if variable1=0 判断为假,所以其后的执行语句 insert into t1(filed) values(11);被跳过,没有被执行。
    接着判断if parameter1=0 then为真,所以执行update t1 set filed=filed+1;。如果parameter1的值为NULL,则下一条UPDATE语句将被执行。现在表中只有一行数据,值为1,UPDATE后,值变为2。               

    CASE  指令
    上面的例子中我们使用了IF ELSE判断语句,如果有多个判断条件的话,使用多条IF ELSE判断语句是不是特别麻烦!那么这个时候,可以使用下面这个指令 CASE。
mysql> create procedure pro7(in parameter1 int)
    -> begin
    -> declare variable1 int;
    -> set variable1=parameter1+1;
    -> case variable1
    ->     when 0 then insert into t1(filed) values(10);
    ->     when 1 then insert into t1(filed) values(20);
    ->     else insert into t1(filed) values(30);
    -> end case;
    -> end;//
Query OK, 0 rows affected (0.00 sec)
使用case是不是要比使用IF判断语句要简单一些!
mysql> call pro7(0)//
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1//
+——-+
| filed |
+——-+
|     2 |
|    20 |
+——-+
2 rows in set (0.00 sec)
执行条用存储过程时,传入0,variable1为1,数值20被插入。

     那么 CALL pro7NULL) //的作用是什么?这个CALL语句做了那些动作?
    我们可以执行看一下:
mysql> call pro7(NULL)//
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1//
+——-+
| filed |
+——-+
|     2 |
|    20 |
|    30 |
+——-+
3 rows in set (0.00 sec)

    答案是调用pro7时,MySQL插入了另一条包含数值30的记录。原因是变量variable1的值为NULL,CASE语句的ELSE部分就被执行了。