CREATE PROCEDURE COLTOROW(id VARCHAR(5))
begin
DECLARE count1 INT;
DECLARE count2 INT;
DECLARE BBNBKANRIBG VARCHAR(5);
DECLARE sql_ VARCHAR(5000) DEFAULT '';
DECLARE SEIKYUNEGAPI DATE;
DECLARE KAISYUUHI DATE;
DECLARE cur1 CURSOR FOR SELECT BBNBKANRIBG,SEIKYUNEGAPI,KAISYUUHI FROM managesystem.m016_tbl WHERE BBNBKANRIBG=id;
SET sql_ = 'CREATE TEMPORARY TABLE tmp_table (BBNBKANRIBG VARCHAR(5) , ';
SELECT COUNT(*) INTO count1 FROM managesystem.m016_tbl WHERE BBNBKANRIBG=id;
SET count2 = count;
WHILE(count1 > 0)
SET sql_ = sql_ + 'SEIKYUNEGAPI'+count1 + ' DATETIME,KAISYUUHI'+count1 + ' DATETIME';
SET count1 =count1 -1;
END WHILE;
SET sql_ = sql_ + ')';
PREPARE stmt FROM sql_ ;
execute stmt;
select sql_;
SELECT * FROM tmp_table; OPEN cur1; WHILE(count2 > 0)
FETCH cur1 INTO BBNBKANRIBG,SEIKYUNEGAPI,KAISYUUHI;
IF count2= 1 THEN
INSERT INTO tmp_table(BBNBKANRIBG) VALUES (BBNBKANRIBG);
ELSE
INSERT INTO tmp_table(SEIKYUNEGAPI+count2,KAISYUUHI+count2) VALUES (SEIKYUNEGAPI,KAISYUUHI);
END IF;
SET count2 =count2 -1;
END WHILE; SELECT * FROM tmp_table
end;
begin
DECLARE count1 INT;
DECLARE count2 INT;
DECLARE BBNBKANRIBG VARCHAR(5);
DECLARE sql_ VARCHAR(5000) DEFAULT '';
DECLARE SEIKYUNEGAPI DATE;
DECLARE KAISYUUHI DATE;
DECLARE cur1 CURSOR FOR SELECT BBNBKANRIBG,SEIKYUNEGAPI,KAISYUUHI FROM managesystem.m016_tbl WHERE BBNBKANRIBG=id;
SET sql_ = 'CREATE TEMPORARY TABLE tmp_table (BBNBKANRIBG VARCHAR(5) , ';
SELECT COUNT(*) INTO count1 FROM managesystem.m016_tbl WHERE BBNBKANRIBG=id;
SET count2 = count;
WHILE(count1 > 0)
SET sql_ = sql_ + 'SEIKYUNEGAPI'+count1 + ' DATETIME,KAISYUUHI'+count1 + ' DATETIME';
SET count1 =count1 -1;
END WHILE;
SET sql_ = sql_ + ')';
PREPARE stmt FROM sql_ ;
execute stmt;
select sql_;
SELECT * FROM tmp_table; OPEN cur1; WHILE(count2 > 0)
FETCH cur1 INTO BBNBKANRIBG,SEIKYUNEGAPI,KAISYUUHI;
IF count2= 1 THEN
INSERT INTO tmp_table(BBNBKANRIBG) VALUES (BBNBKANRIBG);
ELSE
INSERT INTO tmp_table(SEIKYUNEGAPI+count2,KAISYUUHI+count2) VALUES (SEIKYUNEGAPI,KAISYUUHI);
END IF;
SET count2 =count2 -1;
END WHILE; SELECT * FROM tmp_table
end;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货