[mysql]存储过程

CREATE DEFINER=`motor`@`%` PROCEDURE `NewProc`()
BEGIN
	 -- 自定义变量
-- 	declare stopflag int default 0;
	DECLARE done BOOLEAN DEFAULT 0 ;

--   DECLARE var_me DOUBLE DEFAULT NULL ;
--   DECLARE var_pay_time TIMESTAMP DEFAULT NULL ;
  DECLARE var_en_name VARCHAR (100) DEFAULT NULL ;
  DECLARE var_ex_name VARCHAR (100) DEFAULT NULL ;
	DECLARE v_id int DEFAULT NULL ;
	DECLARE v_name VARCHAR (100) DEFAULT NULL ;

	declare cur CURSOR for select * from td_basic_mile_file;
-- 	1 游标是保存查询结果的临时内存区域
--   2 游标变量uname_cur保存了查询的临时结果，实际上就是查询结果集


--   declare continue handler for not found set stopflag=1;
	declare continue handler for not found set done=1;
-- 	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET done = 1 ;
--   3 声明句柄，当游标变量中保存的结果都查询一遍(遍历)，到达结尾，就把变量stopflag设置为1 用于循环中判断是否遍历结束

  #Routine body goes here...
	delete from td_node_code;


	open cur; # 打开游标

	-- 批读取数据到指定变量上
  FETCH cur INTO var_en_name,var_ex_name;
	while(stopflag=0) DO # 如果游标还没到结尾，就继续循环
  begin
		select id,name into v_id,v_name from td_basic_station where name=var_en_name;
    insert into td_node_code (road_node_id) values(v_id);
    FETCH cur INTO var_en_name,var_ex_name;
  end;
  end while;
  close cur;
END

转载于:https://my.oschina.net/u/2464371/blog/3094788