mysql function cursor,mysql function with cursor

-- --------------------------------------------------------------------------------

-- Routine DDL

-- Note: comments before and after the routine body will not be stored by the server

-- --------------------------------------------------------------------------------

DELIMITER $$

CREATE DEFINER=`root`@`localhost` FUNCTION `get_product_warrany_type`(

pid int

) RETURNS varchar(1024) CHARSET utf8

BEGIN

declare v1 nvarchar(256) ;

declare return_value nvarchar(1024) default '';

DECLARE done INT DEFAULT 0; ##

declare cursor1 cursor for

select warranty_type from warranty_info,product_warranty

where warranty_info.warranty_id = product_warranty.warranty_id

and product_warranty.product_id = pid;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; # must declare after cursor

open cursor1;

Loop1: loop

fetch cursor1 into v1;

IF done = 1 THEN# must after fetch in loop

LEAVE Loop1;

END IF;

if (length(return_value) = 0) then

set return_value = v1;

else

set return_value = concat(return_value,',',v1);

END if;

END loop Loop1;

CLOSE cursor1;

RETURN return_value;

END