Example

 

Let’s look at an example:

 

Suppose we want to get the names and genders of all employees in our employees table and combine them into a single line of text, here’s how we do it:

 

DELIMITER $$

CREATE FUNCTION get_employees () RETURNS VARCHAR(255) DETERMINISTIC

BEGIN

DECLARE v_employees VARCHAR(255) DEFAULT '';

DECLARE v_name VARCHAR(255);

DECLARE v_gender CHAR(1);

DECLARE v_done INT DEFAULT 0;

DECLARE cur CURSOR FOR

      SELECT em_name, gender FROM employees;

 

DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_done = 1;

 

OPEN cur;

 

employees_loop: LOOP

      FETCH cur INTO v_name, v_gender;

      IF v_done = 1 THEN LEAVE employees_loop;

            ELSE SET v_employees = concat(v_employees, ', ', v_name, ': ', v_gender...