赞
踩
好久没有写过存储过程了,今天又把mysql的存储过程熟悉了一次,对于新手来说这是一个很好的demo.
具体代码如下:
- DROP PROCEDURE IF EXISTS `proc_demo`$$
-
- CREATE DEFINER=`root`@`%` PROCEDURE `proc_demo`()
- BEGIN
- DECLARE done INT;
- DECLARE pid INT;
- DECLARE cur CURSOR FOR SELECT DISTINCT placement_id FROM t_placement_type_rel;
- DECLARE CONTINUE HANDLER FOR NOT FOUND SET done =1;
- OPEN cur;
- read_loop:LOOP
- FETCH cur INTO pid;
- IF done THEN
- LEAVE read_loop;
- END IF;
- INSERT INTO t_placement_type_rel(placement_id,type_id,STATUS,create_time) VALUES(pid,6,1,SYSDATE());
- END LOOP;
- END$$

- create table `t_placement_type_rel` (
- `id` int (11),
- `placement_id` int (11),
- `type_id` int (11),
- `status` int (1),
- `create_time` timestamp
- );
- 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');
- 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');
- 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');
- 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');
- 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');
- 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');
- 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');
- 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');
- 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');
- 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');
- 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');
- 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');
- 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');
- 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');
- 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');
- 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');
- 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');
- 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');
- 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');
- 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');
- insert into `t_placement_type_rel` (`id`, `placement_id`, `type_id`, `status`,

Copyright © 2003-2013 www.wpsshop.cn 版权所有,并保留所有权利。