PL/SQL - DAY 6

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

[문제15]

1차원 날짜 배열 변수를 선언한 후 7월의 공강 날짜를 입력한 후 화면과 같이 출력하는 프로그램을 작성하세요.
<화면결과>
7월의 공강 일수는 2일 입니다.
공강 날짜는 2024-07-12 입니다.
공강 날짜는 2024-07-26 입니다.

<풀이>

DECLARE
    TYPE v_array_type IS TABLE OF date INDEX BY pls_integer;
    v_tab v_array_type;
BEGIN
    v_tab(1) := to_date('2024-07-12','yyyy-mm-dd');
    v_tab(2) := to_date('2024-07-26','yyyy-mm-dd');
    
    dbms_output.put_line('7월의 공강 일수는 '||v_tab.count||'일 입니다.');
    FOR i IN v_tab.first..v_tab.last LOOP
        IF v_tab.exists(i) THEN
            dbms_output.put_line('공강 날짜는 ' || v_tab(i)||' 입니다.');
        END IF;
    END LOOP;
END;
/

[문제16]

배열변수안에 있는 사원 번호 값을 기준으로 (100,110,200) 그 사원의 last_name, hire_date, department_name 정보를 배열변수에 담아놓은 후 화면과 같이 출력하는 프로그램을 작성하세요.
<화면결과>
100 사원의 이름은 King, 입사한 날짜는 2003-06-17, 근무 부서이름은 Executive 입니다.
110 사원의 이름은 Chen, 입사한 날짜는 2005-09-28, 근무 부서이름은 Finance 입니다.
200 사원의 이름은 Whalen, 입사한 날짜는 2003-09-17, 근무 부서이름은 Administration 입니다.

<풀이>

DECLARE
    TYPE v_record_type IS RECORD
    ( emp_id number,
      name varchar2(30),
      day date,
      dept_name varchar2(30));
    TYPE v_array_type IS TABLE OF v_record_type INDEX BY pls_integer;
    v_tab v_array_type;
BEGIN
    FOR i IN (SELECT rownum, employee_id FROM hr.employees  WHERE employee_id IN (100,110,200)) LOOP
        SELECT employee_id,last_name, hire_date, 
            (SELECT department_name
             FROM hr.departments
             WHERE department_id = a.department_id)
        INTO v_tab(i.rownum)
        FROM hr.employees a
        WHERE employee_id = i.employee_id;
        dbms_output.put_line(v_tab(i.rownum).emp_id || '사원의 이름은 '|| v_tab(i.rownum).name||', 입사한 날짜는 '||v_tab(i.rownum).day||', 근무 부서이름은 '||v_tab(i.rownum).dept_name||' 입니다.');
    END LOOP;
END;
/

문자열 요소번호

INDEX BY varchar2(32767) 까지 구성 가능

DECLARE
    TYPE my_array_type IS TABLE OF varchar2(1000) INDEX BY varchar2(20);
    v_array my_array_type;
    v_name varchar2(20);
BEGIN
    v_array('DBWR') := 'Database Buffer Cache의 Dirty Buffer를 DataFile에 기록하는 프로세스';
    v_array('LGWR') := 'Redo Log Buffer에 있는 redo entry를 online redo log file에 기록하는 프로세스';
    v_array('SMON') := 'Instance failure가 발생했을때 Instance recovery를 수행하는 프로세스';
    v_array('PMON') := 'Process failure 발생시 정리하는 작업을 수행하는 프로세스';
    v_array('CKPT') := 'CheckPoint 발생시 DBWR에게 알리고 체크포인트 정보를 데이터파일 헤더, 컨트롤 파일에 갱신하는 프로세스';
    dbms_output.put_line('**** Background Process ****');
    v_name := v_array.first;
    dbms_output.put_line(v_name||' : ' || v_array(v_name));
    LOOP
         v_name := v_array.next(v_name); --v_name에 다음 문자열 요소번호를 덮어씌움
         EXIT WHEN v_name IS NULL; -- 다음 요소번호가 없으면 null이 들어가고 null일때 종료한다.
        dbms_output.put_line(v_name||' : ' || v_array(v_name));
    END LOOP;
END;
/

