DELIMITER $$DROP PROCEDURE IF EXISTS explode_table $$
CREATE PROCEDURE explode_table(bound VARCHAR(255))BEGIN
DECLARE uid INT DEFAULT 0;
DECLARE user TEXT;
DECLARE occurance INT DEFAULT 0;
DECLARE i INT DEFAULT 0;
DECLARE splitted_user TEXT;
DECLARE portfolio_admins TEXT;
DECLARE portfolio_mgrs TEXT;
DECLARE service_finance_pocs TEXT;
DECLARE program TEXT;
DECLARE MIPR TEXT;
DECLARE alert TEXT;
DECLARE status TEXT;
DECLARE timestamp TIMESTAMP;
DECLARE done INT DEFAULT 0;
DECLARE cur1 CURSOR FOR SELECT alerts.uid, alerts.service_project_mgrs,
alerts.portfolio_admins, alerts.portfolio_mgrs,
alerts.service_finance_pocs, alerts.program,
alerts.MIPR, alerts.alert, alerts.status, alerts.timestamp
FROM alerts
WHERE alerts.service_project_mgrs != ”;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DROP TEMPORARY TABLE IF EXISTS table2;
CREATE TEMPORARY TABLE table2(
`uid` INT NOT NULL,
`user` VARCHAR(255) NOT NULL,
`portfolio_admins` VARCHAR(255),
`portfolio_mgrs` VARCHAR(255),
`service_finance_pocs` VARCHAR(255),
`program` VARCHAR(255),
`MIPR` VARCHAR(255),
`alert` VARCHAR(255),
`status` VARCHAR(255),
`timestamp` TIMESTAMP
) ENGINE=Memory;
OPEN cur1;
read_loop: LOOP
FETCH cur1 INTO uid, user, portfolio_admins, portfolio_mgrs, service_finance_pocs, program, MIPR, alert, status, timestamp;
IF done THEN
LEAVE read_loop;
END IF;
SET occurance = (SELECT LENGTH(user)
– LENGTH(REPLACE(user, bound, ”))
+1);
SET i=1;
WHILE i <= occurance DO
SET splitted_user = (SELECT REPLACE(SUBSTRING(SUBSTRING_INDEX(user, bound, i),
LENGTH(SUBSTRING_INDEX(user, bound, i – 1)) + 1), ‘,’, ”));
INSERT INTO table2 VALUES (uid, splitted_user, portfolio_admins, portfolio_mgrs, service_finance_pocs,
program, MIPR, alert, status, timestamp);
SET i = i + 1;
END WHILE;
END LOOP;
SELECT * FROM table2;
CLOSE cur1;
END; $$
viztales 
You must be logged in to post a comment.