mysql - 我的第一个存储过程出错 - 救命!

Question

这是我的第一个存储过程。我发现调试它非常困难。花一点时间在这上面帮助我

create procedure myworld.perform_target_proc(
  IN inp_usr_id integer,
  IN inp_tgt_src_id integer,
  IN inp_tgt_src_type varchar(30),
  IN inp_tgt_usr_id integer,
  IN tgt_usr_msg text,
  out tgt_res varchar(30)
)
BEGIN
  declare target_count integer
  select count(target_id) from target where usr_id=inp_usr_id and tgt_src_id=inp_tgt_src_id and tgt_src_type=inp_tgt_src_type
and tgt_usr_id=inp_tgt_usr_id into target_count
  if target_count=0 then
    begin
    insert into target(usr_id, tgt_src_id, tgt_src_type, tgt_usr_id, tgt_usr_msg) values
    (inp_usr_id, inp_tgt_src_id, inp_tgt_src_type, inp_tgt_usr_id, inp_tgt_usr_msg)
    set tgt_res = 'new target created'
    end
  else
    set tgt_res = 'target already exist'
  end if
END |

Answer 1

看起来您缺少一些分号。

create procedure myworld.perform_target_proc(
  IN inp_usr_id integer,
  IN inp_tgt_src_id integer,
  IN inp_tgt_src_type varchar(30),
  IN inp_tgt_usr_id integer,
  IN tgt_usr_msg text,
  out tgt_res varchar(30)
)
BEGIN
  declare target_count integer;
  select count(target_id) from target where usr_id=inp_usr_id and tgt_src_id=inp_tgt_src_id and tgt_src_type=inp_tgt_src_type
and tgt_usr_id=inp_tgt_usr_id into target_count;
  if target_count=0 then
    insert into target(usr_id, tgt_src_id, tgt_src_type, tgt_usr_id, tgt_usr_msg) values
    (inp_usr_id, inp_tgt_src_id, inp_tgt_src_type, inp_tgt_usr_id, inp_tgt_usr_msg)
    set tgt_res = 'new target created';
  else
    set tgt_res = 'target already exist';
  end if;
END |