一个含有临时表创建的存储过程__create创建

BEGIN
#Routine body goes here...
declare tmp0 VARCHAR(1000);
declare tmp1 VARCHAR(1000);
declare done int default -1; -- 用于控制循环是否结束
declare cnt int;

 /* 声明游标 */    
declare myCursor cursor for select label,number from label_to_number_table;    
   

/* 当游标到达尾部时，mysql自动设置done=1 */       
declare continue handler for not found set done=1;    

#select count(1) from information_schema.tables where TABLE_SCHEMA='welddb' and table_name='tmp_table2'  into @cnt;  #只能检测非临时表是否存在      
#select @cnt;
#drop temporary table tmp_table2;     

#create TEMPORARY TABLE tmp_table2 select * from label_to_number_table;
create TEMPORARY TABLE tmp_table2(label VARCHAR(500) NOT NULL,number INT NOT NULL);

/* 打开游标 */    
open myCursor;    
    
/* 循环开始 */    
myLoop: LOOP    
    
    /* 移动游标并赋值 */    
    fetch myCursor into tmp0,tmp1;    
      
            -- 游标到达尾部,退出循环  
    if done = 1 then     
    leave myLoop;    
    end if;    
        
    /* do something */    
    -- 循环输出信息  
            #select tmp0,tmp1 ;  
            #insert tmp_table4 select tmp0,tmp1 ;

            -- 可以加入insert,update等语句  
    
/* 循环结束 */    
end loop myLoop;    
    
/* 关闭游标 */    
close myCursor;    

select * from tmp_table2;   

drop temporary table tmp_table2; 

END