问题描述
我无法使更新正常工作。选择工作正常。
create or replace type rec_emp is object(
EMP_ID number,
EMP_F_N varchar2(20),
EMP_L_N varchar2(50),
DEPT number,
emp_salary number);
/
create or replace type T_EMP as table of rec_emp;
/
create or replace type rec_emp_upd is object (
EMP_F_N varchar2(20),
EMP_L_N varchar2(50),
emp_salary NUMBER);
/
create or replace type T_EMP_UPD as table of REC_EMP_UPD;
/
DECLARE
L_EMP T_EMP;
L_EMP_UPD T_EMP_UPD;
begin
select REC_EMP(EMPLOYEE_ID, FIRST_NAME, LAST_NAME, DEPARTMENT_ID, SALARY) BULK COLLECT INTO L_EMP from employees;
forall I in L_EMP.first..L_EMP.last
update employees set salary = salary + 1000
where EMPLOYEE_ID = L_EMP(I).EMP_ID
RETURNING FIRST_NAME, LAST_NAME, SALARY
bulk collect into L_EMP_UPD;
For I in 1..L_EMP_UPD.COUNT
loop
DBMS_OUTPUT.PUT_LINE('New salary of '||L_EMP_UPD(I).FIRST_NAME || L_EMP_UPD(I).LAST_NAME ||' is '||L_EMP_UPD(I).SALARY);
END LOOP;
end;
/
Error report -
ORA-06550: line 12, column 149:
PL/SQL: ORA-00947: not enough values
ORA-06550: line 12, column 1:
PL/SQL: SQL Statement ignored
ORA-06550: line 16, column 53:
PLS-00302: component 'FIRST_NAME' must be declared
ORA-06550: line 16, column 1:
PL/SQL: Statement ignored
06550. 00000 - "line %s, column %s:\n%s"
*Cause: Usually a PL/SQL compilation error. 专家解答
返回rec_emp_upd的实例:
这给出了:
returning rec_emp_upd ( first_name, last_name, salary )
bulk collect into l_emp_upd这给出了:
declare
l_emp t_emp;
l_emp_upd t_emp_upd;
begin
select rec_emp (employee_id,first_name,last_name,department_id,salary)
bulk collect into l_emp
from hr.employees;
forall i in l_emp.first..l_emp.last
update hr.employees
set salary = salary + 1000
where employee_id = l_emp (i).emp_id
returning rec_emp_upd ( first_name, last_name, salary )
bulk collect into l_emp_upd ;
for i in 1..l_emp_upd.count loop
dbms_output.put_line ('New salary of '
|| l_emp_upd (i).emp_f_n
|| l_emp_upd (i).emp_l_n
|| ' is '
|| l_emp_upd (i).emp_salary);
end loop;
end;
/
New salary of DonaldOConnell is 3600
New salary of DouglasGrant is 3600
New salary of JenniferWhalen is 5400
New salary of MichaelHartstein is 14000
New salary of PatFay is 7000
... 文章转载自ASKTOM,如果涉嫌侵权,请发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。




