-- Created on 2012-2-25 by ADMINISTRATOR 
declare
  -- Local variables here
  cursor cur_jg is
    select is_jg,spid
      from spkfk 
     where spid in (select spid
                      from xt_sjd_mx
                     where djbh in ('XSC00425794',
                                    'XSC00426199',
                                    'XSC00426159',
                                    'XSC00425793',
                                    'XSC00425899',
                                    'XSC00425802',
                                    'XSC00426007',
                                    'XSC00425810'));
  v_jg   varchar2(4);
  v_spid varchar2(40);
  sql_str varchar2(400);
  v_ls int;
  v_num int;
 /* sql_new_str varchar2(200);*/
/*  ls_table varchar2(4);*/
begin

  -- Test statements here
     sql_str:= 'CREATE   GLOBAL   TEMPORARY   TABLE   ls_table(  
                                                        a   varchar2(4)   not   null,  
                                                        b   varchar2(40))
                                                        ON   COMMIT   PRESERVE   ROWS';
    execute immediate  sql_str; 
    open cur_jg;
      loop
        fetch cur_jg
          into v_jg, v_spid;
        exit when cur_jg%notfound;
        sql_str:='insert into ls_table values('''||v_jg||''','''||v_spid||''')';
        execute immediate  sql_str; 
        sql_str:='commit';
        execute immediate  sql_str;
        --update spkfk set is_jg = '否' where spid = v_spid;
      end loop;
    close cur_jg;

    for v_num in 1..9 loop
      sql_str:='select a into  v_ls  from ls_table';
      execute immediate  sql_str; 
      dbms_output.put_line(v_ls);
    end loop;
exception
  when others then
    dbms_output.put_line(substr(sqlerrm,1,200));
end;