Mysql存储过程（二）：循环，游标，更新，参数输出y

drop procedure if exists hagonda_insert;
create procedure hagonda_insert()
begin
declare b int default 0; -- 终止循环
declare y_id int;
declare y_code8 varchar(8);
declare y_codeValue varchar(20);
declare h_id int;
declare cur_exp cursor for
select id,code8,codeValue from dd_exgtmp where isvalid = 0 order by id asc; -- 如果需要控制条数，在这里加limit
declare continue handler for not found set b = 1;
open cur_exp;
fetch cur_exp into y_id,y_code8,y_codeValue;
while b<>1 do -- 有多少处理多少，直到没有数据为止
select id into h_id from dd_hagda where code8 = y_code8 and codeValue = y_codeValue;
set @y=h_id;
select @y;
if h_id is not null then
update dd_hagda set code8 = y_code8,codeValue = y_codeValue,adddate = current_timestamp() where id = h_id;
else
insert into dd_hagda (code8,codeValue) values(y_code8,y_codeValue);
end if;

update dd_exgtmp set isvalid = 1,bak2 = current_timestamp() where id = y_id;
fetch cur_exp into y_id,y_code8,y_codeValue;
end while;
close cur_exp;
end;

-- call hagonda_insert();

1，循环 while end while

声明一个变量控制循环终止

其他循环见：http://blog.sina.com.cn/s/blog_4f925fc30100mw2l.html

2，游标

declare cursor

提取参数的时候用tetch into到变量

3，更新

select 字段 into 变量

然后判断变量是否为空判断是否更新

4，输出变量

set @y

select @y;

mysql目前没有提供存储过程调试功能，只能通过输出变量来判断是否功能无误