开发者

PL/SQL - Use of variables in nested queries

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.


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.


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.


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.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