oracle游标 fetch new_data bulk collect into new_data_row limit 10

更新时间: 2019/12/18 14:28:26     点击率: 830
create or replace procedure SHISHUN_YIFACHE_CALL1
is

  icount  number:=0;
  i number:=0;
  t_count number:=0;
  strtype   VARCHAR2(50):='';
  strorder  VARCHAR2(50):='';
  strdate   VARCHAR2(50):='';
  strbread  VARCHAR2(50):='';
  cursor c_dept is select * from SHISHUN_YIFACHE_TEMP where bread='0' ;
  cursor new_data is select *   from V_SHISHUN_YIFACHE@tojmevzjjpt 
                  where rownum<=10 and   PKID not in (select PKID from SHISHUN_YIFACHE_TEMP);
  
  type dept_record is table of  SHISHUN_YIFACHE_TEMP%rowtype;
  v_dept dept_record;
  
  type new_data_rows is  table of  V_SHISHUN_YIFACHE@tojmevzjjpt%rowtype;
  new_data_row new_data_rows;
  
begin
  select to_char(sysdate,'yyyy-MM-dd') into strdate from dual ;
 
  open new_data;
   loop
     exit when new_data%NOTFOUND;
     fetch new_data bulk collect into new_data_row limit 10;
     for i in 1.. new_data_row.count loop
        insert into SHISHUN_YIFACHE_TEMP(
                dtcurrdate            ,
                dtcurrclock           ,
                dnstu_pmtrans         ,
                vcorderno 
               )
                values(
                new_data_row(i).dtcurrdate            ,
                new_data_row(i).dtcurrclock           ,
                new_data_row(i).dnstu_pmtrans         ,
                new_data_row(i).vcorderno  );
        commit;
     end loop;
   end loop;
  
  close new_data;
 

  open c_dept;
  loop
    exit when c_dept%NOTFOUND;
    fetch c_dept bulk collect into v_dept limit 10;

    for i in 1.. v_dept.count loop
      strorder := 'E'||  v_dept(i).VCORDERNO;
      strtype := v_dept(i).DNSTU_PMTRANS;

      update SHISHUN_YIFACHE_TEMP set bread='1' where PKID=v_dept(i).PKID;
      if  v_dept(i).DNSTU_PMTRANS='4' then
        
        
        transenergy(v_dept(i).DTCURRDATE,v_dept(i).DTCURRCLOCK);
         

      end if;
  end loop;
  end loop;
  close c_dept;
  commit;

  EXCEPTION
  WHEN OTHERS THEN
  begin
    rollback;
  end;
end;