设为首页 加入收藏

TOP

mysql procedure存储过程循环,条件判断实例
2011-06-12 21:51:20 来源: 作者: 【 】 浏览:1969次 评论:0

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)

您看到此篇文章时的感受是:
Tags: 责任编辑:administrator
】【打印繁体】【投稿】【收藏】 【推荐】【举报】【评论】 【关闭】 【返回顶部
分享到QQ空间
分享到: 
上一篇mysql 循环与条件判断sql查询语句.. 下一篇mysql 创建数据库,删除数据库实..

评论

帐  号: 密码: (新用户注册)
验 证 码:
表  情:
内  容:

相关栏目

最新文章

图片主题

热门文章

推荐文章

相关文章

广告位