下面给出一个具体例子:
在model数据库中创建如下表:
DROP TABLE IF EXISTS `model`.`model_director`;
CREATE TABLE `model`.`model_director` (
`model_id` int(11) NOT NULL,
`director_id` int(11) NOT NULL,
PRIMARY KEY (`model_id`,`director_id`),
) ENGINE=InnoDB DEFAULT CHARSET=utf8 ROW_FORMAT=FIXED;
创建存储过程:
DELIMITER $$
DROP PROCEDURE IF EXISTS `testINVarchar` $$
CREATEPROCEDURE `testINVarchar`(IN inTableName VARCHAR(128),OUT inNUM INT)
BEGIN
SET @tmpNUM = 0;
SET @SQLStr=CONCAT('SELECT COUNT(*) INTO
@tmpNUM FROM ',inTableName );
PREPARE stmt from @SQLStr;
EXECUTE stmt;
SET inNUM =
@tmpNUM;
END $$
DELIMITER ;
不能直接将数据INTO到OUT变量中,还有另一个要注意的问题是,这里不能使用DECLARE声名的局部变量,因为使用CONCAT的时候会将其变成字符串!所以一定要使用SET来声名变量。
--错误做法1(OUT变量直接在SQL语句中使用)
DELIMITER $$
DROP PROCEDURE IF EXISTS `testINVarchar` $$
CREATE PROCEDURE `testINVarchar`(IN inTableName VARCHAR(128),OUT inNUM INT)
BEGIN
SET @SQLStr=CONCAT('SELECT COUNT(*) INTO ',
inNUM ,' FROM ',inTableName );
PREPARE stmt from @SQLStr;
EXECUTE stmt;
END $$
DELIMITER ;
--错误做法2(使用DECLARE声名变量是不可以在CONCAT中使用!)
DELIMITER $$
DROP PROCEDURE IF EXISTS `testINVarchar` $$
CREATE PROCEDURE `testINVarchar`(IN inTableName VARCHAR(128),OUT inNUM INT)
BEGIN
DECLARE tmpNUM = 0;
SET @SQLStr=CONCAT('SELECT COUNT(*) INTO
tmpNUM FROM ',inTableName );
PREPARE stmt from @SQLStr;
EXECUTE stmt;
SET inNUM = tmpNUM;
END $$
DELIMITER ;