Mysql存储过程案例

#删除

DROP PROCEDURE IF EXISTS `p_house_set_premit`;

#创建获取房间最低价格和统计房间数存储过程

DELIMITER //

CREATE DEFINER=`dev_stand_admin`@`%` PROCEDURE `p_house_set_premit`(in in_house_info_id VARCHAR(50))

COMMENT '获取房间最低价格和统计房间数'

BEGIN

#定义变量

DECLARE new_price double;#最低价

DECLARE newcount INT;#房间数

#变量赋值

select rental into new_price from lend_room_info where area_type='1' and house_info_id=in_house_info_id ORDER BY rental limit 1;

select count(*) as count into newcount from lend_room_info where area_type='1' and house_info_id=in_house_info_id;

#执行update

update lend_house_info set room_count=newcount,lower_price=new_price where id=in_house_info_id;

commit;

END;//

#执行

call p_house_set_premit(5);