mysql存储过程《2》续
通过传递节假日信息基础表主键、节日开始时间，添加或修改日期信息表中所属节日、所属节日第几天信息，达到配置节假日信息功能。
在mysql存储过程《2》中使用了loop完成此配置节假日的功能，现在使用另外两种循环方式完成此功能。
一：使用while。
DROP PROCEDURE IF EXISTS holiday; CREATE PROCEDURE holiday ( IN holiday INT, IN startTime VARCHAR (12) ) BEGIN DECLARE C INT DEFAULT 0; DECLARE dateOne VARCHAR (12); DECLARE offsetValue INT DEFAULT 1; DECLARE done INT DEFAULT FALSE; DECLARE cu CURSOR FOR SELECT date FROM util_date WHERE date >= startTime LIMIT C; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; SELECT days INTO C FROM util_holiday WHERE id = holiday; UPDATE util_date SET holidayid = '0', dayoffset = '0' WHERE YEAR = SUBSTRING(startTime, 1, 4) AND holidayid = holiday; OPEN cu; FETCH cu INTO dateOne; WHILE done = FALSE DO UPDATE util_date SET holidayid = holiday, dayoffset = offsetValue WHERE date = dateOne; SET offsetValue = offsetValue + 1; FETCH cu INTO dateOne; END WHILE; CLOSE cu; END
二：使用repeat。
DROP PROCEDURE IF EXISTS holiday; CREATE PROCEDURE holiday ( IN holiday INT, IN startTime VARCHAR (12) ) BEGIN DECLARE C INT DEFAULT 0; DECLARE dateOne VARCHAR (12); DECLARE offsetValue INT DEFAULT 1; DECLARE done INT DEFAULT FALSE; DECLARE cu CURSOR FOR SELECT date FROM util_date WHERE date >= startTime LIMIT C; DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE; SELECT days INTO C FROM util_holiday WHERE id = holiday; UPDATE util_date SET holidayid = '0', dayoffset = '0' WHERE YEAR = SUBSTRING(startTime, 1, 4) AND holidayid = holiday; OPEN cu; REPEAT FETCH cu INTO dateOne; IF done = FALSE THEN UPDATE util_date SET holidayid = holiday, dayoffset = offsetValue WHERE date = dateOne; SET offsetValue = offsetValue + 1; END IF; UNTIL done END REPEAT ; CLOSE cu; END
mysql中的while和repeat就像是java中的while和do……while。
欢迎大家阅读《mysql存储过程《二》续_mysql》,跪求各位点评，by 搞代码
搞代码网（gaodaima.com）提供的所有资源部分来自互联网，如果有侵犯您的版权或其他权益，请说明详细缘由并提供版权或权益证明然后发送到邮箱[email protected]，我们会在看到邮件的第一时间内为您处理，或直接联系QQ：872152909。本网站采用BY-NC-SA协议进行授权
转载请注明原文链接：mysql存储过程《二》续_mysql
转载请注明原文链接：mysql存储过程《二》续_mysql
