declare 
 CURSOR emp_cursor IS  select flowno,betcode from tlot where lotno in('J00009','J00010');
 v_flowno tlot.flowno%TYPE;
 v_betcode tlot.betcode%TYPE;
 v_peilu tlot.peilu%TYPE;
 v_char varchar2(2000);
 v_result varchar2(4000);
 v_sql varchar2(4000);
 begin 

    open emp_cursor;
    loop
      fetch emp_cursor into v_flowno,v_betcode;
      EXIT WHEN emp_cursor%NOTFOUND;
      --dbms_output.put_line('betcode：'||substr(v_betcode,5,2)||',flowno：'||v_flowno||',length='||length(substr(v_betcode,5,2))); 
         v_result :='|'||substr(v_betcode,5,2)||'*|';
       v_char :=substr(v_betcode,8);

          for i in 1.. length(v_char) loop
           if mod(i,2)=0 then
               v_result :=v_result||substr(v_char,i-1,2)||':3.1'||substr(v_char,i-1,2)||'|';

           end if;  
            end loop;   

            v_sql :='update tlot set peilu='''||v_result||''',instate=1 where flowno='''||v_flowno||'''';
            dbms_output.put_line(v_sql);
            Execute immediate v_sql;
            commit;
      end loop;
      close emp_cursor;
  end;