遍历PL/SQL中的一列 [英] Iterate over a column in PL/SQL

问题描述

我有一个包含EmpID，Empname，Salary的表Emp，并且我正在尝试为每位员工进行计算.但是我在尝试遍历每个emp进行计算时遇到了问题.我不能使用显式游标.

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列表:

So right now I am just trying to create the list of empIDs:

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; 

但是我得到了错误: PLS-00320:此表达式类型的声明不完整或格式错误

But I get the error: PLS-00320: the declaration of the type of this expression is incomplete or malformed

推荐答案

在PL/SQL中迭代表中行的最简单方法是执行

The simplest way to iterate over the rows in a table in PL/SQL is to do something like

BEGIN
  FOR employees IN (SELECT emp_id FROM emp)
  LOOP
    dbms_output.put_line( employees.emp_id );
  END LOOP;
END;

或者，如本例所示，您可以将所有EID值提取到PL/SQL集合中并遍历该集合

Alternately, you could fetch all the EID values into a PL/SQL collection and iterate over the collection, as in this example

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;

但是，如果您的查询可以返回数千行，则将所有数据提取到集合中可能会使用更多的PGA内存，并且您可能需要使用LIMIT子句来提取块中的行.但这似乎已经超越了我们自己.

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.

这篇关于遍历PL/SQL中的一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！