DELIMITER $$
USE `db_hisystem`$$
DROP PROCEDURE IF EXISTS `pro_getRegisterInfoIndex`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `pro_getRegisterInfoIndex`(IN thisDate VARCHAR(20), OUT thisIndex VARCHAR(30),OUT flog INT)
BEGIN
DECLARE am_count INT DEFAULT -1; /*上午已挂号人数*/
DECLARE pm_count INT DEFAULT -1; /*下午已挂号人数*/
DECLARE am_xh INT DEFAULT -1; /*上午限制号数*/
DECLARE pm_xh INT DEFAULT -1; /*下午限制号数*/
DECLARE strDate VARCHAR(10) DEFAULT "";
DECLARE strTime VARCHAR(8) DEFAULT "";
DECLARE strAMU VARCHAR(20) DEFAULT "";
DECLARE strNoon VARCHAR(20) DEFAULT "";
DECLARE strPMD VARCHAR(20) DEFAULT "";
SET thisIndex='-1';
SET flog=-1; /*flog=0 表示满 flog=-1 表示未满 */
SET strDate =LEFT(thisDate,10);
SET strTime =RIGHT(thisDate,8);
SET strAMU =CONCAT(strDate,' 00:00:00');
SET strNoon =CONCAT(strDate,' 12:00:00');
SET strPMD =CONCAT(strDate,' 23:59:59');
SELECT COUNT(*) INTO am_count FROM tb_register WHERE tb_register.rDateTime>strAMU AND tb_register.rDateTime<=strNoon GROUP BY id;
SELECT COUNT(*) INTO pm_count FROM tb_register WHERE tb_register.rDateTime>strNoon AND tb_register.rDateTime<=strPMD GROUP BY id;
SELECT nCount INTO am_xh FROM tb_XH WHERE id='am';
SELECT nCount INTO pm_xh FROM tb_XH WHERE id='pm';
/*当天上午号*/
IF UNIX_TIMESTAMP(thisDate)>UNIX_TIMESTAMP(strAMU) AND UNIX_TIMESTAMP(thisDate)<=UNIX_TIMESTAMP(strNoon) THEN
IF am_count>-1 AND am_count< am_xh THEN
SET am_count=am_count+1;
SET thisIndex= CONCAT('AM:[ ',am_count,' ]');
SELECT am_count;
ELSE
SET flog=0;/* flog=0 表示上午限号已满自动转到下午排号*/
IF pm_count>-1 AND pm_count<pm_xh THEN
SET pm_count=pm_count+1;
SET thisIndex= CONCAT('PM:[ ',pm_count,' ]');
ELSE
SET flog=1;/*flog=1 表示当日号已满 */
END IF;
END IF;
END IF;
/*当天下午号*/
IF UNIX_TIMESTAMP(thisDate)>UNIX_TIMESTAMP(strNoon) AND UNIX_TIMESTAMP(thisDate)<=UNIX_TIMESTAMP(strPMD) THEN
IF pm_count>-1 AND pm_count<pm_xh THEN
SET pm_count=pm_count+1;
SET thisIndex= CONCAT('PM:[ ',pm_count,' ]');
ELSE
SET flog=1;/*表示当日已排完*/
END IF;
END IF;
END$$
DELIMITER ;
一个简单的mysql存储过程的例子
最新推荐文章于 2024-05-06 10:39:48 发布