PL/SQL - Use of variables in nested queries

Question

Hey,

I'd like to do something like this with PL/SQL:

for ACCOUNT in account_cursor
loop

   for related_data in (select something from table where some_column = ACCOUNT.column)
   loop

   endloop;       

endloop;

(caps for emphasis)

I'm away from my dev environment so I can't test this out, so please ignore any minor syntactical errors. I am more concerned about whether I can use the variables in this way.

EDIT:

An explination of what I'm trying to achieve. I have two huge tables: accounts and log. I want to get the log data for a specific few accounts. Because of the number of records a straight select/join is extremely slow. I am trying to speed up the proccess by first selecting the accounts I am interested in and then joining these to the log table. Any advice would be appreciated.

Answer 1

You appear to be mixing an Explicit cursor (account_cursor) with an implicit cursor (related_data)...

Aside from ENDLOOP needing to be END LOOP, it's syntactically correct & valid. You can see examples of both types of cursors in this link. But I wonder what you're actually doing to see if the cursor approach is actually necessary.

Answer 2

Please read the response above before using this approach. Technically, yes, you can access the variables .. something like this.

for v_dept_rec in (select deptno from dept where deptno in (10,20)) loop
     for v_emp_rec in (select empno from emp where deptno = V_DEPT_REC.DEPT_NO) loop

      <<<Process here>>

     end loop;
end loop;

"I've written the query using joins/where clauses and I'm looking for possible speed improvements using this approach"

Invariably, this is pretty slow as this is row-by-row processing. If you can post a representative code for your logic, you will get responses which indicate how do process the same in Sets.

Answer 3

As Rajesh observed, doing this in a cursor will be very slow.

A straight select/join of relatively few values should only be slow on huge tables if the query isn't using appropriate indexes - check that indexes on the account number exist in both accounts and log tables. If they do, check that your query is using them in the explain plan.