My experience on my daily works... helping others ease each other

Wednesday, August 17, 2011

MySQL - Creating a loop in function

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....
Share:

0 comments:

About Me

Somewhere, Selangor, Malaysia
An IT by profession, a beginner in photography

Blog Archive

Blogger templates