NESTED TABLE(중첩테이블)

  • 선언시 미리 넣을 값들을 알고 있을때 사용하는것이 좋음.
  • 중첩테이블은 최대 2G까지 동적으로 증가할 수 있다.
  • 연관배열과 달리 인덱스키는 자동으로 1번 시작해서 만들어 진다.
  • 미리 선언된 초기값으로 배열공간이 확정이 되어버린다.
    • 추가하려면 배열공간을 확장해줘야 한다.
    • 확장 메소드 : extend(확장공간)
DECLARE
    TYPE tab_char_type IS TABLE OF varchar2(10); -- INDEX BY가 생략되면 자동으로 NESTED TABLE 타입,자동으로 1번부터 시작
    v_city tab_char_type := tab_char_type('대전','서울','대구','광주');
BEGIN 
    
    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(v_city.first));
   
END;
/

NESTED TABLE 배열에 값을 추가 하고 싶을경우 

DECLARE
    TYPE tab_char_type IS TABLE OF varchar2(10); -- INDEX BY가 생략되면 자동으로 NESTED TABLE 타입,자동으로 1번부터 시작
    v_city tab_char_type := tab_char_type('대전','서울','대구','광주');
BEGIN 
    v_city.extend(2); -- 2개의 요소공간을 추가
   v_city(5) := '부산'; -- 배열 공간을 확장하지 않고서는 오류 발생
   v_city(6) := '강릉';
   dbms_output.put_line(v_city.count);
   v_city.delete(2); -- 2번 요소값만 삭제하고 그 요소 공간은 남겨놓는다
   dbms_output.put_line(v_city.count); -- 실제값에 대한 수만 카운트
   v_city(2) := '제주'; --비어져 있는 2번 요소 공간에 다시 값 추가
   v_city.extend; -- 1개 요소만 추가
   v_city(7) := '인천';
    v_city.extend(5,7); -- 7번 인덱스에 있는 값을 5번 복사
    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('요소번호가 존재하지 않습니다.');
    END IF;
    END LOOP;   
END;
/

VARRAY(Variable Size Array)

  • varray에는 고정된 상환값이 있습니다.
    선언시에 상한값을 지정해야 한다.
  • varray의 쵀대 크기는 중첩테이블에서와 같이 2G 입니다.
  • 가변길이 배열
  • delete 메소드 사용 불가
    • null로 값 변경해줘야 한다.
  • trim을 이용하면 뒤에 값 삭제 가능.
    단, 공간자체를 삭제해버리기 때문에 값을 추가하려면 extend 작업필요
DECLARE
    TYPE tab_char_type IS VARRAY(5) OF varchar2(10);
    v_city tab_char_type := tab_char_type('서울','부산','제주','대전');

BEGIN
    v_city.extend(1); --고정 상한값 5를 벗어나서 확장작업을 할수는 없다.
    v_city(5) := '강릉';
    --v_city.delete(2); -- varrary에서는 요소번호를 이용해서 삭제를 할 수 없다.
    v_city(2) := null; -- delete가 불가능 하기 때문에 null로 값 변경
    v_city.trim(2); -- 젤 뒤의 값을 n개 삭제해줌, 아예 값의 공간을 삭제해버림
    v_city.extend;
    v_city(4) := '광주';
    FOR i IN v_city.first..v_city.last LOOP
        dbms_output.put_line(v_city(i));
    END LOOP;
END;
/

Explicit Cursor(명시적 커서)

cursor : sql 실행 메모리 영역

  • 여러개의 행을 fetch해야 할 경우 명시적 커서를 사용해야 한다.
  • 커서를 프로그래머가 관리 해야 한다.

1) 커서 선언 : 이름이 있는 SQL 영역(메모리)을 선언
2) 커서 open : 메모리 할당하고 그곳에서 SQL문이 실행하고 결과 집합(active set)을 생성
3) fetch : 메모리에 있는 결과 집합의 행을 변수에 로드하는 작업
4) 커서 close : 메모리 해제한다.

명시적 커서 속성

  • 명시적커서이름%NOTFOUND : fetch한게 없으면 TRUE, 있으면 FALSE
  • 명시적커서이름%FOUND : fetch한게 있으면 TRUE, 없으면 FALSE
  • 명시적커서이름%ISOPEN : 명시적 커서가 OPEN 되어 있으면 TRUE, 아니면 FALSE
  • 명시적커서이름%ROWCOUNT : fetch한 건수

