251107 TIL

2025. 11. 7. 18:04ㆍCourses/아이티윌 오라클 DBA 과정

조합 데이터 유형(Cont.)

연관 배열(associative array, index by table)

2개의 열을 포함하는 조합 데이터 유형
key, value 집합으로 구성
key : 정수(pls_integer(-2GB ~ 2GB)), 문자열(varchar2(32767(11g), 65536(12c))) 데이터 유형의 primary key(unique, not null)
value : 스칼라, 레코드 데이터 유형

DECLARE
    TYPE tab_char_type IS TABLE OF varchar2(10) INDEX BY pls_integer;
    v_city tab_char_type;
BEGIN
    v_city(1) := '서울';
    v_city(2) := '부산';
    v_city(3) := '광주';
    v_city(4) := '대구';

    dbms_output.put_line(v_city(1));
    dbms_output.put_line(v_city(2));
    dbms_output.put_line(v_city(3));
    dbms_output.put_line(v_city(4));
END;
/

배열 메소드

DECLARE
    TYPE tab_char_type IS TABLE OF varchar2(10) INDEX BY pls_integer;
    v_city tab_char_type;
BEGIN
    v_city(1) := '서울';
    v_city(2) := '부산';
    v_city(3) := '광주';
    v_city(4) := '대구';

    dbms_output.put_line('count : ' || v_city.count);
    dbms_output.put_line('first : ' || v_city.first);
    dbms_output.put_line('last : ' || v_city.last);
    dbms_output.put_line('next(1) : ' || v_city.next(1));
    dbms_output.put_line('prior(3) : ' || v_city.prior(3));
END;
/

count : 배열 안에 포함하는 값의 수
first : 배열의 첫 번째(가장 작은 번호) 인덱스 번호
last : 배열의 마지막(가장 큰 번호) 인덱스 번호
next(n) : 배열 인덱스 n번 뒤에 오는 인덱스 번호
prior(n) : 배열 인덱스 n번 앞에 오는 인덱스 번호
delete : 배열 모든 값을 삭제
delete(n) : n번 값을 삭제
delete(m, n) : m ~ n번 값 삭제
exists(n) : n번 key-value가 존재하면 TRUE, 존재하지 않으면 FALSE

반복문으로 배열 요소 조회

DECLARE
    TYPE tab_char_type IS TABLE OF varchar2(10) INDEX BY pls_integer;
    v_city tab_char_type;
BEGIN
    v_city(1) := '서울';
    v_city(2) := '부산';
    v_city(3) := '광주';
    v_city(4) := '대구';

    FOR i IN v_city.first..v_city.last LOOP
        dbms_output.put_line(v_city(i));
    END LOOP;
END;
/

존재하지 않는 요소 접근 → 오류

DECLARE
    TYPE tab_char_type IS TABLE OF varchar2(10) INDEX BY pls_integer;
    v_city tab_char_type;
BEGIN
    v_city(1) := '서울';
    v_city(2) := '부산';
    v_city(3) := '광주';
    v_city(4) := '대구';

    v_city.delete(3);

    FOR i IN v_city.first..v_city.last LOOP
        dbms_output.put_line(v_city(i));
    END LOOP;
END;
/

-- ORA-01403: no data found

IF 문 + exists(i) 를 통해 해당 요소가 존재하는지 확인 후 처리

-- 오류 해결
DECLARE
    TYPE tab_char_type IS TABLE OF varchar2(10) INDEX BY pls_integer;
    v_city tab_char_type;
BEGIN
    v_city(1) := '서울';
    v_city(2) := '부산';
    v_city(3) := '광주';
    v_city(4) := '대구';

    v_city.delete(3);

    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('key-value가 존재하지 않습니다.');
        END IF;
    END LOOP;
END;
/

다중 행 수정

DECLARE
    TYPE tab_id_type IS TABLE OF number INDEX BY pls_integer;
    v_tab tab_id_type;
BEGIN
    v_tab(1) := 100;
    v_tab(2) := 110;
    v_tab(3) := 120;

    FOR i IN v_tab.first..v_tab.last LOOP
        UPDATE hr.employees
        SET salary = salary * 1.1
        WHERE employee_id = v_tab(i);
    END LOOP;

    ROLLBACK;
