CREATE DEFINER=`root`@`%` PROCEDURE `simpleproc`(
IN USER_ID INT,
IN STEP INT,
INOUT CPIC_ID INT,
OUT FILENAME INT
)
BEGIN
SET @TYPER = CONCAT('Typer', STEP);
SET @STATE = CONCAT('State', STEP); set @sql=concat('update ', @t, ' set ',
@typer, '=', USER_ID, ',', @state, '=1 where pic_id>=', CPIC_ID,
' and ', @STATE, '=0 LIMIT 1;');
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
SET @SQL = CONCAT('SELECT PIC_ID, PICNAME FROM STATE WHERE ',
@typer, '=', USER_ID, ' AND ', @state, '=1 LIMIT 1 INTO CPIC_ID, FILENAME');
PREPARE stmt2 FROM @sql;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
END;
IN USER_ID INT,
IN STEP INT,
INOUT CPIC_ID INT,
OUT FILENAME INT
)
BEGIN
SET @TYPER = CONCAT('Typer', STEP);
SET @STATE = CONCAT('State', STEP); set @sql=concat('update ', @t, ' set ',
@typer, '=', USER_ID, ',', @state, '=1 where pic_id>=', CPIC_ID,
' and ', @STATE, '=0 LIMIT 1;');
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1;
SET @SQL = CONCAT('SELECT PIC_ID, PICNAME FROM STATE WHERE ',
@typer, '=', USER_ID, ' AND ', @state, '=1 LIMIT 1 INTO CPIC_ID, FILENAME');
PREPARE stmt2 FROM @sql;
EXECUTE stmt2;
DEALLOCATE PREPARE stmt2;
END;
call `simpleproc` (1234, 2, @W, @q); @SQL 的一个结果是:update STATE set Typer2=1234,State2=1 where pic_id>=1 and State2=0 LIMIT 1;单独执行没有问题
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1; 就没有问题。
@typer, '=', USER_ID, ',', @state, '=1 where pic_id>=', CPIC_ID,
' and ', @STATE, '=0 LIMIT 1;'); 我已经将 这里的 @t 已经改为 实际表名 “STATE” 。 set @sql=concat('update STATE set ',
@typer, '=', USER_ID, ',', @state, '=1 where pic_id>=', CPIC_ID,
' and ', @STATE, '=0 LIMIT 1;'); 将:
PREPARE stmt1 FROM @sql;
EXECUTE stmt1;
DEALLOCATE PREPARE stmt1; 替换为:
SELECT @SQL;可以编译保存,执行结果发现 sql 语句是正确的。
测试代码: SET @W=1;
call `simpleproc` (1234, 2, @W, @q); @SQL 的结果是:update STATE set Typer2=1234,State2=1 where pic_id>=1 and State2=0 LIMIT 1;
在WINDOWS下不分大小写没问题,LINUX下没有@state定义。
mysql> CREATE PROCEDURE `simpleproc`(
-> IN USER_ID INT,
-> IN STEP INT,
-> INOUT CPIC_ID INT,
-> OUT FILENAME INT
-> )
-> BEGIN
-> SET @TYPER = CONCAT('Typer', STEP);
-> SET @STATE = CONCAT('State', STEP);
->
-> set @sql=concat('update ', @t, ' set ',
-> @typer, '=', USER_ID, ',', @state, '=1 where pic_id>=', CPIC_ID,
-> ' and ', @STATE, '=0 LIMIT 1;');
->
->
-> PREPARE stmt1 FROM @sql;
-> EXECUTE stmt1;
-> DEALLOCATE PREPARE stmt1;
->
->
-> SET @SQL = CONCAT('SELECT PIC_ID, PICNAME FROM STATE WHERE ',
-> @typer, '=', USER_ID, ' AND ', @state, '=1 LIMIT 1 INTO CPIC_ID, FILEN
AME');
->
-> PREPARE stmt2 FROM @sql;
-> EXECUTE stmt2;
-> DEALLOCATE PREPARE stmt2;
->
-> END;
->
-> //
Query OK, 0 rows affected (0.13 sec)mysql> delimiter ;
mysql>