当前位置:   article > 正文

mysql 存储过程

mysql 存储过程

好久没有写过存储过程了,今天又把mysql的存储过程熟悉了一次,对于新手来说这是一个很好的demo.

具体代码如下:

  1. DROP PROCEDURE IF EXISTS `proc_demo`$$
  2. CREATE DEFINER=`root`@`%` PROCEDURE `proc_demo`()
  3. BEGIN
  4. DECLARE done INT;
  5. DECLARE pid INT;
  6. DECLARE cur CURSOR FOR SELECT DISTINCT placement_id FROM t_placement_type_rel;
  7. DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =1;
  8. OPEN cur;
  9. read_loop:LOOP
  10. FETCH cur INTO pid;
  11. IF done THEN
  12. LEAVE read_loop;
  13. END IF;
  14. INSERT INTO t_placement_type_rel(placement_id,type_id,STATUS,create_time) VALUES(pid,6,1,SYSDATE());
  15. END LOOP;
  16. END$$

创建数据库脚本代码如下:

  1. create table `t_placement_type_rel` (
  2. `id` int (11),
  3. `placement_id` int (11),
  4. `type_id` int (11),
  5. `status` int (1),
  6. `create_time` timestamp
  7. );

插入测试数据:

  1. insert into `t_placement_type_rel` (`id`, `placement_id`, `type_id`, `status`, `create_time`) values('1','1','1','0','2015-06-09 09:43:13');
  2. insert into `t_placement_type_rel` (`id`, `placement_id`, `type_id`, `status`, `create_time`) values('2','1','2','0','2015-06-09 09:43:13');
  3. insert into `t_placement_type_rel` (`id`, `placement_id`, `type_id`, `status`, `create_time`) values('3','1','3','1','2015-06-09 09:43:13');
  4. insert into `t_placement_type_rel` (`id`, `placement_id`, `type_id`, `status`, `create_time`) values('4','1','4','1','2015-06-09 09:43:13');
  5. insert into `t_placement_type_rel` (`id`, `placement_id`, `type_id`, `status`, `create_time`) values('5','2','1','1','2015-06-09 09:53:48');
  6. insert into `t_placement_type_rel` (`id`, `placement_id`, `type_id`, `status`, `create_time`) values('6','2','2','0','2015-06-09 09:53:48');
  7. insert into `t_placement_type_rel` (`id`, `placement_id`, `type_id`, `status`, `create_time`) values('7','2','3','0','2015-06-09 09:53:48');
  8. insert into `t_placement_type_rel` (`id`, `placement_id`, `type_id`, `status`, `create_time`) values('8','2','4','1','2015-06-09 09:53:48');
  9. insert into `t_placement_type_rel` (`id`, `placement_id`, `type_id`, `status`, `create_time`) values('9','3','1','0','2015-06-09 10:12:45');
  10. insert into `t_placement_type_rel` (`id`, `placement_id`, `type_id`, `status`, `create_time`) values('10','3','2','0','2015-06-09 10:12:45');
  11. insert into `t_placement_type_rel` (`id`, `placement_id`, `type_id`, `status`, `create_time`) values('11','3','3','0','2015-06-09 10:12:45');
  12. insert into `t_placement_type_rel` (`id`, `placement_id`, `type_id`, `status`, `create_time`) values('12','3','4','1','2015-06-09 10:12:45');
  13. insert into `t_placement_type_rel` (`id`, `placement_id`, `type_id`, `status`, `create_time`) values('13','4','1','0','2015-06-09 10:19:29');
  14. insert into `t_placement_type_rel` (`id`, `placement_id`, `type_id`, `status`, `create_time`) values('14','4','2','1','2015-06-09 10:19:29');
  15. insert into `t_placement_type_rel` (`id`, `placement_id`, `type_id`, `status`, `create_time`) values('15','4','3','0','2015-06-09 10:19:29');
  16. insert into `t_placement_type_rel` (`id`, `placement_id`, `type_id`, `status`, `create_time`) values('16','4','4','1','2015-06-09 10:19:29');
  17. insert into `t_placement_type_rel` (`id`, `placement_id`, `type_id`, `status`, `create_time`) values('17','5','1','0','2015-06-09 14:39:12');
  18. insert into `t_placement_type_rel` (`id`, `placement_id`, `type_id`, `status`, `create_time`) values('18','5','2','0','2015-06-09 14:39:12');
  19. insert into `t_placement_type_rel` (`id`, `placement_id`, `type_id`, `status`, `create_time`) values('19','5','3','1','2015-06-09 14:39:12');
  20. insert into `t_placement_type_rel` (`id`, `placement_id`, `type_id`, `status`, `create_time`) values('20','5','4','1','2015-06-09 14:39:12');
  21. insert into `t_placement_type_rel` (`id`, `placement_id`, `type_id`, `status`,
声明:本文内容由网友自发贡献,不代表【wpsshop博客】立场,版权归原作者所有,本站不承担相应法律责任。如您发现有侵权的内容,请联系我们。转载请注明出处:https://www.wpsshop.cn/w/空白诗007/article/detail/785539
推荐阅读
相关标签
  

闽ICP备14008679号