oracle 使用 BULK COLLECT 将多个列分配给集合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/26087449/
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
Assigning more than one column to a collection using BULK COLLECT
提问by redsoxlost
I am not sure why I am getting this error with this code, Please help me with debugging this code Thanks in advance.
我不确定为什么我会收到此代码的错误，请帮助我调试此代码，提前致谢。
declare
type emp_t is table of employees%rowtype
index by pls_integer;
rec emp_t;
begin
select employee_id,salary,manager_id bulk collect into rec
from employees where rownum <100;
forall i in 1..rec.last
update employees
set salary=salary+10
where employee_id=rec(i).employee_id;
end;
ORA-06550: line 7, column 3:
PL/SQL: ORA-00913: too many values
ORA-06550: line 6, column 3:
PL/SQL: SQL Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error.
*Action:
I have changed the code to the below format but it's still giving me "expression is of wrong type error"
我已将代码更改为以下格式，但它仍然给我“表达式类型错误”
declare
type emp_t is table of employees%rowtype
index by pls_integer;
rec emp_t;
begin
for val in (
select employee_id,salary,manager_id
from employees where rownum <100)
loop
rec:=val;
end loop;
forall i in 1..rec.last
update employees
set salary=salary+10
where employee_id=rec(i).employee_id;
end;
回答by Sylvain Leroux
Use either one or the other:
使用其中之一：
TYPE emp_t IS TABLE OF employees%ROWTYPE INDEX BY PLS_INTEGER;
-- ^^^^^^^^^^^^^^^^^
-- each record is "one row" of table `employees`
...
SELECT * BULK COLLECT INTO rec FROM employees WHERE ROWNUM < 100;
-- ^
-- get all columns for each row
Or
或者
TYPE emp_rec IS RECORD (
employee_id employees.employee_id%TYPE,
salary employees.salary%TYPE,
manager_id employees.manager_id%TYPE
);
TYPE emp_t IS TABLE OF emp_rec
-- ^^^^^^^
-- each record only contains the necessary fields
...
SELECT employee_id,salary,manager_id BULK COLLECT INTO rec FROM employees WHERE ROWNUM < 100;
-- ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
-- get only what we need (and in that particular order)
Very probably you are looking for the second form. Using a custom type for the RECORDyou are trying to bulk collect. Please notice the use of %TYPEattributein the record declaration. That's an alias to the type of each column.
您很可能正在寻找第二种形式。为RECORD您尝试批量收集的使用自定义类型。请注意记录声明中%TYPE属性的使用。这是每列类型的别名。
回答by user1897277
emp_tis declared as ROWTYPE, that includes all the columns,
but while retrieving records your are retrieving only employee_id,salary,manager_id,
that could be the reason of your error.
emp_t声明为ROWTYPE，其中包括所有列，
但是在检索记录时您只检索employee_id,salary,manager_id，
这可能是您出错的原因。
Try this :
尝试这个 ：
declare
type emp_t is table of employees%rowtype
index by pls_integer;
rec emp_t;
begin
select employee_id,salary,manager_id bulk collect into rec
from employees where rownum <100;
forall i in 1..rec.last
update employees
set salary=salary+10
where employee_id=rec(i).employee_id;
end;
The second one seem to have other issues like TYPE difference VAR and REC
第二个似乎还有其他问题，例如 TYPE 差异 VAR 和 REC
