|
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目前没有提供存储过程调试功能，只能通过输出变量来判断是否功能无误 (责任编辑：IT) |