mysql 的存储过程 循环 变更某个表里的字段

/*
Navicat MySQL Data Transfer

Source Server : localhost_3306
Source Server Version : 50505
Source Host : localhost:3306
Source Database : kamanage

Target Server Type : MYSQL
Target Server Version : 50505
File Encoding : 65001

Date: 2018-09-11 13:50:36
*/

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for `students_tow`
-- ----------------------------
DROP TABLE IF EXISTS `students_tow`;
CREATE TABLE `students_tow` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=44 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of students_tow
-- ----------------------------
INSERT INTO `students_tow` VALUES ('16', 'songqiaoli', '1');
INSERT INTO `students_tow` VALUES ('17', 'zhangpeng', '8');
INSERT INTO `students_tow` VALUES ('18', 'zhangchenghao', '8');
INSERT INTO `students_tow` VALUES ('19', 'songqiaoli', '2');
INSERT INTO `students_tow` VALUES ('20', 'zhangpeng', '8');
INSERT INTO `students_tow` VALUES ('21', 'zhangchenghao', '8');
INSERT INTO `students_tow` VALUES ('22', 'songqiaoli', '3');
INSERT INTO `students_tow` VALUES ('23', 'zhangpeng', '8');
INSERT INTO `students_tow` VALUES ('24', 'zhangchenghao', '8');
INSERT INTO `students_tow` VALUES ('25', 'songqiaoli', '4');
INSERT INTO `students_tow` VALUES ('26', 'zhangpeng', '8');
INSERT INTO `students_tow` VALUES ('27', 'zhangchenghao', '8');
INSERT INTO `students_tow` VALUES ('29', 'songqiaoli', '5');
INSERT INTO `students_tow` VALUES ('30', 'zhangpeng', '8');
INSERT INTO `students_tow` VALUES ('31', 'zhangchenghao', '8');
INSERT INTO `students_tow` VALUES ('32', 'songqiaoli', '6');
INSERT INTO `students_tow` VALUES ('33', 'zhangpeng', '8');
INSERT INTO `students_tow` VALUES ('34', 'zhangchenghao', '8');
INSERT INTO `students_tow` VALUES ('35', 'songqiaoli', '7');
INSERT INTO `students_tow` VALUES ('36', 'zhangpeng', '8');
INSERT INTO `students_tow` VALUES ('37', 'zhangchenghao', '8');
INSERT INTO `students_tow` VALUES ('38', 'songqiaoli', '8');
INSERT INTO `students_tow` VALUES ('39', 'zhangpeng', '8');
INSERT INTO `students_tow` VALUES ('40', 'zhangchenghao', '8');

 

 

 

================================================================以上是数据==========================================

 

 

要求，把姓名叫zhangpeng  的人的age,从1开始     累数的+1

 

create PROCEDURE changeAge ()
　　begin
　　　　　　declare index_id varchar(20);
　　　　　　declare named varchar(20);
　　　　　　declare age int (11);
　　　　　　declare change_age int(1) default 1;
　　　　　　declare end_tag int default 0;
　　　　　　declare list CURSOR for SELECT id,name,age from students_tow where name='zhangpeng';
　　　　　　DECLARE CONTINUE HANDLER for not FOUND SET end_tag=1;
　　　　　　open list;
　　　　　　repeat
　　　　　　fetch list into index_id,named,age;

　　　　　　　　if not end_tag then
　　　　　　　　　　　　　　select change_age;
　　　　　　　　　　　　　　update students_tow set age=change_age where id=index_id;
　　　　　　　　end if;
　　　　　　set change_age=change_age+1 ;
　　　　　　until end_tag end repeat;

　　end;

call changeAge();
drop PROCEDURE changeAge

SELECT name,age from students_tow where name='zhangpeng'

转载于:https://www.cnblogs.com/songqiaoli/p/9627169.html