DELIMITER $$ DROP PROCEDURE IF EXISTS `ff`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `ff`(tablename VARCHAR(10)) BEGIN SET @asql=CONCAT('select count(*) into @num from ',tablename); PREPARE stml FROM @asql; EXECUTE stml; DEALLOCATE PREPARE stml; END$$DELIMITER ;
这样吗? call ff('analog_data_201107',@i); select @i; 还是报错了: 1318-Incorrect number of arguments for PROCEDURE test.ff;expectected 1,got 2
DELIMITER $$DROP PROCEDURE IF EXISTS `ff`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `ff`(tablename VARCHAR(10),OUT num INT) BEGIN SET @asql=CONCAT('select count(*) into @num from ',tablename); PREPARE stml FROM @asql; EXECUTE stml; SET num=@num; DEALLOCATE PREPARE stml; END$$call ff('analog_data_201107',@i); select @i;
mysql> DELIMITER $$ mysql> mysql> mysql> DROP PROCEDURE IF EXISTS `ff`$$ Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `ff`(tablename VARCHAR(50)) -> BEGIN -> SET @asql=CONCAT('select count(*) into @num from ',tablename); -> PREPARE stml FROM @asql; -> EXECUTE stml; -> DEALLOCATE PREPARE stml; -> END$$ Query OK, 0 rows affected (0.00 sec)mysql> mysql> DELIMITER ; mysql> call `ff`('t_Summary_zhipiao'); Query OK, 0 rows affected (0.00 sec)mysql> select @num; +------+ | @num | +------+ | 2200 | +------+ 1 row in set (0.00 sec)mysql> select count(*) from t_Summary_zhipiao; +----------+ | count(*) | +----------+ | 2200 | +----------+ 1 row in set (0.00 sec)
谢谢 各位了,问题解决了 begin set @strSql=concat( 'select count(0) into @num from ',tableName); prepare stmt from @strSql; execute stmt; set num=@num; end按照6楼的方法解决了,谢谢 WWWWA 了。呵呵 原来是少写了一句 set num=@num;分少啊,结贴了……
DROP PROCEDURE IF EXISTS `ff`$$CREATE DEFINER=`root`@`localhost` PROCEDURE `ff`(tablename VARCHAR(10))
BEGIN
SET @asql=CONCAT('select count(*) into @num from ',tablename);
PREPARE stml FROM @asql;
EXECUTE stml;
DEALLOCATE PREPARE stml;
END$$DELIMITER ;
这样吗?
call ff('analog_data_201107',@i);
select @i;
还是报错了:
1318-Incorrect number of arguments for PROCEDURE test.ff;expectected 1,got 2
BEGIN
SET @asql=CONCAT('select count(*) into @num from ',tablename);
PREPARE stml FROM @asql;
EXECUTE stml;
SET num=@num;
DEALLOCATE PREPARE stml;
END$$call ff('analog_data_201107',@i);
select @i;
mysql>
mysql>
mysql> DROP PROCEDURE IF EXISTS `ff`$$
Query OK, 0 rows affected, 1 warning (0.00 sec)mysql>
mysql> CREATE DEFINER=`root`@`localhost` PROCEDURE `ff`(tablename VARCHAR(50))
-> BEGIN
-> SET @asql=CONCAT('select count(*) into @num from ',tablename);
-> PREPARE stml FROM @asql;
-> EXECUTE stml;
-> DEALLOCATE PREPARE stml;
-> END$$
Query OK, 0 rows affected (0.00 sec)mysql>
mysql> DELIMITER ;
mysql> call `ff`('t_Summary_zhipiao');
Query OK, 0 rows affected (0.00 sec)mysql> select @num;
+------+
| @num |
+------+
| 2200 |
+------+
1 row in set (0.00 sec)mysql> select count(*) from t_Summary_zhipiao;
+----------+
| count(*) |
+----------+
| 2200 |
+----------+
1 row in set (0.00 sec)
set @strSql=concat( 'select count(0) into @num from ',tableName);
prepare stmt from @strSql;
execute stmt;
set num=@num;
end按照6楼的方法解决了,谢谢 WWWWA 了。呵呵 原来是少写了一句 set num=@num;分少啊,结贴了……