END;
/

존재하지 않는 요소 접근 → 오류
- 배열 변수에 없는 요소를 참조하려고 하는 순간 오류 발생
- 프로그램은 비정상적인 종료가 발생. 이 때 수행하고 있는 트랜잭션들은 자동 rollback 발생

DECLARE
    TYPE tab_id_type IS TABLE OF number INDEX BY pls_integer;
    v_tab tab_id_type;
BEGIN
    v_tab(1) := 100;
    v_tab(2) := 110;
    v_tab(3) := 120;
    v_tab(5) := 130;

    FOR i IN v_tab.first..v_tab.last LOOP
        UPDATE hr.employees
    SET salary = salary  * 1.1
    WHERE employee_id = v_tab(i);
    END LOOP;

    ROLLBACK;
END;
/

-- ORA-01403: no data found

문제

-- [문제12] 배열 변수에 있는 100,101,102,103,104,200 사원들의 근무 년수를 출력하고 근무 년수가 20년 이상이면 salary를 10% 인상하는 프로그램을 작성해주세요.
DECLARE
    TYPE id_type IS TABLE OF number INDEX BY pls_integer;
    v_id id_type;
    v_year number;
    v_sal employees.salary%type;
    v_new_sal v_sal%type;
BEGIN
    v_id(1) := 100;
    v_id(2) := 101;
    v_id(3) := 102;
    v_id(4) := 103;
    v_id(5) := 104;
    v_id(6) := 200;

    FOR i in v_id.first..v_id.last LOOP
        IF v_id.exists(i) THEN
            SELECT trunc(months_between(sysdate, hire_date)/12), salary
            INTO v_year, v_sal
            FROM hr.employees
            WHERE employee_id = v_id(i);

            IF v_year >= 20 THEN
                UPDATE hr.employees
                SET salary = salary * 1.1
                WHERE employee_id = v_id(i);

                IF sql%found THEN
                    SELECT salary
                    INTO v_new_sal
                    FROM hr.employees
                    WHERE employee_id = v_id(i);

                    dbms_output.put_line(v_id(i) || '번 사원의 근무 년수는 ' || v_year || '년이므로 급여 인상');
                    dbms_output.put_line(v_sal || ' -> ' || v_new_sal);
                ELSE
                    dbms_output.put_line(v_id(i) || '번 사원 급여 인상 실패 오류');
                END IF;
            ELSE
                dbms_output.put_line(v_id(i) || '번 사원의 근무 년수는 ' || v_year || '년이므로 급여 인상 불가');
            END IF;
            dbms_output.new_line();
        END IF;
    END LOOP;

    ROLLBACK;
END;
/

2차원 배열

-- %rowtype을 이용한 이차원 배열
DECLARE
    TYPE tab_type IS TABLE OF departments%rowtype INDEX BY pls_integer;
    v_tab tab_type;
BEGIN
    FOR i IN 1..5 LOOP
        SELECT *
        INTO v_tab(i)
        FROM hr.departments
        WHERE department_id = 10 * i;
    END LOOP;  

    FOR j IN v_tab.first..v_tab.last LOOP
        dbms_output.put_line(v_tab(j).department_id || ' ' || v_tab(j).department_name || ' ' || v_tab(j).manager_id || ' ' || v_tab(j).location_id);
    END LOOP;

END;
/

-- record 타입을 직접 선언한 이차원 배열
DECLARE
    TYPE rec_type IS RECORD(
        id number,
        name varchar2(30),
        mgr number,
        loc number
    );
    TYPE tab_type IS TABLE OF rec_type INDEX BY pls_integer;
    v_tab tab_type;
BEGIN
    FOR i in 1..5 LOOP
        SELECT *
        INTO v_tab(i)
        FROM hr.departments
        WHERE department_id = 10 * i;
    END LOOP;

    FOR j IN v_tab.first..v_tab.last LOOP
        dbms_output.put_line(v_tab(j).id || ' ' || v_tab(j).name || ' ' || v_tab(j).mgr || ' ' || v_tab(j).loc);
    END LOOP;
END;
/

문제

