mysql如何存储过程返回记录的更新条数

#ROW_COUNT()返回被前面语句升级的、插入的或删除的行数。
#这个行数和 mysql 客户端显示的行数及 mysql_affected_rows() C API 函数返回的值相同。
use test;
create table t(id int,NAME varchar(200),addr varchar(200));
INSERT INTO t VALUES(1),(2),(3);
#未查询到dml的行数,应该是数据已经提交导致
SELECT ROW_COUNT();

DELETE FROM t WHERE id IN(1,2);

SELECT ROW_COUNT();
----------------------------------------------
#调用procedure,f_error_code为输出参数
#  if char_length(f_str2) != 0 then的参数传递时需要加3个单引号,应该是bug
call `sp_test_prepare`(1,un1,ustr11,‘‘‘ustr2‘‘‘,@f_error_code);
call `sp_test_prepare`(1,n1,str,‘‘‘str2‘‘‘,@f_error_code);

SELECT ROW_COUNT();
SELECT @f_error_code;

create table lk5(id int,name varchar(100),str varchar(100),str2 varchar(100));
truncate lk5;
insert into lk5 values(1,n1,str11,str21),(2,n2,str12,str22);
select * from lk5;
------------
#定义分隔符
DELIMITER $$

DROP PROCEDURE IF EXISTS `test`.`sp_test_prepare` $$

CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_test_prepare`(
  IN f_id int,
  IN f_name varchar(64), 
  IN f_str varchar(255),
  IN f_str2 varchar(255),
 OUT f_error_code boolean
)
BEGIN
  declare cnt int default 0;
  set @stmt = concat(update lk5 set `name` =‘‘‘,f_name,‘‘‘, str = ‘‘‘,f_str,‘‘‘‘);
  set f_error_code = FALSE;
  if char_length(f_str2) != 0 then
    set @stmt = concat(@stmt,, str2 = ,f_str2);
  end if;
  set @stmt = concat(@stmt,  where id = ,f_id);
  prepare s1 from @stmt;
  execute s1;
  -- Must be above of the deallocate statement.
  -- 只能放在这里才能显示出正确的结果。
  set cnt = row_count();
  deallocate prepare s1;
  if cnt > 0 then
    set f_error_code = TRUE;
   end if;
END$$

#DELIMITER ;

 

mysql如何存储过程返回记录的更新条数

上一篇:mysql 5.7.28 中GROUP BY报错问题 SELECT list is not in GROUP BY clause and contains no


下一篇:MySQL 5.6无法通过Navicat远程连接 1045 - Access denied for user 'root'@'::1' (using password: YES) 的解决办法