231030 PL/SQL 중첩테이블(NESTED TABLE) , VARRAY(VARIABL-SIZE ARRAY) 가변길이의 배열방식, 2차원 배열

■ NESTED TABLE(중첩테이블)
- index by table 과 달리 인덱스키(요소번호)는 자동으로 1번 시작해서 최대 2G 생성할 수 있다. 

# v_city tab_char_type := tab_char_type('서울', '대전', '부산', '광주');
- 사용하는 경우: 값을 알고 있고, 값이 적을 경우에 사용

DECLARE
    TYPE tab_char_type IS TABLE OF varchar2(10);
    v_city tab_char_type := tab_char_type('서울', '대전', '부산', '광주'); -- 초기값입력 및 타입
BEGIN
    DBMS_OUTPUT.PUT_LINE(v_city.FIRST);
    DBMS_OUTPUT.PUT_LINE(v_city.LAST);
    DBMS_OUTPUT.PUT_LINE(v_city.NEXT(1));
    DBMS_OUTPUT.PUT_LINE(v_city.PRIOR(4));
    DBMS_OUTPUT.PUT_LINE(v_city.COUNT);
    
    v_city.DELETE(2);
    DBMS_OUTPUT.PUT_LINE(v_city.COUNT);    
    
    FOR i IN v_city.FIRST .. v_city.LAST LOOP
        IF v_city.EXISTS(i) THEN
            DBMS_OUTPUT.PUT_LINE(v_city(i));
        ELSE
            DBMS_OUTPUT.PUT_LINE(i||' 요소는 존재하지 않습니다.');
        END IF;
    END LOOP;
END;
/

# BEGIN 에서 배열 추가
- 오류발생 ORA-06533: Subscript beyond count

DECLARE
    TYPE tab_char_type IS TABLE OF varchar2(10);
    v_city tab_char_type := tab_char_type('서울', '대전', '부산', '광주'); -- 초기값입력 및 사이즈고정, 타입
BEGIN    
    v_city(5) := '대구'; -- 사이즈 고정으로 오류
    
    FOR i IN v_city.FIRST .. v_city.LAST LOOP
        IF v_city.EXISTS(i) THEN
            DBMS_OUTPUT.PUT_LINE(v_city(i));
        ELSE
            DBMS_OUTPUT.PUT_LINE(i||' 요소는 존재하지 않습니다.');
        END IF;
    END LOOP;
END;
/

# 사이즈 할당  

-  변수명.EXTEND : 배열사이즈 1개 추가

DECLARE
    TYPE tab_char_type IS TABLE OF varchar2(10);
    v_city tab_char_type := tab_char_type('서울', '대전', '부산', '광주'); -- 초기값입력 및 사이즈고정, 타입
BEGIN    
    v_city.EXTEND(1); -- 사이즈 1 추가
    v_city(5) := '대구'; 
    
    FOR i IN v_city.FIRST .. v_city.LAST LOOP
        IF v_city.EXISTS(i) THEN
            DBMS_OUTPUT.PUT_LINE(v_city(i));
        ELSE
            DBMS_OUTPUT.PUT_LINE(i||' 요소는 존재하지 않습니다.');
        END IF;
    END LOOP;
END;
/

# 배열 사이즈 추가

- 변수명. EXTEND(N) : N개의 새로운 요소 추가

DECLARE
    TYPE tab_char_type IS TABLE OF varchar2(10);
    v_city tab_char_type := tab_char_type('서울', '대전', '부산', '광주'); -- 초기값입력 및 사이즈고정, 타입
BEGIN    
    v_city.EXTEND(10); -- 10개의 새로운 요소를 추가한다.
    v_city(5) := '대구';
    DBMS_OUTPUT.PUT_LINE(v_city.COUNT);
    
    DBMS_OUTPUT.PUT_LINE(v_city.FIRST);
    DBMS_OUTPUT.PUT_LINE(v_city.LAST);
    
    FOR i IN v_city.FIRST .. v_city.LAST LOOP
        IF v_city.EXISTS(i) THEN
            DBMS_OUTPUT.PUT_LINE(v_city(i));
        ELSE
            DBMS_OUTPUT.PUT_LINE(i||' 요소는 존재하지 않습니다.');
        END IF;
    END LOOP;
END;
/

# 배열 사이즈 복제

- 변수명.EXTEND(N,M) = (N번 복제, M번 행)

