231031 PL/SQL CURSOR

# 실행계획을 공유하면 안되는 경우, DATA의 분포도에 따라(불균등)

SELECT employee_id, last_name, job_id
FROM hr.employees
WHERE department_id = 80
AND job_id = 'SA_MAN';
        
SELECT employee_id, last_name, job_id
FROM hr.employees
WHERE department_id = 50
AND job_id = 'ST_MAN';

DECLARE 
    CURSOR param_cur_80 IS
        SELECT employee_id, last_name, job_id
        FROM hr.employees
        WHERE department_id = 80 -- 값을 변수
        AND job_id = 'SA_MAN'; -- 값을 변수
        -- 변수 처리하지 않으면 동일한 실행계획이면 성능이 떨어지게 된다. 

    CURSOR param_cur_50 IS
        SELECT employee_id, last_name, job_id
        FROM hr.employees
        WHERE department_id = 50
        AND job_id = 'ST_MAN';
    
    v_rec1 param_cur_80%ROWTYPE;
    
BEGIN
    OPEN param_cur_80;
    LOOP
        FETCH param_cur_80 INTO v_rec1;
            EXIT WHEN param_cur_80%NOTFOUND;
        dbms_output.put_line(v_rec1.last_name);
    END LOOP;
    CLOSE param_cur_80;
    
    dbms_output.put_line(' ');
    
    FOR v_rec2 IN param_cur_50 LOOP
        dbms_output.put_line(v_rec2.last_name);
    END LOOP;
END;
/

# parameter 를 포함한 cursor : (이유) 실행계획을 공유하기 위해서
# parameter 변수 선언시에 size는 표현하지 않습니다.

DECLARE 
    CURSOR param_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_rec1 param_cur%ROWTYPE;
    
BEGIN
    OPEN param_cur(80, 'SA_MAN'); -- 실행계획 생성 → bind 단계(엿보기, data 보기)
    -- OPEN 시점에 값을 지정해줘야 한다.
    LOOP
        FETCH param_cur INTO v_rec1;
            EXIT WHEN param_cur%NOTFOUND;
        dbms_output.put_line(v_rec1.last_name);
    END LOOP;
    CLOSE param_cur;
    
    dbms_output.put_line(' ');
    
    FOR v_rec2 IN param_cur(50, 'ST_MAN') LOOP -- 실행 계획은 위에서 생성된 부분에서 다시 보게 된다.
        dbms_output.put_line(v_rec2.last_name);
    END LOOP;
END;
/