匿名块

declare
cursor C_APPLY_POLICY_TO_BE_UPDATE is
     select 
            a.apply_policy_no, 
            a.sale_no,
            b.sign_time
      from apply_base_info a, apply_sale b
     where a.sale_no = b.sale_no
       and b.sign_time is null
       and a.updated_date >=  TO_DATE('20120623', 'yyyymmdd')
       and a.updated_date <   TO_DATE('20120713', 'yyyymmdd')
       and a.system_id='01'
       and a.underwrite_mark in('1','4');

  V_COUNT            NUMBER := 0; --每500单提交更新1次
  V_APPLY_POLICY_NO  VARCHAR2(20);
  V_SALE_NO          VARCHAR2(20);
  V_SIGN_TIME        DATE;
  V_HANDLE_TIME      DATE;
  V_PRINT_NUM       NUMBER:=0;
begin
  V_COUNT := 0;
  OPEN C_APPLY_POLICY_TO_BE_UPDATE;

  LOOP
    FETCH C_APPLY_POLICY_TO_BE_UPDATE
      INTO V_APPLY_POLICY_NO,V_SALE_NO,V_SIGN_TIME;
    EXIT WHEN C_APPLY_POLICY_TO_BE_UPDATE%NOTFOUND;
  V_PRINT_NUM := V_PRINT_NUM +1 ;
  select max(HANDLE_TIME)
    into V_HANDLE_TIME
    from underwrite_info
   where document_no = V_APPLY_POLICY_NO
     and underwrite_mark in ('1', '4');
                             
    IF V_HANDLE_TIME IS NOT NULL THEN
      update apply_sale
         set sign_time = V_HANDLE_TIME
      
       where sale_no = V_SALE_NO;
    
      V_COUNT := V_COUNT + 1;
      IF V_COUNT = 500 THEN
        COMMIT;
        V_COUNT := 0;
      END IF;
      
   end if;
  
  END LOOP;

  CLOSE C_APPLY_POLICY_TO_BE_UPDATE;
  COMMIT;
  dbms_output.put_line('一共处理'||V_PRINT_NUM||'条记录.');
end;  
/