DECLARE
    TYPE tab_char_type IS TABLE OF varchar2(10);
    v_city tab_char_type := tab_char_type('서울', '대전', '부산', '광주'); -- 초기값입력 및 사이즈고정, 타입
BEGIN    
    v_city.EXTEND(10); -- 10개의 새로운 요소를 추가한다.
    v_city.EXTEND(3,1); -- EXTEND(N,M) = (N번 복제, M번 행)
    v_city(5) := '대구';
    DBMS_OUTPUT.PUT_LINE(v_city.COUNT);
    
    DBMS_OUTPUT.PUT_LINE(v_city.FIRST);
    DBMS_OUTPUT.PUT_LINE(v_city.LAST);
    
    FOR i IN v_city.FIRST .. v_city.LAST LOOP
        IF v_city.EXISTS(i) THEN
            DBMS_OUTPUT.PUT_LINE(v_city(i));
        ELSE
            DBMS_OUTPUT.PUT_LINE(i||' 요소는 존재하지 않습니다.');
        END IF;
    END LOOP;
END;
/

# 선언 지정

DECLARE
    TYPE emp_id_type IS TABLE OF number;
    v_id emp_id_type := emp_id_type(100, 110, 200);
    
    a  VARCHAR2(30);
    b  DATE;
    c  VARCHAR2(50);
    
BEGIN
    
    FOR i IN v_id.FIRST .. v_id.LAST LOOP
        SELECT e.last_name, e.hire_date, d.department_name
        INTO a, b, c
        FROM hr.employees e, hr.departments d 
        WHERE e.department_id = d.department_id
        AND employee_id = v_id(i);
        
        DBMS_OUTPUT.PUT_LINE(v_id(i)||' 사원의 이름은 '||a||', 입사한 날짜는 '||b||', 근무 부서이름은 '||c||' 입니다.');
    END LOOP;
END;
/

# 할당 미지정으로 오류발생
- 오류 ORA-06531: Reference to uninitialized collection
- 선언부에서 'v_id emp_id_type := emp_id_type(100, 110, 200);' 지정해주자!

DECLARE
    TYPE emp_id_type IS TABLE OF number;
    v_id emp_id_type;
    
    a  VARCHAR2(30);
    b  DATE;
    c  VARCHAR2(50);
    
BEGIN
    v_id.EXTEND;
    v_id(1) := 100;
    
    FOR i IN v_id.FIRST .. v_id.LAST LOOP
        SELECT e.last_name, e.hire_date, d.department_name
        INTO a, b, c
        FROM hr.employees e, hr.departments d 
        WHERE e.department_id = d.department_id
        AND employee_id = v_id(i);
        
        DBMS_OUTPUT.PUT_LINE(v_id(i)||' 사원의 이름은 '||a||', 입사한 날짜는 '||b||', 근무 부서이름은 '||c||' 입니다.');
    END LOOP;
END;
/

■ VARRAY(VARIABL-SIZE ARRAY) : 가변길이의 배열방식
- VARRAY 고정된 상한값이 있다. 
- 최대 2G 까지 가능.
- NESTED TABLE와 차이점은 고정된 상한값이 있다는 점이다. 

# VARRAY 설정

DECLARE
    TYPE tab_char_type IS VARRAY(5) OF varchar2(10); -- 입력값: 최소 1 ~ 최대 5
    v_city tab_char_type := tab_char_type('서울', '대전', '부산', '광주'); 
BEGIN
    
    FOR i IN v_city.FIRST .. v_city.LAST LOOP
        IF v_city.EXISTS(i) THEN
            DBMS_OUTPUT.PUT_LINE(v_city(i));
        ELSE
            DBMS_OUTPUT.PUT_LINE(i||' 요소는 존재하지 않습니다.');
        END IF;
    END LOOP;
END;
/

 

# 오류발생 ORA-06533: Subscript beyond count

DECLARE
    TYPE tab_char_type IS VARRAY(5) OF varchar2(10); -- 입력값: 최소 1 ~ 최대 5
    v_city tab_char_type := tab_char_type('서울', '대전', '부산', '광주'); 
BEGIN
    v_city(5) := '인천';
    
    FOR i IN v_city.FIRST .. v_city.LAST LOOP
        IF v_city.EXISTS(i) THEN
            DBMS_OUTPUT.PUT_LINE(v_city(i));
        ELSE
            DBMS_OUTPUT.PUT_LINE(i||' 요소는 존재하지 않습니다.');
        END IF;
    END LOOP;
