How to set cursor field name from another cursor? Oracle

I need to take cursor fields names from another cursor like this:

FOR rec1 IN (SELECT * FROM table1) LOOP  
   FOR rec2 IN (SELECT * FROM table2) LOOP  
       IF rec1.[rec2.field_name] <> '*' THEN 
         ...                                         开发者_运维问答 
       END IF;
   END LOOP;
END LOOP;

Oracle is really not designed for this kind of behavior. The only way I can think of to achieve this is to use dynamic PL/SQL to produce the functionality you're looking for:

declare
  v_field_value varchar2(2000);
begin
  FOR rec1 IN (SELECT * FROM table1) LOOP  
     FOR rec2 IN (SELECT * FROM table2) LOOP  
         EXECUTE IMMEDIATE 'begin :value := :rec1.' 
                           || :rec2.field_name || '; end;' 
            USING OUT v_field_value, IN rec1;
         IF v_field_value  <> '*' THEN 
           ...                                          
         END IF;
     END LOOP;
  END LOOP;
end;

However, just because this approach can work doesn't mean you should use it. If your field is not a string, for instance, Oracle will implicitly convert the value, which may result in a different value than what you expect. If this were my code, I would only use this as a last resort, after considering implementing the same functionality outside the database and redesigning the database's structure to avoid the need for this type of code.

Based on the comment the error mentioned in the comment, I've modified the code so to pass the records in using bind variables.

I am not sure, I understand what your problem is, you can access the outer cursor-loop's record just like you would expect from a variable that is declared in a scope above the current one.

for rec1 in (SELECT * FROM table1) loop
   for rec2 in (SELECT * FROM table2) loop
       if rec1.field = 1 and rec2.field_name <> '*' then
         ...                                          
       end if;
   end loop;
end loop;

kinda like

declare
  i Integer;
begin
  declare 
     x Integer;
  begin
     i := x;
  end;
end;