16 PL/SQL RECORD, Array

ryunyryuny 2023. 10. 27. 17:26

[문제] 사원번호를 입력값으로 받아서 그 사원의 급여를 출력하는 프로그램을 작성. 또한 급여 1000당 별(*) 하나를 출력.
<화면출력>
사원번호 : 200
급여 : 4400
별점 : ****

DECLARE
    emp_id hr.employees.employee_id%type := 200;
    sal hr.employees.salary%type;
    star varchar2(10);
BEGIN
    SELECT salary
    INTO sal
    FROM hr.employees
    WHERE employee_id = emp_id;

    FOR i IN 1..trunc(sal/1000) LOOP
        star := star||'*';
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('사원번호 : '||emp_id);
    DBMS_OUTPUT.PUT_LINE('급   여 : '||sal);
    DBMS_OUTPUT.PUT_LINE('별   점 : '||star);

    FOR i IN 1..trunc(sal/1000) LOOP
        DBMS_OUTPUT.PUT('*');   -- 가로로 메모리에만 누적
    END LOOP;

    DBMS_OUTPUT.NEW_LINE;   -- 줄바꿈
END;
/
>> NEW_LINE을 사용하여 줄바꿈으로서 PUT 메모리에 누적된 데이터가 출력 됨

[문제] 사원들의 근속 연수가 20년 이상이면 10% 인상급여로 수정하는 프로그램을 생성
<화면결과>
100 사원의 입사일은 03/06/17 근속연수는 20년입니다.
100 사원의 급여가 24000 -> 26400 수정했습니다.

DECLARE
    v_id hr.employees.employee_id%type := 100;
    v_day hr.employees.hire_date%type;
    v_sal_before hr.employees.salary%type;
    v_sal_after hr.employees.salary%type;
    v_years number;

BEGIN
    SELECT hire_date, salary
    INTO v_day, v_sal_before
    FROM hr.employees
    WHERE employee_id = v_id;

    v_years := trunc(months_between(sysdate,v_day)/12);
    DBMS_OUTPUT.PUT_LINE(v_id||' 사원의 입사일은 '||to_char(v_day,'yy/mm/dd')||' 근속연수는 '||v_years||'년입니다.');

    IF v_years >= 20 THEN               -- 조건에 맞으면 UPDATE
        UPDATE hr.employees
        SET salary = salary * 1.1
        WHERE employee_id = v_id;

        IF sql%found THEN               -- DML작업 데이터가 있다면 조회
            SELECT salary
            INTO v_sal_after           -- 수정 후 데이터 값
            FROM hr.employees
            WHERE employee_id = v_id;

            DBMS_OUTPUT.PUT_LINE(v_id||' 사원의 급여가 '||v_sal_before||' -> '||v_sal_after||' 수정했습니다.');

            COMMIT;                     -- 저장
        ELSE
            ROLLBACK;
        END IF;
    ELSE
        DBMS_OUTPUT.PUT_LINE(v_id||' 사원은 급여를 수정할 수 없습니다.');
    END IF;
END;
/

■ 조합데이터 유형
- 다중값을 보유할 수 있다.
- 레코드 : 서로 다른 유형의 값을 저장
- 배열 : 동일한 데이터 유형의 값을 저장
    - index by table(연관배열)
    - nested table(중첩테이블)
    - varray

DECLARE
    v_dept_id hr.departments.department_id%type;
    v_dept_name hr.departments.department_name%type;
    v_mgr_id hr.departments.manager_id%type;
    v_loc_id hr.departments.location_id%type;

BEGIN
    SELECT *
    INTO v_dept_id, v_dept_name, v_mgr_id, v_loc_id --> 테이블 칼럼 개수만큼 계속 생성 필요
    FROM hr.departments
    WHERE department_id = 10;

    DBMS_OUTPUT.PUT_LINE(v_dept_id);
    DBMS_OUTPUT.PUT_LINE(v_dept_name);
    DBMS_OUTPUT.PUT_LINE(v_mgr_id);
    DBMS_OUTPUT.PUT_LINE(v_loc_id);

END;
/

↓

DECLARE
    TYPE dept_record_type IS RECORD
