PL/SQL 객체) CURSOR

안녕하세요, 코린이의 코딩 학습기 채니 입니다.

 

개인 포스팅용으로 내용에 오류 및 잘못된 정보가 있을 수 있습니다.

---

CURSOR

- 자료형

- SQL 실행 결과 Result Set(private sql 메모리 영역)에 접근할 수 있는 포인터

- 여러 행인 경우, 한 행씩 순차적으로 접근할 수 있음

 

커서 종류

① 암묵적 커서

  → 모든 SQL 실행과 동시에 암묵적 커서가 생성되어 처리 됨

  → 암묵적 커서 생명 주기 : open - fetch - close

② 명시적 커서

  → 명시적 커서 생명 주기 : 선언 - open - fetch - close

 

커서 속성

%rowcount : 최근 fetch된 행의 수 (DQL), 처리된 행의 수 (DML)

%notfound : open/fecth 전에는 null → fetch된 행이 존재하면 false, 존재하지 않으면 true로 변환

%found : open/fetch 전에는 null → fetch된 행이 존재하면 true, 존재하지 않으면 false로 변환

%isopen : open된 상태라면 true, close된 상태라면 false

 

---

암묵적 커서

declare
    erow employee%rowtype;
begin
    select
        *
    into
        erow
    from 
        employee
    where
        emp_id = '&사번';
        
    if sql%found then
        dbms_output.put_line(sql%rowcount || '행이 조회');
    end if;
end;
/

실행 결과(Result set)에 접근할 수 있는 암묵적 커서가 생성되고, open ~ fetch(가져옴) ~ close 처리 되었습니다.

 

암묵적 커서에 sql 키워드로 접근하여 sql%found : fetch된 행이 존재하면, sql%rowcount : DQL이므로 fetch(가져온) 행의 수를 리턴해주었기 때문에 '1행이 조회'가 출력되었습니다.

 

---

명시적 커서

- PL/SQL 안에서 SQL의 Result Set에 직접 접근하기 위해 선언

- 여러 행을 처리할 수 있음

- 명시적 커서의 생명 주기 : 선언 - open - fetch - close

 

cursor를 기본 loop으로 실행하기

매개변수가 없는 명시적 커서 (기본 loop 이용)

-- 매개변수가 없는 커서
declare
    -- 선언
    cursor emp_cursor
    is 
    select * from employee;
    
    -- 조회된 행을 담을 변수
    erow employee%rowtype;
begin
    -- open(쿼리 실행)
    open emp_cursor;
    
    -- fetch (가져옴)
    loop
        fetch emp_cursor into erow; -- 한 행씩 fetch해서 erow 변수에 대입
        exit when emp_cursor%notfound;   -- 탈출 조건 명시
        
        dbms_output.put_line('사번 : ' || erow.emp_id);
        dbms_output.put_line('이름 : ' || erow.emp_name);
        dbms_output.put_line('전화번호 : ' || erow.phone);
        dbms_output.new_line;
    end loop;
    
    -- close
    close emp_cursor;	-- 메모리에서 result set을 반환
end;
/

이 외 24행 출력

※ 흐름 파악

employee 테이블의 Result set에 접근할 수 있는 커서(emp_cursor) 선언

쿼리(select  * from employee) 실행을 위해 open!

여러 행일 경우 한 행씩 순차적으로 접근하기 때문에 fetch 시 erow에 한 행 한 행씩 접근하여 사번/이름/전화번호를 출력 한 후 더 이상 fetch 될 행이 존재하지 않는다면 반복문 탈출 후 close!

 

매개변수가 있는 명시적 커서 (기본 loop 이용)

-- 매개변수가 있는 커서
declare
    cursor emp_cursor_by_dept (p_dept_code employee.dept_code%type)
    is
    select * from employee where dept_code = p_dept_code;
    
    erow employee%rowtype;
