078 - [Oracle PL/SQL] INDICES OF

 

 

# 테이블 인덱스가 순차적이지 않은 경우, 인덱스에 순차접근하면 오류가 발생한다.

declare
    type emp_table_type is table of number index by binary_integer;
    emp_table emp_table_type;
begin
    emp_table(1):=100;
    emp_table(2):=101;
    emp_table(3):=102;
    emp_table(100):=103;
    
    dbms_output.put_line(emp_table.first);
    dbms_output.put_line(emp_table.last);
    
    forall i in emp_table.first..emp_table.last save exceptions  
    update employees
    set salary=salary+5
    where employee_id =emp_table(i);
    commit;

end;
------------------------------------
end;
Error report -
ORA-22160: element at index [4] does not exist
ORA-06512: at line 13
22160. 00000 -  "element at index [%s] does not exist"
*Cause:    Collection element at the given index does not exist.
*Action:   Specify the index of an element which exists.

 

 

 

이러한 오류를 피하기 위해서 자동으로 누락된 인덱스를 스킵하는 옵션이 있다 - INDICES OF

declare
    type emp_table_type is table of number index by binary_integer;
    emp_table emp_table_type;
begin
    emp_table(1):=100;
    emp_table(2):=101;
    emp_table(3):=102;
    emp_table(100):=103;
    dbms_output.put_line(emp_table.first);
    dbms_output.put_line(emp_table.last);
    
    forall i in INDICES OF emp_table  
    update employees
    set salary=salary+5
    where employee_id =emp_table(i);
    commit;

end;
---------

select EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY from employees
where employee_id in (100,101,102,103);
------------------------------------------------------------------------
EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY
----------- -------------------- ------------------------- ----------
        100 Steven               King                           24120
        101 Neena                Kochhar                        17020
        102 Lex                  De Haan                        17020
        103 Alexander            Hunold                          9000
        
------------------------------------------------------------------------
PL/SQL 실행 전/후
------------------------------------------------------------------------

EMPLOYEE_ID FIRST_NAME           LAST_NAME                     SALARY
----------- -------------------- ------------------------- ----------
        100 Steven               King                           24125
        101 Neena                Kochhar                        17025
        102 Lex                  De Haan                        17025
        103 Alexander            Hunold                          9005