一个简单的mysql存储过程的例子

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 ;