使用 For 循环在 Oracle 过程中检索多行
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/3451499/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using For loop to retrieve multiple rows in Oracle procedure
提问by tom
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)
我正在研究存储过程，我需要检索一组结果并单独处理每个元素，然后返回整个结果。（使用 3 个不同的表）
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. Im using Oracle 9i & TOAD 9
附注。我使用 Oracle 9i & TOAD 9
Thanks,
Tom
谢谢，
汤姆
回答by dpbradley
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.
循环内的 SELECT 需要有一个 INTO 子句来处理值 - 从您的代码中不清楚您要做什么，但我怀疑游标循环内的嵌套 SELECT/JOIN 可以更好地编写为主游标中的三表连接。
回答by Allan
Below is a possible solution, making a fair number of assumptions. As written, it returns the result as a ref cursor containing data from all 3 tables (it would be trivial to make it return a ref cursor for each table, but that would be more work for a dubious result).
下面是一个可能的解决方案，做出了相当多的假设。正如所写的那样，它将结果作为包含来自所有 3 个表的数据的引用游标返回（让它为每个表返回一个引用游标是微不足道的，但对于可疑的结果来说，这将是更多的工作）。
However, unless you're really doing something in the PL/SQL that you can't do in SQL, you'd be much better off doing this directly in SQL.
但是，除非您真的在 PL/SQL 中做一些在 SQL 中无法做的事情，否则直接在 SQL 中做这件事会好得多。
create object EMP_PAYROLL_BENEFIT as object (
em_id number,
some_payroll_column number,
some_benefit_column number);
create type NT_EMP_PAYROLL_BENEFIT as table of EMP_PAYROLL_BENEFIT;
create or replace procedure GET_EMP_RSLT(p_output OUT sys_refcursor) IS
CURSOR ecursor IS select emp_id
from temp_employee te
join payroll_info pi
on te.emp_id = pi.emp_id
join benefit_info bi
on te.emp_id = bi.emp_id
where some_column = 'some condition';
v_results NT_EMP_PAYROLL_BENEFIT;
BEGIN
open ecursor;
fetch ecursor bulk collect into v_results;
close ecursor;
for i = v_results.first..v_results.last loop
v_results.some_benefit_column := some_payroll_column + i;
end loop;
open p_output for select * from table(v_results);
end;
回答by a'r
You need to add an INTO clause to specify which local variables to place the selected data, eg.
您需要添加一个 INTO 子句来指定放置所选数据的局部变量，例如。
select ID, Name
into myID, myName
from emp
回答by ThinkJet
There are too many syntactical and ideological errors in your code. Therefore, read, please, PL/SQL documentation here, especially PL/SQL Architecture sectionto understand difference between SQL and PL/SQL (generally SQL - query language, PL/SQL - programming language) and sections for your case:
你的代码中有太多的语法和意识形态错误。因此，请阅读此处的 PL/SQL 文档，尤其是PL/SQL 体系结构部分，以了解 SQL 和 PL/SQL（通常是 SQL - 查询语言，PL/SQL - 编程语言）和适用于您的情况的部分之间的区别：
- "Understanding PL/SQL Procedures"and "Understanding PL/SQL Functions"
- "Cursor FOR Loops"and "Using cursor FOR Loops"
Full PL/SQL reference for Oracle 9i R2 available at this link.
可在此链接中获得 Oracle 9i R2 的完整 PL/SQL 参考。
Set of all Oracle 9i R2 documentation can be found here.
可以在此处找到所有 Oracle 9i R2 文档集。
