游标嵌套问题

wishrr

set serveroutput on;
declare
  v_last_time date;
  v_cardnum cardmast.cardnum%type;   
  v_table_name audtrail.table_name%type;
  v_fid_name audtrail.fld_name%type;
  v_index_1 audtrail.index_1%type;
  v_index_2 audtrail.index_2%type;
  v_index_3 audtrail.index_3%type;
  v_index_4 audtrail.index_4%type;
  v_before_val audtrail.before_val%type;
  v_after_val audtrail.after_val%type;
  v_change_data audtrail.change_date%type;
  v_change_time audtrail.change_time%type;
  v_fld_type audtrail.fld_type%type;
  v_comments audtrail.comments%type;
  c_user audtrail.user_id%type;   
  cursor card_cursor is  
  select cardnum from cardmast,temp_cif where cardmast.cif=temp_cif.cif;
   
  cursor aud_cursor(v_time date,v_index audtrail.index_1%type) is  
  select * from audtrail where index_1 = v_index and change_date = v_time;
begin
        select last_eod_date into v_last_time from eod_ctrl;
dbms_output.put_line('ok---------'||v_last_time);   
  open card_cursor;
  begin
  fetch next from card_cursor into @v_last_time;
  while(@@fetch_status = 0)   
  open aud_cursor;
  fetch next from aud_cursor into v_table_name,v_fid_name,v_index_1,v_index_2,v_index_3,v_index_4,v_before_val,v_after_val,
  c_user,v_change_data,v_change_time,v_fld_type,v_comments;
  while(@@fetch_status = 0)
  begin
  fetch next from aud_cursor into v_table_name,v_fid_name,v_index_1,v_index_2,v_index_3,v_index_4,v_before_val,v_after_val,
  c_user,v_change_data,v_change_time,v_fld_type,v_comments;
  end;
  close aud_cursor;
  deallocate aud_cursor;
  fetch next from card_cursor into @v_last_time;
  end;
  close card_cursor;
  deallocate card_cursor;
  exception
  when no_data_found then
dbms_output.put_line('the audtrail!');
  when others then
  dbms_output.put_line('no reason');
end;
/
报错
  fetch next from card_cursor into @v_last_time;
  *
ERROR at line 27:
ORA-06550: line 27, column 17:
PLS-00103: Encountered the symbol "FROM" when expecting one of the following:
. into bulk
请问怎么解决，想了两天了，未解决~

renxiao2003

游标嵌套容易出错。

wishrr

我把代码更改了一下，但是运行的速度好慢呀，这样是不行的，请问这种情况怎么解决呀，难道用多表查询吗？

open  card_cursor;
      loop
       fetch card_cursor into  v_cardnum;
       exit when card_cursor%notfound;
        dbms_output.put_line('yes1'||v_cardnum);
        open aud_cursor(v_last_time,v_cardnum);
        loop
        fetch aud_cursor into v_table_name,v_fid_name,v_index_1,v_index_2,v_index_3,v_index_4,v_before_val,v_after_val,
        c_user,v_change_data,v_change_time,v_fld_type,v_comments;
        exit when aud_cursor%notfound;        
         dbms_output.put_line('oh!you like me');
        end loop;
        close aud_cursor;
      end loop;
    close card_cursor;

renxiao2003

游标套游标，当然慢了啊。

wishrr

是要用多表查询，这样效率会快一点吗？