(dept_id hr.departments.department_id%type
        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 * INTO 레코드 변수...
- 행 레벨의 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 rec_type IS RECORD -- 레코드 선언 시 한번에 선언
        (v_sal number(8),
        v_minsal number(8) default 1000,
        v_hire_date hr.employees.hire_date%type,
        v_rec hr.employees%rowtype);

    v_myrec rec_type;

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;
/

CREATE TABLE hr.retired_emp
    (empno number(4),
    ename varchar2(20),
    job varchar2(10),
    mgr number(4),
    hiredate date,
    leavedate date,
    sal number(10),
    comm number(7,2),
    deptno number(2));

desc hr.retired_emp

DECLARE
    v_id number := 115;
    v_emp_rec hr.employees%rowtype;

BEGIN
    SELECT *
    INTO v_emp_rec
    FROM hr.employees
    WHERE employee_id = v_id;

    INSERT INTO hr.retired_emp(empno,ename,job,mgr,hiredate,leavedate,sal,comm,deptno)
    VALUES(v_emp_rec.employee_id, v_emp_rec.first_name, v_emp_rec.job_id, v_emp_rec.manager_id,
        v_emp_rec.hire_date, sysdate, v_emp_rec.salary, v_emp_rec.commission_pct, v_emp_rec.department_id);
END;
/

SELECT * FROM hr.retired_emp;
ROLLBACK;

↓

DECLARE
    v_id number := 115;
    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 = v_id;

    INSERT INTO hr.retired_emp VALUES v_rec;    -- 행레벨의 INSERT. 데이터타입 동일
END;
/

SELECT * FROM hr.retired_emp;
COMMIT;

DECLARE
    v_id number := 115;
    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 = v_id;

    -- 레코드변수안에 있는 필드 값 수정
    v_rec.ename := upper(v_rec.ename);
    v_rec.leavedate := to_date('2003-10-01','yyyy-mm-dd');
    v_rec.comm := 0;

    -- 행레벨의 UPDATE
    UPDATE hr.retired_emp
    SET ROW = v_rec
    WHERE empno = v_id;
END;
/

SELECT * FROM hr.retired_emp;
ROLLBACK;

■ 1차원 배열

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.count);     -- 배열에 저장된 값의 개수
    DBMS_OUTPUT.PUT_LINE(v_city.first);     -- 배열의 첫번째 인덱스 번호 (가장 작은)
    DBMS_OUTPUT.PUT_LINE(v_city.last);      -- 배열의 마지막 인덱스 번호 (가장 큰)
    DBMS_OUTPUT.PUT_LINE(v_city.next(3));   -- 배열의 인덱스 n번 뒤에 오는 인덱스 번호
    DBMS_OUTPUT.PUT_LINE(v_city.prior(3));  -- 배열의 인덱스 n번 앞에 오는 인덱스 번호
END;
/

- pls_integer : PL/SQL 전용 데이터 타입. 정수, -2^31~2^31-1(-2G~2G-1)
보편적 많이 사용. number보다 속도가 빠름

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));

    FOR i IN v_city.first..v_city.last LOOP     -- first ~ last 내 값 출력
        DBMS_OUTPUT.PUT_LINE(v_city(i));
    END LOOP;

    v_city(4) := '전주';      -- 수정
    v_city(5) := '인천';      -- 추가

    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);   -- 배열의 n번 요소를 삭제

    FOR i IN v_city.first..v_city.last LOOP -- 오류 : first : 1, last : 4인데 3번을 찾으려고 하니 없어서 오류가 남
        DBMS_OUTPUT.PUT_LINE(v_city(i));
    END LOOP;

END;
/
>> ORA-01403: no data found

↓

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(i||' 요소는 존재하지 않습니다.');
        END IF;
    END LOOP;

END;
/

# 배열 요소 중에 없는 요소를 참조하게 되면 NO_DATA_FOUND 오류 발생한다.
# 배열변수.EXISTS(n) : n번 요소가 존재하면 TRUE, 존재하지 않으면 FALSE 리턴한다.

# 배열.delete : 배열의 모든 요소 삭제
배열.delete(n) : 배열의 n번 요소를 삭제
배열.delete(m,n) : 배열의 m~n까지 요소 삭제

DECLARE
    TYPE table_id_type IS TABLE OF number INDEX BY pls_integer;
    v_tab table_id_type;
BEGIN
    v_tab(1) := 100;
    v_tab(2) := 200;

    UPDATE hr.employees
    SET salary = salary * 1.1
    WHERE employee_id = v_tab(1);   -- 실행계획 1

    UPDATE hr.employees
    SET salary = salary * 1.1
    WHERE employee_id = v_tab(2);   -- 실행계획 2
END;
/
ROLLBACK;

↓ 비슷한 실행계획을 묶어보자 >> 배열 + 반복문 사용

DECLARE
    TYPE table_id_type IS TABLE OF number INDEX BY pls_integer;
    v_tab table_id_type;
BEGIN
    v_tab(1) := 100;
    v_tab(2) := 200;

    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;

END;
/

-- 인덱스 번호가 순차적이지 않을 경우

DECLARE
    TYPE table_id_type IS TABLE OF number INDEX BY pls_integer;
    v_tab table_id_type;
BEGIN
    v_tab(1) := 100;
    v_tab(3) := 200;

    FOR i IN v_tab.first..v_tab.last LOOP
        IF v_tab.EXISTS(i) THEN         -- 존재여부체크
            UPDATE hr.employees
            SET salary = salary * 1.1
            WHERE employee_id = v_tab(i);
        ELSE
            DBMS_OUTPUT.PUT_LINE(i||' 요소는 존재하지 않습니다.');
        END IF;
    END LOOP;
END;
/

# 배열 반복 사용 시 EXISTS 습관적으로 사용하자.

SCALOR 변수

employee_id

레코드 변수 v_rec -> 테이블 칼럼 순서대로 작성

detp_id

dept_name

dept_mgr

dept_loc

배열 1차원

employee_id(1)

employee_id(2)

employee_id(3)

employee_id(4)

배열 2차원

detp_id	dept_name	dept_mgr	dept_loc
.	.	.	.
.	.	.	.
.	.	.	.

'Oracle SQL' 카테고리의 다른 글

18 PL/SQL EXCEPTION (1)	2023.10.31
17 PL/SQL Nested Table, VARRAY, Explicit Cursor (0)	2023.10.30
15 PL/SQL WHILE LOOP, FOR, Implicit Cursor (1)	2023.10.26
14 PL/SQL Variable, CASE, LOOP (0)	2023.10.25
13-1 SAVEPOINT (0)	2023.10.24

'Oracle SQL'의 다른글

현재글16 PL/SQL RECORD, Array

Record.

16 PL/SQL RECORD, Array

'Oracle SQL' 카테고리의 다른 글

티스토리툴바