I was looking for foreach loop to be used in MySQL database and found a simple article written in MySQL development group. In MySQL, it does not have foreach loop but instead replace in with LOOP.
Based on that, I create my looping function and would like to share with you guys... Check it out the code below :)
DELIMITER $$
DROP FUNCTION IF EXISTS `myFunc` $$
CREATE DEFINER=`mydb`@`%` FUNCTION `myFunc`(p_id varchar(15), m_id varchar(2), q_id varchar (2), years varchar(4), r_id varchar(5), d_id varchar(10), s_id varchar(10), cws varchar(10)) RETURNS int(11)
READS SQL DATA
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE jsid VARCHAR(2);
DECLARE jsd VARCHAR(100);
DECLARE cur1 CURSOR FOR SELECT job_id, job_desc FROM mydb.t_job_sector where cws like concat(concat('%',job_id),'%');
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO jsid, jsd;
IF done THEN
LEAVE read_loop;
END IF;
IF jsid IS NOT NULL AND jsd IS NOT NULL THEN
INSERT INTO mydb.t_work_choice VALUES (p_id, jsid, jsd, r_id, d_id, s_id);
END IF;
END LOOP;
CLOSE cur1;
RETURN 0;
END $$
DELIMITER ;
.... hopes that helps others too....
0 comments:
Post a Comment