mysql 存储过程

最新推荐文章于 2024-11-01 14:31:36 发布

爱吃肉的兔儿

最新推荐文章于 2024-11-01 14:31:36 发布

阅读量493

点赞数

分类专栏： MySQL 文章标签： mysql

本文链接：https://blog.csdn.net/sampson_lee/article/details/50393629

版权

本文回顾了MySQL存储过程，适合新手学习。通过具体的代码示例，详细介绍了如何创建和使用存储过程。

摘要由CSDN通过智能技术生成

好久没有写过存储过程了，今天又把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`,