mysql - 存储函数中的继续处理程序仅调用一次

Question

存储过程为未找到表异常声明一个继续处理程序。当异常发生时，处理程序被调用一次。我希望它被调用的次数与函数被调用的次数一样多。

如何为供应商-客户的每种组合运行处理程序内的代码？

drop function if exists continue_handler_surprise;

delimiter //;

drop table if exists logg;
create table if not exists logg (msg text);

create function continue_handler_surprise(
  customer_company_id integer,
  supplier_company_id integer,
  limit_old_catalogs integer)
  returns integer deterministic
  begin
    declare tmp_t varchar(90) default null;
    declare continue handler for sqlstate '42S02'
    begin
      select concat('cache_s',
                   supplier_company_id, '_c',
                   customer_company_id) into tmp_t;
      insert into logg select concat('create temp table?: ',
                   tmp_t, ' lim: ', limit_old_catalogs);
      create temporary table if not exists tmp_t (id int);
    end;
    do (select null from `tmp_t` limit 0);
    insert into logg  select concat('handled?: ',
                   coalesce(tmp_t, ' tmp_t=null '));
    return rand() * 4010;
  end //;
delimiter ;

set @lim = 33;

       select continue_handler_surprise(207,  2032, @lim) as item_id
union  select continue_handler_surprise(2543, 2032, @lim) as item_id
union  select continue_handler_surprise(2543, 2005, @lim) as item_id
union  select continue_handler_surprise(2543, 2006, @lim) as item_id;

select continue_handler_surprise(33, 44, 1) as "no-union 1";
select continue_handler_surprise(10, 20, 50) as "no-union 2";

select '========== log:' as '';
select msg from logg;

我以为这可能是工会的事情，但事实并非如此。

示例输出

$ mysql < continue-handler.sql 
item_id
2589
60
551
2576

no-union 1
3209
no-union 2
296

========== log:

msg
create temp table?: cache_s2032_c207 lim: 33
handled?: cache_s2032_c207
handled?:  tmp_t=null 
handled?:  tmp_t=null 
handled?:  tmp_t=null 
handled?:  tmp_t=null 
handled?:  tmp_t=null

请注意 tmp_t 变量未在处理程序中初始化。

Answer 1

佩布卡克；

tmp_t 初始化位置错误。应该是这样的:

select concat('cache_s', supplier_company_id, '_c', customer_company_id) into tmp_t;

do (select null from `tmp_t` limit 0);