存储过程的变量和流程

声明 DECLARE 与 使用 变量

在Stored routines中，除了可以声明需要的参数外，如果需要处理比较复杂的资料，你也可以宣告「局部变量、local variables」。下列是声明区域变数的语法与位置：

下列是几种宣告区域变数的范例：

DELIMITER $$
CREATE PROCEDURE procedure_name()
  BEGIN
    -- 声明一个INT类型的变量
    DECLARE num1 INT;
    -- 声明一个变量并赋值为 0
    DECLARE num2 INT DEFAULT 0;
    -- 声明多个变量并赋值为 1
    DECLARE num3, num4, num5 INT DEFAULT 1;
  END;
$$
DELIMITER ;

宣告需要的区域变数后，你就可以在stored routines中使用它们，需要指定变数值的话

可以使用下列两种语法

第一种方式

DELIMITER $$
CREATE FUNCTION function_n()
  RETURNS INT
  BEGIN
    -- 声明三个 【INT】 类型参数
    DECLARE param1, param2, param3 INT DEFAULT 1;
    SET param1 = 1, param2 = 1;
    SET param3 = param1 + param2;
    RETURN param3;
  END $$
DELIMITER ;

第二种方式


DELIMITER $$
CREATE FUNCTION function_n()
  RETURNS VARCHAR(64)
  BEGIN
    -- 声明三个 【INT】 类型参数
    DECLARE param1, param2, param3 INT DEFAULT 1;
    SELECT (param1 + param2) INTO param3;
    RETURN param3;
  END $$
DELIMITER ;

下列是宣告与使用「SET」叙述指定变数的范例：

下列的范例使用「SELECT」叙述，把查询叙述回传的资料指定给变数：

在Stored routines中宣告区域变数，一定要放在「BEGIN」与「END」区块中：

在一个Stored routines中，除了基本的「BEGIN」与「END」区块，也可以再使用「BEGIN」与「END」设定一个区块，每一个区块都可以宣告需要的区域变数：

在「BEGIN」与「END」区块中宣告的区域变数，只有在宣告的区块中有效，这也是它称为区域变数的原因：

如果你使用一个已经被清除的区域变数，在建立stored routines时不会有问题，不过使用的时候就会发生错误：

在同一个区块宣告变数时，不可以使用同样的变数名称；不过你可以在内层区块中，使用外层区块已经使用过的变数名称，可是要特别注意它们的有效范围：

注：在撰写stored routines时，如果在多个区块中宣告变数，应该还是使用不同的变数名称会好一些。

判断

建立与使用stored routines可以帮你一次执行许多叙述，简化资料库的操作；除了这个好处外，stored routines还提供许多判断的语法，让你可以执行需要的判断，再根据判断的结果执行不同的工作。

If

MySQL在stored routines中提供「IF」叙述，你可以在「IF」叙述中设定判断的条件，与条件成立时要执行的工作。

语法如下

IF condition THEN 叙述
  ELSEIF condition THEN 叙述
  ELSE 叙述
END IF

下列的procedure范例接收一个表示体重的整数参数，它会使用这个参数来判断体重是否太重，如果超过100公斤的话，就会显示「You are heavy!」：

image

DROP PROCEDURE IF EXISTS test_heavy;

DELIMITER $$
CREATE PROCEDURE test_heavy(IN param INT)
  BEGIN
    IF param > 100
    THEN SELECT "你是猪啊！" AS '体重';
    ELSEIF param < 100
      THEN SELECT "你瘦的跟个猴似的！" AS '体重';
    ELSE SELECT "你不是人！" AS '体重';
    END IF;
  END;
$$
DELIMITER ;

CALL test_heavy(160);

你可以依照需求在「IF」叙述中使用多个「ELSEIF」来判断不同的条件，也可以使用一个「ELSE」来处理所有条件都不成立时要执行的工作：

呼叫上列的「test_weight3」procedure范例会有下列的结果

标准体重会依照身高与性别而不同，所以会有类似下列这样的表格：

