018 - [Oracle PL/SQL] Collections - Index by Table of records

<샘플코드에서 사용한 데이터는 HR 스키마이고, 오라클 설치시 생성할 수 있는 기본 스키마 입니다>

 

 

# index by table of record

declare
    type tab_no is table of employees%rowtype
    index by pls_integer;
    
    v_tab_no tab_no;
    v_total number;
begin
    v_tab_no(1).employee_id:=1;
    v_tab_no(1).first_name:='ahmed';
    v_tab_no(1).last_name:='jad';
    
    v_tab_no(2).employee_id:=2;
    v_tab_no(2).first_name:='khaled';
    v_tab_no(2).last_name:='yaser';
    
    dbms_output.put_line(v_tab_no(1).employee_id||' '||v_tab_no(1).first_name||' '||v_tab_no(1).last_name);
    dbms_output.put_line(v_tab_no(2).employee_id||' '||v_tab_no(2).first_name||' '||v_tab_no(2).last_name);

end;
----------------------------
1 ahmed jad
2 khaled yaser

 

 

 

# for문을 이용한 loop 

declare
    type tab_no is table of employees%rowtype
    index by pls_integer;
    
    v_tab_no tab_no;

begin
    for i in 100..104
    loop
        select * into v_tab_no(i)
        from
        employees 
        where employee_id=i;
        dbms_output.put_line(v_tab_no(i).employee_id||' '||
                          v_tab_no(i).first_name||' '||v_tab_no(i).last_name  
                         );
    end loop;
 

end;
-------------------------------------
100 Steven King
101 Neena Kochhar
102 Lex De Haan
103 Alexander Hunold
104 Bruce Ernst

 

 

 

declare
    type tab_no is table of employees%rowtype
    index by pls_integer;
    
    v_tab_no tab_no;

begin
    for i in 100..104
    loop
        select * into v_tab_no(i)
        from
        employees 
        where employee_id=i;
        
    end loop;
    
    for i in v_tab_no.first..v_tab_no.last
    loop
        dbms_output.put_line(i||' '||v_tab_no(i).employee_id||' '||v_tab_no(i).first_name||' '||v_tab_no(i).last_name||' '||v_tab_no(i).salary);
    end loop;
 
end;
-------------------------------------
100 100 Steven King 24000
101 101 Neena Kochhar 17000
102 102 Lex De Haan 17000
103 103 Alexander Hunold 9000
104 104 Bruce Ernst 6000