251110 TIL

2025. 11. 10. 18:58ㆍCourses/아이티윌 오라클 DBA 과정

CURSOR

SQL문 실행 메모리 영역
SELECT문 : PARSE → BIND → EXECUTE → FETCH
DML문 : PARSE → BIND → EXECUTE

암시적(Implicit) CURSOR

SELECT … INTO …
- 반드시 1건만 FETCH 해야 함
- 0건 : no_data_found
- 2건 이상 : too_many_rows
DML

명시적(Explicit) CURSOR

SELECT문 수행 시에 여러 개의 행을 FETCH해야 한다면 꼭 명시적 커서를 이용해야 함
프로그래머가 커서를 생성, 관리해야 함

DECLARE
    v_name varchar2(30);
BEGIN
    SELECT last_name
    INTO v_name
    FROM hr.employees
    WHERE department_id = 20;

    dbms_output.put_line(v_name);
END;
/

-- ORA-01422: exact fetch returns more than requested number of rows

2건 이상 fetch되는 경우 오류 발생 → 명시적 커서 사용

명시적 커서 사용법

커서 선언 : 이름이 있는 SQL 영역(메모리) 선언
OPEN : 커서 이름으로 메모리 할당, parse, bind, execute, active set(결과 집합)
FETCH : 커서 안에 있는 결과 집합을 변수에 로드하는 단계
CLOSE : 커서를 해제(메모리 해제)

DECLARE
    /* 1. 커서 선언 */
    CURSOR emp_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;

    /* Scalar Type 변수 선언 */
    v_name varchar2(30);
    v_sal number;
    v_dept_name varchar2(30);
BEGIN
    /* 2. CURSOR OPEN */
    IF NOT emp_cur%isopen THEN
        OPEN emp_cur;
    END IF;

    /* 3. FETCH */
    LOOP
        FETCH emp_cur INTO v_name, v_sal, v_dept_name;
        EXIT WHEN emp_cur%notfound;
        dbms_output.put_line(v_name || ' ' || v_sal || ' ' || v_dept_name);
    END LOOP;

    dbms_output.put_line(emp_cur%rowcount || '행이 인출되었습니다.');

    /* 4. CURSOR CLOSE */
    IF emp_cur%isopen THEN
        CLOSE emp_cur;
        dbms_output.put_line('emp_cur가 close되었습니다.');
    END IF;
END;
/

명시적 커서 속성

커서명%FOUND : fetch한 행이 있으면 True, 없으면 False
커서명%NOTFOUND : fetch한 행이 없으면 True, 있으면 False
커서명%ROWCOUNT : fetch한 행 수 리턴
커서명%ISOPEN : 커서가 열려있으면 True, 닫혀있으면 False
- ORA-01001: invalid cursor : 커서를 OPEN 하지 않은 상태에서 FETCH를 수행하면 발생하는 오류

Record 타입 사용

DECLARE
    /* 1. 커서 선언 */
    CURSOR emp_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;

    /* Record Type 선언 */
    TYPE rec_type IS RECORD(
        id number,
        name varchar2(30),
        sal number,
        dept_name varchar2(30)
    );
    v_rec rec_type;
BEGIN
    /* 2. CURSOR OPEN */
    OPEN emp_cur;

    /* 3. FETCH */
    LOOP
        FETCH emp_cur INTO v_rec;
        EXIT WHEN emp_cur%notfound;
        dbms_output.put_line(v_rec.id || ' ' || v_rec.name || ' ' || v_rec.sal || ' ' || v_rec.dept_name);
    END LOOP;

    dbms_output.put_line(emp_cur%rowcount || '행이 인출되었습니다.');

    /* 4. CURSOR CLOSE */
    CLOSE emp_cur;
END;
/

%rowtype

DECLARE
    /* 1. 커서 선언 */
    CURSOR emp_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;

    /* FETCH에서 사용해야 할 레코드 타입 변수, 커서를 기반으로 하는 레코드 타입의 변수 */
    v_rec emp_cur%rowtype;
BEGIN
    /* 2. CURSOR OPEN */
    OPEN emp_cur;

    /* 3. FETCH */
    LOOP
        FETCH emp_cur INTO v_rec;
        EXIT WHEN emp_cur%notfound;
        dbms_output.put_line(v_rec.employee_id || ' ' || v_rec.last_name || ' ' || v_rec.salary || ' ' || v_rec.department_name);
    END LOOP;

    dbms_output.put_line(emp_cur%rowcount || '행이 인출되었습니다.');

    /* 4. CURSOR CLOSE */
    CLOSE emp_cur;
END;
/

별칭

DECLARE
    /* 1. 커서 선언 */
    CURSOR emp_cur IS
        SELECT e.employee_id id, e.last_name name, e.salary sal, d.department_name dept_name
        FROM hr.employees e, hr.departments d
        WHERE e.department_id = 20
        AND d.department_id = 20;

    /* FETCH에서 사용해야 할 레코드 타입 변수, 커서를 기반으로 하는 레코드 타입의 변수 */
    v_rec emp_cur%rowtype;
BEGIN
    /* 2. CURSOR OPEN */
    OPEN emp_cur;

    /* 3. FETCH */
    LOOP
        FETCH emp_cur INTO v_rec;
        EXIT WHEN emp_cur%notfound;
        dbms_output.put_line(v_rec.id || ' ' || v_rec.name || ' ' || v_rec.sal || ' ' || v_rec.dept_name);
    END LOOP;

    dbms_output.put_line(emp_cur%rowcount || '행이 인출되었습니다.');

    /* 4. CURSOR CLOSE */
    CLOSE emp_cur;
