大神，使用forall 出错，无法调试，请支招

大神，使用forall 报错，无法调试，请支招

CREATE OR REPLACE PROCEDURE give_raises_in_department (
    dept_in IN employee.department_id%TYPE
  , newsal  IN employee.salary%TYPE
)
IS
    TYPE employee_aat IS TABLE OF employee.employee_id%TYPE
       INDEX BY PLS_INTEGER;
    TYPE salary_aat IS TABLE   OF employee.salary%TYPE
       INDEX BY PLS_INTEGER;
     TYPE hire_date_aat IS TABLE OF employee.hire_date%TYPE
        INDEX BY PLS_INTEGER;

     employee_ids employee_aat;
     salaries     salary_aat;
     hire_dates   hire_date_aat;

     approved_employee_ids employee_aat;
     denied_employee_ids   employee_aat;
     denied_salaries       salary_aat;
     denied_hire_dates     hire_date_aat;
 


     PROCEDURE retrieve_employee_info
     IS
     BEGIN
        SELECT employee_id, salary, hire_date
        BULK COLLECT INTO employee_ids, salaries, hire_dates
          FROM employee
         WHERE department_id= dept_in;
     END;


     PROCEDURE partition_by_eligibility
     IS
     BEGIN
        FOR indx IN employee_ids.FIRST .. employee_ids.LAST
        LOOP
           IF comp_analysis_is_eligible(employee_ids(indx))
           THEN
              approved_employee_ids(indx) := employee_ids(indx);
           ELSE
              denied_employee_ids(indx) := employee_ids(indx);
              denied_salaries(indx)     := salaries(indx);
              denied_hire_dates(indx)   := hire_dates(indx);
           END IF;
        END LOOP;
     END;


     PROCEDURE add_to_history
     IS
     BEGIN
        FORALL indx IN denied_employee_ids.FIRST .. denied_employee_ids.LAST
           INSERT INTO employee_history
                       (employee_id
                      , salary
                      , hire_date
                      , activity
                       )
                VALUES (denied_employee_ids(indx)
                      , denied_salaries(indx)
                      , denied_hire_dates(indx)
                      , 'RAISE DENIED'
                       );
     END;


     PROCEDURE give_the_raise
     IS
     BEGIN
        FORALL indx IN approved_employee_ids.FIRST .. approved_employee_ids.LAST
           UPDATE employee
              SET salary = newsal
            WHERE employee_id = approved_employee_ids (indx);
     END;

 BEGIN
     retrieve_employee_info;
     partition_by_eligibility;
     add_to_history;
     give_the_raise;
 END give_raises_in_department;
/

下面建表的SQl

create table EMPLOYEE
(
  EMPLOYEE_ID   NUMBER,
  SALARY        NUMBER,
  HIRE_DATE     DATE,
  DEPARTMENT_ID NUMBER
)

下面是function
  

create or replace function comp_analysis_is_eligible(
dept_id in number
) return boolean
is
begin

if (mod(dept_id,3)=0) then
return true;
else
return false  ;
end if;
end;

报表阿，大神们，请求一下，报ORA-22160:下标[3]中的元素不存在
------解决思路----------------------
partition_by_eligibility这里
你用if else来判断
导致approved。。。和denied。。。数组各有部分元素没有值。然后下面再用没有值的这些元素，就会报错

IF comp_analysis_is_eligible(employee_ids(indx))
           THEN
              approved_employee_ids(indx) := employee_ids(indx);
           ELSE
              denied_employee_ids(indx) := employee_ids(indx);
              denied_salaries(indx)     := salaries(indx);
              denied_hire_dates(indx)   := hire_dates(indx);
           END IF;
改成
IF comp_analysis_is_eligible(employee_ids(indx))
           THEN
              approved_employee_ids(indx) := employee_ids(indx);
              denied_employee_ids(indx) := NULL;
              denied_salaries(indx)     := NULL;
              denied_hire_dates(indx)   := NULL;
           ELSE
             approved_employee_ids(indx) := NULL;
              denied_employee_ids(indx) := employee_ids(indx);
              denied_salaries(indx)     := salaries(indx);
              denied_hire_dates(indx)   := hire_dates(indx);
           END IF;

这样应该就不会报错了。至于业务问题要你自己来判断