231030 PL/SQL CURSOR, 명시적 CURSOR

■ CURSOR
- SQL 문 실행 메모리 영역
- PARSE, BIND, EXECUTE(ACTIVE SET), FETCH(ACTIVE SET 결과를 화면 출력 VS 프로그램 변수에 LOAD 하는 단계) 
- 암시적 커서(Implicit CURSOR) 
1. SELECT .. INTO 절 : 반드시 1개 행 값만 FETCH해야 함.
    2. 오라클이 자동으로 커서 관리를 한다.
- 0개 행 : NO_DATA_FOUND
- 2개 이상 : TOO_MANY_ROWS
3. DML(INSERT, UPDATE, DELETE, MERGE), TCL(COMMIT, ROLLBACK, SAVEPOINT)
- 명시적 커서(Explicit CURSOR)
1. 여러개의 행을 FETCH 해야 한다면 명시적 커서를 이용해야 한다. 
2. 프로그래머(사용자) 커서를 관리해야 한다.
3. 0건, 1건, 여러건 이든 상관없이 사용가능.

# 암시적커서 : 1개의 행만 출력

SELECT * FROM hr.employees WHERE employee_id = 100;

# 명시적커서(중복성+null 값 포함) : 0개의 행이상 출력

SELECT * FROM hr.employees WHERE department_id = 20;

 

CURSOR 속성
암시적 CURSOR		명시적 CURSOR
SQL%ROWCONT	처리건수 확인.  가장 최근 수행된 영향받은 행의 수	커서명%ROWCOUNT	FETCH 한 행의 수 를 리턴. 현재까지 반환된 모든 행의 수
SQL%FOUND	영향을 입은행(ROW)가 있다면 TRUE,  없으면 FLASE 을 리턴	커서명%FOUND	FETCH 한 행이  없으면 FALSE,  있으면 TRUE 를 리턴
SQL%NOTFOUND	영향을 입은 행(ROW)가  없다면 TRUE,  있다면 FALSE 을 리턴	커서명%NOTFOUND	FETCH 한 행이  없으면 TRUE, 있으면 FALSE 를 리턴
SQL%ISOPEN	암시적 커서가 열려있는지 여부 확인 항상 FALSE	커서명%ISOPEN	열린 상태인지 여부를 판별

# 명시적 CURSOR

DECLARE
    /* 1. (명시적)cursor 선언 
            단, INTO 는 쓰지 않는 것. */
    CURSOR emp_cur/* 메모리명 */ IS 
        SELECT last_name 
        FROM hr.employees
        WHERE department_id = 20; /* 변수 : BIND 단계 */
    
    /* SELECT 컬럼명만큼 변수 선언 */
    --v_name varchar2(30);
    v_name hr.employees.last_name%TYPE;
BEGIN
    /* 2. cursor open: 메모리 할당, SELECT 문에 대해서 PARSE(실행계획+문법체크+의미분석), BIND(변수), EXECUTE(결과값) 
          ACTIVE SET 결과 -> USER 전달 */
    OPEN emp_cur;
    
    /* 3. FETCH 단계 
          ACTIVE SET 결과 : 첫번째 데이터를 변수에 로드 작업. 
          ※ 여러건의 데이터가 있으면 꼭 '반복문'안에서 FETCH 작업을 수행해야 한다. 
          */
    LOOP 
        FETCH emp_cur INTO v_name;
            EXIT WHEN emp_cur%NOTFOUND; /* FETCH 값(ROW) 없으면 TRUE, 있으면 FALSE */
        DBMS_OUTPUT.PUT_LINE(v_name);
    END LOOP;
    
    /* 4. CURSOR CLOSE : CURSOR 해지
          - 메모리사용량 줄이기 위해서
          - 프로그램 CLOSE 하면 자동적으로 닫혀서 괜찮으나, 습관적으로 써주는 게 좋다. */
    CLOSE emp_cur;
    
END;
/

 

< 작성법 >

	(명시적) CURSOR
1. (명시적) CURSOR 선언 단계	DECLARE  1. CURSOR 선언 CURSOR 메모리명 IS SQL 문 (SELECT 문, INSERT문, UPDATE문, DELETE문) ;
2. CURSOR OPEN 단계	BEGIN 2. CURSOR OPEN :  - 메모리 할당, ⓐ parse(실행계획, 문법체크, 의미분석), ⓑ bind(변수), ⓒ execute(결과값)  - ACTIVE SET 결과 -> USER(프로그래머, 사용자, 개발자 등) 전달 OPEN 메모리명;
3. FETCH 단계	3. FETCH  ACTIVE SET 결과 : 첫 번째 데이터를 변수에 로드(INSERT) 작업. ※ 여러 개의 데이터가 있으면 꼭 반복문 안에세 FETCH 작업을 수행해야 한다. - 반복문 : LOOP문, WHILE LOOP문, FOR LOOP문  LOOP         FETCH 메모리명 INTO 변수명;                EXIT WHEN 메모리명%NOTFOUND; --FETCH 값(row) 없으면 true, 있으면 false          dbms_output.put_line(변수명); END LOOP;
4. CURSOR CLOSE 단계	4. CURSOR 해지 1) 메모리사용량 줄이기 위함. 2) 프로그램 CLOSE 하면 자동적으로 시스템닫히기하나, 습관적으로 작성해주자! CLOSE 메모리명;
	END; /

 

# cursor 선언 단계에서 변수

DECLARE
    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;
    
    /* 종속관계 */
    v_name hr.employees.last_name%TYPE;
    v_sal hr.employees.salary%TYPE;
    v_dept_name hr.departments.department_name%TYPE;
BEGIN
    OPEN emp_cur;
    
    LOOP 
        FETCH emp_cur INTO v_name, v_sal, v_dept_name;
            EXIT WHEN emp_cur%NOTFOUND; /* FETCH 값(ROW) 없으면 TRUE, 있으면 FALSE */
        DBMS_OUTPUT.PUT_LINE(v_name||' '||v_sal||' '||v_dept_name);
    END LOOP;
    
    CLOSE emp_cur;
END;
/

 

# 변수 → 레코드 타입으로 변경 

DECLARE
    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;
    
    /* rowtype 으로 변경 */    
    v_rec emp_cur%ROWTYPE;
    
BEGIN

    OPEN emp_cur;
    
    LOOP 
        FETCH emp_cur INTO v_rec;
            EXIT WHEN emp_cur%NOTFOUND; 
        /* 컬럼명이 아닌 필드명으로 작성. */
        DBMS_OUTPUT.PUT_LINE(v_rec.last_name||' '||v_rec.salary||' '||v_rec.department_name);
    END LOOP;
    
    CLOSE emp_cur;
END;
/

 

# 코드의 간결화, 가독성 + 적은 메모리화

DECLARE
    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;
    
BEGIN
    /* 암시적으로 선언: 레코드 변수(자동 생성) v_rec  */
    FOR v_rec IN emp_cur LOOP 
        DBMS_OUTPUT.PUT_LINE(v_rec.last_name);
        DBMS_OUTPUT.PUT_LINE(v_rec.salary);
        DBMS_OUTPUT.PUT_LINE(v_rec.department_name);
    END LOOP;

END;
/

 

# CURSOR TABLE 에 SELECT 문 생성. 

→ 이 코드에서는 명시적커서의 속성을 사용할 수 없다. 

BEGIN
    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);
        DBMS_OUTPUT.PUT_LINE(v_rec.salary);
        DBMS_OUTPUT.PUT_LINE(v_rec.department_name);
    END LOOP;
END;
/