Can we use JOINS in cursors? - Oracle

Why not? We can use almost any type of query (including joins, sub-queries etc) with cursor declarations. If you have any expressions as columns, then you are required to provide an alias for each of them.  The following program is a demonstration:

declare

  cursor c_emp is

        select ename,sal*12 as annsal,dname

        from emp,dept

        where emp.deptno = dept.deptno;

  r_emp c_emp%rowtype;

begin

  open c_emp;

  loop

        fetch c_emp into r_emp;

        exit when c_emp%notfound;

        dbms_output.put_line(r_emp.ename || ' - ' || r_emp.annsal || ' - ' || r_emp.dname);

  end loop;

  close c_emp;

end;

In the above program, “sal*12” is an expression provided with an alias “annsal” (annual salary). The beauty of ‘r_emp’ is that it automatically contains ‘annsal’ as a member without having any declaration. And I provide the cursor FOR loop version for the above program as follows:

declare

  cursor c_emp is

        select ename,sal*12 as annsal,dname

        from emp,dept

        where emp.deptno = dept.deptno;

begin

  for r_emp in c_emp

  loop

        dbms_output.put_line(r_emp.ename || ' - ' || r_emp.annsal || ' - ' || r_emp.dname);

  end loop;

end;