oracle 如何在Oracle过程中将选择结果存储到变量中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1347091/
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
How to store selection result in to variable in Oracle procedure
提问by Micha? Ziober
I write a simple procedure. I try to store selection result in variable. I use "SELECT INTO" query but I can not doing this.
我写了一个简单的程序。我尝试将选择结果存储在变量中。我使用“SELECT INTO”查询，但我不能这样做。
Example:
例子：
DECLARE
v_employeeRecord employee%ROWTYPE;
BEGIN
SELECT * INTO v_employeeRecord
FROM Employee WHERE Salary > 10;
END;
回答by Adam Paynter
You have a couple options. You could turn that query into a cursor:
你有几个选择。您可以将该查询转换为游标：
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;
Or, you can create a TABLEvariable:
或者，您可以创建一个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;
I haven't tried these samples in Oracle, so you may get compiler errors...
我还没有在 Oracle 中尝试过这些示例，因此您可能会遇到编译器错误...
回答by Vincent Malgrat
IF your SELECT returns more than one row, you won't be able to use the SELECT INTO synthax.
如果您的 SELECT 返回多于一行，您将无法使用 SELECT INTO 合成器。
You will need to build a loop to navigate through the resulte set:
您将需要构建一个循环来浏览结果集：
Adamdemonstrated how you would use an explicit cursor and a bulk collect loop. I will show how you can build the simplest loop possible (implicit cursor, doesn't need a DECLARE section):
Adam演示了如何使用显式游标和批量收集循环。我将展示如何构建最简单的循环（隐式游标，不需要 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;
