PL/SQL - DAY 7

BUMSOO·2024년 7월 10일
PL/SQL오라클 DBA

[문제19]

부서정보 사원정보를 아래 화면과 같이 출력해주세요.
<화면결과>
**** 10부서 사원 정보 ****
부서번호 : 10, 부서이름 : Administration, 부서장 : 200, 부서위치 : 1700
* 200 Whalen Jennifer 4400 03/09/17

**** 20부서 사원 정보 ****
부서번호 : 20, 부서이름 : Marketing, 부서장 : 201, 부서위치 : 1800
*201 Hartstein Michael 1300004/02/17
202 Fay Pat 600005/08/17

<풀이>

BEGIN
  --FOR CURSOR절은 자동 OPEN FETCH CLOSE 수행   
   FOR v_rec_2 IN (SELECT *
                    FROM hr.departments a
                    WHERE EXISTS (SELECT 1
                                    FROM hr.employees
                                    WHERE department_id = a.department_id)) LOOP
            dbms_output.put_line('**** ' || v_rec_2.department_id || '부서 사원 정보 ****');
            dbms_output.put_line('부서번호 : '||v_rec_2.department_id||', 부서이름 : '||v_rec_2.department_name||', 부서장 : '||
            v_rec_2.manager_id || ', 부서위치 : '||v_rec_2.location_id);
            
        FOR v_rec IN (SELECT a.employee_id,a.last_name,
                            a.first_name,a.salary,
                            a.hire_date,a.department_id,
                            b.department_name,b.manager_id, b.location_id
                       FROM hr.employees a
                            JOIN hr.departments b ON a.department_id = b.department_id
                       WHERE a.department_id = v_rec_2.department_id)  LOOP
            
                IF v_rec.employee_id = v_rec_2.manager_id THEN
                    dbms_output.put_line('*' ||v_rec.employee_id || ' ' || v_rec.last_name || ' ' || v_rec.first_name || ' ' || v_rec.salary || v_rec.hire_date);
                ELSE
                    dbms_output.put_line(v_rec.employee_id || ' ' || v_rec.last_name || ' ' || v_rec.first_name || ' ' || v_rec.salary || v_rec.hire_date);
                END IF;    
            
        END LOOP;
       dbms_output.put_line('');        
    END LOOP; 
END;
/

PARAMETER를 포함한 CURSOR

  • 사용 이유는 실행계획을 공유하기 위해서
    • 값의 분포도가 고루게 되어 있을 경우 실행계획을 공유하는게 좋다.
  • 파라미터를 선언할때 데이터사이즈를 기입하면 안된다.
DECLARE
    /* 파라미터를 통한 커서*/
    CURSOR parm_cur(p_id number, p_job varchar2) IS --타입 사이즈는 쓰지 않는다.
        SELECT employee_id, last_name, job_id
        FROM hr.employees
        WHERE department_id = p_id
        AND job_id = p_job;

    v_rec_1 parm_cur%rowtype;
    
BEGIN
    OPEN parm_cur(80,'SA_MAN'); --오픈 시점에 값을 넣음
    
    LOOP
        FETCH parm_cur INTO v_rec_1;
        EXIT WHEN parm_cur%NOTFOUND;
        dbms_output.put_line(v_rec_1.last_name);
    END LOOP;
    
    CLOSE parm_cur;
    
    FOR v_rec_2 IN parm_cur(50,'ST_MAN') LOOP
        dbms_output.put_line(v_rec_2.last_name);	
    END LOOP;
    
END;
/

ROWID

  • 행의 고유 주소
  • ROWID를 통한 SCAN은 BY USER ROWID SCAN을 이용
    • 데이터를 조회할 수 있는 방법중 가장 빠름
    • BY INDEX ROWID SCAN : 인덱스 테이블로 들어가 해당 조건에 맞는 ROWID를 찾고 찾은 ROWID로 조회를 해야 하기 때문에 여러번의 I/O가 발생
  • 가상컬럼
DECLARE
    CURSOR sal_cur IS 
        SELECT e.rowid,e.employee_id, e.last_name, e.salary, d.department_name
        FROM hr.employees e, hr.departments d
        WHERE e.department_id = 20
        AND d.department_id = 20;

BEGIN
    FOR emp_rec IN sal_cur LOOP
        dbms_output.put_line(emp_rec.last_name);
        dbms_output.put_line(emp_rec.salary);
        dbms_output.put_line(emp_rec.department_name);
        
        UPDATE hr.employees
        SET salary = salary*1.1
        WHERE rowid = emp_rec.rowid; --by user rowid scan
        -- transaction이 발생되어서 commit 하기전까지는 다른 세션에서 해당 행에 대한 transaction 작업에 대해 lock이 걸린다.
    END LOOP;
