oracle 迭代 PL/SQL 中的列
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4729838/
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
Iterate over a column in PL/SQL
提问by Leo
I have a table Emp with EmpID, Empname, Salary and I am trying to do a calculation for each employee. But I am having problems trying to iterate over each emp to do the calculation. I cant use explicit cursors though.
我有一个包含 EmpID、Empname、Salary 的 Emp 表，我正在尝试为每个员工进行计算。但是我在尝试迭代每个 emp 以进行计算时遇到问题。虽然我不能使用显式游标。
So right now I am just trying to create the list of empIDs:
所以现在我只是想创建 empID 列表：
Declare
aRows Number;
eid emp_ID%TYPE;
Begin
Select Count(*)
Into aRows
from emp;
Select emp_ID
Into eid
From emp;
FOR days IN 1..Tot_Rows
Loop
Dbms_Output.Put_Line(eid);
eid := eid + 1;
End Loop;
END;
But I get the error: PLS-00320: the declaration of the type of this expression is incomplete or malformed
但我收到错误：PLS-00320：此表达式类型的声明不完整或格式错误
回答by Justin Cave
The simplest way to iterate over the rows in a table in PL/SQL is to do something like
在 PL/SQL 中迭代表中行的最简单方法是执行类似的操作
BEGIN
FOR employees IN (SELECT emp_id FROM emp)
LOOP
dbms_output.put_line( employees.emp_id );
END LOOP;
END;
Alternately, you could fetch all the EID values into a PL/SQL collection and iterate over the collection, as in this example
或者，您可以将所有 EID 值提取到一个 PL/SQL 集合中并迭代该集合，如本例所示
DECLARE
TYPE emp_id_tbl IS TABLE OF emp.emp_id%type;
l_emp_ids emp_id_tbl ;
BEGIN
SELECT emp_id
BULK COLLECT INTO l_emp_ids
FROM emp;
FOR i IN l_emp_ids .FIRST .. l_empnos.LAST
LOOP
dbms_output.put_line( l_emp_ids (i) );
END LOOP;
END;
If your query can return thousands of rows, however, fetching all the data into the collection may use more of the PGA memory than you'd like and you may need to fetch rows in chunks using the LIMIT clause. But that would seem to be getting ahead of ourselves at this point.
但是，如果您的查询可以返回数千行，那么将所有数据提取到集合中可能会使用比您想要的更多的 PGA 内存，并且您可能需要使用 LIMIT 子句以块的形式提取行。但在这一点上，这似乎超越了我们自己。
回答by Alex Poole
Justin Cave has explained how to do it, but to specifically look at the error you got, that was because of this:
Justin Cave 已经解释了如何做到这一点，但要专门查看你得到的错误，那是因为：
eid emp_ID%TYPE;
When using the %TYPEyou have to specify the table name as well as the column name:
使用时，%TYPE您必须指定表名和列名：
eid emp.emp_ID%TYPE;
If you were selecting all the columns in the row you could also look at %ROWTYPE.
如果您选择行中的所有列，您还可以查看%ROWTYPE.
Your approach was also making two assumptions: that the initial select into eidfound the lowest ID, which is by no means guaranteed; and that all the subsequent ID values are sequential. And you're declaring and populating aRowsbut referring to Tot_Rows.
您的方法还做出了两个假设：初始 select intoeid找到了最低的 ID，这绝不是保证；并且所有后续的 ID 值都是连续的。你声明和填充aRows但指的是Tot_Rows.