END;
/

# 변수명.EXTEND; 으로 상한값만큼만 추가

DECLARE
    TYPE tab_char_type IS VARRAY(5) OF varchar2(10); -- 입력값: 최소 1 ~ 최대 5
    v_city tab_char_type := tab_char_type('서울', '대전', '부산', '광주'); 
BEGIN
    v_city.EXTEND;
    v_city(5) := '인천';
    
    FOR i IN v_city.FIRST .. v_city.LAST LOOP
        IF v_city.EXISTS(i) THEN
            DBMS_OUTPUT.PUT_LINE(v_city(i));
        ELSE
            DBMS_OUTPUT.PUT_LINE(i||' 요소는 존재하지 않습니다.');
        END IF;
    END LOOP;
END;
/

# 오류 ORA-06532: Subscript outside of limit
- 상한값 이상으로 추가하게 되면 오류발생하니 주의할 사항! 

DECLARE
    TYPE tab_char_type IS VARRAY(5) OF varchar2(10); -- 입력값: 최소 1 ~ 최대 5
    v_city tab_char_type := tab_char_type('서울', '대전', '부산', '광주'); 
BEGIN
    v_city.EXTEND(2);
    v_city(5) := '인천';
    
    FOR i IN v_city.FIRST .. v_city.LAST LOOP
        IF v_city.EXISTS(i) THEN
            DBMS_OUTPUT.PUT_LINE(v_city(i));
        ELSE
            DBMS_OUTPUT.PUT_LINE(i||' 요소는 존재하지 않습니다.');
        END IF;
    END LOOP;
END;
/

# DELETE 오류
- PLS-00306: wrong number or types of arguments in call to 'DELETE'

DECLARE
    TYPE tab_char_type IS VARRAY(5) OF varchar2(10); -- 입력값: 최소 1 ~ 최대 5
    v_city tab_char_type := tab_char_type('서울', '대전', '부산', '광주'); 
BEGIN
    v_city.EXTEND(2);
    v_city(5) := '인천';
    
    -- 삭제 
    v_city.DELETE(2);
    
    FOR i IN v_city.FIRST .. v_city.LAST LOOP
        IF v_city.EXISTS(i) THEN
            DBMS_OUTPUT.PUT_LINE(v_city(i));
        ELSE
            DBMS_OUTPUT.PUT_LINE(i||' 요소는 존재하지 않습니다.');
        END IF;
    END LOOP;
END;
/

# TRIM : 삭제 기능이 있으나, 뒤에 있는 요소부터 삭제.

DECLARE
    TYPE tab_char_type IS VARRAY(5) OF varchar2(10); -- 입력값: 최소 1 ~ 최대 5
    v_city tab_char_type := tab_char_type('서울', '대전', '부산', '광주'); 
BEGIN
    v_city.EXTEND;
    v_city(5) := '인천';
    
    -- 제일 뒤에 있는 요소만 삭제
    v_city.TRIM;
    
    FOR i IN v_city.FIRST .. v_city.LAST LOOP
        IF v_city.EXISTS(i) THEN
            DBMS_OUTPUT.PUT_LINE(v_city(i));
        ELSE
            DBMS_OUTPUT.PUT_LINE(i||' 요소는 존재하지 않습니다.');
        END IF;
    END LOOP;
END;
/

# VARRAY 타입으로 변경

DECLARE
    TYPE emp_id_type IS VARRAY(3) OF number;
    v_id emp_id_type := emp_id_type(100, 110, 200);
    
    a  VARCHAR2(30);
    b  DATE;
    c  VARCHAR2(50);
    
BEGIN

    FOR i IN v_id.FIRST .. v_id.LAST LOOP
        SELECT e.last_name, e.hire_date, d.department_name
        INTO a, b, c
        FROM hr.employees e, hr.departments d 
        WHERE e.department_id = d.department_id
        AND employee_id = v_id(i);
        
        DBMS_OUTPUT.PUT_LINE(v_id(i)||' 사원의 이름은 '||a||', 입사한 날짜는 '||b||', 근무 부서이름은 '||c||' 입니다.');
    END LOOP;
END;
/

■ 2차원 배열
1. 레코드 타입을 먼저 선언 
2. INDEX BY TABLE 생성해야 한다.

FOR i IN 1..5 LOOP
    SELECT *
    INTO 2차원 배열변수(i)
    FROM hr.departments
    WHERE department_id = i*10;