END;
/

FOR UPDATE

  • 행 단위로 LOCK을 거는 명령어
  • FOR UPDATE OF 고유컬럼 WAIT 초
    • 해당 고유 컬럼이 있는 테이블에만 LOCK을 걸음
    • 이미 다른 SESSION에서 LOCK을 걸어놓으면, 설정 초 이후 오류메시지 발생 후 LOCK에서 빠져나옴
    • NOWAIT : 기다리지 않고 바로 오류 메시지 발생 후 빠져나옴
  • WHERE CURRENT OF 커서이름 사용시 반드시 같이 작성해야함
DECLARE
    CURSOR sal_cur IS 
        SELECT e.employee_id, e.last_name, e.salary, d.department_name
        FROM hr.employees e, hr.departments d
        WHERE e.department_id = 20
        AND d.department_id = 20 -- 조인을 한 상태에서 LOCK을 걸면 조인 테이블들의 조건 행은 모두 LOCK이 걸린다.
        FOR UPDATE; 
        /*다른 세션이 해당 행에 접근 못하게 lock을 건다. lock은 행 단위로 걸린다. 
        1)FOR UPDATE OF e.employee_id WAIT 3; e.employee_id 가 있는 테이블에만 lock을 건다.(해당 테이블의 고유한 컬럼 이름을 OF 뒤에 작성)
        2)wait 3은 해당 행에 LOCK을 걸려고 하는데 이미  LOCK이 걸려있으면 3초만 기다렸다가 오류메시지를 발생시키고 빠져나온다.
        3)NOWAIT는 해당 행에 LOCK을 걸려고 하는데 이미 LOCK이 걸려있으면 기다리지않고 바로 오류메시지를 발생시키고 빠져나온다.*/
BEGIN
    FOR emp_rec IN sal_cur LOOP -- 해당시점에 LOCK이 걸림
        dbms_output.put_line(emp_rec.last_name);
        dbms_output.put_line(emp_rec.salary);
        dbms_output.put_line(emp_rec.department_name);
        
        UPDATE hr.employees
        SET salary = salary*1.1
        WHERE CURRENT OF sal_cur; 
        /*by user rowid scan , WHERE CURRENT OF는 ROWID를 가지고 조건을 찾는데 FOR UPDATE 구문이랑 항상 같이 사용되어야 한다. FOR UPDATE에서 락을 걸때 ROWID를 가지고 락을 걸기 때문이다.*/
    END LOOP;
    
    ROLLBACK;
END;
/

RETURNING

DML문(insert,update,delete)은 작동시 parse, bind, execute까지만 하고 fetch는 하지 않는다.
하지만 DML문을 수행 후 수행 결과를 보려면 SELECT 문을 통한 중복 I/O가 발생하기 때문에 개발된 명령어

  • PL/SQL에서만 구현 가능(변수 사용 때문에)
  • 반드시 하나의 행만 fetch 해야한다.
DECLARE
    v_sal number;
    v_name varchar2(30);
BEGIN

    UPDATE hr.employees
    SET salary = salary *1.1
    WHERE employee_id = 200
    RETURNING last_name,salary INTO v_name,v_sal; 
    
    dbms_output.put_line(sql%rowcount);
    IF sql%found THEN
        dbms_output.put_line(v_sal);
        dbms_output.put_line(v_name);
    END IF;
    ROLLBACK;
END;
/

BULK COLLECT INTO

  • 오라클 서버는 두가지 엔진을 사용하여 PL/SQL 블록과 서브 프로그램을 실행한다.
  1. PL/SQL 엔진 - 프로시저문을 실행하지만 SQL문은 SQL 엔진에 전달한다.
  2. SQL 엔진 - SQL문을 구문 분석 및 실행하고 경우에 따라 PL/SQL 엔진에 데이터를 반환 한다.

  • PL/SQL 엔진과 SQL 엔진 사이에 문맥전환이 발생할 때가 성능상에 문제가 발생할때 이다

    • BULK COLLECT INTO를 사용하여 SQL엔진과 PL/SQL엔진 사이에 문맥전환을 줄일 수 있다.
      즉, 명시적인 커서를 이용해서 대량의 데이터를 FETCH 시점에 PL/SQL엔진에게 전달 하는 부분에서 문제가 발생한다. 이를 해결하는 방법은 대량으로 바인드 할 수 있도록 해서 전달 하면 성능상에 이점이 있다.

  • BULK된 SQL데이터를 PL/SQL엔진이 받을라면 2차원 배열의 변수로 받아야 하는데, NESTED TABLE(중첩테이블) or INDEX BY TABLE(연관배열)로 받을 수 있다.

<중첩테이블>

 DECLARE
   TYPE tab_type IS TABLE OF hr.employees%rowtype;
   v_tab tab_type;

