PL/SQL - DAY 5

BUMSOO·2024년 7월 8일

조합데이터 유형

스칼라 유형(scalar : 단일값만 보유하는 변수)과는 달리 다중값을 보유할 수 있다.

레코드(record) : 서로 다른 데이터 유형의 값을 저장

배열(array) : 동일한 데이터 유형의 값을 저장(1차원 배열)

index by table(연관배열)

nested table(중첩테이블)

varray
: 레코드 타입을 응용해서 2차원 배열을 생성할 수 있다.

레코드(record)

TYPE 레코드타입이름 IS RECORD (필드명1 데이터타입, 필드명2 데이터타입, 필드명3 데이터타입);
변수명 레코드타입; 선언

DECLARE
    --레코드 타입 선언
   TYPE dept_record_type IS  RECORD --레코드 타입 이름 선언
    (dept_id number,  -- 하나하나는 다 필드 이다.
     dept_name varchar2(30),
     dept_mgr number,
     dept_loc number);
    v_rec dept_record_type; --변수에 레코드타입 설정
BEGIN
    SELECT * 
    INTO v_rec
    FROM hr.departments
    WHERE department_id = 10;
    dbms_output.put_line('부서번호' || v_rec.dept_id); -- 레코드변수.필드이름
    dbms_output.put_line('부서이름' || v_rec.dept_name);
    dbms_output.put_line('매니저 번호' || v_rec.dept_mgr); 
    dbms_output.put_line('지역번호' || v_rec.dept_loc);
END;
/

%rowtype(레코드)

테이블 또는 뷰의 컬럼에서 컬럼 및 데이터 유형을 가지고 온다.

장점 : 테이블 컬럼의 구조를 모르더라도 알 필요가 없으며 런타임에 정보를 가지고 온다.

%rowtype 속성 사용시 유용할 때?

SELECT * 문

행레벨의 INSERT문,UPDATE문

DECLARE
    --레코드 타입 선언
    v_rec hr.departments%rowtype;
BEGIN
    SELECT * 
    INTO v_rec
    FROM hr.departments
    WHERE department_id = 10;
    dbms_output.put_line('부서번호' || v_rec.department_id); -- 레코드변수.원본컬럼이름
    dbms_output.put_line('부서이름' || v_rec.department_name);
    dbms_output.put_line('매니저 번호' || v_rec.manager_id); 
    dbms_output.put_line('지역번호' || v_rec.location_id);
END;
/

<응용버전>

DECLARE
    TYPE emp_rec IS RECORD
    (v_sal number,
    v_minsal number default 1000,
    v_hire_date hr.employees.hire_date%type,
    v_rec hr.employees%rowtype); --레코드필드안에 레코드필드가 들어갈수 있다.
    v_myrec emp_rec;
BEGIN
    v_myrec.v_sal := v_myrec.v_minsal + 500;
    v_myrec.v_hire_date := sysdate;
    
    SELECT *
    INTO v_myrec.v_rec
    FROM hr.employees
    WHERE employee_id = 100;

    dbms_output.put_line('이름 : ' || v_myrec.v_rec.first_name);
    dbms_output.put_line('근무연수 : ' || trunc(months_between(v_myrec.v_hire_date, v_myrec.v_rec.hire_date)/12) || '년');
END;
/

행레벨 INSERT

INSERT하려는 테이블의 컬럼구조와 record변수의 컬럼구조가 동일할 경우 INSERT INTO 테이블 VALUES 레코드변수; 가능하다.

DECLARE
    v_rec hr.retired_emp%rowtype;

BEGIN
    SELECT employee_id, first_name, job_id, manager_id, hire_date,sysdate,salary,commission_pct, department_id
    INTO v_rec
    FROM hr.employees
    WHERE employee_id = 115;

    INSERT INTO hr.retired_emp  VALUES v_rec; --테이블의 컬럼구조와 레코드타입의 컬럼과 똑같으면 해당 방법 이용 
    
    dbms_output.put_line(sql%rowcount || '개의 행이 INSERT 되었습니다.');
    
END;
/

행레벨 UPDATE

전체 컬럼을 UPDATE 할때 테이블의 컬럼구조와 record변수의 컬럼구조가 동일할 경우 UPDATE 테이블 SET ROW = 레코드변수; 가능하다.

DECLARE
    v_rec hr.retired_emp%rowtype;

