如何存储选择结果变量在Oracle过程(How to store selection result

我写了一个简单的程序。 我尝试存储的选择结果的变量。 我使用“SELECT INTO”查询，但我不能这样做。

例：

DECLARE
     v_employeeRecord  employee%ROWTYPE;
BEGIN
 SELECT * INTO v_employeeRecord
      FROM Employee WHERE Salary > 10;
END;

Answer 1:

你有几个选项。 你可以把该查询到游标：

DECLARE
     CURSOR v_employeeRecords IS
          SELECT * FROM Employee WHERE Salary > 10;
     v_employeeRecord  employee%ROWTYPE;
BEGIN
     FOR v_employeeRecord IN v_employeeRecords LOOP
          /* Do something with v_employeeRecord */
     END LOOP;
END;

或者，你可以创建一个TABLE变量：

DECLARE
     v_employeeRecord  employee%ROWTYPE;
     v_employeeRecords IS TABLE OF employee%ROWTYPE;
     i BINARY_INTEGER;
BEGIN
 SELECT * BULK COLLECT INTO v_employeeRecords
      FROM Employee WHERE Salary > 10;

 i := v_employeeRecords.FIRST;
 WHILE v_employeeRecords.EXISTS(i) LOOP
     v_employeeRecord := v_employeeRecords(i);
     /* Do something with v_employeeRecord */
     i := v_employeeRecords.NEXT(i);
 END;
END;

我还没有尝试在甲骨文这些样品，所以你可能会得到编译器错误...

Answer 2:

如果你的SELECT返回多行，你将无法使用SELECT INTO synthax。

您将需要建立一个遍历resulte集导航：

亚当演示了如何使用显式游标和散装收集循环。 我会告诉你如何构建最简单的循环可能（隐式游标，不需要DECLARE部分）：

BEGIN
   FOR c_emp IN (SELECT * 
                   FROM Employee 
                  WHERE Salary > 10) LOOP
      /* do something with each row, for example:*/
      UPDATE foo SET bar = bar + c_emp.salary WHERE id = c_emp.id;
   END LOOP;
END;

文章来源: How to store selection result in to variable in Oracle procedure