<정석 방법>

DECLARE
    -- 커서 선언
    CURSOR v_cur IS
        SELECT 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;
        
        v_name varchar2(30);
        v_sal number;
        v_dept_name varchar2(30);
BEGIN
    -- 커서 open :메모리 할당, parse, bind, execute, 결과 집합 생성
    IF NOT v_cur%ISOPEN THEN
        OPEN v_cur;
    END IF;
    
    -- FETCH : 메모리에 있는 결과집합(active set)을 변수에 로드
    LOOP
        FETCH v_cur INTO v_name, v_sal, v_dept_name;
        EXIT WHEN v_cur%NOTFOUND;
        dbms_output.put_line(v_name||' ' ||v_sal|| ' '|| v_dept_name);
    END LOOP;
    -- 메모리 해제
    CLOSE v_cur;
    
END;
/

<%rowtype을 이용한 방법>

DECLARE
    CURSOR v_cur IS
        SELECT 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;
    /* 커서를 기반으로 하는 레코드 변수 선언*/    
    v_rec v_cur%rowtype;   
BEGIN
    IF NOT v_cur%ISOPEN THEN
        OPEN v_cur;
    END IF;
    
    LOOP
        FETCH v_cur INTO v_rec;
        EXIT WHEN v_cur%NOTFOUND;
        dbms_output.put_line(v_rec.last_name||' ' ||v_rec.salary|| ' '|| v_rec.department_name);
    END LOOP;
    
    CLOSE v_cur;
    
END;
/

FOR LOOP 구조를 이용해서 명시적 커서 OPEN,FETCH,CLOSE를 자동으로 수행하고, 레코드 변수도 자동으로 생성된다.

FOR 레코드변수 IN 명시적커서이름 LOOP
	수행할 로직
END LOOP;

<간단 버전>

DECLARE
    CURSOR v_cur IS
        SELECT 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 v_rec IN v_cur LOOP
        dbms_output.put_line(v_rec.last_name||' ' ||v_rec.salary|| ' '|| v_rec.department_name);
    
    END LOOP;
END;
/
  • subquery를 사용하는 커서 for loop 구조
    • 명시적 커서 이지만 커서 이름이 없기 때문에 명시적 커서 속성을 사용할 수 없다.
    • 마찬가지로 OPEN, FETCH, CLOSE를 자동으로 수행
BEGIN
	-- v_rec는 레코드타입
    -- SELECT 절은 기존에 명시적커서에서 선언한 부분
   FOR v_rec IN (SELECT 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) LOOP
        dbms_output.put_line(v_rec.last_name||' ' ||v_rec.salary|| ' '|| v_rec.department_name);
    
    END LOOP;
END;
/

[문제17]

2006년도에 입사한 사원들의 근무 도시이름별로 급여의 총액, 평균을 출력해주세요.
<화면출력>
Seattle 도시에 근무하는 사원들의 총액 급여는 ₩10,400이고 평균 급여는 ₩5,200 입니다.

<정석 풀이>

DECLARE
    CURSOR v_cur IS 
        SELECT CITY, sum(salary) as sum_sal, round(avg(salary),2) as avg_sal
        FROM (SELECT salary, department_id
                FROM hr.employees
                WHERE extract(year from hire_date) = 2006) a    
            join hr.departments b on a.department_id = b.department_id
            join hr.locations c on b.location_id = c.location_id
            GROUP BY CITY;
    
    v_city varchar2(30);
    v_sum_sal number;
    v_avg_sal number;

BEGIN
    IF NOT v_cur%ISOPEN THEN
        OPEN v_cur;
    END IF;
    
    LOOP
        FETCH v_cur INTO v_city, v_sum_sal, v_avg_sal;
        EXIT WHEN v_cur%NOTFOUND;
        dbms_output.put_line(v_city || ' 도시에 근무하는 사원들의 총액 급여는 ' ||to_char(v_sum_sal,'l999G999')||'이고 평균 급여는 '||
        to_char(v_avg_sal,'l999G999D')||'입니다.');
   END LOOP;
   
   CLOSE v_cur;

END;
/

<%rowtype을 활용한 풀이>