END LOOP;

# RECORD 레코드 함수 사용, FOR문외 출력

DECLARE
    TYPE dept_rec_type IS RECORD (
        id    NUMBER,
        name  VARCHAR2(30),
        mgr   NUMBER,
        loc   NUMBER
    );
    
    v_rec dept_rec_type;
BEGIN
    FOR i IN 1..5 LOOP
        SELECT *
        INTO v_rec
        FROM hr.departments
        WHERE department_id = i*10;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE(v_rec.id||' '||v_rec.name||' '||v_rec.mgr||' '||v_rec.loc);
END;
/

# FOR문내 출력

DECLARE
    TYPE dept_rec_type IS RECORD (
        id    NUMBER,
        name  VARCHAR2(30),
        mgr   NUMBER,
        loc   NUMBER
    );
    
    v_rec dept_rec_type;
BEGIN
    FOR i IN 1..5 LOOP
        SELECT *
        INTO v_rec
        FROM hr.departments
        WHERE department_id = i*10;
        
         DBMS_OUTPUT.PUT_LINE(v_rec.id||' '||v_rec.name||' '||v_rec.mgr||' '||v_rec.loc);   
    END LOOP;
   
END;
/

# RECORD 사용하지 않는다면, 테이블명%rowtype 사용 

DECLARE    
    v_rec hr.departments%rowtype;
BEGIN
    FOR i IN 1..5 LOOP
        SELECT *
        INTO v_rec
        FROM hr.departments
        WHERE department_id = i*10;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE(v_rec.department_id||' '||v_rec.department_name||' '||v_rec.manager_id||' '||v_rec.location_id);
END;
/

# 2차원배열 : 기본적으로 레코드형식 선언 

DECLARE
    TYPE dept_rec_type IS RECORD (
        id    NUMBER,
        name  VARCHAR2(30),
        mgr   NUMBER,
        loc   NUMBER
    );    
    v_rec dept_rec_type; -- 레코드 배열의 변수
    
    TYPE dept_tab_type IS TABLE OF v_rec%TYPE /*레코드타입변수*/ INDEX BY PLS_INTEGER;
    v_tab dept_tab_type; -- 2차원 배열의 변수
BEGIN
    FOR i IN 1..5 LOOP
        SELECT *
        INTO v_tab(i) -- 2차원배열
        FROM hr.departments
        WHERE department_id = i*10;        
    END LOOP;
    
    FOR i IN v_tab.FIRST .. v_tab.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(v_tab(i).id||' '||v_tab(i).name||' '||v_tab(i).mgr||' '||v_tab(i).loc);    
    END LOOP;
    
END;
/

# ★ 레코드변수 없이 유저 타입 사용

DECLARE
    TYPE dept_rec_type IS RECORD (
        id    NUMBER,
        name  VARCHAR2(30),
        mgr   NUMBER,
        loc   NUMBER
    );
    
    TYPE dept_tab_type IS TABLE OF dept_rec_type INDEX BY PLS_INTEGER;
    v_tab dept_tab_type; -- 2차원 배열의 변수
BEGIN
    FOR i IN 1..5 LOOP
        SELECT *
        INTO v_tab(i)
        FROM hr.departments
        WHERE department_id = i*10;        
    END LOOP;
    
    FOR i IN v_tab.FIRST .. v_tab.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(v_tab(i).id||' '||v_tab(i).name||' '||v_tab(i).mgr||' '||v_tab(i).loc);    
    END LOOP;
    
END;
/

# 레코드 타입 x, 테이블의 구조, 타입을 동일하게 사용 + 출력시 테이블 컬럼명으로 변경.(흔하지 않는 구조)

DECLARE
    TYPE dept_tab_type IS TABLE OF hr.departments%rowtype INDEX BY PLS_INTEGER;
    v_tab dept_tab_type; -- 2차원 배열의 변수
BEGIN
    FOR i IN 1..5 LOOP
        SELECT *
        INTO v_tab(i) 
        FROM hr.departments
        WHERE department_id = i*10;        
    END LOOP;
    
    FOR i IN v_tab.FIRST .. v_tab.LAST LOOP
        DBMS_OUTPUT.PUT_LINE(v_tab(i).department_id||' '||v_tab(i).department_name||' '||v_tab(i).manager_id||' '||v_tab(i).location_id);    
    END LOOP;
    
END;
/