Stored procedure to parse long string into tokens by using delimiter

DELIMITER $$

DROP PROCEDURE IF EXISTS `datawarehouse`.`aggregateStudent`$$

CREATE DEFINER=`root`@`%` PROCEDURE `aggregateStudent`(IN adata LONGTEXT)

BEGIN

DECLARE tempData LONGTEXT;

DECLARE fulldata LONGTEXT;

DECLARE st1 LONGTEXT;

DECLARE sValue LONGTEXT ;

DECLARE rFulldata LONGTEXT;

DECLARE firstrecord LONGTEXT ;

DECLARE len INT ;

DECLARE recordsize INT ;

DECLARE studentid VARCHAR(50);

DECLARE subjectid VARCHAR(50);

DECLARE chapterID VARCHAR(50);

DECLARE formLevel VARCHAR(50);

DECLARE classlevel VARCHAR(50);

DECLARE schoolid VARCHAR(50);

DECLARE tempSchool VARCHAR(50);

DECLARE score DOUBLE(12,2);

DECLARE nomore_data INT DEFAULT 0;

DECLARE flen INT ;

DECLARE ids INT ;

DECLARE no_schoolinfo INT DEFAULT 0;

SET fulldata=adata;

SET firstrecord= SUBSTRING(fulldata,1,INSTR(fulldata,'**')-1);

SET rFulldata= SUBSTRING(fulldata,LENGTH(firstrecord)+3,LENGTH(fulldata));

SET tempData=firstrecord;

SET recordsize=LENGTH(firstrecord);

CREATE TABLE IF NOT EXISTS tempstore1 (v1 VARCHAR(50));

firstLoop: WHILE (recordsize > 1) DO

SET sValue= SUBSTRING(tempData,1,INSTR(tempData,',')-1);

SET st1= SUBSTRING(tempData,LENGTH(sValue)+2,LENGTH(tempData));

SET tempData=st1;

SET len = LENGTH(sValue);

WHILE ( len > 1) DO

INSERT INTO tempstore1 VALUES(sValue);

SET sValue= SUBSTRING(tempData,1,INSTR(tempData,',')-1);

SET st1= SUBSTRING(tempData,LENGTH(sValue)+2,LENGTH(tempData));

SET tempData=st1;

SET len = LENGTH(sValue);

END WHILE ;

SELECT v1 INTO studentid FROM tempstore1 LIMIT 0,1;

SELECT v1 INTO subjectid FROM tempstore1 LIMIT 1,1;

SELECT v1 INTO chapterID FROM tempstore1 LIMIT 2,1;

SELECT v1 INTO score FROM tempstore1 LIMIT 3,1;

DELETE FROM tempstore1 ;

BEGIN

DECLARE schoolInfo CURSOR FOR

SELECT s.code,s.form_id,s.school_id FROM school_ilms.classroom s WHERE s.id IN

(SELECT a.classroom_id FROM school_ilms.classroom_student a WHERE a.students_id IN

( SELECT u.id FROM school_ilms.user_ilms u WHERE u.registrationNo=studentid))AND s.term_id IN (SELECT YEAR(CURDATE()));

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_schoolinfo=1;

OPEN schoolInfo ;

SET no_schoolinfo =0 ;

WHILE (no_schoolinfo <>

FETCH schoolInfo INTO classlevel,formLevel,tempSchool ;

SELECT CODE INTO schoolid FROM school_ilms.school WHERE id= tempSchool ;

END WHILE ;

CLOSE schoolInfo;

INSERT INTO tempstore1 VALUES(classlevel);

INSERT INTO tempstore1 VALUES(formLevel);

INSERT INTO tempstore1 VALUES(schoolid);

END ;

SELECT v1 INTO classlevel FROM tempstore1 LIMIT 0,1;

SELECT v1 INTO formLevel FROM tempstore1 LIMIT 1,1;

SELECT v1 INTO schoolid FROM tempstore1 LIMIT 2,1;

CALL getSchoolDetails(studentid,subjectid,chapterID,formLevel,classlevel,score,schoolid);

DELETE FROM tempstore1 ;

SET firstrecord= SUBSTRING(rFulldata,1,INSTR(rFulldata,'**')-1);

SET rFulldata= SUBSTRING(rFulldata,LENGTH(firstrecord)+3,LENGTH(rFulldata));

SET tempData=firstrecord;

SET recordsize = LENGTH(firstrecord);

END WHILE firstLoop;

CALL update_classroom_level_assessment();

CALL update_subject_assessment();

CALL update_chapter_assessment();

CALL update_classlevel_assessment();

CALL update_district_assessment();

CALL update_state_assessment();

DROP TABLE IF EXISTS tempstore1;

END$$

DELIMITER ;

sample data for the above code:

aggregateStudent('ST810317015813,SK00002Form4B,Form4,SUB0002044,T0002044010100,

90,SK00002,**ST810317015813,SK00002Form4B,Form4,SUB0001044,T0001044010100,86,

SK00002,**ST810831083223,SK00003Form4C,Form4,SUB0001044,T0001044030700,30,

SK00003,**ST810818083223,SK00001Form4A,Form4,SUB0001044,T0001044030700,30,

SK00001,**');