行列转换的存储过程，这个存储过程哪里有问题啊？
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;
[解决办法]
首先,没有 DELIMITER
[解决办法]
呵呵，看到 SELECT COUNT(*) INTO count1 FROM managesystem.m016_tbl WHERE BBNBKANRIBG=id; 看不下去了。自己分析吧。
[解决办法]
PREPARE stmt FROM sql_ ;
这两个变量stmt和 sql_ 是动态的.不能一开始就declare