-- [문제13] 배열변수안에 있는 사원번호(100,110,200)를 기준으로 그 사원의 last_name,hire_date,department_name 정보를 배열변수에 담아 놓은 후 화면에 출력하는 프로그램 작성해주세요.
DECLARE
    TYPE id_type IS TABLE OF number INDEX BY pls_integer;
    TYPE rec_type IS RECORD(
        last_name employees.last_name%type,
        hire_date employees.hire_date%type,
        department_name departments.department_name%type
    );
    TYPE tab_type IS TABLE OF rec_type INDEX BY pls_integer;
    v_id id_type;
    v_tab tab_type;
BEGIN
    v_id(1) := 100;
    v_id(2) := 110;
    v_id(3) := 200;

    FOR i IN v_id.first..v_id.last LOOP
        SELECT e.last_name, e.hire_date, d.department_name
        INTO v_tab(i)
        FROM hr.employees e, hr.departments d
        WHERE e.department_id = d.department_id
        AND e.employee_id = v_id(i);
    END LOOP;

    FOR j IN v_tab.first..v_tab.last LOOP
        dbms_output.put_line(v_tab(j).last_name || ' ' || v_tab(j).hire_date || ' ' || v_tab(j).department_name);
    END LOOP;
END;
/

중첩 테이블(Nested Table)

최대 2GB까지 동적으로 증가 가능
배열 변수에 저장하는 값들을 미리 알고 있을 때 사용하면 편리

DECLARE
    TYPE id_type IS TABLE OF number;
    v_id id_type := id_type(100, 110, 120);
BEGIN
    FOR i IN v_id.first..v_id.last LOOP
        dbms_output.put_line(v_id(i));
    END LOOP;
END;
/

index by 문이 없음 → Oracle이 내부적으로 인덱스를 지정
DECLARE절에서 선언하면서 초기화할 수도 있고, 선언만 한 뒤 BEGIN 절에서 초기화할 수도 있음

새로운 값 입력 시 오류

DECLARE
    TYPE id_type IS TABLE OF number;
    v_id id_type := id_type(100, 110, 120, 130);
BEGIN
    FOR i IN v_id.first..v_id.last LOOP
        dbms_output.put_line(v_id(i));
    END LOOP;
    dbms_output.put_line(v_id.count);
    v_id(5) := 140; -- 오류 발생
    dbms_output.put_line(v_id.count);
END;
/

-- ORA-06533: Subscript beyond count

nested table에 새로운 값 입력 시 공간이 없어 오류 발생

extend

DECLARE
    TYPE id_type IS TABLE OF number;
    v_id id_type := id_type(100, 110, 120, 130);
BEGIN
    FOR i IN v_id.first..v_id.last LOOP
        dbms_output.put_line(v_id(i));
    END LOOP;
    dbms_output.put_line(v_id.count);
    v_id.extend;
    v_id(5) := 140;
    dbms_output.put_line(v_id.count);

    v_id.extend(2);
    v_id(6) := 150;
    v_id(7) := 160;
    dbms_output.put_line(v_id.count);
END;
/

기존 중첩 테이블 변수에 새로운 값을 입력하려면 먼저 공간을 확장한 후 새로운 값을 입력해야 함
공간을 확장해주는 메소드
extend만 쓰면 공간 1개만 확장
extend(n) 으로 크기를 지정해주면 n만큼 공간 확장

DECLARE
    TYPE id_type IS TABLE OF number;
    v_id id_type := id_type(100, 110, 120, 130, 140, 150);
BEGIN
    v_id.extend(3, 1); -- 1번 요소 값을 3번 복사

    FOR i IN v_id.first..v_id.last LOOP
        dbms_output.put_line(v_id(i));
    END LOOP;
END;
/

extend(m, n) : n번 요소를 m번 복사해서 추가

요소 값 수정

DECLARE
    TYPE id_type IS TABLE OF number;
    v_id id_type := id_type(100, 110, 120, 130, 140, 150);
BEGIN
    v_id(2) := 200; -- 요소 값 수정
    FOR i IN v_id.first..v_id.last LOOP
        dbms_output.put_line(v_id(i));
    END LOOP;
END;
/

요소 삭제

DECLARE
    TYPE id_type IS TABLE OF number;
    v_id id_type := id_type(100, 110, 120, 130, 140, 150);
