我写了一个存储过程,在命令行模式下CALL是正常的,该存储过程只有两个输入,没有输出,完成的工作,仅仅是往一张表中插入一条记录。但是到了PHPMYADMIN或者使用php脚本执行,则会报错“PROCEDURE can't return a result set in the given context”,存储过程也没有执行?有高手知道为什么么?怎么解决?
我写的存储过程,看起来很长,其实仔细看下,结构非常简单,从其他表中找出最大值,以及发生最大值对应的时间,然后插入另外一张表中。 CREATE PROCEDURE TMONZYY (IN SYSNAME CHAR(8), IN SGNAME CHAR(10)) BEGIN DECLARE YES_DATE CHAR(10); DECLARE TABLENM CHAR(10); SET YES_DATE = DATE_SUB(CURDATE(),INTERVAL 1 DAY); SET TABLENM = CONCAT('TMON',LEFT(CONCAT(LEFT(YES_DATE,4),SUBSTRING(YES_DATE,6,2),SUBSTRING(YES_DATE,9,2)),6)); #FIND TOTAL CAPACITY OF THE SG SET @SQL1 = CONCAT('SELECT MAX(TOTAL_CAP) INTO @TOTALCAP FROM ',TABLENM,' WHERE SG_NAME="',SGNAME,'" AND DATE="',YES_DATE,'" AND SYS_NAME="',SYSNAME,'"'); PREPARE SQLSTR1 FROM @SQL1; EXECUTE SQLSTR1; DEALLOCATE PREPARE SQLSTR1; #FIND MIN FREE CAPACITY OF THE SG SET @SQL2 = CONCAT('SELECT MIN(FREE_CAP) INTO @FREECAP FROM ',TABLENM,' WHERE SG_NAME="',SGNAME,'" AND DATE="',YES_DATE,'" AND SYS_NAME="',SYSNAME,'"'); PREPARE SQLSTR2 FROM @SQL2; EXECUTE SQLSTR2; DEALLOCATE PREPARE SQLSTR2; #FIND THE TIME WHICH MAX CAPACITY OCCUR SET @SQL3 = CONCAT('SELECT TIME INTO @CAPTIME FROM ',TABLENM,' WHERE SG_NAME="',SGNAME,'" AND DATE="',YES_DATE,'" AND SYS_NAME="',SYSNAME,'" AND FREE_CAP LIKE ',@FREECAP,' LIMIT 1'); PREPARE SQLSTR3 FROM @SQL3; EXECUTE SQLSTR3; DEALLOCATE PREPARE SQLSTR3; #FIND THE MAX IORATE SET @SQL4 = CONCAT('SELECT MAX(IO_RATE) INTO @IORATE FROM ',TABLENM,' WHERE SG_NAME="',SGNAME,'" AND DATE="',YES_DATE,'" AND SYS_NAME="',SYSNAME,'"'); PREPARE SQLSTR4 FROM @SQL4; EXECUTE SQLSTR4; DEALLOCATE PREPARE SQLSTR4; #FIND THE TIME WHICH MAX IORATE OCCUR SET @SQL5 = CONCAT('SELECT TIME INTO @RATETIME FROM ',TABLENM,' WHERE SG_NAME="',SGNAME,'" AND DATE="',YES_DATE,'" AND SYS_NAME="',SYSNAME,'" AND IO_RATE =',@RATEMAX,' LIMIT 1'); PREPARE SQLSTR5 FROM @SQL5; EXECUTE SQLSTR5; DEALLOCATE PREPARE SQLSTR5; #FIND THE MAX IORESP SET @SQL6 = CONCAT('SELECT MAX(IO_RESP) INTO @IORESP FROM ',TABLENM,' WHERE SG_NAME="',SGNAME,'" AND DATE="',YES_DATE,'" AND SYS_NAME="',SYSNAME,'"'); PREPARE SQLSTR6 FROM @SQL6; EXECUTE SQLSTR6; DEALLOCATE PREPARE SQLSTR6; #FIND THE TIME WHICH MAX IORESP OCCUR SET @SQL7 = CONCAT('SELECT TIME INTO @RESPTIME FROM ',TABLENM,' WHERE SG_NAME="',SGNAME,'" AND DATE="',YES_DATE,'" AND SYS_NAME="',SYSNAME,'" AND IO_RESP LIKE ',@RESPMAX,' LIMIT 1'); PREPARE SQLSTR7 FROM @SQL7; EXECUTE SQLSTR7; DEALLOCATE PREPARE SQLSTR7; INSERT INTO TMONMAX VALUES(SYSNAME,SGNAME,YES_DATE,@TOTALCAP,@FREECAP,@CAPTIME,@RATEMAX,@RATETIME,@RESPMAX,@RESPTIME); END //
http://bugs.php.net/bug.php?id=42548
CREATE PROCEDURE TMONZYY (IN SYSNAME CHAR(8), IN SGNAME CHAR(10))
BEGIN
DECLARE YES_DATE CHAR(10);
DECLARE TABLENM CHAR(10);
SET YES_DATE = DATE_SUB(CURDATE(),INTERVAL 1 DAY);
SET TABLENM = CONCAT('TMON',LEFT(CONCAT(LEFT(YES_DATE,4),SUBSTRING(YES_DATE,6,2),SUBSTRING(YES_DATE,9,2)),6));
#FIND TOTAL CAPACITY OF THE SG
SET @SQL1 = CONCAT('SELECT MAX(TOTAL_CAP) INTO @TOTALCAP FROM ',TABLENM,' WHERE SG_NAME="',SGNAME,'" AND DATE="',YES_DATE,'" AND SYS_NAME="',SYSNAME,'"');
PREPARE SQLSTR1 FROM @SQL1;
EXECUTE SQLSTR1;
DEALLOCATE PREPARE SQLSTR1;
#FIND MIN FREE CAPACITY OF THE SG
SET @SQL2 = CONCAT('SELECT MIN(FREE_CAP) INTO @FREECAP FROM ',TABLENM,' WHERE SG_NAME="',SGNAME,'" AND DATE="',YES_DATE,'" AND SYS_NAME="',SYSNAME,'"');
PREPARE SQLSTR2 FROM @SQL2;
EXECUTE SQLSTR2;
DEALLOCATE PREPARE SQLSTR2;
#FIND THE TIME WHICH MAX CAPACITY OCCUR
SET @SQL3 = CONCAT('SELECT TIME INTO @CAPTIME FROM ',TABLENM,' WHERE SG_NAME="',SGNAME,'" AND DATE="',YES_DATE,'" AND SYS_NAME="',SYSNAME,'" AND FREE_CAP LIKE ',@FREECAP,' LIMIT 1');
PREPARE SQLSTR3 FROM @SQL3;
EXECUTE SQLSTR3;
DEALLOCATE PREPARE SQLSTR3;
#FIND THE MAX IORATE
SET @SQL4 = CONCAT('SELECT MAX(IO_RATE) INTO @IORATE FROM ',TABLENM,' WHERE SG_NAME="',SGNAME,'" AND DATE="',YES_DATE,'" AND SYS_NAME="',SYSNAME,'"');
PREPARE SQLSTR4 FROM @SQL4;
EXECUTE SQLSTR4;
DEALLOCATE PREPARE SQLSTR4;
#FIND THE TIME WHICH MAX IORATE OCCUR
SET @SQL5 = CONCAT('SELECT TIME INTO @RATETIME FROM ',TABLENM,' WHERE SG_NAME="',SGNAME,'" AND DATE="',YES_DATE,'" AND SYS_NAME="',SYSNAME,'" AND IO_RATE =',@RATEMAX,' LIMIT 1');
PREPARE SQLSTR5 FROM @SQL5;
EXECUTE SQLSTR5;
DEALLOCATE PREPARE SQLSTR5;
#FIND THE MAX IORESP
SET @SQL6 = CONCAT('SELECT MAX(IO_RESP) INTO @IORESP FROM ',TABLENM,' WHERE SG_NAME="',SGNAME,'" AND DATE="',YES_DATE,'" AND SYS_NAME="',SYSNAME,'"');
PREPARE SQLSTR6 FROM @SQL6;
EXECUTE SQLSTR6;
DEALLOCATE PREPARE SQLSTR6;
#FIND THE TIME WHICH MAX IORESP OCCUR
SET @SQL7 = CONCAT('SELECT TIME INTO @RESPTIME FROM ',TABLENM,' WHERE SG_NAME="',SGNAME,'" AND DATE="',YES_DATE,'" AND SYS_NAME="',SYSNAME,'" AND IO_RESP LIKE ',@RESPMAX,' LIMIT 1');
PREPARE SQLSTR7 FROM @SQL7;
EXECUTE SQLSTR7;
DEALLOCATE PREPARE SQLSTR7;
INSERT INTO TMONMAX VALUES(SYSNAME,SGNAME,YES_DATE,@TOTALCAP,@FREECAP,@CAPTIME,@RATEMAX,@RATETIME,@RESPMAX,@RESPTIME);
END //
应该出在接口上