BEGIN
    SELECT *
    INTO v_rec
    FROM hr.retired_emp
    WHERE empno = 115;

    dbms_output.put_line(v_rec.ename);
    v_rec.ename := upper(v_rec.ename);
    dbms_output.put_line(v_rec.ename);
    
    dbms_output.put_line(v_rec.leavedate);
    v_rec.leavedate := to_date('2024-07-05','yyyy-mm-dd');
    dbms_output.put_line(v_rec.leavedate);
    
    dbms_output.put_line(v_rec.comm);
    v_rec.comm := 0;
    dbms_output.put_line(v_rec.comm);
    
    UPDATE hr.retired_emp
    SET ROW = v_rec --전체컬럼은 ROW로 표시
    WHERE empno=115;
        
END;
/

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

연관배열은 2개의 열을 포함하는 배열 구조이다.

요소번호 : 정수(pls_integer), 문자열 데이터 유형의 primary key(방번호)

값 : 스칼라 또는 레코드 데이터 유형의 열

<1차원 배열>

요소번호	값

TYPE 타입이름 IS TABLE OF 값(데이터타입) INDEX BY 요소번호(데이터타입);

DECLARE
    TYPE tab_char_type IS TABLE OF varchar2(10) INDEX BY pls_integer; --pls_integer의 범위는 -2G~2G 까지(총4G)이다.숫자형식
    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;
/

FOR LOOP를 이용한 개선코드

DECLARE
    TYPE tab_char_type IS TABLE OF varchar2(10) INDEX BY pls_integer; --pls_integer의 범위는 -2G~2G 까지(총4G)이다.숫자형식
    v_city tab_char_type;
BEGIN
    v_city(1) := '서울';
    v_city(2) := '대전';
    v_city(3) := '부산';
    v_city(4) := '광주';
    
    FOR i in 1..4 LOOP
    dbms_output.put_line(v_city(i));
    END LOOP;
END;
/

배열변수 메소드

배열변수.count : 배열의 저장된 요소의 수 리턴
배열변수.first : 배열의 가장 작은 인덱스 번호(요소번호)
배열변수.last : 배열의 가장 큰 인덱스 번호(요소번호)
배열변수.next(n) : 배열의 인덱스 n번 뒤에 오는 인덱스 번호 리턴
배열변수.prior(n) : 배열의 인덱스 n번 앞에 오는 인덱스 번호 리턴
배열변수.delete(n) : 배열의 n번 요소 삭제
배열변수.delete(n,m) : 배열의 n번부터 m번 범위의 요소 삭제
배열변수.delete : 배열의 모든 요소 삭제
배열변수.exists(n) : n번 요소가 존재하면 TRUE 아니면 FALSE를 반환

DECLARE
    TYPE v_tab_type IS TABLE OF varchar2(10) INDEX BY pls_integer;
    v_tab v_tab_type;
BEGIN
    v_tab(1) := '서울';
    v_tab(3) := '대전';
    v_tab(5) := '부산';
    v_tab(7) := '대구';
    
    dbms_output.put_line(v_tab.count);
    dbms_output.put_line(v_tab.first);
    dbms_output.put_line(v_tab.last);
    dbms_output.put_line(v_tab.next(1));
    dbms_output.put_line(v_tab.prior(7));
    v_tab.delete(3);
    IF v_tab.exists(7) THEN
        dbms_output.put_line('7번 요소번호가 존재합니다');
    END IF;
END;
/

[문제14]

배열변수에 있는 100,101,102,103,104,200 사원들의 근무한 개월수를 출력하고 근무 개월수가 250개월 이상 되었으면 급여를 10% 인상한 급여로 수정하는 프로그램 작성해주세요.
<화면출력 결과>
100 사원은 근무개월수가 170개월입니다. 급여는 10% 인상되었습니다. 인상된 급여는 999,999원 입니다.
101 사원은 근무개월수가 150개월 입니다. 급여는 인상할 수 없습니다.

<풀이>

DECLARE
    TYPE table_type IS TABLE OF number INDEX BY pls_integer;
    v_tab table_type;
    
    v_sal hr.employees.salary%type;
    v_month number;
    v_up_sal number;
    
BEGIN
     FOR i IN 1..5 LOOP
        v_tab(i) := 100+i-1;
    END LOOP;
    v_tab(6) := 200;
    
    FOR i IN v_tab.first..v_tab.last LOOP
        IF v_tab.exists(i) THEN
            SELECT trunc(months_between(sysdate,hire_date)), salary
            INTO v_month, v_sal
            FROM hr.employees
            WHERE employee_id = v_tab(i);
            IF v_month >= 250 THEN
                v_up_sal := v_sal *1.1;
                UPDATE hr.employees
                SET salary = v_up_sal
                WHERE employee_id = v_tab(i);
                dbms_output.put_line(v_tab(i)||' 사원은 근무개월수가 '||v_month||'개월 입니다.');
                dbms_output.put_line('급여가 '||v_sal||'원에서 10% 인상되어 '||v_up_sal||'원입니다');
            ELSE
                dbms_output.put_line(v_tab(i)||' 사원은 근무개월수가 '||v_month||'개월 입니다.');
                dbms_output.put_line('급여는 인상할 수 없습니다.');
            END IF;
        END IF;
    END LOOP;
    ROLLBACK;