身高范围 性别 标准体重
160~164 男 58
165~169 男 60
160~164 女 56
170~174 男 64

下列是一个依照上列表格所完成的标准体重函式：

DELIMITER $$
CREATE FUNCTION getStandardHeavy(height INT, gender CHAR(1))
  RETURNS INT
  BEGIN
    DECLARE standard INT DEFAULT 0;
    IF height IN ('155', '153') AND gender = 'M'
    THEN SELECT 58 INTO standard;
    ELSEIF (height BETWEEN 165 AND 169) AND gender = 'M'
      THEN SET standard = 65;
    ELSE
      SELECT 60 INTO standard;
    END IF;
    RETURN standard;
    END;
$$
DELIMITER ;

SELECT getStandardHeavy(168,'M')

Case

在stored routines中还可以使用「CASE」叙述执行条件判断的工作。

CASE叙述有两种语法

第一种语法跟「IF」叙述是很类似的

CASE 
  WHEN condition THEN 叙述;
  WHEN condition THEN 叙述;
  ELSE 叙述;
END CASE;

以判断体重的需求来说

第一种、使用「CASE」叙述同样可以完成：


DROP PROCEDURE IF EXISTS test_weight;

DELIMITER $$
CREATE PROCEDURE test_weight(IN p_weight INT, IN p_height INT, IN p_gender CHAR(1))
  BEGIN
    DECLARE standard INT DEFAULT getStandardHeavy(p_height, p_gender);
    CASE
      WHEN p_weight > standard
      THEN SELECT '你特么有点肿了啊！',standard;
      WHEN p_weight > standard
      THEN SELECT '你身材真好！';
    ELSE SELECT '你特么是人妖么！';
    END CASE;
  END;
$$
DELIMITER ;

CALL test_weight(185,100,'M')

第二种、CASE 叙述还可以使用下列这种语法：

DROP PROCEDURE IF EXISTS test_weight;

DELIMITER $$
CREATE PROCEDURE test_weight(IN p_weight INT, IN p_height INT, IN p_gender CHAR(1))
  BEGIN
    DECLARE standard INT DEFAULT getStandardHeavy(p_height, p_gender);
    CASE p_weight
      WHEN 85 THEN SELECT '你特么有点肿了啊！';
      WHEN 60 THEN SELECT '你的身材很好';
      END CASE ;
  END;
$$
DELIMITER ;

CALL test_weight(85,100,'M')

这样的语法很适合使用在类似「ENUM」资料型态的判断，例如下列这个判断季节的procedure：

使用这种「CASE」语法来执行判断工作时，要特别注意错误资料的处理：

你应该加入「ELSE」来预防错误资料造成的问题：

循环

循环

在stored routines中如果需要执行一个工作多次的时候，就可以使用「循环、loops」，搭配使用判断与循环，把一些固定又繁复的工作撰写成stored routines储存起来，可以大幅度简化资料库的操作。

While

下列是可以用来执行一个工作多次的「WHILE」循环语法：

image

你必须依照需求设定「WHILE」循环语法中的判断条件，由它来控制循环是否继续执行：


CREATE PROCEDURE test_while()
  BEGIN
    DECLARE num INT DEFAULT 14;
    WHILE num > 0 AND num < 20 DO
      SELECT num;
      SET num = num + 1;
    END WHILE;
  END;
CALL test_while()

下面的例子，可以从1加到参数指定的数字

DELIMITER $$
CREATE FUNCTION test_while(num INT)
  RETURNS INT
  BEGIN
    DECLARE param INT DEFAULT 0;
    DECLARE total INT DEFAULT 0;
    WHILE param <= num DO
      SET total = param + total;
      SET param = param + 1;
    END WHILE;
    RETURN total;
  END;
$$
DELIMITER ;

SELECT test_while(100);  -- 5050

REPEAT

下列是可以用来执行一个工作多次的「REPEAT」循环语法：