DECLARE
    CURSOR v_cur IS 
        SELECT CITY, sum(salary) as sum_sal, round(avg(salary),2) as avg_sal
        FROM (SELECT salary, department_id
                FROM hr.employees
                WHERE extract(year from hire_date) = 2006) a    
            join hr.departments b on a.department_id = b.department_id
            join hr.locations c on b.location_id = c.location_id
            GROUP BY CITY;
    
    v_rec v_cur%rowtype;

BEGIN
    IF NOT v_cur%ISOPEN THEN
        OPEN v_cur;
    END IF;
    
    LOOP
        FETCH v_cur INTO v_rec;
        EXIT WHEN v_cur%NOTFOUND;
        dbms_output.put_line(v_rec.city || ' 도시에 근무하는 사원들의 총액 급여는 ' ||to_char(v_rec.sum_sal,'l999G999')||'이고 평균 급여는 '||
        to_char(v_rec.avg_sal,'l999G999D')||'입니다.');
   END LOOP;
   
   CLOSE v_cur;

END;
/

<FOR LOOP 풀이>

DECLARE
    CURSOR v_cur IS 
        SELECT CITY, sum(salary) as sum_sal, round(avg(salary),2) as avg_sal
        FROM (SELECT salary, department_id
                FROM hr.employees
                WHERE extract(year from hire_date) = 2006) a    
            join hr.departments b on a.department_id = b.department_id
            join hr.locations c on b.location_id = c.location_id
            GROUP BY CITY;

BEGIN
    FOR v_rec IN v_cur LOOP 
        dbms_output.put_line(v_rec.city || ' 도시에 근무하는 사원들의 총액 급여는 ' ||to_char(v_rec.sum_sal,'l999G999')||'이고 평균 급여는 '||
        to_char(v_rec.avg_sal,'l999G999D')||'입니다.');
   END LOOP;

END;
/

[느낀점]

PL/SQL에서만 쓸수 있는 조합데이터 유형에는 크게 Scalar 유형, Record 유형, Array유형 3가지가 있는데, Scalar 유형에는 단일 타입만 사용 가능하고 Record유형은 여러 타입이 가능하지만 요소번호는 사용 불가능하다. Array유형에는 또 3가지 유형이 있는데, 연관배열, NESTED TABLE(중첩 테이블), VARRAY가 있다. 연관 배열은 1차원 배열, 2차원 배열을 만들 수 있는데 확장 측면에서 자유로운 점이 장점이라 나는 해당 유형을 자주 쓸 것 같다. 그리고 오늘 배운 NESTED TABLE과 VARRAY는 각각 고정된 크기가 있고 해당 크기를 확장할라면 따로 extend 작업을 해줘야 한다는 점에서 불편하였다. 오후에는 암시적커서(implicit cursor)에 이은 명시적커서(explicit cursor)를 배웠다. 명시적 커서는 암시적 커서와 다르게 커서를 선언해주고 OPEN, FETCH, CLOSE 작업을 통해 단일행값뿐만 아니라 다중행값들 또한 변수에 담아 출력을 가능하게 해주었다. 처음에는 문법적인 부분에서 이해가 어려웠지만, 강사님께서 단계별로 기본문법, 조금 심플한 문법, 아주 심플한 문법으로 가르쳐 주셔서 따라하다보니 마지막에는 이 명시적 커서 구조를 이해하게 되었다. 오늘의 가장 인상적인 부분은 내가 이전에 FOR LOOP문을 하면서 python의 for list같은걸 하고싶어서 구글링 했을때 나온 답이 for select 절이 가능 하다는 거였는데, 이게 사실 알고보니 명시적 커서를 for 레코드변수 in 명시적커서 구문이었다는 점이다.
그래서 그동안 레코드변수.필드를 사용해야만 해당 필드를 사용할수 있던 의문이 풀리는 순간이었다. 오늘 명시적 커서를 좀 배우면서 점점 오라클 구조속으로 빨려 들어가는 느낌이 들어, 이전에 했던 SQL은 정말 기본으로 깔고 가야겠다 라는 생각이 들었다.

profile
BUMSOO
이전 포스트

PL/SQL - DAY 5

다음 포스트

PL/SQL - DAY 7

0개의 댓글