56,675
社区成员
发帖
与我相关
我的任务
分享
DROP PROCEDURE IF EXISTS getTotalCounts;
create procedure getTotalCounts(in tableName varchar(100),in whereText varchar(100),out totalCounts int)
comment '获取表总记录数'
begin
set @table_name=tableName;
set @total = totalCounts;
set @where_text=whereText;
set @s=' select count(*) into @total from ' + @table_name+ ' where '+ where_text;
prepare stmt from @s;
execute stmt;
set totalCounts = @total;
DEALLOCATE prepare stmt;
end;
CallableStatement proc =con.prepareCall("{call getTotalCounts(?,?,?)}");
proc.setString(1, "blog_article");
proc.setString(2,"cate_id>0");
proc.registerOutParameter(3,Types.INTEGER);
proc.execute();
begin
select count(*) into totalCounts from tableName where where_Text;
end
DELIMITER $$
DROP PROCEDURE IF EXISTS `getTotalCounts` $$
CREATE PROCEDURE `getTotalCounts`(in tableName varchar(100),in whereText varchar(100),out totalCounts integer(11))
BEGIN
set @table_name=tableName;
set @where_text=whereText;
set @s=concat('SELECT count(*) as totalCounts FROM ',@table_Name,' Where ', @where_text);
prepare stmt from @s;
execute stmt;
END $$
DELIMITER ;
调用:
CALL getTotalCounts("Customers","Left(customername,1)='b'",@a);
mysql> use c1
Database changed
mysql> CALL getTotalCounts("Customers","Left(customername,1)='b'",@a);
+-------------+
| totalCounts |
+-------------+
| 13 |
+-------------+
1 row in set (0.01 sec)
Query OK, 0 rows affected (0.01 sec)