begin
    open emp_cursor_by_dept('&부서코드');
    
    loop
        fetch emp_cursor_by_dept into erow;
        exit when emp_cursor_by_dept%notfound;
        
        dbms_output.put_line('사번 : ' || erow.emp_id);
        dbms_output.put_line('이름 : ' || erow.emp_name);
        dbms_output.put_line('부서코드 : ' || erow.dept_code);
        dbms_output.new_line;
    end loop;
    
    close emp_cursor_by_dept;
end;
/

※ 흐름 파악

매개변수가 있는 커서이기 때문에 open 시 매개인자를 넘겨줌

매개인자를 받은 후 쿼리(select * from employee where dept_code = p_dept_code;)를 실행 (커서 open!)

실행결과인 Result set을 가지고 fetch하여 %notfound 될 때까지 행을 가져와 출력!

더 이상 가져올 행이 없다면 반복문 탈출 후 커서를 close(메모리에서 Result set 반환)

 

 

cursor를 for-in-loop으로 실행하기

- open ~ fetch ~ close를 for in loop이 처리

- fetch된 행을 대입할 변수 선언 불필요

- 모든 행을 fetch하면 자동으로 exit 처리

 

매개변수 없는 명시적 커서 (for-in-loop 이용)

-- 매개변수가 없는 커서
declare
    -- 선언
    cursor emp_cursor
    is 
    select * from employee;
begin
    -- for 행변수 in 커서명(오픈) loop ~ end loop;
    for erow in emp_cursor loop
        dbms_output.put_line('사번 : ' || erow.emp_id);
        dbms_output.put_line('이름 : ' || erow.emp_name);
        dbms_output.put_line('전화번호 : ' || erow.phone);
        dbms_output.new_line;
    end loop;

end;
/

이 외 24행 출력

open ~ fetch ~ close를 for-in-loop이 처리 해주기 때문에 훨씬 간결해진 것을 확인할 수 있습니다.

커서에서 가져온 행을 그대로 erow에 대입하여 처리해줍니다.

 

매개변수가 있는 커서 (for-in-loop 이용)

-- 매개변수가 있는 커서
declare
    cursor emp_cursor_by_dept (p_dept_code employee.dept_code%type)
    is
    select * from employee where dept_code = p_dept_code;
begin

    for erow in emp_cursor_by_dept('&부서코드') loop
        dbms_output.put_line('사번 : ' || erow.emp_id);
        dbms_output.put_line('이름 : ' || erow.emp_name);
        dbms_output.put_line('부서코드 : ' || erow.dept_code);
        dbms_output.new_line;
    end loop;

end;
/

매개변수가 있는 커서 또한 for-in-loop을 이용하여 훨씬 간결해진 것을 확인할 수 있습니다.

 

 

응용) 직급코드를 전달 받아서 해당 직급 사원을 출력하는 프로시저

create or replace procedure proc_emp_info_by_job_code (
    p_job_code employee.job_code%type  
)  
is
    cursor emp_cursor(pc_job_code employee.job_code%type)
    is
    select * from employee where job_code = pc_job_code;
begin
    for erow in emp_cursor(p_job_code) loop
        dbms_output.put_line('사번 : ' || erow.emp_id);
        dbms_output.put_line('이름 : ' || erow.emp_name);
        dbms_output.put_line('직급코드 : ' || erow.job_code);
        dbms_output.new_line;
    end loop;
end;
/

begin
    proc_emp_info_by_job_code('&직급코드');
end;
/

'값'의 이동 흐름

① 프로시저 호출 시 매개인자를 넘겨줌 (J2)

② p_job_code에 'J2'가 대입

③ 커서 오픈 시 p_job_code를 매개인자로 넘겨줌 (J2)

④ pc_job_code에 'J2'가 대입

⑤ where절에서 job_code가 'J2'인 사원을 추려낸 Result set을 가지고 값 출력

 

값의 이동 흐름이며, 실행 순서는 create ~ is ~ begin ~ end입니다.

create과 is에서는 공간만 선언한 것이므로 실행 순서는 위와 같습니다. (헷갈리지 말기!)