mysql 简单存储游标应用

DELIMITER $$

 

USE `sigao_dftx`$$

 

DROP PROCEDURE IF EXISTS `pass_result`$$

 

CREATE DEFINER=`root`@`localhost` PROCEDURE `pass_result`(

titleName VARCHAR(5000)

)

BEGIN

DECLARE result_value VARCHAR(5000);  

DECLARE user_id VARCHAR(50); 

DECLARE question_id BIGINT(20);

DECLARE targetuser VARCHAR(80); 

DECLARE done INT DEFAULT FALSE;

DECLARE uqCursor CURSOR FOR SELECT o.result,o.userid,o.questionid,o.targetuser FROM userquestion o,sendemailaftercompleted sm,title t WHERE o.targetuser =sm.email AND sm.titleid = t.id AND t.`name`=titleName LIMIT 300;  

## 将结束标志绑定到游标

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;

TRUNCATE  user_topic_score;

##打开游标

OPEN uqCursor;

##开始循环

read_loop: LOOP

    ##提取游标里的数据

    FETCH uqCursor INTO result_value,user_id,question_id,targetuser;

    ## 声明结束的时候

    IF done THEN

      LEAVE read_loop;

    END IF;

     ## 提取用户题目和答案

     CALL proc_split(result_value,';',user_id,question_id,targetuser);

     END LOOP;

  ## 关闭游标

  CLOSE uqCursor;

END$$

 

DELIMITER ;

 

 

DELIMITER $$

 

USE `sigao_dftx`$$

 

DROP PROCEDURE IF EXISTS `proc_split`$$

 

CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_split`(

    inputstring VARCHAR(5000),

    delim VARCHAR(20),

    user_id VARCHAR(50),

    question_id BIGINT(20),

    targetuser VARCHAR(80)

)

BEGIN

    DECLARE strlen INT;

    DECLARE last_index INT;

    DECLARE topicId VARCHAR(50);

    DECLARE resultId VARCHAR(50);

    DECLARE cur_index INT;

    DECLARE cur_char VARCHAR(200);

    DECLARE len INT;

    DECLARE T_R VARCHAR(50);

    SET cur_index=1;

    SET last_index=0;

    SET strlen=LENGTH(inputstring);  

    

    WHILE(cur_index<=strlen) DO    

    BEGIN

        IF SUBSTRING(inputstring FROM cur_index FOR 1)=delim OR cur_index=strlen THEN

            SET len=cur_index-last_index-1;

            IF cur_index=strlen THEN

               SET len=len+1;

            END IF;

            SET  T_R=SUBSTRING(inputstring FROM (last_index+1) FOR len);

   SET  T_R= REPLACE(T_R,'TopicId:','');

   SET  T_R=REPLACE(T_R,'Result:','');

   SET  T_R=REPLACE(T_R,';','');

   SET  topicId=SUBSTRING(T_R FROM 1  FOR LOCATE('$', T_R)-1);

   SET  resultId=SUBSTRING(T_R FROM (LOCATE('$', T_R)+1));

   ##select topic_Id,result_id;

   ##SET  T_R=replace(replace(REPLACE(T_R,';',''),'TopicId:','')),'Result:','');

            ##INSERT INTO user_topic_score(topic_result,user_id,question_id) VALUES(SUBSTRING(inputstring FROM (last_index+1) FOR len),user_id,question_id);

            INSERT INTO user_topic_score(user_id,question_id,topic_id,result_id,targetuser) VALUES(user_id,question_id,topicId,resultId,targetuser);

            SET last_index=cur_index;

        END IF;

        SET cur_index=cur_index+1;

    END;

    END WHILE;

END$$

 

DELIMITER ;