使用游标的 mysql 过程

Question

DELIMITER $$

CREATE PROCEDURE INSERT_NONE_HISTORY_CHECKBOX()
BEGIN
  DECLARE note_id bigint(20);

  FOR c1 IN 
    (SELECT question_id 
     FROM question_master 
     WHERE question_type LIKE '%check box%') 
  LOOP

    SELECT note_section_id INTO note_id 
    FROM answer_master 
    WHERE question_id = c1.question_id 
    LIMIT 1;

    INSERT INTO answer_master(QUESTION_ID, NOTE_SECTION_ID, ANSWER_TEXT
      , ROS_INPUT_TEXT, HAS_CHILD_QUES, MEDICATIONS_LIST_ID, STATUS_CODE)
    VALUES(c1.question_id,note_id,'none',null,0,null,1);

  END LOOP;

END $$

DELIMITER ;

我收到类似::的错误

Script line: 3 You have an error in your SQL syntax; check the manual that corresponds to >your MySQL server version for the right syntax to use near 'for c1 in (select question_id >from question_master where question_type like '%ch' at line 6

我做错了什么？

Answer 1

我认为 MySQL 不支持 FOR IN 语法，您必须使用它声明游标和循环。

DELIMITER $$

CREATE PROCEDURE INSERT_NONE_HISTORY_CHECKBOX()
BEGIN
  DECLARE note_id bigint(20);
  DECLARE Myquestion_id INTEGER;
  DECLARE done BOOLEAN DEFAULT 0; //loop variable
  DECLARE cur1 CURSOR FOR 
    SELECT question_id 
    FROM question_master 
    WHERE question_type LIKE '%check box%'; //declare the cursor
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;  //stop when done.

  OPEN cur1;  //Open it.
  insert_loop: LOOP

    FETCH cur1 INTO myquestion_id;
    IF done THEN LEAVE insert_loop; END IF;
    SELECT note_section_id INTO note_id 
    FROM answer_master 
    WHERE question_id = c1.question_id 
    LIMIT 1;

    INSERT INTO answer_master(QUESTION_ID, NOTE_SECTION_ID, ANSWER_TEXT
      , ROS_INPUT_TEXT, HAS_CHILD_QUES, MEDICATIONS_LIST_ID, STATUS_CODE)
    VALUES(myquestion_id,note_id,'none',null,0,null,1);

  END LOOP;
  CLOSE cur1;

END $$

DELIMITER ;

语法有点麻烦，但是应该可以。

参见:http://dev.mysql.com/doc/refman/5.0/en/cursors.html