BEGIN
   SELECT *
   BULK COLLECT INTO v_tab
   FROM hr.employees
   WHERE department_id = 20;
   
   FOR i IN v_tab.first..v_tab.last LOOP
       dbms_output.put_line(v_tab(i).employee_id);
       dbms_output.put_line(v_tab(i).salary);
   END LOOP;
END;
/

<연관배열>

DECLARE
   TYPE tab_type IS TABLE OF hr.employees%rowtype INDEX BY pls_integer;
   v_tab tab_type;

BEGIN
   SELECT *
   BULK COLLECT INTO v_tab
   FROM hr.employees
   WHERE department_id = 20;
   
   FOR i IN v_tab.first..v_tab.last LOOP
       dbms_output.put_line(v_tab(i).employee_id);
       dbms_output.put_line(v_tab(i).salary);
   END LOOP;
END;
/

오늘 배운 BULK COLLECT INTO를 복습하기 위해 문제 19번을 명시적 커서가 아닌 BULK COLLECT INTO로 풀어보았다. 여기서 포인트는 서브 FOR문이 끝난 후 서브 2차원 배열을 DELETE를 통해 비워준뒤 다음 메인절 조건에 맞는 값들로 채워 넣는 점이었다.

DECLARE
    TYPE dept_id IS TABLE OF hr.departments%rowtype;
    v_id dept_id;
    
    TYPE v_rec_type IS RECORD
        ( employee_id number,
          last_name varchar2(30),
          first_name varchar2(30),
          salary number,
          hire_date date,
          department_id number,
          department_name varchar2(30),
          manager_id number,
          location_id number);
    v_rec v_rec_type;
    TYPE v_array IS TABLE OF v_rec%type;
    v_tab v_array;
          
BEGIN
  SELECT *
  BULK COLLECT INTO v_id
  FROM hr.departments a
  WHERE EXISTS ( SELECT 1
                    FROM hr.employees
                    WHERE department_id = a.department_id);
  FOR i IN v_id.first..v_id.last LOOP
    dbms_output.put_line('**** ' || v_id(i).department_id|| '부서 사원 정보 ****');
    dbms_output.put_line('부서번호 : '||v_id(i).department_id||', 부서이름 : '||v_id(i).department_name||', 부서장 : '||
            v_id(i).manager_id || ', 부서위치 : '||v_id(i).location_id);
    SELECT a.employee_id,a.last_name,
           a.first_name,a.salary,
           a.hire_date,a.department_id,
           b.department_name,b.manager_id, b.location_id
    BULK COLLECT INTO v_tab
    FROM hr.employees a
        JOIN hr.departments b ON a.department_id = b.department_id
    WHERE a.department_id = v_id(i).department_id;
    
    FOR j IN v_tab.first..v_tab.last LOOP
        dbms_output.put_line(CASE WHEN v_tab(j).employee_id = v_id(i).manager_id THEN '*' END||v_tab(j).employee_id || ' ' || v_tab(j).last_name || ' ' || v_tab(j).first_name || ' ' || v_tab(j).salary || v_tab(j).hire_date);
    END LOOP;
    v_tab.delete;
  END LOOP;

END;
/

[느낀점]

오늘은 정말 폭풍이 몰아친 하루 같았다. 오전에 쉴때 까지만 해도 오후의 폭풍같은 수업이 올지는 상상도 못했다. 오늘 오후에 배운 내용들은 다 성능의 관점에서 어떻게 하면 I/O를 줄일 수 있을지에 대한 방법들 이었다. 크게 배운것들을 생각해 보면 parameter를 이용한 명시적 커서, 데이터를 조회 하는 방법중 가장 빠른 BY USER ROWID를 이해 하기 위한 ROWID는 무엇이고, ROWID 가상컬럼을 사용하지 않고 BY USER ROWID를 사용하는 FOR UPDATE ~ WHERE CURRENT OF 이랑
DML문은 fetch절을 수행하지 않아 DML작업된 결과를 확인하기 위해 중복 I/O 해야하는것을 보완한 RETURNING문 , 그리고 마지막으로 많은 SQL엔진과 PL/SQL엔진의 문맥교환을 최소화 하기 위한 BULK COLLECT INTO절까지 배워보았다. 이걸 솔직히 하루만에 배워서 마스터 하기란 매우 어려울 것 같다. 그래도 강사님께서 처음부터 업그레이드 방법을 알려주시는게 아니라 오리지널부터 알려주셔서 기초를 탄탄히 하니깐 업그레이드 된 버전을 배워도 받아들이기 더 수월한 것 같다.

profile
BUMSOO
이전 포스트

PL/SQL - DAY 6

다음 포스트

PL/SQL - DAY 8

0개의 댓글