DROP PROCEDURE IF EXISTS countTableRows;
CREATE PROCEDURE countTableRows(IN tableName VARCHAR(10),OUT count INT)
BEGIN
SET @sql = CONCAT('SELECT COUNT(*) INTO @count FROM ', tableName);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET count =@count;
END;
要用表名当参数,就不得不提到prepare预处理语句。
基础用法:
PREPARE stmt from '你的sql语句'; //定义预处理语句
EXECUTE stmt (如果sql有参数的话, USING xxx,xxx); //执行预处理语句
DEALLOCATE PREPARE stmt; //释放资源
作用:
可以在存储过程中动态的拼接表名,字段名,来达到动态查询的效果
sql语句中可以用 ? 通配符来代替参数,这样可以有效的防止sql注入
案例:
CREATE PROCEDURE myTest()
BEGIN
SET @sql = 'SELECT ? AS NUM ';
SET @index = 10;
PREPARE stmt FROM @sql;
EXECUTE stmt USING @index // 传入会话变量填充sql中的 ?
DEALLOCATE PREPARE stmt;
END;
需要注意的是:
SET @sql = CONCAT('SELECT COUNT(*) INTO @count FROM', tableName);
表名在sql中是不能用?来代替的,所以只能 用字符串拼接。
而想要获取execute 执行sql后的结果,直接将 "INTO 变量名 " 拼在预处理sql中就可以了,这里的变量名必须加@,否则调用这个存储过程时会报错。
示例:
-- 测试创建存储过程
DROP PROCEDURE IF EXISTS countTableRows;
CREATE PROCEDURE countTableRows(IN tableName VARCHAR(50),OUT num INT)
BEGIN
-- SET @sql = CONCAT('SELECT * FROM ', tableName);
-- PREPARE stmt FROM @sql;
-- EXECUTE stmt;
-- DEALLOCATE PREPARE stmt;
-- SET count =@count;
select count(*) into num from tableName;
END;
-- 方案2
DROP PROCEDURE IF EXISTS tableExport;
CREATE PROCEDURE tableExport(IN tableName VARCHAR(50),OUT count INT)
BEGIN
SET @sql = CONCAT('SELECT * FROM ', tableName);
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
SET count =@count;
END;
nvicat调用方式:
CALL countTableRows('rooster_attendance_daily',@num);
CALL tableExport('rooster_attendance_daily',@num);