mysql存储过程和游标

DELIMITER //
CREATE  PROCEDURE aa(IN A_In VARCHAR(32),IN B_In VARCHAR(32),IN C_In VARCHAR(32),IN D_In VARCHAR(32))
BEGIN
DECLARE n int;
DECLARE ma int;
DECLARE AValue int;
DECLARE BValue int;
DECLARE CValue int;
DECLARE DValue int;
DECLARE Category varchar(32);
-- 需要定义接收游标数据的变量 
  DECLARE idd int;


  -- 遍历数据结束标志
  DECLARE done INT DEFAULT FALSE;
  -- 游标
  DECLARE cur CURSOR FOR SELECT id FROM hello GROUP BY hello.id;
  -- 将结束标志绑定到游标
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;


  -- 打开游标
  OPEN cur;
  
  -- 开始循环
  read_loop: LOOP
    -- 提取游标里的数据，这里只有一个，多个的话也一样；
    FETCH cur INTO idd;
    -- 声明结束的时候
    IF done THEN
      LEAVE read_loop;
    END IF; 
    -- 这里做你想做的循环的事件
set AValue=(SELECT count(count) from hello where word like A_In and id=idd);
set BValue=(SELECT count(count) from hello where word like B_In and id=idd);
set CValue=(SELECT count(count) from hello where word like C_In and id=idd);
set DValue=(SELECT count(count) from hello where word like D_In and id=idd);
set n=idd;
SELECT idd,AValue,BValue,CValue,DValue;


       INSERT INTO hello1(id,A,B,C,D)  VALUES (idd,AValue ,BValue,CValue,DValue);

set ma=(SELECT MAX(A) from  
(SELECT id, A  from hello1
union select id, B from hello1
union SELECT id, C from hello1
union SELECT id, D from hello1) b where b.id=idd) ;

set Category=(SELECT (CASE WHEN (D=ma) THEN "D" WHEN (C=ma) THEN "C" WHEN (B=ma) THEN "B"
ELSE "A" END)  from hello1 WHERE id=idd);


UPDATE hello1 set hello1.category=Category WHERE id=idd;


  END LOOP read_loop;
  -- 关闭游标
  CLOSE cur;

END
//

DELIMITER ;

------------------------------------------调用这个存储过程call aa('%二%','%一%','%一%','%三%')

mysql数据库数据：见附件

关于存储过程传数组参数，处理见https://www.cnblogs.com/bigcelestial/archive/2013/09/05/3303329.html；

------------------------------------------------关于参数有可能是多个，因为sql 不支持数组--------（条件固定是or相连）--------为了傻瓜式调用我更改----------------------------------------------------------------------------