Oracle 批量更新，返回批量收集失败，ORA-00947

ASKTOM 2019-11-30

731

问题描述

我无法使更新正常工作。选择工作正常。

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
...复制