END;
/

FOR LOOP

명시적 커서 사용 시 레코드 변수 선언, OPEN, FETCH, CLOSE 작업을 자동으로 수행

DECLARE
    /* 1. 커서 선언 */
    CURSOR emp_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;
BEGIN
    FOR v_rec IN emp_cur LOOP
        dbms_output.put_line(v_rec.employee_id || ' ' || v_rec.last_name || ' '  || v_rec.salary || ' ' || v_rec.department_name);
    END LOOP;
END;
/

서브 쿼리를 사용하는 커서

BEGIN
    FOR v_rec IN (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) LOOP
        dbms_output.put_line(v_rec.employee_id || ' ' || v_rec.last_name || ' '  || v_rec.salary || ' ' || v_rec.department_name);
    END LOOP;
END;
/

커서 선언 생략 가능
명시적 커서 속성 사용 불가

문제

-- [문제14] 2006년도에 입사한 사원들의 근무 도시이름별로 급여의 총액, 평균을 출력하세요.
/*
<화면출력>
Seattle 도시에 근무하는 사원들의 총액급여는 ￦10,400 이고 평균급여는 ￦5,200 입니다.
South San Francisco 도시에 근무하는 사원들의 총액급여는 ￦37,800 이고 평균급여는 ￦2,907 입니다.
Southlake 도시에 근무하는 사원들의 총액급여는 ￦13,800 이고 평균급여는 ￦6,900 입니다.
Oxford 도시에 근무하는 사원들의 총액급여는 ￦59,100 이고 평균급여는 ￦8,442 입니다.
*/

DECLARE
    CURSOR cur IS 
        SELECT l.city, to_char(sum(e.salary), 'FML999,999') sum_sal, to_char(trunc(avg(e.salary)), 'FML999,999') avg_sal
        FROM hr.employees e, hr.departments d, hr.locations l
        WHERE e.department_id = d.department_id
        AND d.location_id = l.location_id
        AND hire_date between to_date('20060101', 'yyyymmdd') and to_date('20070101', 'yyyymmdd')-1/24/60/60
        GROUP BY city;
BEGIN
    FOR v_rec IN cur LOOP
        dbms_output.put_line(v_rec.city || ' 도시에 근무하는 사원들의 총액급여는 ' || v_rec.sum_sal || ' 이고 평균급여는 ' || v_rec.avg_sal || ' 입니다.');
    END LOOP;
END;
/

실행 계획 공유 불가

DECLARE
    CURSOR emp_80 IS
        SELECT employee_id, last_name, job_id
        FROM hr.employees
        WHERE department_id = 80
        AND job_id = 'SA_MAN';

    CURSOR emp_50 IS
        SELECT employee_id, last_name, job_id
        FROM hr.employees
        WHERE department_id = 50
        AND job_id = 'ST_MAN';

    v_rec emp_80%rowtype;
BEGIN
    OPEN emp_80;
    LOOP
        FETCH emp_80 INTO v_rec;
        EXIT WHEN emp_80%notfound;
        dbms_output.put_line(v_rec.employee_id || ' ' || v_rec.last_name || ' ' || v_rec.job_id);
    END LOOP;
    CLOSE emp_80;

    FOR v_rec1 IN emp_50 LOOP
        dbms_output.put_line(v_rec1.employee_id || ' ' || v_rec1.last_name || ' ' || v_rec1.job_id);
    END LOOP;
END;
/

파라미터(parameter)를 포함한 cursor

실행 계획을 공유하기 위해 사용
형식 매개변수
- 형식 매개변수 선언 시에는 데이터 타입만 사용(SIZE 사용 불가)
실제 매개변수
- 실제 매개변수는 OPEN 수행 시 형식 매개변수에 대응되게 값 입력

DECLARE
    CURSOR emp_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 emp_cur%rowtype;
BEGIN
    OPEN emp_cur(80, 'SA_MAN');
    LOOP
        FETCH emp_cur INTO v_rec;
        EXIT WHEN emp_cur%notfound;
        dbms_output.put_line(v_rec.employee_id || ' ' || v_rec.last_name || ' ' || v_rec.job_id);
    END LOOP;
    CLOSE emp_cur;

    FOR v_rec1 IN emp_cur(50, 'ST_MAN') LOOP
        dbms_output.put_line(v_rec1.employee_id || ' ' || v_rec1.last_name || ' ' || v_rec1.job_id);
    END LOOP;
END;
/

저작자표시 비영리 변경금지 (새창열림)

'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글

251112 TIL (0)	2025.11.12
251111 TIL (0)	2025.11.11
251107 TIL (0)	2025.11.07
251106 TIL (0)	2025.11.06
251105 TIL (0)	2025.11.05

Joy's Devlog

Joy's Devlog

태그

최근글

댓글

공지사항

아카이브

CURSOR

암시적(Implicit) CURSOR

명시적(Explicit) CURSOR

명시적 커서 사용법

명시적 커서 속성

Record 타입 사용

%rowtype

별칭

FOR LOOP

서브 쿼리를 사용하는 커서

문제

실행 계획 공유 불가

파라미터(parameter)를 포함한 cursor

'Courses > 아이티윌 오라클 DBA 과정' 카테고리의 다른 글

관련글

티스토리툴바