salix/db/routines/vn/procedures/collection_setState.sql

DELIMITER $$
CREATE OR REPLACE DEFINER=`vn`@`localhost` PROCEDURE `vn`.`collection_setState`(vSelf INT, vStateCode VARCHAR(255) COLLATE utf8_general_ci)
BEGIN
/**
 * Modifica el estado de los tickets de una colección.
 *
 * @param vSelf el id del colección
 * @param vStateCode estado a modificar de los tickets
 */
	DECLARE vTicketFk INT;
	DECLARE vDone INT DEFAULT FALSE;
	DECLARE vCursor CURSOR FOR
		SELECT DISTINCT ticketFk
			FROM ticketCollection tc 
			WHERE tc.collectionFk  = vSelf;

	DECLARE CONTINUE HANDLER FOR NOT FOUND SET vDone = TRUE;
	DECLARE EXIT HANDLER FOR SQLEXCEPTION
	BEGIN
		ROLLBACK;
		RESIGNAL;
	END;

	START TRANSACTION;

	OPEN vCursor;

		read_loop: LOOP

		FETCH vCursor INTO vTicketFk;

		IF vDone THEN
			LEAVE read_loop;
		END IF;

		CALL ticket_setState(vTicketFk, vStateCode);

		END LOOP;

	CLOSE vCursor;

	COMMIT;
END$$
DELIMITER ;