我写了一个存储过程,用来获取总行数.
create procedure countNum(out allPages int)
begin
select count(*) into allPages from tempTable;
end;然后通过
call countNum(@abc);
select @abc;
可以得到当前查询的所有行数.但是我想动态的对表进行查询,就修改了存储过程为:
create procedure countNum
(
in tbName varchar(100),
out allPages int
)
begin
set @sqlStr = CONCAT('select count(*) into allPages from ',tbName);
prepare sqlstmt from @sqlStr;
execute sqlstmt;
deallocate prepare sqlstmt;
end;
这样在执行时又会报变量allPages没有定义的错误.请问下,该如何写才能实现动态的对表进行统计行数呢?
create procedure countNum(out allPages int)
begin
select count(*) into allPages from tempTable;
end;然后通过
call countNum(@abc);
select @abc;
可以得到当前查询的所有行数.但是我想动态的对表进行查询,就修改了存储过程为:
create procedure countNum
(
in tbName varchar(100),
out allPages int
)
begin
set @sqlStr = CONCAT('select count(*) into allPages from ',tbName);
prepare sqlstmt from @sqlStr;
execute sqlstmt;
deallocate prepare sqlstmt;
end;
这样在执行时又会报变量allPages没有定义的错误.请问下,该如何写才能实现动态的对表进行统计行数呢?
(
in tbName varchar(100),
out allPages int
)
begin
set @sqlStr = CONCAT('select count(*) into @allPages from ',tbName);
prepare sqlstmt from @sqlStr;
execute sqlstmt; set allPages = @allPages; deallocate prepare sqlstmt;
end;
(
in tbName varchar(100)
)
begin
set @sqlStr = CONCAT('select count(*) from ',tbName);
prepare sqlstmt from @sqlStr;
execute sqlstmt;
deallocate prepare sqlstmt;
end;
create procedure countNum
(
in tbName varchar(100),
out allPages int
)
begin
set @sqlStr = CONCAT('select count(*) into @allPages from ',tbName);
prepare sqlstmt from @sqlStr;
execute sqlstmt;set allPages = @allPages;deallocate prepare sqlstmt;
end;为正确答案,谢谢 习习