BEGIN
    v_id.delete(v_id.last);
    v_id.delete(v_id.count);
    FOR i IN v_id.first..v_id.last LOOP
        dbms_output.put_line(v_id(i));
    END LOOP;
END;
/

delete(인덱스)

DECLARE
    TYPE id_type IS TABLE OF number;
    v_id id_type := id_type(100, 110, 120, 130, 140, 150);
BEGIN
    v_id.trim;
    FOR i IN v_id.first..v_id.last LOOP
        dbms_output.put_line(v_id(i));
    END LOOP;
END;
/

trim : 배열 안에 제일 끝에 있는 요소를 삭제하는 메소드

VARRAY(Variable Size Array)

고정된 상한 값이 있음
최대 크기 2GB

DECLARE
    TYPE id_type IS VARRAY(6) OF number;
    v_id id_type := id_type(100, 110, 120, 130, 140, 150);
BEGIN
    FOR i IN v_id.first..v_id.last LOOP
        dbms_output.put_line(v_id(i));
    END LOOP;
END;
/

상한 값 초과 시 오류

DECLARE
    TYPE id_type IS VARRAY(5) OF number;
    v_id id_type := id_type(100, 110, 120, 130, 140, 150);
BEGIN
    FOR i IN v_id.first..v_id.last LOOP
        dbms_output.put_line(v_id(i));
    END LOOP;
END;
/

-- ORA-06532: Subscript outside of limit

배열에 입력할 수 있는 최대치를 넘었을 경우 오류 발생

새로운 값 입력 시 오류

DECLARE
    TYPE id_type IS VARRAY(10) OF number;
    v_id id_type := id_type(100, 110, 120, 130, 140, 150);
BEGIN
    dbms_output.put_line(v_id.count);
    v_id(7) := 160;
    FOR i IN v_id.first..v_id.last LOOP
        dbms_output.put_line(v_id(i));
    END LOOP;
END;
/

-- ORA-06533: Subscript beyond count

extend

DECLARE
    TYPE id_type IS VARRAY(10) OF number;
    v_id id_type := id_type(100, 110, 120, 130, 140, 150);
BEGIN
    dbms_output.put_line(v_id.count);
    v_id.extend; -- 새로운 값을 입력하려고 할 때 공간을 확장해야 함
    v_id(7) := 160;
    FOR i IN v_id.first..v_id.last LOOP
        dbms_output.put_line(v_id(i));
    END LOOP;
END;
/

요소 삭제

DECLARE
    TYPE id_type IS VARRAY(10) OF number;
    v_id id_type := id_type(100, 110, 120, 130, 140, 150);
BEGIN
    v_id.delete(6);
    FOR i IN v_id.first..v_id.last LOOP
        dbms_output.put_line(v_id(i));
    END LOOP;
END;
/

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

delete 사용 불가 → 오류 발생

DECLARE
    TYPE id_type IS VARRAY(10) OF number;
    v_id id_type := id_type(100, 110, 120, 130, 140, 150);
BEGIN
    v_id.trim;
    FOR i IN v_id.first..v_id.last LOOP
        dbms_output.put_line(v_id(i));
    END LOOP;
END;
/

trim만 가능
중간 요소 삭제 불가
trim(n) : 맨 뒤 n개 요소 삭제

저작자표시 비영리 변경금지 (새창열림)

'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글

251111 TIL (0)	2025.11.11
251110 TIL (0)	2025.11.10
251106 TIL (0)	2025.11.06
251105 TIL (0)	2025.11.05
251104 TIL (0)	2025.11.04

Joy's Devlog

Joy's Devlog

태그

최근글

댓글

공지사항

아카이브

조합 데이터 유형(Cont.)

연관 배열(associative array, index by table)

배열 메소드

반복문으로 배열 요소 조회

다중 행 수정

문제

2차원 배열

문제

중첩 테이블(Nested Table)

새로운 값 입력 시 오류

extend

요소 값 수정

요소 삭제

VARRAY(Variable Size Array)

상한 값 초과 시 오류

새로운 값 입력 시 오류

extend

요소 삭제

'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글

관련글

티스토리툴바