mysql一次存储过程

mysql 神圣 5年前 (2015-11-22) 852次浏览 0个评论

有两张表

A表

CREATE TABLE `class` (
`classid` int(11) NOT NULL AUTO_INCREMENT COMMENT ‘主键’,
`one` varchar(255) DEFAULT NULL,
`two` varchar(255) DEFAULT NULL,
`three` varchar(255) DEFAULT NULL,
`four` varchar(255) DEFAULT NULL,
PRIMARY KEY (`classid`)
) ENGINE=InnoDB AUTO_INCREMENT=70 DEFAULT CHARSET=utf8;

B表

CREATE TABLE `students` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`student` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

要求把B表中的student字段中每四个一组插入A表中的one、two、three、four字段中 数据不够用jiaoshi补齐

用存储过程实现

我的一个懒做法

CREATE DEFINER=`root`@`localhost` PROCEDURE `insss`()
BEGIN
#Routine body goes here…
DECLARE pageSize INT DEFAULT 4;
DECLARE pageCount INT DEFAULT 0;
DECLARE pageIndex INT DEFAULT 1;
DECLARE dateCount INT DEFAULT 0;
DECLARE j INT DEFAULT 1;
DECLARE i INT DEFAULT 1;
DECLARE num INT DEFAULT 1;
DECLARE m INT DEFAULT 1;
DECLARE one VARCHAR(255) DEFAULT ‘jiaoshi’;
DECLARE two VARCHAR(255) DEFAULT ‘jiaoshi’;
DECLARE three VARCHAR(255) DEFAULT ‘jiaoshi’;
DECLARE four VARCHAR(255) DEFAULT ‘jiaoshi’;
DECLARE mystudent VARCHAR(255) DEFAULT ‘jiaoshi’;
DECLARE cur CURSOR FOR SELECT students.student FROM students ;

SELECT COUNT(*) INTO num FROM students ;

SELECT COUNT(students.id) INTO dateCount FROM students;
SET pageCount = (num+pageSize-1)/pageSize;
SET m=pageCount*pageSize;
OPEN cur;
WHILE i <= num DO

SET pageIndex = (i+pageSize-1)/pageSize;
IF i <=pageSize THEN
SET pageIndex=1;
END if;

FETCH cur INTO mystudent;
if i = 1+(pageIndex-1)*pageSize THEN
SET one = mystudent;
SET two = ‘jiaoshi’;
SET three = ‘jiaoshi’;
SET four = ‘jiaoshi’;
ELSEIF i = 2+(pageIndex-1)*pageSize THEN
SET two = mystudent;
SET three = ‘jiaoshi’;
SET four = ‘jiaoshi’;
ELSEIF i = 3+(pageIndex-1)*pageSize THEN
SET three = mystudent;
SET four = ‘jiaoshi’;
ELSEIF i = 4+(pageIndex-1)*pageSize THEN
SET four = mystudent;
end if;

IF i%4=0 THEN

insert into class(class.one,class.two,class.three,class.four) values(one,two,three,four);
ELSEIF i=num THEN
insert into class(class.one,class.two,class.three,class.four) values(one,two,three,four);
END IF;
SET i=i+1;
END WHILE;

CLOSE cur;

END

喜欢 (0)

您必须 登录 才能发表评论!