Gostaria de saber o que está errado na minha procedure. O cenário é que eu tenho valores nulos e não nulos que são campos de chave primária, estou fazendo um datawarehouse. Como todos esses valores precisam ser calculados, ao executar a procedure, caso algum dos valores que são da chave primária passa na condição para que ela não insira nulo a procedure é executada apenas na primeira linha, depois ela para, mesmo tendo outros valores para serem executados.
USE `poli_datawarehouse`;
DROP PROCEDURE IF EXISTS `quantMessage`;
DELIMITER $$
USE `poli_datawarehouse`$$
CREATE PROCEDURE `quantMessage`()
BEGIN
DECLARE fimDoCursor INT DEFAULT 0;
DECLARE customerId INT;
DECLARE channelId INT;
DECLARE userId INT;
DECLARE contactId INT;
DECLARE messageType VARCHAR(255);
DECLARE messageDir VARCHAR(2);
DECLARE createdAt DATE;
DECLARE quantMsgType INT;
DECLARE quantInsertMessage INT;
DECLARE idCustomer INT;
DECLARE quantIdCustomer INT;
DECLARE idUser INT;
DECLARE quantIdUser INT;
DECLARE idChannelCustomer INT;
DECLARE quantIdChannelCustomer INT;
DECLARE c CURSOR FOR
SELECT customer_id, channel_id, user_id, contact_id, message_type, message_dir, created_at FROM polichat.messages LIMIT 2000;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET fimDoCursor = 1;
OPEN c;
WHILE fimDoCursor = 0
DO
FETCH c INTO customerId, channelId, userId, contactId, messageType, messageDir, createdAt;
IF fimDoCursor = 0 THEN
SELECT id_customers INTO idCustomer FROM poli_datawarehouse.customers WHERE id_poli_customers = customerId;
SELECT COUNT(id_customers) INTO quantIdCustomer FROM poli_datawarehouse.customers WHERE id_poli_customers = customerId;
IF ISNULL(idCustomer) OR quantIdCustomer = 0 THEN
SELECT COUNT(id_customers) INTO quantIdCustomer FROM poli_datawarehouse.customers WHERE name = 'Não há valor referente';
IF quantIdCustomer = 0 THEN
INSERT INTO poli_datawarehouse.customers(name, created_at) VALUES ('Não há valor referente', CURDATE());
END IF;
SELECT id_customers INTO idCustomer FROM poli_datawarehouse.customers WHERE name = 'Não há valor referente';
END IF;
SELECT id_users INTO idUser FROM poli_datawarehouse.users WHERE id_poli_users = userId;
SELECT COUNT(id_users) INTO quantIdUser FROM poli_datawarehouse.users WHERE id_poli_users = userId;
IF ISNULL(idUser) OR quantIdUser = 0 THEN
SELECT COUNT(id_users) INTO quantIdUser FROM poli_datawarehouse.users WHERE name = 'Não há valor referente';
IF quantIdUser = 0 THEN
INSERT INTO poli_datawarehouse.users (name, created_at) VALUES ('Não há valor referente', CURDATE());
END IF;
SELECT id_users INTO idUser FROM poli_datawarehouse.users WHERE name = 'Não há valor referente';
END IF;
SELECT id_channel_customer INTO idChannelCustomer FROM poli_datawarehouse.channel_customers WHERE id_poli_channel_customer = channelId;
SELECT COUNT(id_channel_customer) INTO quantIdChannelCustomer FROM poli_datawarehouse.channel_customers WHERE id_poli_channel_customer = channelId;
IF ISNULL(idChannelCustomer) OR quantIdChannelCustomer = 0 THEN
SELECT COUNT(id_channel_customer) INTO quantIdChannelCustomer FROM poli_datawarehouse.channel_customers WHERE name = 'Não há valor referente';
IF quantIdCustomer = 0 THEN
INSERT INTO poli_datawarehouse.channel_customers (name, created_at) VALUES ('Não há valor referente', CURDATE());
END IF;
SELECT id_channel_customer INTO idChannelCustomer FROM poli_datawarehouse.channel_customers WHERE name = 'Não há valor referente';
END IF;
SELECT COUNT(*) INTO quantMsgType FROM polichat.messages WHERE message_type NOT LIKE 'sys%' AND message_dir COLLATE utf8mb4_unicode_ci NOT LIKE 'l'
AND DATE(created_at) = createdAt AND user_id = idUser AND customer_id = idCustomer AND message_dir LIKE messageDir AND message_type LIKE messageType
AND channel_id = idChannelCustomer AND contact_id = contactId;
SELECT COUNT(*) INTO quantInsertMessage FROM poli_datawarehouse.quant_message WHERE DATE(created_at) = createdAt AND id_user = idUser AND id_customer = idCustomer
AND message_dir LIKE messageDir AND message_type LIKE messageType AND id_channel_customer = idChannelCustomer AND id_contact = contactId;
IF quantInsertMessage = 0 THEN
INSERT INTO poli_datawarehouse.quant_message(id_customer, id_user, id_channel_customer, id_contact, message_type, message_dir, created_at, quant_msg_type)
VALUES(idCustomer, idUser, idChannelCustomer, contactId, messageType, messageDir, createdAt, quantMsgType);
END IF;
END IF;
END WHILE;
CLOSE c;
END$$
DELIMITER ;
Todos os campos do meu select formam uma única chave. O que devo fazer?