你必须依照需求在「REPEAT」循环语法中的「UNTIL」设定判断条件，由它来控制循环是否继续执行：


DROP FUNCTION IF EXISTS test_repeat;

DELIMITER $$
CREATE FUNCTION test_repeat(num INT)
  RETURNS INT
  BEGIN
    DECLARE total INT DEFAULT 0;
    DECLARE param INT DEFAULT 0;
    REPEAT
      SET total = total + param;
      SET param = param + 1;
    UNTIL
    param > num  -- 此处是重点 是不成立的时候跳出循环 
    END REPEAT;
    RETURN total;
  END;
$$
DELIMITER ;

SELECT test_repeat(100) -- 5050

LOOP

下列是可以用来执行一个工作多次的 LOOP 循环语法：

如果只是单纯的使用「LOOP」循环 的话，只要进入循环后，就会不断重复执行循环中的叙述，永远不会停止，陷入死循环：

DELIMITER $$
CREATE FUNCTION test_loop(num INT)
  RETURNS INT
  BEGIN
    LOOP
      SELECT '这样会进入死循环，因为没有跳出条件！！！！！';
        END LOOP;
      RETURN 1;
    END;
$$
DELIMITER ;

标签

在使用「BEGIN-END」、「WHILE」、「REPEAT」与「LOOP」四种区块时，都可以为它们设定「标签、label」

标签是由你自己为这些区块取的名字，下列使用「LOOP」回圈来说明标签的设定规则，这个规则同样适用在其它三种区块：

在一般的状况下，通常不需要为区块设定标签。如果为了控制sotred routines的执行流程，才会设定区块的标签。

设定标签以后，就可以搭配使用「LEAVE」叙述来控制流程，相当于java中的 break

下列是「LEAVE」叙述在「LOOP」循环中的效果

「LEAVE」叙述在其它三种区块中有同样的效果：

搭配使用「LEAVE」叙述来控制流程，就可以控制「LOOP」回圈在需要的时候离开。下列的「summary_loop」范例可以为你从1开始加总到参数指定的数字

DELIMITER $$
CREATE FUNCTION test_loop(num INT)
  RETURNS INT
  BEGIN
    DECLARE param INT DEFAULT 0;
    DECLARE total INT DEFAULT 0;
    ll: LOOP
      SET total = param + total;
      SET param = param + 1;
      IF param > num
      THEN LEAVE ll;
      END IF;
    END LOOP;
    RETURN total;
  END;
$$
DELIMITER ;

SELECT test_loop(100) -- 5050

DELIMITER $$
CREATE FUNCTION test_while(num INT)
  RETURNS INT
  BEGIN
    DECLARE param INT DEFAULT 0;
    DECLARE total INT DEFAULT 0;
    ll: WHILE param <= num DO
      SET total = param + total;
      SET param = param + 1;
      IF param = 3
      THEN LEAVE ll;
      END IF;
    END WHILE;
    RETURN total;
  END;
$$
DELIMITER ;

设定标签以后，也可以搭配使用「ITERATE」叙述来控制流程，相当于java中的 continue

下列是「ITERATE」叙述在「LOOP」循环中的效果：

「ITERATE」叙述不可以使用在「BEGIN-END」区块中，不过它在其它两种区块中有同样的效果：

下列的「summary_iterate」范例可以为你从1开始加总到参数指定的数字，不过额外使用「ITERATE」叙述控制，让这个function只会加总奇数：

DROP FUNCTION  test_iterate;
DELIMITER $$
CREATE FUNCTION test_iterate(num INT)
  RETURNS INT
  BEGIN
    DECLARE param INT DEFAULT 0;
    DECLARE total INT DEFAULT 0;
    ite: WHILE param < num DO
      SET param = param + 1;
      IF param % 2 <> 0
      THEN ITERATE ite;
      END IF;
      SET total = total + param;
    END WHILE;
    RETURN total;
  END;
$$
DELIMITER ;

SELECT test_iterate(100); -- 2500

results matching ""

    No results matching ""