create or replace procedure p_addhom is
begin
declare
 str_sql varchar2(3000);
 l_names  province_py.pingying%type;

 cursor c is select pingying from province_py where sign=1 order by pingying;
 begin

   open c;
   loop
     fetch c  into  l_names ;
     exit when c%notfound;
        str_sql:='  merge into huangy.temp_add_'||l_names|| ' t ';
        str_sql:=str_sql||' using ';
        str_sql:=str_sql||' (select ';
        str_sql:=str_sql||' ID,QIYEMINGCHENG,FARENDAIBIAO,JINGYINGFANWEI,JINGJIHANGYE,HANGYELEIBIEHAO,JINGJILEIXING, ';                       
        str_sql:=str_sql||' ZHUCERIQI,XINGZHENGQUHAO,YOUZHENGBIANMA,DIZHI,DIANHUA,CHUANZHEN,ZHIGONGRENSHU,ZHUCEZIJIN, ';                  
        str_sql:=str_sql||' CHENGSHI,HAOMA800,WANGZHANDIZHI,E_MAIL,ZHIZHAOBIANHAO,QIYEJIBIE,DIANHUAQUHAO,LIANXIREN, ';                   
        str_sql:=str_sql||' LIANXIRENDIANHUA,STATE,null INSERT_DATE,null BUILD_DATE,null TOTAL_PERSON,null REGISTER_BANKROLL,''HUMONE'' REMARK1,';                                 
        str_sql:=str_sql||' null REMARK2,null MAX_SORT,null MIDDLE_SORT,null MIN_SORT,QUXIAN,1 STATUS  ';
        str_sql:=str_sql||' from com_humone where py='''||l_names ||''') h ';
        str_sql:=str_sql||' on (t.QIYEMINGCHENG = h.QIYEMINGCHENG) ';
        str_sql:=str_sql||' when not matched then ';
        str_sql:=str_sql||' insert ( ID,QIYEMINGCHENG,FARENDAIBIAO,JINGYINGFANWEI,JINGJIHANGYE,HANGYELEIBIEHAO,JINGJILEIXING, ';                                   
        str_sql:=str_sql||' ZHUCERIQI,XINGZHENGQUHAO,YOUZHENGBIANMA,DIZHI,DIANHUA,CHUANZHEN,ZHIGONGRENSHU,ZHUCEZIJIN, ';                                   
        str_sql:=str_sql||' CHENGSHI,HAOMA800,WANGZHANDIZHI,E_MAIL,ZHIZHAOBIANHAO,QIYEJIBIE,DIANHUAQUHAO,LIANXIREN, ';                                   
        str_sql:=str_sql||' LIANXIRENDIANHUA,STATE,INSERT_DATE,BUILD_DATE,TOTAL_PERSON,REGISTER_BANKROLL,REMARK1, ';                                
        str_sql:=str_sql||' REMARK2,MAX_SORT,MIDDLE_SORT,MIN_SORT,QUXIAN,STATUS )';
        str_sql:=str_sql||' values ';
        str_sql:=str_sql||' ( h.ID,h.QIYEMINGCHENG,h.FARENDAIBIAO,h.JINGYINGFANWEI,h.JINGJIHANGYE,h.HANGYELEIBIEHAO,h.JINGJILEIXING, ';                                   
        str_sql:=str_sql||' h.ZHUCERIQI,h.XINGZHENGQUHAO,h.YOUZHENGBIANMA,h.DIZHI,h.DIANHUA,h.CHUANZHEN,h.ZHIGONGRENSHU,h.ZHUCEZIJIN, ';                                   
        str_sql:=str_sql||' h.CHENGSHI,h.HAOMA800,h.WANGZHANDIZHI,h.E_MAIL,h.ZHIZHAOBIANHAO,h.QIYEJIBIE,h.DIANHUAQUHAO,h.LIANXIREN, ';                                   
        str_sql:=str_sql||' h.LIANXIRENDIANHUA,h.STATE,h.INSERT_DATE,h.BUILD_DATE,h.TOTAL_PERSON,h.REGISTER_BANKROLL,h.REMARK1, ';                                
        str_sql:=str_sql||' h.REMARK2,h.MAX_SORT,h.MIDDLE_SORT,h.MIN_SORT,h.QUXIAN,h.STATUS)';
       --execute immediate str_sql;      
       DBMS_OUTPUT.PUT_LINE(str_sql);
       commit;
   end loop;
   close c;
 end;
 end;