The for loop in Oracle is easy to use. However, when a for loop containing the select statement is used, you still find something you didn't pay attention to before.
My code is as follows:
Declare
Val1 date;
Val2 date;
Begin
For I in (select empno from emp_s) loop
Select hiredate into val1 from (select empno, hiredate, rank () over (order by hiredate) a from emp_s where empno = I) where a = 1;
Select hiredate into val2 from (select empno, hiredate, rank () over (order by hiredate) a from emp_s where empno = I) where a = 2;
Dbms_output.put_line (val2-val1 );
End loop;
Exception
When others then
Dbms_output.put_line (sqlerrm );
End;
Two PLS-00382 errors are thrown during compilation. Modified and compiled! (The modification should be marked in red !)
Declare
Val1 date;
Val2 date;
Begin
For I in (select empno from emp_s) loop
Select hiredate into val1 from (select empno, hiredate, rank () over (order by hiredate) a from emp_s where empno = I. empno) where a = 1;
Select hiredate into val2 from (select empno, hiredate, rank () over (order by hiredate) a from emp_s where empno = I. empno) where a = 2;
Dbms_output.put_line (val2-val1 );
End loop;
Exception
When others then
Dbms_output.put_line (sqlerrm );
End;
From the two examples above, we can clearly see that in the for loop containing the select statement, variable I is processed as a table type (even if only one column of data is obtained in your select statement ). So when we change I to I. empno, the compilation passes smoothly.