END;
/

<2차원 배열>

DECLARE
	/* 레코드를 먼저 선언한다*/
    TYPE dept_rec_type IS RECORD
    ( id number,
      name varchar2(30),
      mgr number,
      loc number);
    v_rec dept_rec_type;
    /* 선언된 레코드 타입을 타입으로 가지는 변수를 %type해서 데이터타입을 가져온다*/
    TYPE dept_tab_type IS TABLE OF v_rec%type INDEX BY pls_integer; -- 레코드 타입을 배열값 타입으로 지정해준다
    v_tab dept_tab_type;

BEGIN
    FOR i IN 1..5 LOOP
        SELECT *
        INTO v_tab(i) 
        FROM hr.departments
        WHERE department_id = i*10;
        dbms_output.put_line(v_tab(i).id); --2차원배열.필드이름
        dbms_output.put_line(v_tab(i).name);
        dbms_output.put_line(v_tab(i).mgr);
        dbms_output.put_line(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;

BEGIN
    FOR i IN 1..5 LOOP
        SELECT *
        INTO v_tab(i) 
        FROM hr.departments
        WHERE department_id = i*10;
        dbms_output.put_line(v_tab(i).id);
        dbms_output.put_line(v_tab(i).name);
        dbms_output.put_line(v_tab(i).mgr);
        dbms_output.put_line(v_tab(i).loc);
    END LOOP;
    
END;
/


또는 

DECLARE
    /* 배열의 타입을 %rowtype을 활용하여 타입을 설정해줄수 있다.*/
    TYPE dept_tab_type IS TABLE OF hr.departments%rowtype INDEX BY pls_integer; 
    v_tab dept_tab_type;

BEGIN
    FOR i IN 1..5 LOOP
        SELECT *
        INTO v_tab(i) 
        FROM hr.departments
        WHERE department_id = i*10;
        dbms_output.put_line(v_tab(i).department_id); --hr.departments의 컬럼이름으로 필드값을 변경해주어야 한다.
        dbms_output.put_line(v_tab(i).department_name);
        dbms_output.put_line(v_tab(i).manager_id);
        dbms_output.put_line(v_tab(i).location_id);
    END LOOP;
    
END;
/

[느낀점]

오늘은 새로운 데이터 유형을 배우는 시간이었다. 그동안은 PL/SQL을 하면서 변수를 선언 할때 단일값만 보유하는 스칼라타입으로 선언하였다. 하지만 데이터 타입에는 스칼라만 있는게 아니라 레코드, 배열 이 있다. 레코드는 한개의 데이터 타입안에 여러 데이터 유형의 값을 저장할수 있는 타입인데, 배울때 마치 C 프로그램의 배열을 배우는 느낌이었다. 레코드를 만드는건 SQL의 테이블을 만드는 작업과 비슷했다. 근대 역시 사람은 계속 간단해 지고 싶은 욕망이 있어 컬럼 전체에 대한 fetch가 필요한 경우 %rowtype 이라는 기능을 이용해 간단하게 컬럼 전체를 레코드 타입으로 만들 수 있었다. 행레벨의 INSERT, UPDATE를 할때도 테이블의 컬럼구조와 record 변수의 컬럼구조가 동일 할 경우 간단하게 수행 가능했다. 그 뒤 오늘 배웠던 것중 가장 친근 했던건 단연 연관배열이었다. 1차원 배열은 마치 pandas의 Series와 같은 느낌이었고, 2차원 배열은 DataFrame을 보는 것 같아 내적 친밀감을 느꼈다. 배열변수에는 여러 메소드 들이 있었는데 역시 여기서도 FOR문을 활용하면 값에 접근하거나 변화를 주기 좋아보였다.~~모든 프로그램언어에서 역시 반복문은 핵심 기능인거 같다.~~ 1차원 배열은 정해진 틀 안에서 선언이 가능 했지만 2차원 배열은 이전에 배웠던 레코드를 활용하여 응용하는 느낌으로 만들어야 해서 손에 익을라면 여러번 만드는 작업을 해봐야 할 것 같다.

BUMSOO

이전 포스트

PL/SQL - DAY 4

다음 포스트