PL/SQL - 在嵌套查询中使用变量

发布于 2024-09-24 23:49:44 字数 418 浏览 7 评论 0原文

我想用 PL/SQL 做这样的事情：（

for ACCOUNT in account_cursor
loop

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

   endloop;       

endloop;

大写强调）

我远离我的开发环境，所以我无法测试它，所以请忽略任何小的语法错误。我更关心的是我是否可以这样使用变量。

编辑：

解释我想要实现的目标。我有两个巨大的表：帐户和日志。我想获取特定几个帐户的日志数据。由于记录数量较多，直接选择/连接速度非常慢。我试图通过首先选择我感兴趣的帐户然后将它们加入到日志表中来加快该过程。任何建议将不胜感激。

原文

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.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

满栀 2024-10-01 23:49:45

您似乎将显式游标 (account_cursor) 与隐式游标 (related_data) 混合在一起...

除了 ENDLOOP 需要是 END LOOP ，它在语法上是正确的 &有效的。您可以在此链接中查看两种类型游标的示例。但我想知道您实际上在做什么来查看光标方法是否确实有必要。

回复收藏 0 原文

白色秋天 2024-10-01 23:49:45

在使用此方法之前，请阅读上面的回复。从技术上讲，是的，您可以访问变量..类似这样。

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;

“我使用以下方式编写了查询
连接/where 子句，我正在寻找
为了可能提高速度，使用
这种方法”

总是非常慢，因为这是逐行处理。如果您可以为您的逻辑发布代表性代码，您将得到指示如何在集合中处理相同代码的响应。

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.

回复收藏 0 原文