使用For循环在Oracle过程中检索多行 [英] Using For loop to retrieve multiple rows in Oracle procedure

问题描述

我正在处理存储过程，在该过程中，我需要检索一组结果并分别处理每个元素，然后返回整个结果.(使用3个不同的表)

Im working on stored procedure where I need to retrieve a set of results and process each element individually and then return the entire result.(using 3 different tables)

我对数据库不太熟悉，但是这里是我能想到的..

Im not too familiar with databases, but heres what I was able to come up with..

create or replace procedure GET_EMP_RSLT
  IS

CURSOR ecursor IS select emp_id from temp_employee where 'some condition';

BEGIN

FOR empidset in ecursor  

  LOOP

  Select * from 

    (select * from payroll_info where emp_id = empidset.emp_id) a

    left join 

    (select * from benefit_info where emp_id = empidset.emp_id) b 
     on a.emp_id = b.emp_id    

  END LOOP;

END;

执行时，出现以下错误.

On execution, I get the following error..

an INTO clause is expected in this SELECT statement : "Select * from"

任何人都可以解释如何纠正此错误并获得所需的结果吗?

can anyone please explain on how do I correct this error and get the required results?

PS.我正在使用Oracle 9i&蟾蜍9

PS. Im using Oracle 9i & TOAD 9

谢谢，
汤姆

推荐答案

循环内部的SELECT需要具有INTO子句才能处理值-您的代码尚不清楚您要执行的操作，但是我怀疑在游标循环中嵌套的SELECT/JOIN可以更好地写成在主游标中的三表联接.

The SELECT inside of your loop needs to have an INTO clause to process the values - it is not clear from your code what you're trying to do, but I suspect the nested SELECT's/JOIN inside of the cursor loop could be better written as a three table join in the main cursor.

这篇关于使用For循环在Oracle过程中检索多行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！