mysql數據庫利用procedure存儲過程實現條件判斷代碼實例

mysql> delimiter $$
    mysql> CREATE PROCEDURE myProc()
        -> DETERMINISTIC
        -> BEGIN
        ->   DECLARE counter INT DEFAULT 0;
        ->
        ->   simple_loop: LOOP
        ->     SET counter=counter+1;
        ->     select counter;
        ->     IF counter=10 THEN
        ->        LEAVE simple_loop;
        ->     END IF;
        ->   END LOOP simple_loop;
        ->   SELECT 'I can count to 10';
        -> END$$
    Query OK, 0 rows affected (0.00 sec)

    mysql>
    mysql> delimiter ;
    mysql>
    mysql> call myProc();
    +---------+
    | counter |
    +---------+
    |       1 |
    +---------+
    1 row in set (0.00 sec)

    +---------+
    | counter |
    +---------+
    |       2 |
    +---------+
    1 row in set (0.02 sec)

    +---------+
    | counter |
    +---------+
    |       3 |
    +---------+
    1 row in set (0.02 sec)

    +---------+
    | counter |
    +---------+
    |       4 |
    +---------+
    1 row in set (0.02 sec)

    +---------+
    | counter |
    +---------+
    |       5 |
    +---------+
    1 row in set (0.02 sec)

    +---------+
    | counter |
    +---------+
    |       6 |
    +---------+
    1 row in set (0.02 sec)

    +---------+
    | counter |
    +---------+
    |       7 |
    +---------+
    1 row in set (0.02 sec)

    +---------+
    | counter |
    +---------+
    |       8 |
    +---------+
    1 row in set (0.02 sec)

    +---------+
    | counter |
    +---------+
    |       9 |
    +---------+
    1 row in set (0.33 sec)

    +---------+
    | counter |
    +---------+
    |      10 |
    +---------+
    1 row in set (0.33 sec)

    +-------------------+
    | I can count to 10 |
    +-------------------+
    | I can count to 10 |
    +-------------------+
    1 row in set (0.33 sec)

    Query OK, 0 rows affected (0.33 sec)


實例二

    mysql> CREATE PROCEDURE myProc()
        -> BEGIN
        ->     DECLARE i int;
        ->     SET i=1;
        ->     myloop: LOOP
        ->          SET i=i+1;
        ->          IF i=10 THEN
        ->                   LEAVE myloop;
        ->          END IF;
        ->     END LOOP myloop;
        ->     SELECT 'I can count to 10';
        ->
        -> END$$
    Query OK, 0 rows affected (0.00 sec)

    mysql>
    mysql> delimiter ;
    mysql>
    mysql> call myProc();
    +-------------------+
    | I can count to 10 |
    +-------------------+
    | I can count to 10 |
    +-------------------+
    1 row in set (0.00 sec)

    Query OK, 0 rows affected (0.00 sec

帶有條件

    mysql> create procedure increment (IN in_count INT)
        -> BEGIN
        -> declare count INT default 0;
        ->
        ->     increment: loop
        ->         set count = count + 1;
        ->         if count < 20 then
        ->             iterate increment;
        ->         end if;
        ->         if count > in_count then
        ->             leave increment;
        ->         end if;
        ->     end loop increment;
        ->     select count;
        -> END
        -> //
    Query OK, 0 rows affected (0.00 sec)

    mysql>
    mysql> delimiter ;
    mysql> call increment(3);
    +-------+
    | count |
    +-------+
    |    20 |
    +-------+
    1 row in set (0.00 sec)

    Query OK, 0 rows affected (0.00 sec)