mysql-loop

DELIMITER $$

 

CREATE PROCEDURE build_email_list (INOUT email_list varchar(4000))

BEGIN

 

DECLARE v_finished INTEGER DEFAULT 0;

        DECLARE v_email varchar(100) DEFAULT "";

 

-- declare cursor for employee email

DEClARE email_cursor CURSOR FOR

SELECT email FROM employees;

 

-- declare NOT FOUND handler

DECLARE CONTINUE HANDLER

        FOR NOT FOUND SET v_finished = 1;

 

OPEN email_cursor;

 

get_email: LOOP

 

FETCH email_cursor INTO v_email;

 

IF v_finished = 1 THEN

LEAVE get_email;

END IF;

 

-- build email list

SET email_list = CONCAT(v_email,";",email_list);

 

END LOOP get_email;

 

CLOSE email_cursor;

 

END$$

 

DELIMITER ;

DECLARE col_names CURSOR FOR
SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS
WHERE table_name = 'employee'
ORDER BY ordinal_position;
OPEN col_names;
select found_rows() into num_rows;
set i=1;
set @updated='updated columns:';
set logs='';

the_loop: LOOP      
IF i > num_rows THEN
LEAVE the_loop;
END IF;
FETCH col_names INTO col_name;
SET @updated = CONCAT(@updated, 'OLD', ' changed into ', 'NEW', ' ');
set oldvalue=concat(col_name);
set RealOldValue=oldValue;
insert into CHANGE_LOG(DESCRIPT) values (RealOldvalue);
SET i = i + 1;
END LOOP the_loop;

CLOSE col_names;

xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
delimiter $$
CREATE PROCEDURE curdemo()
BEGIN
  DECLARE done INT DEFAULT FALSE;
  DECLARE a CHAR(16);
  DECLARE b, c INT;
  DECLARE cur1 CURSOR FOR SELECT id,data FROM hr.employee;
  DECLARE cur2 CURSOR FOR SELECT i FROM hr.payroll;
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

  OPEN cur1;
  OPEN cur2;

  read_loop: LOOP
    FETCH cur1 INTO a, b;
    FETCH cur2 INTO c;
    IF done THEN
      LEAVE read_loop;
    END IF;
    IF b < c THEN
      INSERT INTO test.t3 VALUES (a,b);
    ELSE
      INSERT INTO test.t3 VALUES (a,c);
    END IF;
  END LOOP;

  CLOSE cur1;
  CLOSE cur2;
END;
$$
delimiter ;