Is it possible to run on variables within the cursor in SQL?

Question

Suppose I have the following databases:

T1 (a, b, c), T2 (a, c, d)

And I want to make a block in the following way:

declare

cursor info is select b, d
from T1, T2
where T1.a = T2.a and T1.c = T2.c

begin
open info
for temp in info (x)
dbms_output.put_line (temp.b || temp.d);
end loop
close info
end;

Is it even legal to run on variables within the cursor?!? Because I know there is no point in making a for loop when there is a cursor .. but does it compile...?

Thank you very much:)

Littlefoot Littlefoot · Accepted Answer · 2021-02-17T19:36:10

Yes, why wouldn't you be able to do that? It is a parametrized cursor. See comments within code.

SQL> set serveroutput on
SQL>
SQL> declare
  2    l_deptno dept.deptno%type;                         --> will be used in a cursor
  3
  4    cursor info (par_deptno dept.deptno%type) is       --> parametrized cursor
  5      select d.dname, e.ename
  6        from emp e join dept d on d.deptno = e.deptno
  7        where d.deptno = par_deptno;                   --> use it here
  8  begin
  9    l_deptno := 20;
 10
 11    for temp in info (l_deptno) loop
 12      dbms_output.put_line(temp.dname ||' '|| temp.ename);
 13    end loop;
 14  end;
 15  /
RESEARCH SMITH
RESEARCH JONES
RESEARCH SCOTT
RESEARCH ADAMS
RESEARCH FORD

PL/SQL procedure successfully completed.

SQL>

If you use it in a cursor FOR loop, you don't have to open the cursor, declare cursor variable (to hold the result), pay attention when to exit the loop or close the cursor - Oracle does it for you.

Therefore, what you said:

there is no point in making a for loop when there is a cursor

is simply wrong.

Is it possible to run on variables within the cursor in SQL?

1 Answers