MySQL supports the IF, CASE, ITERATE, LEAVE LOOP, WHILE, and REPEAT constructs for flow control within stored programs. It also supports RETURN within stored functions.
Suppose we have user table
CREATE TABLE IF NOT EXISTS `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`username` varchar(20) NOT NULL,
`pwd` varchar(64) NOT NULL,
`email` varchar(50) NOT NULL,
`lasttime` date NOT NULL,
`term` varchar(20) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
INSERT INTO `user` (`id`, `username`, `pwd`, `email`, `lasttime`, `term`) VALUES
(1, 'admin', '123456', 'admin@example.com', '2014-07-01', 'summer'),
(2, 'jiansen', '123456', 'jiansen@example.com', '2014-02-03', 'spring'),
(3, 'tom', '333333', 'tom@example.com', '2014-10-09', 'winter');
We want to convert lasttime (date format) to spring, summer or winter term.
1) SELECT in control flow in MySQL
SELECT CASE
WHEN MONTH(DATE(lasttime))>= 1 AND MONTH(DATE(lasttime))<=4 THEN
'spring'
WHEN MONTH(DATE(lasttime))>= 5 AND MONTH(DATE(lasttime))<=8 THEN
'summer'
WHEN MONTH(DATE(lasttime))>= 9 AND MONTH(DATE(lasttime))<=12 THEN
'winter'
END AS Term
FROM user
2) UPDATE in control flow in MySQL
UPDATE user SET term=CASE
WHEN MONTH(DATE(lasttime))>= 1 AND MONTH(DATE(lasttime))<=4 THEN
'spring'
WHEN MONTH(DATE(lasttime))>= 5 AND MONTH(DATE(lasttime))<=8 THEN
'summer'
WHEN MONTH(DATE(lasttime))>= 9 AND MONTH(DATE(lasttime))<=12 THEN
'winter'
END
Video: Example of control flow to select and update in MySQL
